| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem Using 'Mod'
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem Using 'Mod'
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 "RyanH" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem Using 'Mod'
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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... | 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 | | | "RyanH" wrote in message | ... | 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 | | |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem Using 'Mod'
Jim,
Ah! That's good to know, but I can't imagine why one would want to use it that way. Bernie "Jim Rech" wrote in message ... 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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... | 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 | | | "RyanH" wrote in message | ... | 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 | | |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem Using 'Mod'
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 "RyanH" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem Using 'Mod'
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 In article , RyanH wrote: 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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem Using 'Mod'
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" wrote in message ... 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 "RyanH" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem Using 'Mod'
If the number entered/selected is a multiple of 16 this would be true:
Case Val(controls("cboRevealH" & i).Value) Mod 16 = 0 -- Jim "RyanH" wrote in message ... | 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 | | | "RyanH" wrote in message | ... | 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 | | | |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem Using 'Mod'
Jim,
He wants multiples of 1/16th. Bernie MS Excel MVP "Jim Rech" wrote in message ... If the number entered/selected is a multiple of 16 this would be true: Case Val(controls("cboRevealH" & i).Value) Mod 16 = 0 -- Jim "RyanH" wrote in message ... | 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 | | | "RyanH" wrote in message | ... | 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 | | | |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem Using 'Mod'
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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... | Jim, | | He wants multiples of 1/16th. | | Bernie | MS Excel MVP | | | "Jim Rech" wrote in message ... | If the number entered/selected is a multiple of 16 this would be true: | | Case Val(controls("cboRevealH" & i).Value) Mod 16 = 0 | | -- | Jim | "RyanH" wrote in message | ... | | 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 | | | | | | "RyanH" wrote in message | | ... | | 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 | | | | | | | | | |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
| Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
| problem with a conditional max problem | Excel Discussion (Misc queries) | |||
| Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming | |||
Linear Mode
