Problem with defined type in Excel VBA Userform

Question

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"

Add a Private, i.e. Private Function Lab (a As Integer, b As Integer) As Bottom_Rit). — Storax
– Storax, Commented Apr 23, 2022 at 7:44
TThe code as provided does not compile and does not present the error message referenced. Please ensure that you put Option Explicit at the start of each Module/Class/Form. Dim BxR_1 is a module level variable so should be declared with Public or Private not Dim. In Lab(a,b) the variables a and b have not been defined. The code runs fine once tthese points have been implemented. You should also consider installing the free and fantastic Rubberduck addin for VBA, specifically for the code inspections. — freeflow
– freeflow, Commented Apr 23, 2022 at 7:55
@Storax, I feel confused, because before I made this question post, I for about two days tried many combinations with Private and Public and every time I had errors. But now I tried to add Private status to the function and it seems to work fine ... (FacePalm) — AlienScientist
– AlienScientist, Commented Apr 24, 2022 at 5:18
@freeflow, Thank You for Your answer, You were right I added Button1_Click(), I finally made it work, I followed Storax advice, and I yet did not use Option Explicit, I have seen many advices to use it but for the time it seems to work fine without it, thank You — AlienScientist
– AlienScientist, Commented Apr 24, 2022 at 5:26
Fair enough. Option Explicit is generally only used by those who don't want to end up with strange unexplained errors in their code. — freeflow
– freeflow, Commented Apr 24, 2022 at 7:54

Storax · Accepted Answer · 2022-04-24 13:46:41Z

1

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.

edited Apr 24, 2022 at 13:46

answered Apr 24, 2022 at 13:32

Storax

12.7k3 gold badges21 silver badges36 bronze badges

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

4 Comments

T.M. Over a year ago

Fyi C.f. SO post "Only UDT defined in ..." - The Help reference Type statement isn't too helpful stating "In .. class modules, user-defined types are public by default. This visibility can be changed by using the Private keyword."

AlienScientist Over a year ago

@Storax, Yes, and thanks for the link, after reading what option explicit is doing I will use it now, because I always declare variables anyways...

Cristian Buse Feb 19 at 11:51

Using Friend instead of Public for the class method definiton will allow the use of class-defined UDTs.

Storax Feb 19 at 19:28

Could you elaborate? I cannot get it to work, but that is probably my fault.

Collectives™ on Stack Overflow

Problem with defined type in Excel VBA Userform

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related