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