0

Why does this work:

Sub f1(a() as Variant)
    Debug.print "Ok"
End Sub

Sub main()
    Dim a(2) as Variant
    Dim a2(2) as Variant
    a(0) = a2
    f1 a2
End Sub

But this doesn't:

Sub f1(a() as Variant)
    Debug.print "Ok"
End Sub

Sub main()
    Dim a(2) as Variant
    Dim a2(2) as Variant
    a(0) = a2
    f1 a(0)
End Sub

The objective is to manipulate arrays or arrays in VBA, recursively, and to be able to call functions on subpart of the tree. But it looks like when I call arr(0), VBA thinks it is a simple Variant, because arr has the type of an array of Variant (not an array of arrays of variants). Thus, it doesn't see that arr(0) it is an array by itself. And it panics.

I don't know how to make VBA understand that it's ok, he can execute the code, there is no type issue here.

I also didn't find a way to declare a type as an "array of array of variant" to make things clear from the start.

Note: I finally found a way to bypass the problem by : creating a variable of the right type, putting its value to the element of the table and use it. Example here, by adding the value "t" :

Sub f1(a() as Variant)
    Debug.print a(0)
End Sub

Sub main()
    Dim a(2) as Variant
    Dim a2(2) as Variant
    a2(0) = "It works!"
    a(0) = a2
    Dim t() as Variant
    t = a(0)
    f1 t
End Sub

But it looks wrong : I should be able to declare the types clearly enough from the start and I should not have to re-cast my types when I want to use them.

So, any idea on how to do it the best way ?

See description. I want to understand how to manipulate arrays of arrays of variant in VBA, recursively, the proper way without type issues.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Vincent Vanhecke
  • 1
  • 1
    Iirc, if you'd use `Sub f1(a As Variant)` you could use `f1 a(0)`. The reason you're unable to send it as you have it now is because you're sending an element of the a array and while the element does include its own array, it's not seen as such type-wise. Others can correct me if I'm wrong. Using an in-between `t()` shouldn't over-complicate your code imo. Also, here are two links to maybe help you get some insight: https://stackoverflow.com/a/9436309/19353309 and https://stackoverflow.com/questions/73058611/write-an-array-of-arrays-to-a-range-vba – Notus_Panda Feb 21 '23 at 17:01
  • Change a() to a in both cases and all is well. In your second case you get an error because you have passed a single variant to a method expecting an array of variants. i.e at the parameter level a variant <> array of variants. – freeflow Feb 21 '23 at 18:39
  • Thank you @Notus_Panda & freeflow. You are both right: when I define the argument as a simple Variant, I am still able to access its indexes as if it was an array. Not an intuitive way compared to other languages but it works ! And thank you Notus_Panda for the links, I learned a few things on multi-dim arrays in VBA. – Vincent Vanhecke Feb 21 '23 at 21:26

0 Answers0