0

I have a simple question (I think). Let's say I set a global variable in a standard module in Book1 to an open (different) workbook:

Public Sub InitGlobals()
    Set gwkb = Workbooks("book2.xlsx")
End Sub

Now I close book2.xlsx manually. What is the status of gwkb? This is what I get when I run various tests in the immediate window:

?gwkb is nothing
False
?isempty(gwkb)
False
?isnull(gwkb)
False

However, if I ask for any property of gwkb, such as gwkb.Name, I get an automation error.

My question is: is there a way to test for this condition, without resorting to some sort of "On Error Resume Next" test?

Thanks for your help.

Community
  • 1
  • 1
user3803315
  • 71
  • 1
  • 3
  • 1
    The following [post](http://stackoverflow.com/a/9373914/2521004) is a good way to test if the workbook is still open. – Automate This Nov 13 '14 at 18:45
  • possible duplicate of [Detect whether Excel workbook is already open (using VBA)](http://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open-using-vba) – Automate This Nov 13 '14 at 19:30
  • @Gary's Student & PortlandRunner -- Thanks for the quick response, but I don't really want to test if book2.xlsx is open. I want to know if it is still "safe" to use gwkb. For example, in my application I won't know the exact filename that gwkb is set to, but if I try to use gwkb.Name, I get an error. I guess I was wondering if there might be a status / test in VBA for gwkb beyond "is nothing", "isNull", or "isEmpty" to determine if it is still ok to use as an object. Perhaps my only option is to write a function like "bIsOK(gwkb)" that checks for an error? Thanks again. – user3803315 Nov 13 '14 at 19:45
  • if gwkb still exists , it's because you declared it as public or global, and not inside a sub/function, neither passed it as argument. So the variable name still exists, but is linked to an empty workbook (or object depending how you declared it -not shown in your code-) – Patrick Lepelletier Nov 16 '14 at 00:17

1 Answers1

1

If Book2 is initially open and may be closed then looping over the open books will not find it:

Public Sub InitGlobals()
    Dim gwkb As Workbook, wb As Workbook
    Set gwkb = Workbooks("Book2.xlsx")
    gwkb.Close
    For Each wb In Workbooks
        If wb.Name = "Book2" Then
            MsgBox wb.Name & " is open "
        End If
    Next wb
End Sub
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • `if not iserror(gwkb) then msgbox gwkb.name` , sometimes used after a `i= application.match ()` for testing if the i variable didn't return an empty result – Patrick Lepelletier Nov 16 '14 at 00:24
  • by the way +1 for this nice way of not using error handle. could turn it into a function and use it often ^^ – Patrick Lepelletier Nov 16 '14 at 00:29
  • @PatrickLepelletier .....it is a trade-off....error-handling is not used, but looping is used.....this may loose efficiency if there are many workbooks open at the same time. – Gary's Student Nov 16 '14 at 03:29