Storing string in names

M

Michael Tse

Hi,
I stored some invisible data in the workbook by using the names. It
works perfectly in most case. But when I assigned a string that only
contains numeric chars to a name. It comes out with an integer and the chars
has been changed! (Just like when u type "0001" into a cell in excel it
auto-format and change it to "1")
Anyone knows how to prevent the auto-format happened in the content of
names? Thx.
Here is the quote of my code:

Dim key as string
key = "0000304"
ThisWorkbook.Names.Add Name:="key", RefersTo:=key, Visible:=False
MsgBox [key]
'I want "0000304" but it gave "304"


Thanks a lot

Michael
 
J

Jan Karel Pieterse

Hi,

All you can do is add a letter to the string, e.g.:

Dim key As String
key = "00001"
ThisWorkbook.Names.Add "key", "A" & key, False
MsgBox ThisWorkbook.Names("key")

But the name will then contain this:

="A00001"

So you will need to strip away the unneeded junk to get
your numbercode back.

Why are you storing things in names?
You could also use variables declared outside procedures
(at the top of a module).

Regards,

Jan Karel Pieterse
Excel TA/MVP
 
A

Alfred B

Hi Michael. Use ' in front of the number -> Excel handles it as a string.
Like: "'0000304"

Regards, Alfred
 
J

John Green

Michael,

You could do it this way, which specifies that your value is a string:

key = "=""0000304"""
 
M

Michael Tse

Thanks for the idea. This is a pratical approach and I will take it if there
is no other better approach. (Since I still hope the string can be read and
written directly)

I use names instead of variables since the data need to be persist in the
workbook after it
is saved. U know, variables will be lost after the workbook is closed.

Michael

Jan Karel Pieterse said:
Hi,

All you can do is add a letter to the string, e.g.:

Dim key As String
key = "00001"
ThisWorkbook.Names.Add "key", "A" & key, False
MsgBox ThisWorkbook.Names("key")

But the name will then contain this:

="A00001"

So you will need to strip away the unneeded junk to get
your numbercode back.

Why are you storing things in names?
You could also use variables declared outside procedures
(at the top of a module).

Regards,

Jan Karel Pieterse
Excel TA/MVP
-----Original Message-----
Hi,
I stored some invisible data in the workbook by using the names. It
works perfectly in most case. But when I assigned a string that only
contains numeric chars to a name. It comes out with an integer and the chars
has been changed! (Just like when u type "0001" into a cell in excel it
auto-format and change it to "1")
Anyone knows how to prevent the auto-format happened in the content of
names? Thx.
Here is the quote of my code:

Dim key as string
key = "0000304"
ThisWorkbook.Names.Add Name:="key", RefersTo:=key, Visible:=False
MsgBox [key]
'I want "0000304" but it gave "304"


Thanks a lot

Michael


.
Click to expand...
Click to expand...
 
M

Michael Tse

Thx Alfred.
I set added single quote in front of the number but it returned the
string with the quote as well when I retrieve the data from the name.
Cheers,
Michael

Alfred B said:
Hi Michael. Use ' in front of the number -> Excel handles it as a string.
Like: "'0000304"

Regards, Alfred

Michael Tse said:
Hi,
I stored some invisible data in the workbook by using the names. It
works perfectly in most case. But when I assigned a string that only
contains numeric chars to a name. It comes out with an integer and the chars
has been changed! (Just like when u type "0001" into a cell in excel it
auto-format and change it to "1")
Anyone knows how to prevent the auto-format happened in the content of
names? Thx.
Here is the quote of my code:

Dim key as string
key = "0000304"
ThisWorkbook.Names.Add Name:="key", RefersTo:=key, Visible:=False
MsgBox [key]
'I want "0000304" but it gave "304"


Thanks a lot

Michael
Click to expand...
Click to expand...
 
M

Michael Tse

John,

It solved the problem perfectly, thx a lot.

Cheers,
Michael
John Green said:
Michael,

You could do it this way, which specifies that your value is a string:

key = "=""0000304"""


--

John Green - Excel MVP
Sydney
Australia


Hi,
I stored some invisible data in the workbook by using the names. It
works perfectly in most case. But when I assigned a string that only
contains numeric chars to a name. It comes out with an integer and the chars
has been changed! (Just like when u type "0001" into a cell in excel it
auto-format and change it to "1")
Anyone knows how to prevent the auto-format happened in the content of
names? Thx.
Here is the quote of my code:

Dim key as string
key = "0000304"
ThisWorkbook.Names.Add Name:="key", RefersTo:=key, Visible:=False
MsgBox [key]
'I want "0000304" but it gave "304"


Thanks a lot

Michael
Click to expand...
Click to expand...
 
Top