Is variable an integer?

[Otto Moehrbach]

Excel 2003, WinXP
My end goal is to test a cell value, via VBA, to see if it is an integer.
The worksheet Mod function works like this:
Mod(D6,1) returns a 0 if D6 is an integer.
The VBA Mod function rounds all numbers first so I can't use that. So I
used:
MsgBox Application.WorksheetFunction.Mod(Range("D6"),1)
I was hoping to get a 0 if D6 is an integer.
I got an error message that said "Object doesn't support this method".
Where did I go wrong? Thanks for your help. Otto

 

[Bob Phillips]

Otto,

An alternative

If Range("D6") = Int(Range("D6")) Then
MsgBox "Integer"
Else
MsgBox "Not an integer"
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Jake Marx]

Hi Otto,

Just to add to Bob's excellent suggestion, you may want to use error
handling in the case that Int or CInt raises an error:

Public Function gbIsInt(rvValue As Variant) As Boolean
On Error Resume Next
gbIsInt = (rvValue = CInt(rvValue))
On Error GoTo 0
End Function

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Frank Kabel]

Hi Otto

just to add to Bob's and Jake' suggestion why your line
MsgBox Application.WorksheetFunction.Mod(Range("D6"),1)
did produce an error.
WorksheetFunction.Mod is not supported as VBA directly implements the
MOD function. So the corret syntax would be:
MsgBox Range("D6").value mod 1

Though the other solutions are definetly better

Frank

 

[Otto Moehrbach]

Frank
Correct me if I'm wrong but doesn't the VBA Mod function round all
numbers before it does the division? As I understand it, the VBA mod, as in
"somenumber Mod 1" will always return zero regardless of what "somenumber"
is. Thanks for your help. Otto

 

[Otto Moehrbach]

Thanks Bob & Jake. My plan is to first test (with IsNumerical) that it is a
valid number, then test for integer. It is supposed to be an integer every
time. Thanks again for your help. Otto

 

[Frank Kabel]

Frank
Correct me if I'm wrong but doesn't the VBA Mod function round all
numbers before it does the division? As I understand it, the VBA
mod, as in "somenumber Mod 1" will always return zero regardless of
what "somenumber" is. Thanks for your help. Otto
news:[email protected]...

Hi Otto
you're right. It uses only the integer values for the division
(according to the Excel helpfile)

Frank

 

[Otto Moehrbach]

Thanks for the help. I'm always learning. Otto

Hi Otto
you're right. It uses only the integer values for the division
(according to the Excel helpfile)

Frank