Excel – VBA Cannot return a custom Hashtable from a Public Function

excelvba

I have a Hashtable implementation (in class modules folder).
But I get a strange (long) error, for which no help is provided..
Error throws when I make the Function that return Hashtable Public.
It says:
"Private object modules cannot be used in public object modules as parameters or return types for public procedures (**this is what i need), as public data members, or as fields of public user defined types."

I need very simple logic. I want to be able to get a Function inside one Worksheet to performs some check, fill a Hashtable and return it so I can iterate in it in another Worksheet. Is this even possible (How I do it in C#)

Thanks in advance

Best Answer

The error means that you have your class Instancing property set to Private, which means that you can't use it anywhere where it might get used elsewhere. Mark it as PublicNotCreatable and your error will go away. I'm not clear on what exactly it is you're doing in your app, so that's the first thing to try.

Related Solutions

Vba – User Defined Type (UDT) as parameter in public Sub in class module (VB6)

Just define the sub as Friend scope. This compiles fine for me in a VB6 class.

Private Type testtype
  x As String
End Type


Friend Sub testmethod(y As testtype)

End Sub

From your error messages it appears your class is private. If you do want your class to be public - i.e. you are making an ActiveX exe or DLL and you want clients to be able to access the sub - then just make both the type and the sub Public.

Excel – How to return a result from a VBA function

For non-object return types, you have to assign the value to the name of your function, like this:

Public Function test() As Integer
    test = 1
End Function

Example usage:

Dim i As Integer
i = test()

If the function returns an Object type, then you must use the Set keyword like this:

Public Function testRange() As Range
    Set testRange = Range("A1")
End Function

Example usage:

Dim r As Range
Set r = testRange()

Note that assigning a return value to the function name does not terminate the execution of your function. If you want to exit the function, then you need to explicitly say Exit Function. For example:

Function test(ByVal justReturnOne As Boolean) As Integer
    If justReturnOne Then
        test = 1
        Exit Function
    End If
    'more code...
    test = 2
End Function

Documentation: Function Statement

Best Answer

Related Solutions

Vba – User Defined Type (UDT) as parameter in public Sub in class module (VB6)

Excel – How to return a result from a VBA function

Related Topic