Best practice questions, regarding variables and array / Array defined by variable

[spencer_time]

Hello,

I'm working on a large spreadsheet to help me automate crunching data at work.
I've decided to put sets of data into array's (is plural of array, just array?) and am unsure if my approach will slow the end product or cause some other undesired effect.
I, on a daily basis, compare spreadsheets that have 10 sets of raw data, 1 set of "zero" data, 1 set of ideal data, and 10 sets of corrected data. This however is the minimum, I have run the same thing with 96 raw data and 96 corrected data.

I want my spreadsheet I'm writing to be as versatile as possible, so I have a few questions.
1. Is there a way to have one of the dimensions of an array dependent on a variable? The way I show in my example doesn't seem to work.

Dim pts As Integer
pts = 1603
Dim variableArry(pts,11) As Variant

2. Will including more dimensioned arrays than needed noticeably slow my spreadsheet? If so, can I somehow generate them depending on the amount of data I import?(ex. instead of having rawData00-rawData99, only have enough array for the number of datasets I have.)
3. I'm aware that array can be made without the dimensions or lengths defined, is there a way to define the amount of dimensions, and lengths of some, but not all of them? The reason being, that I plan on having some of the elements in the array left blank. I want it to stay a 2D array, and always have 11 elements in the second diminision, but the first deminision
Something like:

Dim variableArry( ,11) As Variant

Instead of:

Dim variableArry() As Variant

Here is what I currently have, it seems unnecessary, and unprofessional. However, I am fine with overlooking that if there isn't a better way.

Const ColA = 0
Const ColB = 1
Const ColC = 2
Const ColD = 3
Const ColE = 4
Const ColF = 5
Const ColG = 6
Const ColH = 7
Const ColI = 8
Const ColJ = 9
Const ColK = 10
Const ColL = 11

Dim rawData00(1603, 11) As Variant
Dim rawData01(1603, 11) As Variant
Dim rawData02(1603, 11) As Variant
Dim rawData03(1603, 11) As Variant
Dim rawData04(1603, 11) As Variant
Dim rawData05(1603, 11) As Variant
Dim rawData06(1603, 11) As Variant
Dim rawData07(1603, 11) As Variant
Dim rawData08(1603, 11) As Variant
Dim rawData09(1603, 11) As Variant
Dim rawData10(1603, 11) As Variant
Dim rawData11(1603, 11) As Variant
Dim rawData12(1603, 11) As Variant
Dim rawData13(1603, 11) As Variant
Dim rawData14(1603, 11) As Variant
Dim rawData15(1603, 11) As Variant
Dim rawData16(1603, 11) As Variant
Dim rawData17(1603, 11) As Variant
Dim rawData18(1603, 11) As Variant
Dim rawData19(1603, 11) As Variant
Dim rawData20(1603, 11) As Variant
Dim rawData21(1603, 11) As Variant
Dim rawData22(1603, 11) As Variant
Dim rawData23(1603, 11) As Variant
Dim rawData24(1603, 11) As Variant
Dim rawData25(1603, 11) As Variant
Dim rawData26(1603, 11) As Variant
Dim rawData27(1603, 11) As Variant
Dim rawData28(1603, 11) As Variant
Dim rawData29(1603, 11) As Variant
Dim rawData30(1603, 11) As Variant
Dim rawData31(1603, 11) As Variant
Dim rawData32(1603, 11) As Variant
Dim rawData33(1603, 11) As Variant
Dim rawData34(1603, 11) As Variant
Dim rawData35(1603, 11) As Variant
Dim rawData36(1603, 11) As Variant
Dim rawData37(1603, 11) As Variant
Dim rawData38(1603, 11) As Variant
Dim rawData39(1603, 11) As Variant
Dim rawData40(1603, 11) As Variant
Dim rawData41(1603, 11) As Variant
Dim rawData42(1603, 11) As Variant
Dim rawData43(1603, 11) As Variant
Dim rawData44(1603, 11) As Variant
Dim rawData45(1603, 11) As Variant
Dim rawData46(1603, 11) As Variant
Dim rawData47(1603, 11) As Variant
Dim rawData48(1603, 11) As Variant
Dim rawData49(1603, 11) As Variant
Dim rawData50(1603, 11) As Variant
Dim rawData51(1603, 11) As Variant
Dim rawData52(1603, 11) As Variant
Dim rawData53(1603, 11) As Variant
Dim rawData54(1603, 11) As Variant
Dim rawData55(1603, 11) As Variant
Dim rawData56(1603, 11) As Variant
Dim rawData57(1603, 11) As Variant
Dim rawData58(1603, 11) As Variant
Dim rawData59(1603, 11) As Variant
Dim rawData60(1603, 11) As Variant
Dim rawData61(1603, 11) As Variant
Dim rawData62(1603, 11) As Variant
Dim rawData63(1603, 11) As Variant
Dim rawData64(1603, 11) As Variant
Dim rawData65(1603, 11) As Variant
Dim rawData66(1603, 11) As Variant
Dim rawData67(1603, 11) As Variant
Dim rawData68(1603, 11) As Variant
Dim rawData69(1603, 11) As Variant
Dim rawData70(1603, 11) As Variant
Dim rawData71(1603, 11) As Variant
Dim rawData72(1603, 11) As Variant
Dim rawData73(1603, 11) As Variant
Dim rawData74(1603, 11) As Variant
Dim rawData75(1603, 11) As Variant
Dim rawData76(1603, 11) As Variant
Dim rawData77(1603, 11) As Variant
Dim rawData78(1603, 11) As Variant
Dim rawData79(1603, 11) As Variant
Dim rawData80(1603, 11) As Variant
Dim rawData81(1603, 11) As Variant
Dim rawData82(1603, 11) As Variant
Dim rawData83(1603, 11) As Variant
Dim rawData84(1603, 11) As Variant
Dim rawData85(1603, 11) As Variant
Dim rawData86(1603, 11) As Variant
Dim rawData87(1603, 11) As Variant
Dim rawData88(1603, 11) As Variant
Dim rawData89(1603, 11) As Variant
Dim rawData90(1603, 11) As Variant
Dim rawData91(1603, 11) As Variant
Dim rawData92(1603, 11) As Variant
Dim rawData93(1603, 11) As Variant
Dim rawData94(1603, 11) As Variant
Dim rawData95(1603, 11) As Variant
Dim rawData96(1603, 11) As Variant
Dim rawData97(1603, 11) As Variant
Dim rawData98(1603, 11) As Variant
Dim rawData99(1603, 11) As Variant

Dim fixtureZero(1603, 11) As Variant

Dim idealData(1603, 11) As Variant

Dim corData00(1603, 11) As Variant
Dim corData01(1603, 11) As Variant
Dim corData02(1603, 11) As Variant
Dim corData03(1603, 11) As Variant
Dim corData04(1603, 11) As Variant
Dim corData05(1603, 11) As Variant
Dim corData06(1603, 11) As Variant
Dim corData07(1603, 11) As Variant
Dim corData08(1603, 11) As Variant
Dim corData09(1603, 11) As Variant
Dim corData10(1603, 11) As Variant
Dim corData11(1603, 11) As Variant
Dim corData12(1603, 11) As Variant
Dim corData13(1603, 11) As Variant
Dim corData14(1603, 11) As Variant
Dim corData15(1603, 11) As Variant
Dim corData16(1603, 11) As Variant
Dim corData17(1603, 11) As Variant
Dim corData18(1603, 11) As Variant
Dim corData19(1603, 11) As Variant
Dim corData20(1603, 11) As Variant
Dim corData21(1603, 11) As Variant
Dim corData22(1603, 11) As Variant
Dim corData23(1603, 11) As Variant
Dim corData24(1603, 11) As Variant
Dim corData25(1603, 11) As Variant
Dim corData26(1603, 11) As Variant
Dim corData27(1603, 11) As Variant
Dim corData28(1603, 11) As Variant
Dim corData29(1603, 11) As Variant
Dim corData30(1603, 11) As Variant
Dim corData31(1603, 11) As Variant
Dim corData32(1603, 11) As Variant
Dim corData33(1603, 11) As Variant
Dim corData34(1603, 11) As Variant
Dim corData35(1603, 11) As Variant
Dim corData36(1603, 11) As Variant
Dim corData37(1603, 11) As Variant
Dim corData38(1603, 11) As Variant
Dim corData39(1603, 11) As Variant
Dim corData40(1603, 11) As Variant
Dim corData41(1603, 11) As Variant
Dim corData42(1603, 11) As Variant
Dim corData43(1603, 11) As Variant
Dim corData44(1603, 11) As Variant
Dim corData45(1603, 11) As Variant
Dim corData46(1603, 11) As Variant
Dim corData47(1603, 11) As Variant
Dim corData48(1603, 11) As Variant
Dim corData49(1603, 11) As Variant
Dim corData50(1603, 11) As Variant
Dim corData51(1603, 11) As Variant
Dim corData52(1603, 11) As Variant
Dim corData53(1603, 11) As Variant
Dim corData54(1603, 11) As Variant
Dim corData55(1603, 11) As Variant
Dim corData56(1603, 11) As Variant
Dim corData57(1603, 11) As Variant
Dim corData58(1603, 11) As Variant
Dim corData59(1603, 11) As Variant
Dim corData60(1603, 11) As Variant
Dim corData61(1603, 11) As Variant
Dim corData62(1603, 11) As Variant
Dim corData63(1603, 11) As Variant
Dim corData64(1603, 11) As Variant
Dim corData65(1603, 11) As Variant
Dim corData66(1603, 11) As Variant
Dim corData67(1603, 11) As Variant
Dim corData68(1603, 11) As Variant
Dim corData69(1603, 11) As Variant
Dim corData70(1603, 11) As Variant
Dim corData71(1603, 11) As Variant
Dim corData72(1603, 11) As Variant
Dim corData73(1603, 11) As Variant
Dim corData74(1603, 11) As Variant
Dim corData75(1603, 11) As Variant
Dim corData76(1603, 11) As Variant
Dim corData77(1603, 11) As Variant
Dim corData78(1603, 11) As Variant
Dim corData79(1603, 11) As Variant
Dim corData80(1603, 11) As Variant
Dim corData81(1603, 11) As Variant
Dim corData82(1603, 11) As Variant
Dim corData83(1603, 11) As Variant
Dim corData84(1603, 11) As Variant
Dim corData85(1603, 11) As Variant
Dim corData86(1603, 11) As Variant
Dim corData87(1603, 11) As Variant
Dim corData88(1603, 11) As Variant
Dim corData89(1603, 11) As Variant
Dim corData90(1603, 11) As Variant
Dim corData91(1603, 11) As Variant
Dim corData92(1603, 11) As Variant
Dim corData93(1603, 11) As Variant
Dim corData94(1603, 11) As Variant
Dim corData95(1603, 11) As Variant
Dim corData96(1603, 11) As Variant
Dim corData97(1603, 11) As Variant
Dim corData98(1603, 11) As Variant
Dim corData99(1603, 11) As Variant

Thanks in advance for any advice.

 

[Eric W]

You can do something like this:

Dim pts as Integer, variableArray as Variant
pts = 1603
Redim variableArray(pts, 11)

As far as your other questions, having more space defined than you actually use isn't usually a problem, unless you exhaust the amount of space on your PC. But you shouldn't have performance issues.

I really wouldn't define 99 versions of the CorDataXX array. I'd make a 3-dimensional array like:

Dim corData(1 to 99, 1 to 1603, 1 to 11) as Variant

or use the Redim as shown before:

Redim corData(1 to ctr1, 1 to ctr2, 1 to 11)


My old CS teacher would certainly like how you defined ColA - ColL though. It might even be better if you used a name like:

Const custName = 1
Const custAddr1 = 2

etc. since once you remove it from the sheet, the column doesn't matter, but the type of data does.


P.S. The plural of array is arrays, no apostrophe.

 

[spencer_time]

Eric W. Thank you for your input. I have got a proof of concept of how I will make it work using a 4D array now, and it seems to be working.

Below is what I am working with so far:

Dim curSheet As Worksheet
Dim x As Integer
Dim pts As Integer, dataSet As Variant
'Dim dataSet(0, 11, 99, 3) As Variant
Dim dbval As Integer
'-----------------------------------------
' ** DIMINSIONS OF DATASET ARRAY **
' D1 of dataSet is number of points of measurement, skip
'       element 0(used for misc), 1 to [variable] pts is actual data.
' D2 of dataSet is columns of data (MISC, FREQ, S11_mag, S11_phase,
'       S21_mag, S21_phase, S12_mag, S12_phase, S22_mag, S22_phase,
'       S21_delta, S12_delta.
' D3 of dataSet is part number, skip element 0(used for misc), 1-99
'       samples of each data type (raw, zero, ideal, corrected).
' D4 defines data type, 0 = raw, 1 = zero, 2 = ideal, 3 = corrected.
'-----------------------------------------

Sub sht2ary()

pts = 1603
ReDim dataSet(pts, 11, 99, 3)

For Each curSheet In ActiveWorkbook.Worksheets

    'if current sheets name is "*WILDCARD* CEQ *WILDCARD*"
    If curSheet.Name Like "*CEQ*" Then

        'make element of array the name of a sheet
        dataSet(x, 11, 1, 1) = curSheet.Name
        dataSet(x, 10, 1, 1) = Mid(curSheet.Name, 6)
        dataSet(x, 9, 1, 1) = Mid(curSheet.Name, 1, 2)
        Debug.Print "sheet name = " + dataSet(x, 11, 1, 1)
        Debug.Print "dB value = " + dataSet(x, 10, 1, 1)
        Debug.Print "part number = " + dataSet(x, 9, 1, 1) + vbNewLine
        dbval = dataSet(x, 10, 1, 1)
        Debug.Print dbval * (dbval / dbval)
        Debug.Print x
        x = x + 1
        
    End If
Next curSheet

End Sub

 

[Eric W]

That seems to be a valid approach. Good luck! Let us know if you have any more questions.

 

[spencer_time]

Followup question. When defining the size of one of the dimensions of an array with a variable, can you do arithmetic at the same time?

So, instead of manually adjusting my pts variable to be one more than it really is like I was doing here

Dim pts As Integer, dataSet As Variant
pts = 1603
ReDim dataSet(pts , 11, 99, 3)

Instead have the pts variable actually be the correct number of points, and do arithmetic while defining the array size like the following:

Dim pts As Integer, dataSet As Variant
pts = 1601
ReDim dataSet(pts + 2 , 11, 99, 3)

Currently my main code isn't working, but I don't *think* that this is the cause (at least not the only cause, it doesn't start working when I change it back) but I would like to confirm that this works how I think it does before I spend too much time running in circles.

Thanks again.

 

[Fluff]

That works fine.

 

[mikerickson]

When defining the size of one of the dimensions of an array with a variable, can you do arithmetic at the same time?

What happened when you tried it?

When testing something new, its sometimes best to start by testing a small snippet of code rather than testing the change on the whole big procedure.

Just run this and see if it falls over.

Sub Test()
    Dim pts As Integer, dataSet As Variant
    pts = 1601
    ReDim dataSet(pts + 2 , 11, 99, 3)

    MsgBox dataSet(1,1,1,1)
End Sub

PS. The OP asks about best practices. Its a good idea to specify the lower bound of and array when dimensioning.

ReDim dataSets(0 to pts+2, 0 to 11, 0 to 99, 0 to 3)

 

[spencer_time]

This works just fine, which I guess confirms that I have some other problem with my final spreadsheet.

PS. The OP asks about best practices. Its a good idea to specify the lower bound of and array when dimensioning.

Thanks for the advice.

That works fine.

Thank you for further confirming for me.