G
Guest
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
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