I would use 5 different worksheets (and hide the worksheets if that's
important). I think it would make updating the table easier (in general).
But using 5 names each an array 91 by 100 worked ok for me. But I think that
you'll need xl2002+ for this. xl2k (and earlier had a limit of 5461 elements,
IIRC).
Option Explicit
Sub LoadVariablesIntoNames()
Dim myArray As Variant
Dim wCtr As Long
Dim wks As Worksheet
For wCtr = 1 To 5
Set wks = Worksheets("sheet" & wCtr)
myArray = wks.Range("a1").Resize(91, 100).Value
Names.Add Name:="Table" & wCtr, RefersTo:=myArray
Next wCtr
End Sub
Sub RetrieveValuesFromName()
Dim myArray(1 To 5) As Variant
Dim wCtr As Long
For wCtr = 1 To 5
myArray(wCtr) = Evaluate("table" & wCtr)
Next wCtr
'what was in sheet3, row 4, column 7?
MsgBox myArray(3)(4, 7)
End Sub
Jeff wrote:
Hi,
I have 5 tables of values. The 5 tables are 91x100 (Rows x Columns). The
tables are in excel. I want to write a macro - that uses the table. It
would be more convenient to put the 5 tables in a 5 x 91 x 100 matrix and
save as a variable - and get rid of the 5 tabs - to reduce the clutter.
How is this done in VBA?
Thanks
--
Dave Peterson
|