EXCEL: How To: Pass Workbook Handle to Sub [RESOLVED - duh]
Esteemed Forum Participants and Lurkers:
===============================
Excel 2003 VBA
I must be doing something dumb here ... I can't seem to pass a workbook reference to a Subroutine ...
Code:
Sub aTest()
Dim aBook As Workbook
Set aBook = ActiveWorkbook 'Set a handle to a WorkBook
Debug.Print aBook.Name 'Prove that we can access by handle
Pass_Book (aBook) '<< THIS FAILS
End Sub
Sub Pass_Book(TestBook As Workbook)
Debug.Print TestBook.Name, TestBook.Sheets(1).Name
End Sub
I get an error when I call "Pass_Book":
Run-time error '438': Object doesn't support this property or method
Thank you for any and all comments, suggestions, and assistance.
Re: EXCEL: How To: Pass Workbook Handle to Sub ???
Take the parenthesis off
VB Code:
Sub aTest()
Dim aBook As Workbook
Set aBook = ActiveWorkbook 'Set a reference to a WorkBook
Debug.Print aBook.Name 'Prove that we can access by reference
Pass_Book aBook '<< THIS WORKS NOW
End Sub
Sub Pass_Book(TestBook As Workbook)
Debug.Print TestBook.Name, TestBook.Sheets(1).Name
End Sub
Re: EXCEL: How To: Pass Workbook Handle to Sub ???
Why in the crappy blazes do the "Hint" popups ALWAYS show parentheses whether they should be there or not? I'll never learn!
Thanks RobDog.
Re: EXCEL: How To: Pass Workbook Handle to Sub [RESOLVED - duh]
You only need the parenthesis when you are calling a sub in this method..
This is old style code, and I very much doubt that people still use it, it is however required if you are interested in a return value of a function, for example to return the button that was pressed on a MsgBox Function, or another way of looking at this is to say..
VB Code:
varName = FunctionName(Parameters)
If you weren't interested in the returned value you would just call the function like so..
But this type of function call is a waste of memory, and if you were to use nothing but non-return calls to functions throughout your code then you would be better off setting these as Sub procedures instead, but that's another argument I don't want to kick off on this forum..