CODEWITHSUNDEEP
excelvba
Thana P.
Thana P.

Reputation: 1

How to pass array to function

I try to pass array to function in VBA EXCEL. I cannot find proper example as I try to do

I try to pass array to function In function declaration I try set variable array as variant or ByRef but not work What's I am wrong?

Dim Retarray(1 To 6, 1 To 2) As String

This is my array Then try to pass to function as 

Smap = checkDevNo(Retarray, Right(CellArray(i, 1), 1))

This is my function declaration

Function checkDevNo(ByRef aaray() As String, aa As Character) As String

Select Case aa

Case "1"
    For j = 1 To 6
        If aaray(j, 1) = 6 Then
            checkDevNo = j
        End If
    Next j
Case "2"
    For j = 1 To 6
        If aaray(j, 1) = 7 Then

        End If
    Next j
End Select
End Function

compile error user define type not define ??

Upvotes: 0

Views: 133

Answers (2)

CLR
CLR

Reputation: 12254

If we're talking Excel VBA, then you'll need to do the following:

Function checkDevNo(aaray As Variant, aa As String) As String

    Select Case aa

        Case "1"
            For j = 1 To 6
                If aaray(j, 1) = 6 Then
                    checkDevNo = j
                End If
            Next j
        Case "2"
            For j = 1 To 6
                If aaray(j, 1) = 7 Then

                End If
            Next j

    End Select

End Function

Upvotes: 0

dotNET
dotNET

Reputation: 35380

IIRC, Character is not a VBA type. You should pass it as String or Byte instead. In Visual Basic, single characters can also be stored in fixed-length strings (Dim myChar As String * 1), but that syntax is not supported for method parameters.

So your function declaration should look like this:

Function checkDevNo(ByRef aaray() As String, aa As String) As String
    ...
End Function

Upvotes: 1

Related Questions