VBA function to validate integer?

[Glory]

I want to check that the quotient is an integer when I divide a variable by ten. Is there a function for that?

 

[CWatts]

You can modify this to suit your need, it basically checks if there is any remainder when dividing by 10 using the MOD function.

Sub Test()
Dim num As Long
Dim Result As String

num = [A1].Value

If num Mod 10 = 0 Then Result = "Integer" Else Result = "Decimal"

[A2].Value = Result
End Sub

 

[Glory]

If Application.WorksheetFunction.Mod(b, 10) = 0 Then is returning "Object doesn't support property or method. b is dimensioned as an Integer, so I don't see what could be going wrong unless Mod isn't supported...

"Most worksheet functions that are not available as methods of the Application object have an equivalent Microsoft Visual Basic built-in operator or function. For example, the Mod() worksheet function is not available as a method of the Application object because there is an equivalent Mod operator built-in to Visual Basic."

But I already tried Mod(b, 10) without worksheetfunction in front.

Any suggestions?

Edit: Wait, never mind, I see what you did there.

Works great, thanks.

 

[mikerickson]

Dim x As Long
x = 13
MsgBox 10 * Int(x / 10) = x:Rem False

x=120
MsgBox 10 * Int(x / 10) = x:Rem True

 

[Glory]

Mike: Why the colon after the variable at the end before the Rem statements?

Edit: I don't forsee any problems with this. Do you?

If b Mod 10 = 0 Then

 

[ZVI]

don't forsee any problems with this. Do you?

If b Mod 10 = 0 Then

Try with b = 3000000000 to get error.
It's because Mod works with Long type range of numbers

 

[ZVI]

BTW, the cell function MOD supports full range of Excel numbers, but VBA one is limited by long range of numbers.

In VBA for full range of numbers you can use this UDF to check if the residue of division by ten is equal to zero (similar to Mike's suggestion)

Function IsMultipleTen(Num) As Boolean
  IsMultipleTen = Num / 10 = Fix(Num / 10)
End Function

 

[Glory]

Okay, great explanation ZVI. Thanks all.

 

[mikerickson]

Mike: Why the colon after the variable at the end before the Rem statements?
[/COLOR]

The colon acts as an indicator that the next bit is to be interpreted as if it were on the next line. Almost the reverse functionality of the continuation underscore.