Problem Using 'Mod'

R

RyanH

I am getting an error: Division By Zero, indicated below. I don' t know why
this is happening. Val(controls("cboRevealH" & i).Value) = 5 in the
immediate window. The goal I am trying to achieve is making sure the
combobox is a multiple of a 16th of an inch.

Private Sub cmbApply_Click()

Dim i As Long
Dim Dimension As String

' ensure valid data is entered into userform
For i = 1 To 6

' if anything is entered in Reveal #i, then test if it is valid
If Not controls("cboRevealH" & i).Value = "" Or _
Not controls("tbxRevealW" & i).Value = "" Or _
Not controls("tbxRevealD" & i).Value = "" Then

' test if reveal data is rounded to nearest 1/16th and not empty
Select Case True
ERROR>> Case Val(controls("cboRevealH" & i).Value) Mod 1 / 16 <> 0:
Dimension = "Height"
Case Val(controls("tbxRevealW" & i).Value) Mod 1 / 16 <> 0:
Dimension = "Width"
Case Val(controls("tbxRevealD" & i).Value) Mod 1 / 16 <> 0:
Dimension = "Depth"
End Select

' show message if a invalid height, width, or depth is entered
If CBool(Dimension) Then
strPrompt = "Please enter a valid " & Dimension & " for
Reveal " & i
strPrompt = strPrompt & ". Round to the nearest 1/16th of
an inch."
intButtons = vbCritical
strTitle = "Problem"
MsgBox strPrompt, intButtons, strTitle
Exit Sub
End If
End If
Next i

End Sub
 
B

Bernie Deitrick

Ryan,

Mod only takes integer values as the second argument. Try it this way

Val(Controls("cboRevealH" & i).Value) * 16 Mod 1 <> 0:
etc.

HTH,
Bernie
MS Excel MVP
 
J

Jim Rech

Mod only takes integer values as the second argument.

Actually Mod takes any number but rounds it to the nearest integer. So
Ryan's 1/16 was rounded to zero causing the division by zero.

--
Jim
| Ryan,
|
| Mod only takes integer values as the second argument. Try it this way
|
| Val(Controls("cboRevealH" & i).Value) * 16 Mod 1 <> 0:
| etc.
|
| HTH,
| Bernie
| MS Excel MVP
|
|
| | >I am getting an error: Division By Zero, indicated below. I don' t know
why
| > this is happening. Val(controls("cboRevealH" & i).Value) = 5 in the
| > immediate window. The goal I am trying to achieve is making sure the
| > combobox is a multiple of a 16th of an inch.
| >
| > Private Sub cmbApply_Click()
| >
| > Dim i As Long
| > Dim Dimension As String
| >
| > ' ensure valid data is entered into userform
| > For i = 1 To 6
| >
| > ' if anything is entered in Reveal #i, then test if it is valid
| > If Not controls("cboRevealH" & i).Value = "" Or _
| > Not controls("tbxRevealW" & i).Value = "" Or _
| > Not controls("tbxRevealD" & i).Value = "" Then
| >
| > ' test if reveal data is rounded to nearest 1/16th and not
empty
| > Select Case True
| > ERROR>> Case Val(controls("cboRevealH" & i).Value) Mod 1 / 16 <> 0:
| > Dimension = "Height"
| > Case Val(controls("tbxRevealW" & i).Value) Mod 1 / 16 <>
0:
| > Dimension = "Width"
| > Case Val(controls("tbxRevealD" & i).Value) Mod 1 / 16 <>
0:
| > Dimension = "Depth"
| > End Select
| >
| > ' show message if a invalid height, width, or depth is
entered
| > If CBool(Dimension) Then
| > strPrompt = "Please enter a valid " & Dimension & " for
| > Reveal " & i
| > strPrompt = strPrompt & ". Round to the nearest 1/16th
of
| > an inch."
| > intButtons = vbCritical
| > strTitle = "Problem"
| > MsgBox strPrompt, intButtons, strTitle
| > Exit Sub
| > End If
| > End If
| > Next i
| >
| > End Sub
| > --
| > Cheers,
| > Ryan
|
|
 
B

Bernie Deitrick

Jim,

Ah! That's good to know, but I can't imagine why one would want to use it that way.

Bernie
 
R

RyanH

Thanks for the reply Bernie, unfortunately that doesn't really do much for
me. Is there a function that would do this below?

16 * Val(controls("cboRevealH" & i)) - Int(16 * Val(controls("cboRevealH" &
i))) = 0

Basically multiple the number in the Combobox by 16 which will return the
total number of 16th's. If the result does not equal a whole number the
number should be considered invalid. It would be nice to have a neat
function for this, any ideas?
--
Cheers,
Ryan


Bernie Deitrick said:
Ryan,

Mod only takes integer values as the second argument. Try it this way

Val(Controls("cboRevealH" & i).Value) * 16 Mod 1 <> 0:
etc.

HTH,
Bernie
MS Excel MVP
Click to expand...
 
J

JE McGimpsey

Perhaps:



Dim dTest As Double
dTest = 16 * Controls("cboRevealH" & i).Value
If Abs(dTest - CInt(dTest)) > 1e-10 Then MsgBox "Invalid"

which allows for some rounding error. Adjust to suit
 
B

Bernie Deitrick

Ryan,

You can certainly create function code for it:

Function Even16(myVal As Double) As Boolean
Even16 = (myVal * 16 = Int(myVal * 16))
End Function

Use it like:

MsgBox Even16(Controls("cboRevealH" & i).Value)

or

If Even16(Controls("cboRevealH" & i).Value) Then
MsgBox "That number was an even 16th"
Else
MsgBox "That number was not an even 16th"
End If

HTH,
Bernie
MS Excel MVP


RyanH said:
Thanks for the reply Bernie, unfortunately that doesn't really do much for
me. Is there a function that would do this below?

16 * Val(controls("cboRevealH" & i)) - Int(16 * Val(controls("cboRevealH" &
i))) = 0

Basically multiple the number in the Combobox by 16 which will return the
total number of 16th's. If the result does not equal a whole number the
number should be considered invalid. It would be nice to have a neat
function for this, any ideas?
Click to expand...
 
J

Jim Rech

If the number entered/selected is a multiple of 16 this would be true:

Case Val(controls("cboRevealH" & i).Value) Mod 16 = 0

--
Jim
| Thanks for the reply Bernie, unfortunately that doesn't really do much for
| me. Is there a function that would do this below?
|
| 16 * Val(controls("cboRevealH" & i)) - Int(16 * Val(controls("cboRevealH"
&
| i))) = 0
|
| Basically multiple the number in the Combobox by 16 which will return the
| total number of 16th's. If the result does not equal a whole number the
| number should be considered invalid. It would be nice to have a neat
| function for this, any ideas?
| --
| Cheers,
| Ryan
|
|
| "Bernie Deitrick" wrote:
|
| > Ryan,
| >
| > Mod only takes integer values as the second argument. Try it this way
| >
| > Val(Controls("cboRevealH" & i).Value) * 16 Mod 1 <> 0:
| > etc.
| >
| > HTH,
| > Bernie
| > MS Excel MVP
| >
| >
| > | > >I am getting an error: Division By Zero, indicated below. I don' t
know why
| > > this is happening. Val(controls("cboRevealH" & i).Value) = 5 in the
| > > immediate window. The goal I am trying to achieve is making sure the
| > > combobox is a multiple of a 16th of an inch.
| > >
| > > Private Sub cmbApply_Click()
| > >
| > > Dim i As Long
| > > Dim Dimension As String
| > >
| > > ' ensure valid data is entered into userform
| > > For i = 1 To 6
| > >
| > > ' if anything is entered in Reveal #i, then test if it is valid
| > > If Not controls("cboRevealH" & i).Value = "" Or _
| > > Not controls("tbxRevealW" & i).Value = "" Or _
| > > Not controls("tbxRevealD" & i).Value = "" Then
| > >
| > > ' test if reveal data is rounded to nearest 1/16th and not
empty
| > > Select Case True
| > > ERROR>> Case Val(controls("cboRevealH" & i).Value) Mod 1 / 16 <> 0:
| > > Dimension = "Height"
| > > Case Val(controls("tbxRevealW" & i).Value) Mod 1 / 16
<> 0:
| > > Dimension = "Width"
| > > Case Val(controls("tbxRevealD" & i).Value) Mod 1 / 16
<> 0:
| > > Dimension = "Depth"
| > > End Select
| > >
| > > ' show message if a invalid height, width, or depth is
entered
| > > If CBool(Dimension) Then
| > > strPrompt = "Please enter a valid " & Dimension & " for
| > > Reveal " & i
| > > strPrompt = strPrompt & ". Round to the nearest 1/16th
of
| > > an inch."
| > > intButtons = vbCritical
| > > strTitle = "Problem"
| > > MsgBox strPrompt, intButtons, strTitle
| > > Exit Sub
| > > End If
| > > End If
| > > Next i
| > >
| > > End Sub
| > > --
| > > Cheers,
| > > Ryan
| >
| >
| >
 
J

Jim Rech

I'm not clear about what actually appears in the combo box so my post was
just in case it was whole numbers (representing 16ths). So 16, 32, 48 would
be valid. If I'm wrong, "never mind" <g>.

--
Jim
| Jim,
|
| He wants multiples of 1/16th.
|
| Bernie
| MS Excel MVP
|
|
| > If the number entered/selected is a multiple of 16 this would be true:
| >
| > Case Val(controls("cboRevealH" & i).Value) Mod 16 = 0
| >
| > --
| > Jim
| > | > | Thanks for the reply Bernie, unfortunately that doesn't really do much
for
| > | me. Is there a function that would do this below?
| > |
| > | 16 * Val(controls("cboRevealH" & i)) - Int(16 *
Val(controls("cboRevealH"
| > &
| > | i))) = 0
| > |
| > | Basically multiple the number in the Combobox by 16 which will return
the
| > | total number of 16th's. If the result does not equal a whole number
the
| > | number should be considered invalid. It would be nice to have a neat
| > | function for this, any ideas?
| > | --
| > | Cheers,
| > | Ryan
| > |
| > |
| > | "Bernie Deitrick" wrote:
| > |
| > | > Ryan,
| > | >
| > | > Mod only takes integer values as the second argument. Try it this
way
| > | >
| > | > Val(Controls("cboRevealH" & i).Value) * 16 Mod 1 <> 0:
| > | > etc.
| > | >
| > | > HTH,
| > | > Bernie
| > | > MS Excel MVP
| > | >
| > | >
| > | > | > | > >I am getting an error: Division By Zero, indicated below. I don' t
| > know why
| > | > > this is happening. Val(controls("cboRevealH" & i).Value) = 5 in
the
| > | > > immediate window. The goal I am trying to achieve is making sure
the
| > | > > combobox is a multiple of a 16th of an inch.
| > | > >
| > | > > Private Sub cmbApply_Click()
| > | > >
| > | > > Dim i As Long
| > | > > Dim Dimension As String
| > | > >
| > | > > ' ensure valid data is entered into userform
| > | > > For i = 1 To 6
| > | > >
| > | > > ' if anything is entered in Reveal #i, then test if it is
valid
| > | > > If Not controls("cboRevealH" & i).Value = "" Or _
| > | > > Not controls("tbxRevealW" & i).Value = "" Or _
| > | > > Not controls("tbxRevealD" & i).Value = "" Then
| > | > >
| > | > > ' test if reveal data is rounded to nearest 1/16th and
not
| > empty
| > | > > Select Case True
| > | > > ERROR>> Case Val(controls("cboRevealH" & i).Value) Mod 1 / 16 <>
0:
| > | > > Dimension = "Height"
| > | > > Case Val(controls("tbxRevealW" & i).Value) Mod 1 /
16
| > <> 0:
| > | > > Dimension = "Width"
| > | > > Case Val(controls("tbxRevealD" & i).Value) Mod 1 /
16
| > <> 0:
| > | > > Dimension = "Depth"
| > | > > End Select
| > | > >
| > | > > ' show message if a invalid height, width, or depth is
| > entered
| > | > > If CBool(Dimension) Then
| > | > > strPrompt = "Please enter a valid " & Dimension & "
for
| > | > > Reveal " & i
| > | > > strPrompt = strPrompt & ". Round to the nearest
1/16th
| > of
| > | > > an inch."
| > | > > intButtons = vbCritical
| > | > > strTitle = "Problem"
| > | > > MsgBox strPrompt, intButtons, strTitle
| > | > > Exit Sub
| > | > > End If
| > | > > End If
| > | > > Next i
| > | > >
| > | > > End Sub
| > | > > --
| > | > > Cheers,
| > | > > Ryan
| > | >
| > | >
| > | >
| >
|
|
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Fool Proof Price Validation Custom Function 0
Validate Text in Textbox to Ensure Text is a Valid Price Format 1
Clear Check Box 2
Pass a Function Value thru several Subs 1
Type Mismatch Error when calling Function 9
Conversion from Number to Word!! Aki 1
Modify The Code For Importing Text File into an Excel File 5
type mismatch error in macro in excel 0

Top