VBA Testing for integer value

[JeffGrant]

Hi All,

I have a test like x = (b-a)/3

where a & b are row numbers.

How can I test that x is an integer. If x is not an integer, then I have to run another routine.

I'm not sure what vba function I can use to check for whole numbers.

Thanks

 

[Joe4]

You can use the INT function, i.e.

If Int(x) = x Then
    MsgBox "x is an integer"
Else
    MsgBox "x is not an integer"
End If

 

[Fluff]

How has x been declared?

 

[RoryA]

You could also test if (b-a) Mod 3 = 0.

 

[JeffGrant]

Hi Joe, thanks for that.

However, it is not as simple as that.

The VBA Int function rounds a supplied number to an integer.

My issue is that if x = (b-a)/3 is not an integer, then the code goes off and and runs a routine which takes quite a while.

If x = (b-a)/3 is an integer, then the code by-passes the extra code

I need to test that x = (b-a)/3 results in a whole number, not a rounded number.

Hi Fluff,

x as been declared as a integer.

cheers

 

[RoryA]

If x is declared as integer, it will always be an integer…

 

[JeffGrant]

You could also test if (b-a) Mod 3 = 0.

Hi Rory -> Perfect man. Thanks

 

[Fluff]

Not sure if you saw Rory's post as you posted at the same time, but there is no point in checking.

 

[Joe4]

However, it is not as simple as that.

The VBA Int function rounds a supplied number to an integer.

I am well aware of that, and taking advantage of that fact. I don't think you are understanding how my logic is working.

You want to check to see if the value in "x" is an integer, right?
So all we have to do is compare "x" to the integer value of "x". If it is equal, then we know x is an integer. If it is not, then it is not.

This becomes clear when you look at some examples.

First, let's suppose x = 1.23
Then substitute into the equation:
Int(x) = x
Int(1.23) = 1.23
1 = 1.23
FALSE
So we know that x is NOT an integer.

Now, let' suppose x = 3
Then substitute into the equation:
Int(x) = x
Int(3) = 3
3 = 3
TRUE
So we know that x IS an integer.

So, you can see how that equation tells us whether x is an integer or not.
Does it make sense now?

 

[JeffGrant]

I am well aware of that, and taking advantage of that fact. I don't think you are understanding how my logic is working.

You want to check to see if the value in "x" is an integer, right?
So all we have to do is compare "x" to the integer value of "x". If it is equal, then we know x is an integer. If it is not, then it is not.

This becomes clear when you look at some examples.

First, let's suppose x = 1.23
Then substitute into the equation:
Int(x) = x
Int(1.23) = 1.23
1 = 1.23
FALSE
So we know that x is NOT an integer.

Now, let' suppose x = 3
Then substitute into the equation:
Int(x) = x
Int(3) = 3
3 = 3
TRUE
So we know that x IS an integer.

So, you can see how that equation tells us whether x is an integer or not.
Does it make sense now?

Thanks Joe.

Much appreciated.