PDA

View Full Version : Solved: Defining a variable number of arrays?



bdl004
08-02-2010, 03:47 AM
Hi guys,

I am having some trouble coming up with a code to do compare sets of data against a reference set to choose appropriate running conditions for a fan. I have a variable number of parameters (usually around 4 but it changes) called Temperature, Pressure, VG, and TG (and more sometimes).

What I need to do is to declare a set of "n" arrays depending on the number of parameters. These arrays are 2 dimensional. For example, if there are 4 parameters, I need the program to define 4 arrays, 5 parameters, 5 arrays, etc. I do not want to use a 3d array because I have massive amounts of data (exceeds excel's memory -- I am running excel 2000).

Is this possible?

Thanks in advance!

GTO
08-02-2010, 04:29 AM
You may wish to look at a dynamic jagged-edge array. In essence, a horizintal array that you would size as needed, in which each element contains your 2D arrays.

By simple example, open a new wb.

Fill in some random vals on the sheets.

Lets say fill A1: C3 on sheet1...
... A1: C4 on Sheet2
...A1:E5 on Sheet3In a Standard Module:


Option Explicit

Sub exaJagged()
Dim wks As Worksheet
Dim SizeableArray_1D As Variant
Dim SizeableSubArray_2D
Dim i As Long

ReDim SizeableArray_1D(1 To ThisWorkbook.Worksheets.Count)

i = 0

For Each wks In ThisWorkbook.Worksheets

i = i + 1
SizeableSubArray_2D = _
wks.Range(wks.Range("A1"), _
wks.Cells(wks.Cells(wks.Rows.Count, "A").End(xlUp).Row, _
wks.Cells(1, wks.Columns.Count).End(xlToLeft).Column))
SizeableArray_1D(i) = SizeableSubArray_2D
Next
End Sub

If you step thru it with Locals displayed you can see it get filled.

For me at least, I've only used a jagged-edge a couple of times, and found myself wanting to stick a needle in my eye by the time I figured out looping/adding/reading the correct elements of the 'sub-array' (for lack of a better term). That said, once done, seemed very quick.

Hope that helps,

Mark

bdl004
08-02-2010, 05:26 AM
Wow I had never heard of that before. That is exactly what I need. Thanks a lot!