Help on Code Snippet Error {Val function}

[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

 

[joeu2004]

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?

Might be a coincidence. What are the values of iFirstDataRow and iTestCol
when you get the error?

 

[Tim Childs]

1 and 58

I thought the E04 was generating an exponential or something?

bw, Tim

 

[joeu2004]

I thought the E04 was generating an exponential or something?

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))," ",","))

 

[Tim Childs]

hi

thks for that, and the suggested workaround. Very helpful
Tim

 

[joeu2004]

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

RTrim is not necessary. Simply:

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

(Hmm, also corrects a copy-and-paste typo in the original expression.)

 

[Tim Childs]

Jo
thanks for your help
Tim

RTrim is not necessary. Simply:

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

(Hmm, also corrects a copy-and-paste typo in the original expression.)