VBA - Why IsNumeric is not evaluating correctly

Question

I have following code in VBA to evaluate a variable lngPNumber to send the "correct" value to a WorksheetFunction.vLookup function:

Dim lngPNumber As Variant
lngPNumber = ActiveSheet.Cells(objInitialCell.Row, INT_ACCP_COL_PNUMBER).Value
        If IsNumeric(lngPNumber) = False Or CDbl(lngPNumber) <> Round(CDbl(lngPNumber)) Then
        lngPNumber = CStr(ActiveSheet.Cells(objInitialCell.Row, INT_ACCP_COL_PNUMBER).Text)
        End If

lngPNumbercan be:

an integer value (f.i. 4111)
a float value (41.111111)
or just a string value (normally "xxxx" but can also be filled with strings like "asdasd", "dasdsadasd", etc...)

In the last both cases, I want to send the Cell Text and not the Cell Value where lngPNumberis obtained.

However, I get a Type Missmatch error if the value is a string like in the last example in the list. Any help?

The If will try to resolve both sides of the Or regardless if the first is false or true, so `CDbl(lngPNumber)` will error if `lngPNumber` is text. — Scott Craner, Dec 11 '17 at 17:48
Explanation [here](https://stackoverflow.com/questions/24641923/vba-short-circuit-and-alternatives) — Taelsin, Dec 11 '17 at 17:51
Here is another example: https://stackoverflow.com/questions/47620201/how-does-and-function-work-in-vba/47620414#47620414 — Scott Craner, Dec 11 '17 at 18:03

score 6 · Accepted Answer · answered Dec 11 '17 at 17:51

6

The If will try to resolve both sides of the Or regardless if the first is false or true, so CDbl(lngPNumber) will error if lngPNumber is text.

So split them into two.

Dim lngPNumber As Variant
lngPNumber = ActiveSheet.Cells(objInitialCell.Row, INT_ACCP_COL_PNUMBER).Value
        If Not IsNumeric(lngPNumber) Then
            lngPNumber = CStr(ActiveSheet.Cells(objInitialCell.Row, INT_ACCP_COL_PNUMBER).Text)
        ElseIF CDbl(lngPNumber) <> Round(CDbl(lngPNumber)) Then
            lngPNumber = CStr(ActiveSheet.Cells(objInitialCell.Row, INT_ACCP_COL_PNUMBER).Text)     
        End If

Now the second will fire only if lngPNumber is a number.

answered Dec 11 '17 at 17:51

Scott Craner

148,073
10
49
81

Nice answer. Wouldn't it be better to nest the second condition (assuming you change the first check to `If IsNumeric(lngPNumber)...`)? That way it will only get checked if it is numeric thus reducing the number of overall checks. – Joseph Wood Dec 11 '17 at 17:54
You still will need the Else to do the same thing @JosephWood as the op wants the negative and when positive the check for integer. – Scott Craner Dec 11 '17 at 17:56
Thanks for the fast, clear and working reply. I have to admit that I am not good at VBA at all, and seems that coming from other languages does not necessarelly help here :) I totally assumed the Or conditions behave like many other languages I have used, but it seems this is not the case. Tnaks also to @Taelsin for directing to a clear explanation of why this is happening. – agustin Dec 11 '17 at 18:00
@ScottCraner I tried to figure it out why in both true and false parts of `If` is **the same expression**? What's the point? – JohnyL Dec 11 '17 at 18:34
@JohnyL I agree, there really is no difference here between `.Value` and `.Text` Really the only thing the OP needs it `LngPNumber = .Value` an avoid the whole IF completely, but I wanted to show how to overcome the problem. – Scott Craner Dec 11 '17 at 18:37
1

@ScottCraner Take a closer look at your code: no matter whether `lngPNumber` is numeric or not you assign it the same `CStr(...)`. So, what's the trick? :) – JohnyL Dec 11 '17 at 18:39
No If the lngpnumber is not numeric or if it is numeric and the number is not equal to the integer I assign it to a string. If it is numeric and the number is equal to its integer than it stays a number. @JohnyL – Scott Craner Dec 11 '17 at 18:43
@ScottCraner Then what's the difference from OP's code? You just divided three conditions into two parts, because `CStr(..)` is the same. – JohnyL Dec 11 '17 at 18:51
@JohnyL The difference is that the OP was getting an error because when the variable was a text string `xxxx` it was trying to convert that to a double with `CDbl(lngPNumber)` vba resolves all criteria on the same line on the If. So when the variable could not be converted to a number then an error was throw. This test whether the variable can be converted to a number before trying to convert it to a double and only if it is a number `IsNumeric(lngPNumber)` is True, will it try to convert it to a double, thus removing the error. In VBA there is nothing like `AndAlso` or `OrElse` – Scott Craner Dec 11 '17 at 18:55
@ScottCraner Ah, yes... I mixed it with OrElse in VB.NET ) Now I got your point :) – JohnyL Dec 11 '17 at 18:58
@ScottCraner so do you mean that in normal circunstances `.Value` is enough for doing a VLOOKUP of `4000` (numeric) or `Four Thousand` & `40.00000` (string. Please take note that in Europa the point is not used as decimal separator but as thousand separator, and in this case it would not make much sense. Therefore the value has to be evaluated as Text). I was not able to become a proper result if I do not change de variable type and define the variable as Variant. – agustin Dec 12 '17 at 13:51
@agustin not sure about the European conversion. so if it works this way for you do it this way. – Scott Craner Dec 12 '17 at 13:57

VBA - Why IsNumeric is not evaluating correctly

1 Answers1