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

Community · Accepted Answer · 2017-05-23 11:44:54Z

3

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

edited May 23, 2017 at 11:44

CommunityBot

11 silver badge

answered Jan 16, 2016 at 9:54

dePatinkin

2,2991 gold badge18 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kapé Over a year ago

So Excel only understands primitive types like String and Integer?

dePatinkin Over a year ago

And arrays and a few more, but yes. Excel evaluates each part of a formula separately, so it needs to be something it knows how to handle. It can't guess what to do with custom types.

Collectives™ on Stack Overflow

Call VBA Class Module in Excel formula

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related