VBA can't assign function return value to array

[ronga2001]

This looks like a simple problem but it's been driving me nuts the past couple of days. The following is the gist of a macro I'm working on, I'm trying to assign the return value of a function to an array in the main sub, but it's throwing a can't assign to array error. I've tried changing vbArr data type from string to variant, likewise data type of switch function. I'm sure the answer is something really basic but for the life of me, I can't see it.

Option Base 1
Public Sub getArr()
  Dim vbArr(1 To 2) As String, newArr(1 To 2) As Variant, k As Integer

  vbArr(1) = a
  vbArr(2) = b
   
  newArr = switch(vbArr) 'this throws can't assign to array error
End Sub

Public Function switch(ByRef myArr()) As String()
  Dim anArr(1 To 2) As String
  anArr(1) = myArr(2)
  anArr(2) = myArr(1)
End Function

 

[RoryA]

You can only assign one array directly to another if the receiving array is dynamic and of the same type as the source.

 

[ronga2001]

This throws off a type mismatch or user defined array type error though, ReDimming anArr doesn't help either:

Option Base 1
Public Sub getArr()
  Dim vbArr(1 To 2) As String, newArr() As String, k As Integer 'changed newArr to string and made it dynamic

  vbArr(1) = a
  vbArr(2) = b
  
  newArr = switch(vbArr)
End Sub

Public Function switch(ByRef myArr()) As String()
  Dim anArr() As String
  anArr(1) = myArr(2)
  anArr(2) = myArr(1)
End Function

 

[Engberg]

I can't explain why or how because I don't understand it myself, this works though.

Option Base 1
Public Sub getArr()
    Dim vbArr() As Variant, newArr() As Variant, k As Integer
    vbArr = Array("a", "b")
'    ReDim Preserve vbArr(1) ' Or these four lines instead of the one above
'    vbArr(1) = "a"
'    ReDim Preserve vbArr(2)
'    vbArr(2) = "b"
    newArr = switch(vbArr)
    Debug.Print newArr(1)
    Debug.Print newArr(2)
End Sub

Public Function switch(ByRef myArr()) As Variant()
  Dim anArr() As Variant
  anArr = Array(myArr(2), myArr(1))
  switch = anArr
End Function

 

[RoryA]

This throws off a type mismatch

Your function expects an array of type Variant and you are passing an array of type String.

 

[ronga2001]

Your function expects an array of type Variant and you are passing an array of type String.

I changed it to type string after your first suggestion (see above) but that didn’t fix it. Also, shouldn’t type variant accommodate all variable types?

 

[RoryA]

Also, shouldn’t type variant accommodate all variable types?

Declaring an array of type variant is not the same as using a variant variable.

I changed it to type string after your first suggestion (see above)

No, you didn't actually. Your code has the function return value as a String array, but the input is still a variant array.

myArr()

is an array of type variant - i.e. it has to be an array, and that array could contain anything; whereas:

myArr

is just a variable of type variant, which may or may not be an array.

This will work:

Public Sub getArr()
  Dim vbArr(1 To 2) As Variant, newArr() As String, k As Integer 'changed newArr to string and made it dynamic

  vbArr(1) = a
  vbArr(2) = b
  
  newArr = switch(vbArr)
End Sub

Public Function switch(ByRef myArr()) As String()
  Dim anArr(1 To 2) As String
  anArr(1) = myArr(2)
  anArr(2) = myArr(1)
End Function

Note that vbArr and myArr are both arrays of type variant and newArr is a dynamic array of the same type (String) as the return type of the switch function.

 

[ronga2001]

I can't explain why or how because I don't understand it myself, this works though.

Option Base 1
Public Sub getArr()
    Dim vbArr() As Variant, newArr() As Variant, k As Integer
    vbArr = Array("a", "b")
'    ReDim Preserve vbArr(1) ' Or these four lines instead of the one above
'    vbArr(1) = "a"
'    ReDim Preserve vbArr(2)
'    vbArr(2) = "b"
    newArr = switch(vbArr)
    Debug.Print newArr(1)
    Debug.Print newArr(2)
End Sub

Public Function switch(ByRef myArr()) As Variant()
  Dim anArr() As Variant
  anArr = Array(myArr(2), myArr(1))
  switch = anArr
End Function

There, I finally got it to work! This works since variables are consistent, they're all of type variant. Thanks.

 

[ronga2001]

Declaring an array of type variant is not the same as using a variant variable.


No, you didn't actually. Your code has the function return value as a String array, but the input is still a variant array.

myArr()

is an array of type variant - i.e. it has to be an array, and that array could contain anything; whereas:

myArr

is just a variable of type variant, which may or may not be an array.

This will work:

Public Sub getArr()
  Dim vbArr(1 To 2) As Variant, newArr() As String, k As Integer 'changed newArr to string and made it dynamic

  vbArr(1) = a
  vbArr(2) = b
 
  newArr = switch(vbArr)
End Sub

Public Function switch(ByRef myArr()) As String()
  Dim anArr(1 To 2) As String
  anArr(1) = myArr(2)
  anArr(2) = myArr(1)
End Function

Note that vbArr and myArr are both arrays of type variant and newArr is a dynamic array of the same type (String) as the return type of the switch function.

Sorry for the late revert, got swamped. You missed some lines in your code so it wouldn't work, made some changes and got it to work:

Option Base 1

Public Sub getArr()
  Dim vbArr(1 To 2) As Variant, newArr() As String, k As Integer 'changed newArr to string and made it dynamic

  vbArr(1) = "a"
  vbArr(2) = "b"
 
  newArr = switch(vbArr)
  Debug.Print Join(newArr, vbCrLf)
End Sub

Public Function switch(ByRef myArr()) As String()
  Dim anArr(1 To 2) As String
  anArr(1) = myArr(2)
  anArr(2) = myArr(1)
  switch = anArr
End Function

Thanks for all the help, idk why they made vba variables so fiddly.

 

[ronga2001]

Sorry for the late revert, got swamped. I missed some lines in my code so it wouldn't work, made some changes and got it to work:

Option Base 1

Public Sub getArr()
  Dim vbArr(1 To 2) As Variant, newArr() As String, k As Integer 'changed newArr to string and made it dynamic

  vbArr(1) = "a"
  vbArr(2) = "b"
 
  newArr = switch(vbArr)
  Debug.Print Join(newArr, vbCrLf)
End Sub

Public Function switch(ByRef myArr()) As String()
  Dim anArr(1 To 2) As String
  anArr(1) = myArr(2)
  anArr(2) = myArr(1)
  switch = anArr
End Function

Thanks for all the help, idk why they made vba variables so fiddly.