Declaring multidimensional array size through a variable

Question

I do have a large data set which has hourly data for some amount of years: I want to make a 4 dimensional array which has the following form:

To get the amount of years i have to find the last observed data and then compare it to the first one, to do so i need the amount of used rows.

Dim YearAmount As Integer

Const Lastrow As Long = Sheets(1).Cells(Sheets(1).Rows.Count,"A").End(xlUp).Row

'This returns an error, saying "Constant expression required"

YearAmount=Datediff("yyyy", Range("A2").Value , Range("A" & Lastrow ).Value) + 1

Dim Data(1 to YearAmount,1 to 12,1 to 31,0 to 23) as Double

I don't know how to get arround properly dimensioning my Data array, should i initialize it as a variant array and then use the ReDim method?, and if so, how would i go about resizing to more dimensions?

Trying to declare LastRow and YearAmount as constants didn't work, since vba yells that i'm trying to set a function return value as the constant, this I do not know how to get around.

Scott Craner · Accepted Answer · 2018-01-24 14:35:01Z

3

First delare the array then redim it to the size.

Dim Data() as Double
Redim Data(1 to YearAmount,1 to 12,1 to 31,0 to 23) as Double

The dim statement can be with the other declarations.

answered Jan 24, 2018 at 14:35

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

alosc Over a year ago

Thanks, i do not know why i was under the impression that ReDim didn't work when working with multidimensional arrays

Scott Craner Over a year ago

You are thinking of Redim Preserve which only allows the expansion of the last dimension.

Collectives™ on Stack Overflow

Declaring multidimensional array size through a variable

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related