If we add some VBA code to a worksheet’s code module, such as:
MsgBox “I am “ & Me.Name
End Sub
we can call that procedure from a different module (but within the same project) using code like:
Now, as well as exposing the ShowName method through the internal Sheet1 object, Excel also makes it available through the Worksheet object, so we can call the same routine from any module in any workbook using code like:
We can also use object variables to call the method, but only if they’re declared As Object (it works with .Worksheets() as that returns a generic Object type rather than a Worksheet type):
Set objTheSheet = Workbooks(“Book1.xls”).Worksheets(“Sheet1”)
objTheSheet.ShowName
Similarly, any public procedures we add to the ThisWorkbook class can be accessed through the Excel workbook object, so we can do things like:
Public Property Get Mine() As Boolean
Mine = True
End Property
Public Sub ShowName()
MsgBox “I am “ & Me.Name
End Sub
‘In a standard module in a different workbook
Sub ShowMyBooks()
Dim objBook As Object
Dim bMine As Boolean
For Each objBook In Workbooks
‘Does it have a “Mine” property, returning True?
bMine = False
On Error Resume Next
bMine = objBook.Mine
On Error Goto 0
‘Yes, so it’s one of ours that we can do stuff with
If bMine Then
objBook.ShowName
End if
Next
End Sub
As well as being more object-orientated (for what that’s worth), this neatly avoids some of the pitfalls of using Application.Run, such the (in)ability to pass parameters ByRef. We also avoid relying on custom document properties or defined names to identify the workbook/sheet as one of ‘ours’ (both of which can easily be broken by the user).
Regards
Stephen Bullen
Nice catch Stephen, One to remember and use!
As always, Iím sure that Stephen is being generous in allowing someone else to point out that this same methodology can also be applied to function procedures .
Place the following simple function in key sheet modules:
Function Mult(a, b)
Mult = a * b
End Function
Then, place this function in a standard module:
Function MultipleFuncResult(m, n)
On Error Resume Next
For Each wks In ThisWorkbook.Worksheets
MultipleFuncResult = _
MultipleFuncResult + wks.Mult(wks.Range(mwks.Range(n))
Next
End Function
Finally, enter =MultipleFuncResult(ìa1î, ìa2î) in a worksheet cell. The cumulative result of the this function will only come from those worksheets that have a Mult function in its sheet module.
Thanks, Stephen!
Gentlemen – You get things to look so very simple :)
Thanks!
Kind regards,
Dennis
So could this be done with add-ins (meaning the showname code would be in the add-in and called from a workbook)? Might be a nice way to invoke add-in functions.
This post was very informative, and helpful to me in an indirect way. I’ve been pairing addins with workbooks, but don’t want any code in the workbooks (otherwise after reading this post I would have done what Alex suggests above). I was using defined names in the workbooks, but when I read Stephen’s comment about their susceptibility to alteration I went in another direction. I’m now using matching VBProject names. So the main application addin checks each workbook that’s activated/deactivated to see if it’s VBProject name matches that of an addin in the same folder. If they match it creates a menu for the workbook based on code stored in the matching addin.
Anyways, thanks Stephen. BTW, reading your book the other day helped me understand how to return more than one argument from a function (if that’s the right way to say it) by passing parameters ByRef. Very cool, my validation code is now much more concise. It’s interesting how as I learn new things the code gets shorter. 2 weeks later and there’s only a couple of screenfuls…
Stephen,
This is incredibly useful for my work. (I am constantly having Application.Run problems).
I am looking forward to receiving your book in the mail (Professional Excel Development)
Andrew
excelthoughts.com