Search code examples
arraysexcelvbastringbyref

ByRef Argumenttype mismatch when passing an array to a sub or function

I am working on a little project for work. All I am trying to do is create an array in Sub1 and call a function, pass the array to that function and have it fill the array.

Very simplified, it looks something like this: 

Private Sub whatever()
  Dim arr(10, 2) As String
  workArray arr
End Sub

Sub workArray(ByRef arr As String)
  '- do stuff here
End Sub

I have googled so much and just can't figure out what I'm doing wrong. I have also tried:

  • call workArray(arr)
  • call workArray arr
  • workArray(arr)
  • workArray arr

I've read that I can only pass an array ByRef, so I am sure that should be alright. The array arr is of type String, the array the function expects is declared as String - that should be alright too. I always get "ByRef argument type mismatch". Is the problem maybe with the type of String? Are String-arrays behaving differently?

Anyone in here kind enough to release me of my pain?

Solution

  • You are missing () in the function, an array needs () after the variable when passing it somewhere else so VBA knows it as an array

    Private Sub whatever()
  Dim arr(10, 2) As String
  workArray arr
End Sub

Sub workArray(ByRef arr() As String)
  '- do stuff here
End Sub

    Edit

    I look around a little bit cperson has an excellent in depth guide on passing arrays to functions and back again. When in doubt, check cperson. He has a load of useful stuff regarding VBA