Search code examples
excelvbatypesuserform

Problem with defined type in Excel VBA Userform


I had found a lot similar topics but no solution only workarounds (many advices to use classes but I do not understand how to do it - I want it to be as simple and short as possible)

Private Type Bottom_Rit
    Bot As Integer
    Rit As Integer
    End Type

Dim BxR_1 As Bottom_Rit

Sub Fpage()
    BxR_1 = Lab(a, b)
End Sub

Private Sub Button1_Click()
    Fpage
End Sub

Function Lab(a As Integer, b As Integer) As Bottom_Rit
    Lab.Bot = a
    Lab.Rit = b
End Function

Trying to repeat the code from this thread link to stackoverflow thread

I get an error message "Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions"


Solution

  • User Defined Type (UDT)

    If you define a UDT within a class you have the following restrictions

    • You cannot use a public UDT in a class and a userform is a kind of class.
    • You cannot use a UDT as return type in a public function in a class.
    • You cannot use a UDT as a parameter in a public function in a class.
    • You can use a UDT in that class locally (i.e use the keyword Private to define it)

    The code from the post is used in a userform therefore the OP has to define the UDT as Private and every function needs also to be private in case the UDT is used in the signature of the function.

    That means the following code will work

    Private Type Bottom_Rit
        Bot As Integer
        Rit As Integer
    End Type
    
    Private Function Lab(a As Integer, b As Integer) As Bottom_Rit
        Lab.Bot = a
        Lab.Rit = b
    End Function
    

    PS I'd also recommend to use Option Explict. You can read about it in this post although not excatly for VBA but it covers it as well.