Subscript out-of-range error with Application.Quit command.

[JazzyLee]

I have the following code in a macro that works perfect on my machine and two other PC's. However, one client is having problems with the closing process. As the code stands, when we run the macro the Excel application closes down completely if no other workbooks are open or closes the related workbook and the macro, leaving any other workbook open. On this problem machine, the client gets a "Subscript out-of-range" 1004 error and nothing shuts down.
CODE:
Sub CloseMacro()
On Error Resume Next
intLoopCntr = 0
For Each wb In Application.Workbooks
If wb.Name = strInfileName Then
wb.Close (False)
intLoopCntr = intLoopCntr - 1
End If
intLoopCntr = intLoopCntr + 1
Next wb
If intLoopCntr = 1 then
Application.Quit
else
Workbooks(strMacroName).Close (False)
endif
End Sub

I took the looping and Application.Quit statement out and changed it to:
Sub CloseMacro()
On Error Resume Next
Workbooks(strInfileName).Close (False)
Workbooks(strMacroName).Close (False)
End Sub

On my machine, the macro closes down and leaves the Excel Application open but on her machine, I no longer get an error message but the macro does not close down.

We are both on Microsoft Office 2003 on a Windows 2000 platform so I don't understand why these codes work the way they should on my machine and doesn't on hers. Any ideas?

 

[xlbo]

Could this be to do with sessions of excel ??

The use may have the option set for workbooks to open in different windows. If this is the case then you cannot use the simple reference to workbooks but must grab the application instance itself (or more easily, set the option to open workbooks in different windows off)

Rgds, Geoff

"Errors have been made....Others will be blamed"

Please read FAQ222-2244 before you ask a question

 

[JazzyLee]

I thought it might have to do with the individual settings in her Excel but couldn't find anything relevant to this problem. This may work if I only knew where this option is so I can turn it off. I looked under Tools/Options but didn't find where to change the setting. Can you please point me in the right direction.

 

[TonyJollans]

Hi JazzyLee,

I'm not sure I agree with Geoff but haven't tested it so can't be sure.

I see you have an On Error statement which means that the error is not occurring in the routine you posted. I suspect that your client has a personal.xls (which will be included in the Workbooks collection and will therefore make your logic false - but that's a separate issue) and has some auto code running in some event.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[PHV]

Just a though: why not simply test the Application.Workbooks.Count to determine if you have to quit ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[earthandfire]

On Error Resume Next will not tell you if an error occured. If you want confirmation (at least while testing) of whether or not an error occured you could try

On Error Goto errortrap
your code
Exit Sub
errortrap:
mgbox "Error" 'and details of error etc
Resume Next

Hope this helps.

 

[combo]

The only reason that can cause 1004 error in the above code can be wrong workbook name in 'strMacroName', what is the string behind it? Can user save the workbook with macros under new name/extension? If so, you can refer to it using 'ThisWorkbook':
ThisWorkbook.Close (False)

combo

 

[JazzyLee]

I have not stepped thru this code in a debug mode on my user's machine but I did on mine (where it works, of course) and the names on both variables are showing up OK. I think I will try to put a break point at the close routine on her machine and then step thru to check the name. I will post back after I do this. It may shed some light to what is really causing this problem on only one machine.