Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is variable an integer?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is variable an integer?
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) "Otto Moehrbach" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is variable an integer?
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] Otto Moehrbach wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is variable an integer?
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 wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is variable an integer?
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 "Frank Kabel" wrote in message ... 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 wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is variable an integer?
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 "Otto Moehrbach" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is variable an integer?
Otto Moehrbach wrote:
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 "Frank Kabel" wrote in message ... Hi Otto you're right. It uses only the integer values for the division (according to the Excel helpfile) Frank |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is variable an integer?
Thanks for the help. I'm always learning. Otto
"Frank Kabel" wrote in message ... Otto Moehrbach wrote: 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 "Frank Kabel" wrote in message ... Hi Otto you're right. It uses only the integer values for the division (according to the Excel helpfile) Frank |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I add an integer to an existing integer? | Excel Worksheet Functions | |||
integer | Excel Worksheet Functions | |||
OutlineLevel and Integer Variable issue | Excel Programming | |||
Not seeing integer | Excel Programming | |||
Use of integer variable for sizing of matrix | Excel Programming |