Pass user-defined type to Function

[Lammy]

Dear all,

I am new to VBA and I am struggling getting the syntax right.

What I am trying to achieve:
- Read a Range from a Sheet
- Paste it in a different Sheet

Limitations are, that the Range and hence the Array store different types, hence the user-defined type ("MyArrayType").

Here is what I tried so far.

Private Type MyArrayType
    ProjectId As Integer
    Location As String
End Type

Public Sub MySubRoutine()

    Dim MyArrayRecords(30) As MyArrayType
   
    ' Read Values from Sheet to Array
    For i = 2 To 30
        With MyArrayRecords(i)
            .ProjectId = Cells(i, 2).Value
            .Location = Cells(i, 1).Value
        End With
    Next i

    ' Paste Array to Sheet
    ProcessArray (MyArrayRecords)
End Sub

Function ProcessArray(MyArrayRecords As MyArrayType) As String

    With ActiveSheet
        .Range("H10").Resize(UBound(MyArrayRecords, 1)).Value = MyArrayRecords
    End With
   
End Function

I can get the code to work if I use a normal Array(), but not with my user-define type. It returns "ByRef argument type mismatch".

Thank you for your insights.

 

[RoryA]

You haven't declared the function to take an array of the type - it should be:

Function ProcessArray(MyArrayRecords() As MyArrayType) As String

 

[Lammy]

Actually, I had the same idea, but when adjusting I still receive
"Type mismatch: array or user-defined type expected"

 

[Lammy]

@Answering because edit-timeout

I think the mistake was actually that I did not use "Call" to execute the function. At least the error message changes:

 

[RoryA]

I'd suggest you switch to a class. UDTs are a little limited in VBA.