VBA function to validate integer?

CWatts

Well-known Member

Joined

Jan 22, 2010

Messages

701

• Apr 9, 2011

• #2

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

Code:

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

 

Upvote 0

Glory

Well-known Member

Joined

Mar 16, 2011

Messages

640

• Apr 9, 2011

• #3

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.

 

Last edited: Apr 9, 2011

Upvote 0

mikerickson

MrExcel MVP

Joined

Jan 15, 2007

Messages

24,355

• Apr 9, 2011

• #4

Code:

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

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

 

Upvote 0

Glory

Well-known Member

Joined

Mar 16, 2011

Messages

640

• Apr 9, 2011

• #5

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

 

Last edited: Apr 9, 2011

Upvote 0

ZVI

MrExcel MVP

Joined

Apr 9, 2008

Messages

3,875

Office Version

1. 2019
2. 2016
3. 2010

Platform

1. Windows

• Apr 9, 2011

• #6

Glory said:

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

If b Mod 10 = 0 Then

Click to expand...

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

 

Upvote 0

ZVI

MrExcel MVP

Joined

Apr 9, 2008

Messages

3,875

Office Version

1. 2019
2. 2016
3. 2010

Platform

1. Windows

• Apr 9, 2011

• #7

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)

Rich (BB code):

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

 

Last edited: Apr 9, 2011

Upvote 0

Glory

Well-known Member

Joined

Mar 16, 2011

Messages

640

• Apr 9, 2011

• #8

Okay, great explanation ZVI. Thanks all.

 

Upvote 0

mikerickson

MrExcel MVP

Joined

Jan 15, 2007

Messages

24,355

• Apr 10, 2011

• #9

Glory said:

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

Click to expand...

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.

 

Upvote 0