VBA Array Wrong Number of Dimensions

Question

I am trying to store a list from a multi dimensional array in a dictionary using VBA.

The second last line gives a wrong dimension error. I know the keyArray is 2 dimensions but I want to store the first item in keyArray which is a list then so on.

keys is an array which stores the primary keys from my table there isn't any issues with that.

For I = 0 To 15
    'Data which should be going in for that key
    If I < 8 Then
        keyArray(I, 0) = dptData(I)
    Else
        keyArray(I, 0) = data2(I - 8)
    End If
        keyArray(I, 1) = keys(I, 0)
        keyArray(I, 2) = keys(I, 1)
        keyArray(I, 3) = keys(I, 2)

        dict.Add keys(I, 0) & " " & keys(I, 1) & " " & keys(I, 2), keyArray(I)
Next I

KeyArray() is two dimensions, you're only specifying one in the dict.Add line. — Bill Hileman, Jul 12 '18 at 18:39
It looks like you're trying to slice an array. Would you like to write a function that takes a multi-dimensional array and a dimension index, and returns a 1D array? `` — Mathieu Guindon, Jul 12 '18 at 18:44
[This should be helpful](https://stackoverflow.com/q/175170/1188513) — Mathieu Guindon, Jul 12 '18 at 18:51
I'm unclear why you didn't include the declaration or redim of keyArray. — , Jul 12 '18 at 18:54
A dictionary has a Keys reserved word. Using that reserved word as the name of another array only confuses matters and could potentially lead to conflicts. — , Jul 12 '18 at 18:59
@Jeeped it's a member of the `Dictionary` class. If every member of every object in every referenced type library is forbidden to use for anything, we're all in trouble! ;-) ...that said, `keys` is indeed a confusing name to use for a 2D array, as is `keyArray`. — Mathieu Guindon, Jul 12 '18 at 19:00
@MathieuGuindon - perhaps but just because you can doesn't mean you should. Here I only see it confusing natters. — , Jul 12 '18 at 19:02

score 0 · Accepted Answer · answered Jul 12 '18 at 18:56

Omitting a subscript from a 2D array doesn't automatically slice it into a 1D array. Use this function if you need complete efficiency and a function that can be used outside of Excel. This function uses the Index worksheet function to perform the slicing, so won't work outside of Excel (and is less efficient, too).

Say you named your function Slice, this should do it (assuming keyArray is 0-based):

dict.Add keys(I, 0) & " " & keys(I, 1) & " " & keys(I, 2), Slice(keyArray, 0)(I)

VBA Array Wrong Number of Dimensions

1 Answers1