PDA

View Full Version : Solved: Range object Id property



Digita
09-11-2007, 12:04 AM
Hi All,

In a new workbook I have:


Sub InputData()
With Sheet1
.Range("A1").ID = "xyz"
.Range("A2").ID = 2 ^ 2 * 5
.Range("A3").ID = .Range("A2").ID + 1
.Range("A4").ID = Format(Now, "medium date")
End With
End Sub

The above code stores values in range A1:A4. The following code displays the values in A1, A3 & A4.


Sub ShowValue()
With Sheet1
MsgBox .Range("a1").ID & " " & .Range("a3").ID & " " & .Range("a4").ID
End With
End Sub

I now save & close this workbook. Then I reopen same & run the ShowValue routine. All the stored values disappear. Memorywise, does the Id property work on the same concept as setting public variables in a project?

Thanks in advance for your input.

Kind regards


KP

mdmackillop
09-11-2007, 12:39 AM
According to VBA help, ID has a particular purpose. Is that the purpose you are using it for?

Bob Phillips
09-11-2007, 01:12 AM
Doesn't store anything in A1:A4 for me, as I expected it not to.

Digita
09-11-2007, 06:47 PM
Hi Md & XLD,

Thanks for your responses. I did a bit of study of the ID property and testing it out on XL 2000 which stores data in range A1:A4. The purpose is to find out if there is any other way we can record a piece of information a form which is invisible to novice users (ie without hiding sheets or range or turning the font color to match the background).

I guess the use of Id property in this exercise is not feasible.

Thanks & regards


KP

Ivan F Moala
09-11-2007, 08:51 PM
You can still use this method BUT you need to save the file as an htm file and load it in Excel as htm file.

Digita
09-11-2007, 09:10 PM
Thanks Ivan. Nice photo.

Bob Phillips
09-12-2007, 12:21 AM
Here is one way



With ActiveWorkbook
.Names.Add Name:="_somename", RefersTo:="some value"
.Names("_somename").Visible = False
End With

mdmackillop
09-12-2007, 12:37 AM
A slight adaption of XLD's code to store and retrieve multiple values
Option Explicit

Sub test()
With ActiveWorkbook
.Names.Add Name:="_somename", RefersTo:="Test1,Test2,Test3,Test4"
.Names("_somename").Visible = False
End With
End Sub


Sub GetData()
Dim arr
arr = Split(Evaluate("_somename"), ",")
Range("A1").Resize(UBound(arr) - LBound(arr) + 1, 1) = Application.Transpose(arr)
End Sub

mdmackillop
09-12-2007, 12:44 AM
Hi Bob,
Can Names be made hidden/visible other than by code?

Bob Phillips
09-12-2007, 12:57 AM
I think not Malcolm, certainly I have not found it.

Digita
09-12-2007, 08:08 PM
Hi again Md & XLd,

This is really an ingenious idea that you guys have come up with. Thank you so much for your inputs.

Kind regards


KP