Help on Code Snippet Error {Val function}

  • Thread starter Thread starter Tim Childs
  • Start date Start date
T

Tim Childs

Hi

I have a test on a cell which is used on a large block of data that can
contain any items e.g. text numbers, formulae etc as follows

Val(.Cells(iFirstDataRow, iTestCol))

When I use it I get an overflow error when the cell contains ("13 E04 01")

When I change the text value
?Val("13 G04 01")
returns 13

What is being computed in the overflow-errored case?

Thanks for any help

Tim
 
Tim Childs said:
I have a test on a cell which is used on a large block of data that can
contain any items e.g. text numbers, formulae etc as follows
Val(.Cells(iFirstDataRow, iTestCol))

When I use it I get an overflow error when the cell contains ("13 E04 01")
When I change the text value
?Val("13 G04 01")
returns 13

What is being computed in the overflow-errored case?
Click to expand...

Might be a coincidence. What are the values of iFirstDataRow and iTestCol
when you get the error?
 
Tim Childs said:
I thought the E04 was generating an exponential or something?
Click to expand...

Almost right.

I did not notice the difference between "13 E04 01" and "13 G04 01".

Note that Val("13 45") is interpreted as Val("1345"). That is, Val ignores
spaces.

So the problem is: Val interprets "13 E04 01" as "13E401". That is indeed
an overflow insofar as it exceeds 1.79769313486232E+308 = (2^1023 -
2^(1023-53))*2.

If you want Val to stop on the first interstitial space, try:

Val(Replace(LTrim(RTrim(.Cells(iFirstDataRow, iTestCol))," ",","))
 
After-thought.... I said:
Val(Replace(LTrim(RTrim(.Cells(iFirstDataRow, iTestCol))," ",","))
Click to expand...

RTrim is not necessary. Simply:

Val(Replace(LTrim(.Cells(iFirstDataRow, iTestCol))," ",","))

(Hmm, also corrects a copy-and-paste typo in the original expression.)
 
Jo
thanks for your help
Tim

joeu2004 said:
RTrim is not necessary. Simply:

Val(Replace(LTrim(.Cells(iFirstDataRow, iTestCol))," ",","))

(Hmm, also corrects a copy-and-paste typo in the original expression.)
Click to expand...
 
Back
Top