On error vba ???

[Alpacino]

I have a macro that only works if the file that it copies from is open
Eg. Code is windows("Alan1.xls").Activate
Undone want to change this line of code.

I want to say On error then msgbox " you must make sure Alan.xls is open to run macro"
Anyone help please.
Thanks

 

[JackDanIce]

Try:

On Error Goto MyMsg
windows("Alan1.xls").Activate

MyMsg:
MsgBox "Alan1.xls must be open for macro to run, please restart"
Exit Sub

 

[Norie]

How about this instead?

If Not IsWBOpen("Alan1.xls") Then
    Msgbox "The workbook Alan1.xls is not open. Stopping execution of code."
    Exit Sub
End If
...
 
Function IsWBOpen(strWBName As String) As Boolean
Dim wb As Workbook
 
        For Each wb In Application.Workbooks
 
            IsWbOpen = wb.Name = strWBName
       Next wb
 
End Function

 

[Alpacino]

So will this show msgbox then exit sub if file is not open??

 

[JackDanIce]

Yes, that's what

Exit Sub

causes

 

[Alpacino]

Do I put the rest of the code for the macro after the line Exit Sub for it to run normally if the file is open ??

 

[ChrisOswald]

Norie, I think you forgot a line in the function.

[FONT=Calibri]Function IsWBOpen(strWBName As String) As Boolean[/FONT]
[FONT=Calibri]Dim wb As Workbook[/FONT]
 
[FONT=Calibri]       For Each wb In Application.Workbooks[/FONT]
 
[FONT=Calibri]           IsWBOpen = wb.Name = strWBName[/FONT]
[FONT=Calibri]           If IsWBOpen Then Exit Function[/FONT]
[FONT=Calibri]      Next wb[/FONT]
 
[FONT=Calibri]End Function[/FONT]

 

[Norie]

Chris

Cheers, felt I'd done something wrong there but it worked when I tested it.

Mind you there werer only 2 workbooks open when I tested.

PS I was originally going to use this but I thought it was wrong for some reason.

    IsWBOpen = IsWbOpen Or (wb.Name = strWBName)

The Exit is probably a better idea anyway.

 

[ChrisOswald]

Norie,

While using 'exit function' will 1/2 the processing time (on average) of the function, I like having the single exit point in the function a lot. (although to limit it to one exit point, you could use an exit for instead)

Although, considering that I wrote a function the other day that had something like 10 exit points, which for some strange reason I had to debug today, I might be a little snakebit at the moment...

What's your thoughts on doing something like this, instead?

Function IsWBOpen(strWBName As String) As Boolean
    Dim dummyWb                         As Workbook
    On Error GoTo OuttaHere
    Set dummyWb = Application.Workbooks(strWBName)
    Set dummyWb = Nothing
    IsWBOpen = True
    Exit Function
OuttaHere:
End Function

I'm curious because this is my general approach to finding out if an object exists/is open/etc.

 

[Norie]

Chris

There's nothing wrong with that type of code.

I probably wouldn't use it myself, for some reason I have some sort of aversion to relying on an error which that sort of does.

Not sure if using Exit (For or Sub) would make much of a difference for a function that loops through a small collection like workbooks.

Of course if the function was more complicated, eg perhaps opening a load of workbooks, then usind Exit might have a positive impact.