Printable View
Hello VBAExpressers,
I need to convert a number to a string. VBA Cstr function would normally handle this chore. However, my case is a bit more complex. I want the converted string to have a leading zero eg: from 123456 (number) to 0123456 (string).
My first attempt to test the output (see the following script) doesn?t work out.
Any idea? Will appreciate your help.Code:Msgbox IsNumeric(Cstr(?0? & 123456))
Thanks in advance & regards
kp
How about:
[vba]MsgBox "0" & CStr(123456)[/vba]
HTH,
[VBA]MsgBox Format(123456, "0000000")[/VBA]
Thanks guys for your quick responses. However, after adding the leading zero to a number I want to force VBA to think that it is a string (hence the Isnumeric test). Hope this would clarify my question.
Both posted codes return strings. Could you post some inputs values and desired results?
First of all, thanks so much for your patience mikerickson and my sincere apology for not making my question clearer. Because VBA automatically discards the zero in front of a number, I have to convert a number into a string and dim it as a string in a sub. This is where I get stuck.
If Cstr converts a number to a string, then the boolean test Isnumeric(Cstr(123456)) should return False. However, the result does not turn out that way when I run the following code. [VBA]MsgBox IsNumeric(Cstr(123456)) [/VBA]
Any idea why?
Thanks again.
Regards
kp
The string "123456" is a numeral, the string representation of a number. Hence, it is a numeric string.
Q.E.D. IsNumeric("123456") = True
Whereas =ISNUMBER(TEXT(1234,"@")) returns false. Where is the consistency in that?
"123456" is numeric, it is not a number.
The spreadsheet function ISNUMBER is not the identical equivalent of VB's IsNumeric.
IsNumeric(Format(1234, "@")) = True
That is semantics, is not a number numeric then?Quote:
"123456" is numeric, it is not a number.
Clearly it is not, but in what way should it be, why is it different. Your reasoning for IsNumeric returning trueQuote:
The spreadsheet function ISNUMBER is not the identical equivalent of VB's IsNumeric.
cannot be used if ISNUMBER doesn't do the same. TEXT(1234,"@") is just as much a string/text representation of a number as your VBA example.Quote:
The string "123456" is a numeral, the string representation of a number. Hence, it is a numeric string.
Q.E.D. IsNumeric("123456") = True
I for one can think of no reasonable argument for them behaving differently. To me, both should examine a string to determine whether it is actually a number, obviously 1234 is a number, but is "1234"?
Yes, both the number 2 and the string "2" are numeric. Sytnax and sematics are often partners.Quote:
Originally Posted by xld
I'm not arguing that MicroSoft made wise decisions about how ISNUMBER and IsNumeric should act. I applaud their use of English to put memnotics into the function names that give a reminders of their functionality.Quote:
Clearly it is not, but in what way should it be, why is it different. Your reasoning for IsNumeric returning true
ISNUMBER returns TRUE when the argument is a number, but not a numeric string.
IsNumeric returns True when the argument is a numeric variant.
Making this small distinction helps me remember the limitations of both functions.
(For some reason, looking at the way MS treats IsNumeric makes data type Date clearer to me.)
Oh that MS were so rational in the way that they implement across the board. The truth is that it is all done by devolved teams and the cross-checking is lamentably lacking, so they implemented different things. If it were rationally determined, it again raises my question, what is the reason for determining that Excel and VBA need different functions?
Hi Guys,
Yeah, it is sometimes mind bogging to understand the logic in VBA programming :banghead:Quote:
123456" is numeric, it is not a number
Really appreciate your responses.
Regards and thanks
kp