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

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

Dave Peterson wrote:
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).

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

Alan Beban

Thanks for the memory jog.

But it did effect =index(), IIRC?????



Alan Beban wrote:

Dave Peterson wrote:
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).

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

Alan Beban

--

Dave Peterson

Dave Peterson wrote:
Thanks for the memory jog.

But it did effect =index(), IIRC?????

Yes

Alan Beban