MS Access/VBA type mismatch when passing arrays to function/subroutine

Question

Can anyone explain to me why, in VBA code for a button on a form in MS-Access 2007, this gives the error "Compile error: Type mismatch: array or user-defined type expected"

Private Sub Button_Click()
    Dim Arr() As Integer
    Foo (Arr())
End Sub

Private Sub Foo(Arr() As Integer)
    Me.Field.Value = "Foo"
End Sub

but this compiles fine?

Private Sub Button_Click()
    Dim Dummy
    Dim Arr() As Integer
    Dummy = Bar (Arr())
End Sub

Private Function Bar(Arr() As Integer)
    Me.Field.Value = "Bar"
End Function

The function/subroutine I'm writing doesn't return anything, but I can't get it to compile unless I assign the return value of my function call to a dummy variable, like in the Bar function above.

Fionnuala · Accepted Answer · 2012-06-20 20:05:16Z

5

If you wish to use brackets around the parameter, you must use Call, so skip the brackets:

Private Sub Button_Click()
    Dim Arr() As Integer
    ''No brackets
    Foo Arr()
    ''Or 
    ''Call Foo(Arr())
End Sub

Private Sub Foo(Arr() As Integer)
    Me.Field.Value = "Foo"
End Sub

answered Jun 20, 2012 at 20:05

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

serme Over a year ago

Seems I need to read up a little more on the ways to call subroutines. Thanks.

Collectives™ on Stack Overflow

MS Access/VBA type mismatch when passing arrays to function/subroutine

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related