Writing VBA array to Excel. Result ends up displaced by one column.

[Richard Buttrey]

Hi,

I've solved this problem before but for the life of me can't remember what I did.

The final line of code in a procedure which writes a VBA array back to Excel is:

Please Login or Register  to view this content.

the arOutData 2nd dimension i.e. the 'columns' is 6

When this is written to A1 on Sheet2 all 6 columns are output but starting in B1, all the data I expect is there.
There is no Option Base line of code so I understand by default this is Base 1 but in any case the output is correct, it's just put in the wrong column

[HSV]

Probably the first element will be empty.

Please Login or Register  to view this content.

[nankw83]

Why do you have +1 ? I tried with a sample data & your line of code & the results get into A1 correctly with #N/A in the last column due to the +1

Please Login or Register  to view this content.

[Richard Buttrey]

Why do you have +1 ? I tried with a sample data & your line of code & the results get into A1 correctly with #N/A in the last column due to the +1

Please Login or Register  to view this content.

Hi,

The array is a 6 'column' array. I found that without the +1 it only populated 5 columns B:F

[nankw83]

As far as I know, your code should work as expected without the +1 but I guess the +1 is just acting as a work around solution to your issue of not placing the data from cell A1 ... Perhaps if you could post your whole code someone can spot the issue

[karedog]

Hi, please examine these code :

- Create two modules

- Code on Module1 (please note, there is no the "Option Base 1" line on this module) :

Please Login or Register  to view this content.

- Code on Module2 :

Please Login or Register  to view this content.

You probably define the array using the same syntax as the one in Sub Test1_Without_OptionBase1(), that is :
- without the "Option Base 1" line code
- Dim arOutData(3, 6) --> without specifying the lbound for the dimension, so VBA will automatically take this value as the Option Base default value (in this case = 0, because not explicitly specified)
- so the above syntax is actually --> Dim arOutData(0 To 3, 0 To 6)

that's why the output is "shifting" one column to right, while actually you have one extra "hidden column", in this case column(0)

[HSV]

Please Login or Register  to view this content.

[Richard Buttrey]

Probably the first element will be empty.

Please Login or Register  to view this content.

Hi,

Mmm,
Thanks for the suggestion. When I try

Please Login or Register  to view this content.

that of course give a subscript out of range since the original '1' indicates the first (rows) dimension. The '2' in the second part indicates the 2nd (columns) dimension.

[Richard Buttrey]

Thanks karedog,

It is indeed the Option Base 1 that was causing the problem.
In this procedure I don't specifically define the array in the way you show, rather I simply read the range into the array with

Please Login or Register  to view this content.

where lRows is the last row of data. Not sure whether that makes any difference.
Once I'd added the Option Base 1 and removed the +1 from the second dimension that cured the anomaly.
I was always under the impression that the default is Base 1 when reading a range into an array - clearly I was mistaken.

Please Login or Register  to view this content.

Thanks once again - and also to the other contributors.

[karedog]

You are welcome Richard, and many thanks for rep.points given.

Regards