Search code examples
vbauser-defined-types

VBA: More questions about custom Type and Function with arrays


I am getting stuck on trying to pass array variable (custom type) to a function. The error comes up with the calling the function with D1, What am I getting wrong here please?

I have tried declaring D1() etc which did not seem correct but did not work anyway.

Public Type CusType
    One_ As Integer
    Two_ As Single
    Thr_ As Single
    Fou_ As Single
End Type

Public Sub Test1()
    Dim D1 As CusType
    Dim D2 As CusType
    Dim D3 As CusType
    Dim Marker As Integer

    Marker = 1

    With D1
        .One_ = 11
        .Two_ = 12
        .Thr_ = 13
        .Fou_ = 14
    End With

    With D2
        .One_ = 21
        .Two_ = 22
        .Thr_ = 23
        .Fou_ = 24
    End With

    With D3
        .One_ = 31
        .Two_ = 32
        .Thr_ = 33
        .Fou_ = 34
    End With

    Dim TestResult As CusType
    TestResult = Test(Marker, **D1, D2, D3**)

    Debug.Print TestResult.One_ & ","; TestResult.Two_ & ","; TestResult.Thr_ & ","; TestResult.Fou_
End Sub

Public Function Test(R, A, B, C) As CusType
    Dim First, Second, Third, Fourth As Single

    If R = 0 Then
        Test = A
    ElseIf R = 1 Then
        Test = B
    Else
        Test = C
    End If
End Function

Solution

  • The error message clearly tells you:

    Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions

    (It is helpful to include exact error messages in the question.)

    You did not declare parameter and return types for Test, so your structures are going to be coerced to and from a Variant.

    Declare the types:

    Public Function Test(ByVal R As Long, A As CusType, B As CusType, C As CusType) As CusType