Store hidden information in a spreadsheet
Hi Remy,
I can create a name and actually just assign a value to it.
In which case, why not additionally hide the name, e.g.:
'=============
Public Sub Tester()
ActiveSheet.Names.Add _
Name:="Total", _
RefersTo:=ActiveSheet.Range("A1").Value, _
Visible:=False
End Sub
'<<=============
---
Regards,
Norman
"Remy" wrote in message
ups.com...
Thanks all to for the help. A hidden sheet is a good idea. Not sure
what you mean by doing it manually with VBE? Would you just create a
code module and write the data in there?
I actually found even one more way, seems like the most fitting, but
not sure if it has some major drawbacks:
I can create a name and actually just assign a value to it. It then
overwrites the RefereTo property and it seems to store it just fine. I
tried it in Excel 2000 and 2003. Does anyone see a major drawback here?
'Create the name
ActiveSheet.Names.Add Name:="Total",
RefersTo:=ActiveSheet.Range("A1")
'Then set the value to something
ActiveWorkbook.Names("Total").Value = "This is a test"
'List all the names with the values
row = 10
For Each n In ActiveWorkbook.Names
Cells(row, 1) = n.Name
Cells(row, 2) = " " & n.RefersTo
Cells(row, 3) = n.Value
row = row + 1
Next n
If you run that, you can see that the value is set to "This is a
test". And that stays even after a save and reopen.
Anyone sees any drawbacks (besides not using it exactly according to
the MS intention)?
Cheers
Remy
|