What Qualifies as a "Numeric Expression"?

G

Gordon Bentley-Mix

G'day!

In the typical unhelpful way of the VBA Help, the topic on the IsNumeric
function makes reference to "numeric expressions". The Help then goes on to
define a "numeric expression" as:

Any expression that can be evaluated as a number. Elements of an expression
can include any combination of keywords, variables, constants, and operators
that result in a number.

Joy! I feel so enlightened...

Does anybody know which "combination of keywords, variables, constants,
and operators" will "result in a number"? For example, I have learned from
experience that a value formatted as standard currency (e.g. "$#,###.00")
returns True, but a value formatted as a percentage (e.g. "#.00%" or one
created using the FormatPercent function) returns False. Of course some
values I would expect to return False - a phone number formatted like
"1-800-555-1212" for example - but if a dollar value returns True then why
not a percentage?

Actually, it's the percentage that's giving me the most trouble. I've had to
write a custom function to "extract" the number/s from the value and then
test this value using IsNumeric and re-apply the formatting to the extracted
value. But it seems a bit silly to have to do this - especially in light of
the built-in FormatPercent function and moreso when IsNumeric is fine with
dollar values. (And I see now that I could just modify this function to be a
sort of "IsPercent" function and then use FormatPercent with this value.)

And just by way of background, what I'm trying to do is validate a value in
the Exit event of a TextBox. However, if I format the value using
FormatPercent then set focus back to the TextBox and exit it again, when the
Exit code triggers the second time, IsNumeric doesn't work. Does anyone have
any suggestions, or am I stuck with my custom "IsPercentage" function?

--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.
 
T

Tony Jollans

The Help is accurate, but, as with most help, uninformative. One reason is
that what actually constitutes a number is, to some extent, dependent upon
the language and the operating system. You might find "$1" to be numeric; I
don't because I'm in the UK; we will both find "1D1", say, to be numeric.
IsNumeric is, for most purposes, and certainly for purposes of dealing with
user input, totally ineffective. Write your own function (which you already
have) to suit your own purposes and forget you ever heard of IsNumeric: it
really isn't worth the effort of trying to understand it.
 
G

Gordon Bentley-Mix

Thanks Tony. I thought as much. I'll limit my use of IsNumeric to those
instances when I want to check when a value is _just_ a number without any
special formatting.
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.


Tony Jollans said:
The Help is accurate, but, as with most help, uninformative. One reason is
that what actually constitutes a number is, to some extent, dependent upon
the language and the operating system. You might find "$1" to be numeric; I
don't because I'm in the UK; we will both find "1D1", say, to be numeric.
IsNumeric is, for most purposes, and certainly for purposes of dealing with
user input, totally ineffective. Write your own function (which you already
have) to suit your own purposes and forget you ever heard of IsNumeric: it
really isn't worth the effort of trying to understand it.


--
Enjoy,
Tony

www.WordArticles.com

Gordon Bentley-Mix said:
G'day!

In the typical unhelpful way of the VBA Help, the topic on the IsNumeric
function makes reference to "numeric expressions". The Help then goes on
to
define a "numeric expression" as:

Any expression that can be evaluated as a number. Elements of an
expression
can include any combination of keywords, variables, constants, and
operators
that result in a number.

Joy! I feel so enlightened...

Does anybody know which "combination of keywords, variables, constants,
and operators" will "result in a number"? For example, I have learned from
experience that a value formatted as standard currency (e.g. "$#,###.00")
returns True, but a value formatted as a percentage (e.g. "#.00%" or one
created using the FormatPercent function) returns False. Of course some
values I would expect to return False - a phone number formatted like
"1-800-555-1212" for example - but if a dollar value returns True then why
not a percentage?

Actually, it's the percentage that's giving me the most trouble. I've had
to
write a custom function to "extract" the number/s from the value and then
test this value using IsNumeric and re-apply the formatting to the
extracted
value. But it seems a bit silly to have to do this - especially in light
of
the built-in FormatPercent function and moreso when IsNumeric is fine with
dollar values. (And I see now that I could just modify this function to be
a
sort of "IsPercent" function and then use FormatPercent with this value.)

And just by way of background, what I'm trying to do is validate a value
in
the Exit event of a TextBox. However, if I format the value using
FormatPercent then set focus back to the TextBox and exit it again, when
the
Exit code triggers the second time, IsNumeric doesn't work. Does anyone
have
any suggestions, or am I stuck with my custom "IsPercentage" function?

--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post
all
follow-ups to the newsgroup.

 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top