0

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"

AlienScientist
  • 13
  • 5
  • Add a `Private`, i.e. `Private Function Lab (a As Integer, b As Integer) As Bottom_Rit).` – Storax Apr 23 '22 at 07: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 Apr 23 '22 at 07: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 Apr 24 '22 at 05: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 Apr 24 '22 at 05: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 Apr 24 '22 at 07:54
  • @Storax, You could post the same answer and I would mark it as a solution – AlienScientist Apr 24 '22 at 08:40
  • @freeflow, ok, I had to read more information, and admit, that option explicit could be more useful to use than not to use – AlienScientist Apr 26 '22 at 10:09

1 Answers1

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.

Storax
  • 11,158
  • 3
  • 16
  • 33
  • Fyi [C.f. SO post "Only UDT defined in ..."](https://stackoverflow.com/questions/25046552/only-user-defined-type-defined-in-public-object-modules-can-be-coerced-when-tryi) - The [Help reference `Type statement`](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/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." – T.M. Apr 24 '22 at 15:12
  • @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... – AlienScientist Apr 26 '22 at 10:13