0

I have trouble accessing the values stored in a multi dimension Variant. Here is my code :

Sub test()

Dim vari() As Variant
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)

ReDim vari(33, 5)
vari = ws.UsedRange.Value
vari = WorksheetFunction.Transpose(vari())

WorksheetFunction.Average(vari(1))

End Sub

here is my file: excel table

When I run this code I get error on the last line telling me "the index does not belong to the selection".

But the vari(1) seems to be something in the spy section. spy section

I know I could directly compute the average using the Range object in which the value are stored in this particular case. I simplified the situation for th purpose of this post but in my project I have multiple workbooks and subs and it is easier for me to use a Variant to store the values.

Any help would be very much appreciated

Paul Delano
  • 3
  • 5
  • 2
    What are you actually trying to do, in general terms? – BigBen May 04 '21 at 18:31
  • 2
    Why not just declare `Dim vari As Variant` (note the lack of parenthesis) and then `vari = ws.UsedRange.Value` (with no `ReDim` required)? Variants can hold arrays of values, and you don't need to dimension them before grabbing this array from a range. – John Coleman May 04 '21 at 18:33
  • I'm guessing that what you actually want to do is something like [this](https://stackoverflow.com/questions/175170/how-do-i-slice-an-array-in-excel-vba). Though if you just want to take the average of the first column, skip using an array and just use a `Range`. – BigBen May 04 '21 at 18:37
  • A transposed 2D array is still a 2D array, and you can't access a "row" or "column" of that array using something like `vari(1)` – Tim Williams May 04 '21 at 21:48

0 Answers0