CODEWITHSUNDEEP
excelvba
Oli
Oli

Reputation: 39

How to pass an array to a sub

Using Excel in Office 365 on Windows 10.

I'm trying to pass an array to a sub but keep getting compile errors

Sub PassArrTest(a(6) As Integer) ' This line here gives Compile error:  Expected: ) Error comes up as I'm typing
    
   MsgBox (a(3))
End Sub    

Sub Macro1()
'
' Macro1 Macro
'
Dim a(6) As Integer
Dim i As Integer
For i = 0 To 5
    a(i) = i + 1
Next i

PassArrTest (a)



End Sub

If I do it like this :-

Sub PassArrTest(a() As Integer) ' a declared as a dynamic array
    MsgBox (a(3))
End Sub



Sub Macro1()
'
' Macro1 Macro
'
Dim a(6) As Integer
Dim i As Integer
For i = 0 To 5
    a(i) = i + 1
Next i

PassArrTest (a)



End Sub

I don't get compile error when typing the code out but when attempting to run I get "compile error Type mismatch: array or user-defined type expected"

Is it possible to pass arrays to a sub in VBA and if so what is the correct syntax?

Upvotes: 1

Views: 88

Answers (1)

FaneDuru
FaneDuru

Reputation: 42256

Try this way, please:

Sub PassArrTest(a() As Integer) ' a declared as a dynamic array
    MsgBox a(3)
End Sub

Sub Macro1()
 Dim a(6) As Integer, i As Integer

 For i = 0 To 5
    a(i) = i + 1
 Next i

 PassArrTest a()
 MsgBox passArrBis(a())
End Sub

Function passArrBis(a() As Integer) As Boolean
    If a(3) > 4 Then
        passArrBis = True
    Else
        passArrBis = False
    End If
End Function

Upvotes: 2

Related Questions