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

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


.

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

Regards, Alfred

"Michael Tse" wrote in 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

Michael,

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

key = "=""0000304"""


--

John Green - Excel MVP
Sydney
Australia


"Michael Tse" wrote in 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

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" ???
???...
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


.

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" ¦b¶l¥ó
¤¤¼¶¼g...
Hi Michael. Use ' in front of the number - Excel handles it as a string.
Like: "'0000304"

Regards, Alfred

"Michael Tse" wrote in 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

John,

It solved the problem perfectly, thx a lot.

Cheers,
Michael
"John Green" ¦b¶l¥ó
¤¤¼¶¼g...
Michael,

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

key = "=""0000304"""


--

John Green - Excel MVP
Sydney
Australia


"Michael Tse" wrote in 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