0

Edit: My real question is how to test if object was set that was instantiated. I am not really looking to "correct" my code. Its just an example.

I have a function that returns a workbook:

Edit: Added code

Sub GetWb() as Workbook

Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Set wM = Application.Workbooks.Open("Z:\somepath.xlsm", ReadOnly:=True)
Application.EnableEvents = True
Application.DisplayAlerts = True
On Error GoTo 0

end sub

In another sub I want to check if that object was set properly by the function. I usually do something like this with objects generally:

dim w as Workbook
set w = GetWb
if w is nothing then
debug.print "no workbook"
else
debug.print "workbook"
end if

However, the is nothing test does not work because the object is instantiated, but was not set so it is something, not nothing.

I have resorted to this ugly solution, which works fine:

dim w as Workbook
set w = GetWb
on error goto someerrorhandling
if w.name = "" then
end if
on error goto 0
'other code here


someerrorhandling: 
msgbox "no workbook"

In other words, I check a property of the object to force an error, or not. There must be a better/cleaner way.

I checked and this link states that the way I am doing it is the best way: VBA: Conditional - Is Nothing

Community
  • 1
  • 1
mountainclimber11
  • 1,339
  • 1
  • 28
  • 51
  • 1
    Can you post the actual code? `Sub GetWb() as Workbook` is a syntax error at very least, and `'code here to set the proper workbook` is likely where the issue is. – Comintern Jan 11 '17 at 16:49
  • I think your solution is close - there may be a better way to write it, but the idea would be the same - catch an error on tyring to access the `Workbook` object. Just wrap it into a `Function` that returns `Boolean` - and you are all set. – Logan Reed Jan 11 '17 at 16:58
  • i added code to the function. – mountainclimber11 Jan 11 '17 at 17:01
  • what happens in the first snippet if you comment out `set w = GetWb`? – Bathsheba Jan 11 '17 at 17:03
  • Edit: My real question is how to test if object was set that was instantiated. I am not really looking to "correct" my code. Its just an example. – mountainclimber11 Jan 11 '17 at 17:09
  • Just FYI you can't really expect a concrete example with a code sample that is clearly nonsense. Your `Is Nothing` test would work if the code you posted were a function. – Rory Jan 11 '17 at 23:49
  • I can't replicate this at all. If `Application.Workbooks.Open` throws I *consistently* have `Is Nothing` return `True` on the variable it was being set to. – Comintern Jan 12 '17 at 00:44

1 Answers1

1

change your error handling for GetWB so it returns nothing incase of error, also use Function instead of sub.

Function GetWb() As Workbook

    Application.DisplayAlerts = False
    Application.EnableEvents = False

    On Error GoTo errHandler:

    Set GetWb = Application.Workbooks.Open("Z:\somepath.xlsm", ReadOnly:=True)
    Application.EnableEvents = True
    Application.DisplayAlerts = True

errHandler:
        If Err.Number <> 0 Then
            Set GetWb = Nothing
            Application.EnableEvents = True
            Application.DisplayAlerts = True
        End If

End Function
cyboashu
  • 10,196
  • 2
  • 27
  • 46
  • 1
    While that will solve the issue, my real question is how to test if object was set that was instantiated. I am not really looking to "correct" my code. Its just an example. I am gathering that there isn't a better way and I should either do it my way or return nothing like you state here. – mountainclimber11 Jan 11 '17 at 17:11
  • 1
    hmm, but with workbooks, it will never happen, if you do ` Dim x As Workbook` and the check its nothing. Only way to create an instance and not set the value is `Dim x As New Workbook` but this case is useless as it will start spraion err: 429 every time you try to use it without setting any workbook. Overkill is to declare with New keyword and trap error 429. – cyboashu Jan 11 '17 at 17:35
  • 2
    @mountainclimber I've also noticed that there is something different when you work with "system" objects like `Workbook` and `Worksheets` collection compared to the objects you create yourself. Not being able to use `Is Nothing` check is one example. Another is - I have generic function to check if item exists in collection and it does not work with `Worksheets`, even though it is theoretically a `Collection` (documentation says that). All you can do with application objects is trial and error to see what works. :-( – Logan Reed Jan 11 '17 at 18:23