I am working with Excel 2002. I have some metadata that i want to store in a spreadsheet. I don't want the data to be exposed to the user. I found some information that lead me to believe that i could store an array in a name but i am not able to make it work the way i expect.

Test 1 -

Dim mydata(
Dim v As Varian

ReDim mydata(1, 1) ' mimic how the actual program will work
mydata(0, 0) = "ForbiddenRange
mydata(0, 1) = "$J$2:$L$98

' Store the array on the worksheet
ActiveWorkbook.ActiveSheet.Names.Add name:="a", RefersTo:=mydat

v = ActiveWorkbook.Names("a"

I expected v(0,0) = "ForbiddenRange" and v(0,1) = "$J$2:$L$98

Trying ?v(0,0) or ?v(0,1) causes a Type Mismatch error. Dumping v by itself i get: ?v ={"ForbiddenRange","$J$2:$L$98";#N/A,#N/A

The only way that I see to work with the data stored in v is as a single string which defeats the purpose of storing it in an array in the first place. How do I get the values back out of the name as an array

Test 2 -

It would be useful to store and retrieve an array of user defined type.

The following code example throws a compiler error at the line which performs RefersTo:data. The error dialog says: "Only user defined types in public object modules can be coerced to or from variant or passed to late bound functions"

This code is in a module and from what i can tell it is public.

Public Type mytes
name As Strin
area As Strin
count As Intege
End Typ

Public v As Varian

Dim data() As mytes

Public Sub tryit(
ReDim data(2

With data(0
.name = "forbidden
.area = "$J$2:$L$98
.count = 1
End Wit

With data(1
.name = "okForYou
.area = "$a$2:$b$98
.count = 2
End Wit

ActiveWorkbook.ActiveSheet.Names.Add name:="b", RefersTo:=dat

Set v = ActiveWorkbook.Names("b") ' trying to evaluate but since the code won't compile we don't get this far
End Su

Can someone tell me what i am missing here (other than possibly a REAL language ;). If this approach doesn't work in Excel can you maybe suggest another way to accomplish my goals here without resorting to writting the metadata to a shadow file or storing it in cells within a sheet

Thank

Mark

Bob Phillips gave some example code:

Public Sub Populate()
Dim aCollection
Dim i As Long

ReDim aCollection(0)
For i = 0 To 5
ReDim Preserve aCollection(i)
aCollection(i) = "i" & CStr(i)
Next i
ActiveWorkbook.Names.Add Name:="PersistentData",
RefersTo:=Join(aCollection)

End Sub

Public Sub Retrieve()
Dim aCollection
Dim i As Long

aCollection = Split(Evaluate(Names("PersistentData").RefersTo))
For i = LBound(aCollection) To UBound(aCollection)
Debug.Print aCollection(i)
Next i

End Sub


In your example, you see that the data is stored in the name as an excel
array. An excel array has no way of representing a UDT.

Another way to get the array back would be to use

Sub tester1()
ReDim mydata(1, 1) ' mimic how the actual program will work
mydata(0, 0) = "ForbiddenRange"
mydata(0, 1) = "$J$2:$L$98"
' Store the array on the worksheet.
ActiveWorkbook.ActiveSheet.Names.Add Name:="a", RefersTo:=mydata
v = Evaluate(ActiveWorkbook.Names("a").RefersTo)
Debug.Print v(1, 1), v(1, 2), v(2, 1), v(2, 2)
End Sub


--
Regards,
Tom Ogilvy


"mark" wrote in message
...
I am working with Excel 2002. I have some metadata that i want to store
in a spreadsheet. I don't want the data to be exposed to the user. I found
some information that lead me to believe that i could store an array in a
name but i am not able to make it work the way i expect.

Test 1 -

Dim mydata()
Dim v As Variant

ReDim mydata(1, 1) ' mimic how the actual program will work
mydata(0, 0) = "ForbiddenRange"
mydata(0, 1) = "$J$2:$L$98"

' Store the array on the worksheet.
ActiveWorkbook.ActiveSheet.Names.Add name:="a", RefersTo:=mydata

v = ActiveWorkbook.Names("a")

I expected v(0,0) = "ForbiddenRange" and v(0,1) = "$J$2:$L$98"

Trying ?v(0,0) or ?v(0,1) causes a Type Mismatch error. Dumping v by
itself i get: ?v ={"ForbiddenRange","$J$2:$L$98";#N/A,#N/A}

The only way that I see to work with the data stored in v is as a single
string which defeats the purpose of storing it in an array in the first
place. How do I get the values back out of the name as an array?

Test 2 -

It would be useful to store and retrieve an array of user defined type.

The following code example throws a compiler error at the line which
performs RefersTo:data. The error dialog says: "Only user defined types in
public object modules can be coerced to or from variant or passed to late
bound functions".

This code is in a module and from what i can tell it is public.

Public Type mytest
name As String
area As String
count As Integer
End Type

Public v As Variant

Dim data() As mytest

Public Sub tryit()
ReDim data(2)

With data(0)
.name = "forbidden"
.area = "$J$2:$L$98"
.count = 10
End With

With data(1)
.name = "okForYou"
.area = "$a$2:$b$98"
.count = 20
End With

ActiveWorkbook.ActiveSheet.Names.Add name:="b", RefersTo:=data

Set v = ActiveWorkbook.Names("b") ' trying to evaluate but since the
code won't compile we don't get this far.
End Sub


Can someone tell me what i am missing here (other than possibly a REAL
language ;). If this approach doesn't work in Excel can you maybe suggest
another way to accomplish my goals here without resorting to writting the
metadata to a shadow file or storing it in cells within a sheet?

Thanks

Mark