Call VBA Class Module in Excel formula

Question

I know that a function of a Module can be called in a formula like this:

=GetMyModuleFunction()

Now I would like to call a Class Module function. I don't know if a Class Module can be instantiated in a formula at all so I've created a function in a Module so I can call it just like =GetMyModuleFunction():

' Class Module MyClassModule:
Public Property Get MyProperty() As String
    MyProperty = "Hello World!"
End Property

Public Function GetMyFunction() As String

    GetMyFunction = "Hello World!"

End Function
' End Class Module MyClassModule

' Module MyModule:
Public Function GetMyClassModule() As MyClassModule

   Set GetMyClassModule = New MyClassModule

End Function
' End Module MyModule

So after that I tried in the formula bar:

=GetMyClassModule().GetMyFunction()
=GetMyClassModule().MyProperty

Which shows an error dialog that the formula is invalid. Is it not possible what I'm trying to achieve here? Currently I use Modules instead but functions and subs with duplicate names are confusing and error prone to use in Modules..

dePatinkin dePatinkin · Accepted Answer · 2016-01-16T09:54:38

Your question is similar to the question asked here: Call VBA function that returns custom type from spreadsheet

You can only return data types that Excel understands from a user-defined function. Excel does not understand custom data types.

But you can wrap your class properties or functions with a regular module function (UDF) like so:

Public Function GetMyClassModuleFunction() As String

    GetMyClassModuleFunction = GetMyClassModule.GetMyFunction()

End Function

Call VBA Class Module in Excel formula

1 Answers