Excel VBA How to populate a multi-dimensional (3d) array with values from multiple excel ranges?

Question

I'm trying to populate a 3d array from data stored in an Excel worksheet. I'm hoping I just don't know the syntax, but it may not be possible to 'batch' assign values to 3D arrays like you can with 2D arrays. Such as...

Dim 2DArray() As Variant
2DArray = Range(A1:C10)

This is so clean and efficient I was hoping for something equivalent, as opposed to using 3 nested for-next loops to populate the data one value at a time.

For background this is to lookup/interpolate a gas compressibility factor Z, which is dependent on specific gravity, pressure and temperature. I have 6 tables (really just named ranges) for 6 different values of specific gravity. Each table has pressure and temperature cross-tabulated with Z factor as the datapoint. Here is the code I have (which doesn't work):

Sub Array3DTest()
    Dim ZTables() As Variant
    ReDim ZTables(1 to 6, 1 to 15, 1 to 9) 
    '6 specific gravity tables, '15 pressure indices, 9 temp indices

    'Here is my feeble attempt to guess the syntax...
    'ZTables = ("tblZFactorSG1", "tblZFactorSG2", "tblZFactorSG3",
    '              "tblZFactorSG4", "tblZFactorSG5", "tblZFactorSG6")

End Sub

To clarify, tblZFactorSG1...SG2...SG3...etc are the 6 named ranges with the cross-tabbed Z factors. All are 15 rows (pressure) by 9 columns (temperature). The last line is commented out because VBA doesn't like the syntax, but maybe you can see what I'm trying to do here. I'm trying to assign the data to the 3Darray in chunks of 15x9. tblZFactorSG1 contains the values that should go in ZTables(1, 1 to 15, 1 to 9). tblZFactorSG2 contains the values that should go in ZTables(2, 1 to 15, 1 to 9).

Thanks in advance for any help.

You'll either have to settle for an array of 6 2-d arrays (each extracted using the .Value approach) or create the 3-d array "manually" using loops. There's no built-in way to do what you want. — Tim Williams
– Tim Williams, Commented May 27, 2014 at 6:11
I think an array of 6 2-d arrays would be useful too. Called a jagged array I think. Would you mind explaining/showing code for the '.Value' approach you mentioned? Thanks for the clarification, Tim. — Chris
– Chris, Commented May 27, 2014 at 12:02

Tim Williams · Accepted Answer · 2014-05-27 16:07:04Z

7

Sub ArrayOfArrays()

    Dim ZTables(1 to 6) As Variant, x as Long, ranges

    ranges = Array("tblZFactorSG1", "tblZFactorSG2", "tblZFactorSG3", _
                 "tblZFactorSG4", "tblZFactorSG5", "tblZFactorSG6")

    For x=1 to 6
        ZTables(x) = ActiveSheet.Range(ranges(x-1)).Value  
    Next x

End Sub

answered May 27, 2014 at 16:07

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

2 Comments

Chris Over a year ago

Works like a charm. Thanks again Tim. Unfortunately I don't have enough credentials to uprate your answer.

redOctober13 Over a year ago

Just to add a note in case this trips anyone else up, you'll access values in ZTables with ZTables(x)(y,z) and not ZTables(x,y,z) (which is what I first tried).

Collectives™ on Stack Overflow

Excel VBA How to populate a multi-dimensional (3d) array with values from multiple excel ranges?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related