1

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?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
McMuellermilch
  • 95
  • 1
  • 10
  • Possible duplicate of [How to pass an array to a function in VBA?](https://stackoverflow.com/questions/26492872/how-to-pass-an-array-to-a-function-in-vba) – Mr.Burns Mar 06 '19 at 15:39

1 Answers1

0

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

Mr.Burns
  • 690
  • 1
  • 10
  • 24
  • Hello Mr. Burns, thank you very much for your answer! Now I get error code 13 - type mismatch. AND I edited my post, maybe my mistake is somewhere else? – McMuellermilch Mar 06 '19 at 15:47
  • @McMuellermilch You now have a new issue, I recommend a new question. SO is to help everyone, present or future. My answer is now invalid when looking at your question now. I recommend rolling back your question to what it was originally and if my answer resolved that issue, mark it as accepted and then ask a new question with your new issue – Mr.Burns Mar 06 '19 at 16:10