Saving Info to Matrix

J

Jeff

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
 
D

Dave Peterson

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
 
A

Alan Beban

Dave said:
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).
Click to expand...

That limit didn't apply to simply transferring arrays to worksheets or
vice versa.

Alan Beban
 
Top