Exposing VBA code through Excel objects

If we add some VBA code to a worksheet’s code module, such as:

Public Sub ShowName()
    MsgBox “I am “ & Me.Name
End Sub

we can call that procedure from a different module (but within the same project) using code like:

Sheet1.ShowName

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:

Workbooks(“Book1.xls”).Worksheets(“Sheet1”).ShowName

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):

Dim objTheSheet As Object
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:

‘In a workbook’s ThisWorkbook module
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

Posted in Uncategorized

6 thoughts on “Exposing VBA code through Excel objects

  1. 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!

  2. 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.

  3. 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…

  4. 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


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *