Named Array

[Jag Man]

I wish to programatically save data associated with particular Worksheet and
use in in other places in the Workbook.
With non-array data, this can be done using Name. E.G.,
Sub mySub()
Dim x As Variant
x = 345.678
y = 1234.5678
Worksheets("Sheet1").Names.Add Name:="SavedNumber", RefersTo:=x
Worksheets("Sheet2").Names.Add Name:="SavedNumber", RefersTo:=y
End Sub

Sub anotherSub()
Sheet1.Range("a1:a1").Value = [Sheet1.SavedNumber]
Sheet1.Range("a2:a2").Value = [Sheet2.SavedNumber]
End Sub

This works just fine. Note that I use the same name, SavedNumber, overloaded
for each Worksheetsheet, making it
easy to write general functions.

Now, back to the array. If I try to use arrays LOCAL to specific Worksheets,
it fails. If I make a global array, it works.
That is, this fails:
Sub ArrayToName()
Dim MyArray(1 To 3)
MyArray(1) = 1.1
MyArray(2) = 2.2
MyArray(3) = 3.3
Worksheets("Sheet1").Names.Add Name:="SavedArray", RefersTo:=MyArray
End Sub

Sub UseArray()
Dim i As Integer
Dim tmpStr As String
Dim x As Double
Dim MyArray As Variant

MyArray = [Sheet1.SavedArray]

For i = 1 To 3
tmpStr = "a" & CStr(i + 8)
x = MyArray(i)
Sheet2.Range(tmpStr).Value = x
Next i

End Sub

However, if I remove the Worksheets("Sheet1") and the Sheet1. it works.

So, is this a VBA quirk, IOW, and undocumented "feature"?

TIA

Ed

 

[Dave Peterson]

Try:

MyArray = [Sheet1!SavedArray]

When I'm having trouble with names, I like to use Jan Karel Pieterse's (with
Charles Williams and Matthew Henson) utility "Name Manager.xla" from
http://www.bmsltd.co.uk/mvp/

It really makes life a lot simpler.

(you could see how that name was really defined. Yeah, you could have gone to
Insert|Name|Paste, but Jan Karel's (et al) utility is a lot nicer.)

I wish to programatically save data associated with particular Worksheet and
use in in other places in the Workbook.
With non-array data, this can be done using Name. E.G.,
Sub mySub()
Dim x As Variant
x = 345.678
y = 1234.5678
Worksheets("Sheet1").Names.Add Name:="SavedNumber", RefersTo:=x
Worksheets("Sheet2").Names.Add Name:="SavedNumber", RefersTo:=y
End Sub

Sub anotherSub()
Sheet1.Range("a1:a1").Value = [Sheet1.SavedNumber]
Sheet1.Range("a2:a2").Value = [Sheet2.SavedNumber]
End Sub

This works just fine. Note that I use the same name, SavedNumber, overloaded
for each Worksheetsheet, making it
easy to write general functions.

Now, back to the array. If I try to use arrays LOCAL to specific Worksheets,
it fails. If I make a global array, it works.
That is, this fails:
Sub ArrayToName()
Dim MyArray(1 To 3)
MyArray(1) = 1.1
MyArray(2) = 2.2
MyArray(3) = 3.3
Worksheets("Sheet1").Names.Add Name:="SavedArray", RefersTo:=MyArray
End Sub

Sub UseArray()
Dim i As Integer
Dim tmpStr As String
Dim x As Double
Dim MyArray As Variant

MyArray = [Sheet1.SavedArray]

For i = 1 To 3
tmpStr = "a" & CStr(i + 8)
x = MyArray(i)
Sheet2.Range(tmpStr).Value = x
Next i

End Sub

However, if I remove the Worksheets("Sheet1") and the Sheet1. it works.

So, is this a VBA quirk, IOW, and undocumented "feature"?

TIA

Ed

 

[Jag Man]

Thanks, Dave. It worked! Strangly, though, that's what I tried first and it
failed. Must have had another problem
active at that time.

I'll take a look at the like. Thanks!

Ed Sowell