Type Mismatch Error when calling Function
Why am I getting a Type Mismatch error an the line indicated below when call
the function? Thanks in advance!!! Sub Test() ' test if dimensions are rounded to nearest 16th Error If InvalidDimensions(tbxHeightFt, tbxHeightIns, tbxWidthFt, tbxWidthIns) Then MsgBox "Please round all dimensions to the nearest 1/16''.", vbCritical StopCode = True Exit Sub End If End Sub ' ensure dimensions must be rounded to the nearest 1/16th of an inch Function InvalidDimensions(Hft As Single, Hins As Single, _ Wft As Single, Wins As Single, _ Optional Dft As Single, Optional Dins As Single) As Boolean ' height If (Hft = 0 And Hins = 0) Or _ ((Hft * 12 + Hins) * 16 < Int(Hft * 12 + Hins) * 16) Then InvalidDimensions = True Exit Function End If ' width If (Wft = 0 And Wins = 0) Or _ ((Wft * 12 + Wins) * 16 < Int(Wft * 12 + Wins) * 16) Then InvalidDimensions = True Exit Function End If ' width If Not IsMissing(Dft) Or Not IsMissing(Dins) Then If (Dft = 0 And Dins = 0) Or _ ((Dft * 12 + Dins) * 16 < Int(Dft * 12 + Dins) * 16) Then InvalidDimensions = True Exit Function End If End If End Function -- Cheers, Ryan |
Type Mismatch Error when calling Function
Add the declaration...
Dim tbxheightft As Single Dim tbxheightIns As Single Dim tbxWidthFt As Single Dim tbxWidthIns As Single PS: To avoid this Use Option Explicit and declare all variables If this post helps click Yes --------------- Jacob Skaria "Ryan H" wrote: Why am I getting a Type Mismatch error an the line indicated below when call the function? Thanks in advance!!! Sub Test() ' test if dimensions are rounded to nearest 16th Error If InvalidDimensions(tbxHeightFt, tbxHeightIns, tbxWidthFt, tbxWidthIns) Then MsgBox "Please round all dimensions to the nearest 1/16''.", vbCritical StopCode = True Exit Sub End If End Sub ' ensure dimensions must be rounded to the nearest 1/16th of an inch Function InvalidDimensions(Hft As Single, Hins As Single, _ Wft As Single, Wins As Single, _ Optional Dft As Single, Optional Dins As Single) As Boolean ' height If (Hft = 0 And Hins = 0) Or _ ((Hft * 12 + Hins) * 16 < Int(Hft * 12 + Hins) * 16) Then InvalidDimensions = True Exit Function End If ' width If (Wft = 0 And Wins = 0) Or _ ((Wft * 12 + Wins) * 16 < Int(Wft * 12 + Wins) * 16) Then InvalidDimensions = True Exit Function End If ' width If Not IsMissing(Dft) Or Not IsMissing(Dins) Then If (Dft = 0 And Dins = 0) Or _ ((Dft * 12 + Dins) * 16 < Int(Dft * 12 + Dins) * 16) Then InvalidDimensions = True Exit Function End If End If End Function -- Cheers, Ryan |
Type Mismatch Error when calling Function
1. be sure to set InvalidDimensions to False at the bottom of the function
(or at the top) 2. check to be sure tbxHeightFt, tbxHeightIns, tbxWidthFt, tbxWidthIns are Dim'ed properly in the main routine to match the function. -- Gary''s Student - gsnu200907 "Ryan H" wrote: Why am I getting a Type Mismatch error an the line indicated below when call the function? Thanks in advance!!! Sub Test() ' test if dimensions are rounded to nearest 16th Error If InvalidDimensions(tbxHeightFt, tbxHeightIns, tbxWidthFt, tbxWidthIns) Then MsgBox "Please round all dimensions to the nearest 1/16''.", vbCritical StopCode = True Exit Sub End If End Sub ' ensure dimensions must be rounded to the nearest 1/16th of an inch Function InvalidDimensions(Hft As Single, Hins As Single, _ Wft As Single, Wins As Single, _ Optional Dft As Single, Optional Dins As Single) As Boolean ' height If (Hft = 0 And Hins = 0) Or _ ((Hft * 12 + Hins) * 16 < Int(Hft * 12 + Hins) * 16) Then InvalidDimensions = True Exit Function End If ' width If (Wft = 0 And Wins = 0) Or _ ((Wft * 12 + Wins) * 16 < Int(Wft * 12 + Wins) * 16) Then InvalidDimensions = True Exit Function End If ' width If Not IsMissing(Dft) Or Not IsMissing(Dins) Then If (Dft = 0 And Dins = 0) Or _ ((Dft * 12 + Dins) * 16 < Int(Dft * 12 + Dins) * 16) Then InvalidDimensions = True Exit Function End If End If End Function -- Cheers, Ryan |
Type Mismatch Error when calling Function
That won't work for me. I forgot to mention to you that tbxHeightFt,
tbxHeightIns, tbxWidthFt, tbxWidthIns are names of textboxes in a userform. When I click a command button on the userform, Sub Test() is called and then the Function is called. I want the values in the textboxes to be passed to the function. Any other solution?? -- Cheers, Ryan "Jacob Skaria" wrote: Add the declaration... Dim tbxheightft As Single Dim tbxheightIns As Single Dim tbxWidthFt As Single Dim tbxWidthIns As Single PS: To avoid this Use Option Explicit and declare all variables If this post helps click Yes --------------- Jacob Skaria "Ryan H" wrote: Why am I getting a Type Mismatch error an the line indicated below when call the function? Thanks in advance!!! Sub Test() ' test if dimensions are rounded to nearest 16th Error If InvalidDimensions(tbxHeightFt, tbxHeightIns, tbxWidthFt, tbxWidthIns) Then MsgBox "Please round all dimensions to the nearest 1/16''.", vbCritical StopCode = True Exit Sub End If End Sub ' ensure dimensions must be rounded to the nearest 1/16th of an inch Function InvalidDimensions(Hft As Single, Hins As Single, _ Wft As Single, Wins As Single, _ Optional Dft As Single, Optional Dins As Single) As Boolean ' height If (Hft = 0 And Hins = 0) Or _ ((Hft * 12 + Hins) * 16 < Int(Hft * 12 + Hins) * 16) Then InvalidDimensions = True Exit Function End If ' width If (Wft = 0 And Wins = 0) Or _ ((Wft * 12 + Wins) * 16 < Int(Wft * 12 + Wins) * 16) Then InvalidDimensions = True Exit Function End If ' width If Not IsMissing(Dft) Or Not IsMissing(Dins) Then If (Dft = 0 And Dins = 0) Or _ ((Dft * 12 + Dins) * 16 < Int(Dft * 12 + Dins) * 16) Then InvalidDimensions = True Exit Function End If End If End Function -- Cheers, Ryan |
Type Mismatch Error when calling Function
First, drop the Single's and use Double's. It'll be easier for you to remember
and you're not gaining anything with modern computers. (Same thing with Integer's and Long's.) So this: Function InvalidDimensions(Hft As Single, Hins As Single, _ Wft As Single, Wins As Single, _ Optional Dft As Single, Optional Dins As Single) becomes Function InvalidDimensions(Hft As Double, Hins As Double, _ Wft As Double, Wins As Double, _ Optional Dft As Double, Optional Dins As Double) And then when you call your function, convert those textbox values to doubles: If InvalidDimensions(cdbl(tbxHeightFt), _ cdbl(tbxHeightIns), _ cdbl(tbxWidthFt), _ cdbl(tbxWidthIns)) then .... You are checking that those textboxes are numeric first, right??? Ryan H wrote: Why am I getting a Type Mismatch error an the line indicated below when call the function? Thanks in advance!!! Sub Test() ' test if dimensions are rounded to nearest 16th Error If InvalidDimensions(tbxHeightFt, tbxHeightIns, tbxWidthFt, tbxWidthIns) Then MsgBox "Please round all dimensions to the nearest 1/16''.", vbCritical StopCode = True Exit Sub End If End Sub ' ensure dimensions must be rounded to the nearest 1/16th of an inch Function InvalidDimensions(Hft As Single, Hins As Single, _ Wft As Single, Wins As Single, _ Optional Dft As Single, Optional Dins As Single) As Boolean ' height If (Hft = 0 And Hins = 0) Or _ ((Hft * 12 + Hins) * 16 < Int(Hft * 12 + Hins) * 16) Then InvalidDimensions = True Exit Function End If ' width If (Wft = 0 And Wins = 0) Or _ ((Wft * 12 + Wins) * 16 < Int(Wft * 12 + Wins) * 16) Then InvalidDimensions = True Exit Function End If ' width If Not IsMissing(Dft) Or Not IsMissing(Dins) Then If (Dft = 0 And Dins = 0) Or _ ((Dft * 12 + Dins) * 16 < Int(Dft * 12 + Dins) * 16) Then InvalidDimensions = True Exit Function End If End If End Function -- Cheers, Ryan -- Dave Peterson |
Type Mismatch Error when calling Function
Use CSng to convert to Single
CSng(expression) If InvalidDimensions(CSng(tbxheightft), CSng(tbxheightIns), CSng(tbxWidthFt), _ CSng(tbxWidthIns)) Then If this post helps click Yes --------------- Jacob Skaria "Ryan H" wrote: That won't work for me. I forgot to mention to you that tbxHeightFt, tbxHeightIns, tbxWidthFt, tbxWidthIns are names of textboxes in a userform. When I click a command button on the userform, Sub Test() is called and then the Function is called. I want the values in the textboxes to be passed to the function. Any other solution?? -- Cheers, Ryan "Jacob Skaria" wrote: Add the declaration... Dim tbxheightft As Single Dim tbxheightIns As Single Dim tbxWidthFt As Single Dim tbxWidthIns As Single PS: To avoid this Use Option Explicit and declare all variables If this post helps click Yes --------------- Jacob Skaria "Ryan H" wrote: Why am I getting a Type Mismatch error an the line indicated below when call the function? Thanks in advance!!! Sub Test() ' test if dimensions are rounded to nearest 16th Error If InvalidDimensions(tbxHeightFt, tbxHeightIns, tbxWidthFt, tbxWidthIns) Then MsgBox "Please round all dimensions to the nearest 1/16''.", vbCritical StopCode = True Exit Sub End If End Sub ' ensure dimensions must be rounded to the nearest 1/16th of an inch Function InvalidDimensions(Hft As Single, Hins As Single, _ Wft As Single, Wins As Single, _ Optional Dft As Single, Optional Dins As Single) As Boolean ' height If (Hft = 0 And Hins = 0) Or _ ((Hft * 12 + Hins) * 16 < Int(Hft * 12 + Hins) * 16) Then InvalidDimensions = True Exit Function End If ' width If (Wft = 0 And Wins = 0) Or _ ((Wft * 12 + Wins) * 16 < Int(Wft * 12 + Wins) * 16) Then InvalidDimensions = True Exit Function End If ' width If Not IsMissing(Dft) Or Not IsMissing(Dins) Then If (Dft = 0 And Dins = 0) Or _ ((Dft * 12 + Dins) * 16 < Int(Dft * 12 + Dins) * 16) Then InvalidDimensions = True Exit Function End If End If End Function -- Cheers, Ryan |
Type Mismatch Error when calling Function
Thanks for the tip on Single/Double. I think I will have to use the syntax
below, because the textboxes may having nothing in them. For example tbxHeightFt may equal 5, but tbxHeighIns may empty, because there are no inches to include in the dimension. I'm still getting the Type Mismatch because when I use CDbl("") it throws an error. If InvalidDimensions(Val(tbxHeightFt), Val(tbxHeightIns), Val(tbxWidthFt),Val (tbxWidthIns)) Then..... Since Val(tbxHeightIns) works, it leads me to another issue. Why in my function does Dft = 0 and Dins = 0, when I specified that they are Optional? Should they be Null? -- Cheers, Ryan "Dave Peterson" wrote: First, drop the Single's and use Double's. It'll be easier for you to remember and you're not gaining anything with modern computers. (Same thing with Integer's and Long's.) So this: Function InvalidDimensions(Hft As Single, Hins As Single, _ Wft As Single, Wins As Single, _ Optional Dft As Single, Optional Dins As Single) becomes Function InvalidDimensions(Hft As Double, Hins As Double, _ Wft As Double, Wins As Double, _ Optional Dft As Double, Optional Dins As Double) And then when you call your function, convert those textbox values to doubles: If InvalidDimensions(cdbl(tbxHeightFt), _ cdbl(tbxHeightIns), _ cdbl(tbxWidthFt), _ cdbl(tbxWidthIns)) then .... You are checking that those textboxes are numeric first, right??? Ryan H wrote: Why am I getting a Type Mismatch error an the line indicated below when call the function? Thanks in advance!!! Sub Test() ' test if dimensions are rounded to nearest 16th Error If InvalidDimensions(tbxHeightFt, tbxHeightIns, tbxWidthFt, tbxWidthIns) Then MsgBox "Please round all dimensions to the nearest 1/16''.", vbCritical StopCode = True Exit Sub End If End Sub ' ensure dimensions must be rounded to the nearest 1/16th of an inch Function InvalidDimensions(Hft As Single, Hins As Single, _ Wft As Single, Wins As Single, _ Optional Dft As Single, Optional Dins As Single) As Boolean ' height If (Hft = 0 And Hins = 0) Or _ ((Hft * 12 + Hins) * 16 < Int(Hft * 12 + Hins) * 16) Then InvalidDimensions = True Exit Function End If ' width If (Wft = 0 And Wins = 0) Or _ ((Wft * 12 + Wins) * 16 < Int(Wft * 12 + Wins) * 16) Then InvalidDimensions = True Exit Function End If ' width If Not IsMissing(Dft) Or Not IsMissing(Dins) Then If (Dft = 0 And Dins = 0) Or _ ((Dft * 12 + Dins) * 16 < Int(Dft * 12 + Dins) * 16) Then InvalidDimensions = True Exit Function End If End If End Function -- Cheers, Ryan -- Dave Peterson |
Type Mismatch Error when calling Function
If you get blank entries in any of the text boxes; then to avoid the error
Use CSng("0" & tbxheightft) "Ryan H" wrote: That won't work for me. I forgot to mention to you that tbxHeightFt, tbxHeightIns, tbxWidthFt, tbxWidthIns are names of textboxes in a userform. When I click a command button on the userform, Sub Test() is called and then the Function is called. I want the values in the textboxes to be passed to the function. Any other solution?? -- Cheers, Ryan "Jacob Skaria" wrote: Add the declaration... Dim tbxheightft As Single Dim tbxheightIns As Single Dim tbxWidthFt As Single Dim tbxWidthIns As Single PS: To avoid this Use Option Explicit and declare all variables If this post helps click Yes --------------- Jacob Skaria "Ryan H" wrote: Why am I getting a Type Mismatch error an the line indicated below when call the function? Thanks in advance!!! Sub Test() ' test if dimensions are rounded to nearest 16th Error If InvalidDimensions(tbxHeightFt, tbxHeightIns, tbxWidthFt, tbxWidthIns) Then MsgBox "Please round all dimensions to the nearest 1/16''.", vbCritical StopCode = True Exit Sub End If End Sub ' ensure dimensions must be rounded to the nearest 1/16th of an inch Function InvalidDimensions(Hft As Single, Hins As Single, _ Wft As Single, Wins As Single, _ Optional Dft As Single, Optional Dins As Single) As Boolean ' height If (Hft = 0 And Hins = 0) Or _ ((Hft * 12 + Hins) * 16 < Int(Hft * 12 + Hins) * 16) Then InvalidDimensions = True Exit Function End If ' width If (Wft = 0 And Wins = 0) Or _ ((Wft * 12 + Wins) * 16 < Int(Wft * 12 + Wins) * 16) Then InvalidDimensions = True Exit Function End If ' width If Not IsMissing(Dft) Or Not IsMissing(Dins) Then If (Dft = 0 And Dins = 0) Or _ ((Dft * 12 + Dins) * 16 < Int(Dft * 12 + Dins) * 16) Then InvalidDimensions = True Exit Function End If End If End Function -- Cheers, Ryan |
Type Mismatch Error when calling Function
I changed the declaration for Dft and Dins from Double to Variant. That
seemed to work great. Thanks! -- Cheers, Ryan "Ryan H" wrote: Thanks for the tip on Single/Double. I think I will have to use the syntax below, because the textboxes may having nothing in them. For example tbxHeightFt may equal 5, but tbxHeighIns may empty, because there are no inches to include in the dimension. I'm still getting the Type Mismatch because when I use CDbl("") it throws an error. If InvalidDimensions(Val(tbxHeightFt), Val(tbxHeightIns), Val(tbxWidthFt),Val (tbxWidthIns)) Then..... Since Val(tbxHeightIns) works, it leads me to another issue. Why in my function does Dft = 0 and Dins = 0, when I specified that they are Optional? Should they be Null? -- Cheers, Ryan "Dave Peterson" wrote: First, drop the Single's and use Double's. It'll be easier for you to remember and you're not gaining anything with modern computers. (Same thing with Integer's and Long's.) So this: Function InvalidDimensions(Hft As Single, Hins As Single, _ Wft As Single, Wins As Single, _ Optional Dft As Single, Optional Dins As Single) becomes Function InvalidDimensions(Hft As Double, Hins As Double, _ Wft As Double, Wins As Double, _ Optional Dft As Double, Optional Dins As Double) And then when you call your function, convert those textbox values to doubles: If InvalidDimensions(cdbl(tbxHeightFt), _ cdbl(tbxHeightIns), _ cdbl(tbxWidthFt), _ cdbl(tbxWidthIns)) then .... You are checking that those textboxes are numeric first, right??? Ryan H wrote: Why am I getting a Type Mismatch error an the line indicated below when call the function? Thanks in advance!!! Sub Test() ' test if dimensions are rounded to nearest 16th Error If InvalidDimensions(tbxHeightFt, tbxHeightIns, tbxWidthFt, tbxWidthIns) Then MsgBox "Please round all dimensions to the nearest 1/16''.", vbCritical StopCode = True Exit Sub End If End Sub ' ensure dimensions must be rounded to the nearest 1/16th of an inch Function InvalidDimensions(Hft As Single, Hins As Single, _ Wft As Single, Wins As Single, _ Optional Dft As Single, Optional Dins As Single) As Boolean ' height If (Hft = 0 And Hins = 0) Or _ ((Hft * 12 + Hins) * 16 < Int(Hft * 12 + Hins) * 16) Then InvalidDimensions = True Exit Function End If ' width If (Wft = 0 And Wins = 0) Or _ ((Wft * 12 + Wins) * 16 < Int(Wft * 12 + Wins) * 16) Then InvalidDimensions = True Exit Function End If ' width If Not IsMissing(Dft) Or Not IsMissing(Dins) Then If (Dft = 0 And Dins = 0) Or _ ((Dft * 12 + Dins) * 16 < Int(Dft * 12 + Dins) * 16) Then InvalidDimensions = True Exit Function End If End If End Function -- Cheers, Ryan -- Dave Peterson |
Type Mismatch Error when calling Function
You could prepend a 0 to the entry:
If InvalidDimensions(cdbl("0" & tbxHeightFt), _ cdbl("0" & tbxHeightIns), _ cdbl("0" & tbxWidthFt), _ cdbl("0" & tbxWidthIns)) then But if you pass non-numeric data, won't you have trouble???? If I screw up and type: qwer in the feet box, won't that cause trouble... I would have guessed that you'd want to make sure all the entries were numbers before you even tried calling that function. If isnumeric(tbxHeightft) = false _ or isnumeric(tbxheightins) = false _ or isnumeric(tbxwidthft) = false _ or isnumeric(tbxwidthins) = false then me.label1.caption = "Please make sure all the entries are numbers!" exit sub end if Add a nice label (or use a msgbox if you want. Ryan H wrote: Thanks for the tip on Single/Double. I think I will have to use the syntax below, because the textboxes may having nothing in them. For example tbxHeightFt may equal 5, but tbxHeighIns may empty, because there are no inches to include in the dimension. I'm still getting the Type Mismatch because when I use CDbl("") it throws an error. If InvalidDimensions(Val(tbxHeightFt), Val(tbxHeightIns), Val(tbxWidthFt),Val (tbxWidthIns)) Then..... Since Val(tbxHeightIns) works, it leads me to another issue. Why in my function does Dft = 0 and Dins = 0, when I specified that they are Optional? Should they be Null? -- Cheers, Ryan "Dave Peterson" wrote: First, drop the Single's and use Double's. It'll be easier for you to remember and you're not gaining anything with modern computers. (Same thing with Integer's and Long's.) So this: Function InvalidDimensions(Hft As Single, Hins As Single, _ Wft As Single, Wins As Single, _ Optional Dft As Single, Optional Dins As Single) becomes Function InvalidDimensions(Hft As Double, Hins As Double, _ Wft As Double, Wins As Double, _ Optional Dft As Double, Optional Dins As Double) And then when you call your function, convert those textbox values to doubles: If InvalidDimensions(cdbl(tbxHeightFt), _ cdbl(tbxHeightIns), _ cdbl(tbxWidthFt), _ cdbl(tbxWidthIns)) then .... You are checking that those textboxes are numeric first, right??? Ryan H wrote: Why am I getting a Type Mismatch error an the line indicated below when call the function? Thanks in advance!!! Sub Test() ' test if dimensions are rounded to nearest 16th Error If InvalidDimensions(tbxHeightFt, tbxHeightIns, tbxWidthFt, tbxWidthIns) Then MsgBox "Please round all dimensions to the nearest 1/16''.", vbCritical StopCode = True Exit Sub End If End Sub ' ensure dimensions must be rounded to the nearest 1/16th of an inch Function InvalidDimensions(Hft As Single, Hins As Single, _ Wft As Single, Wins As Single, _ Optional Dft As Single, Optional Dins As Single) As Boolean ' height If (Hft = 0 And Hins = 0) Or _ ((Hft * 12 + Hins) * 16 < Int(Hft * 12 + Hins) * 16) Then InvalidDimensions = True Exit Function End If ' width If (Wft = 0 And Wins = 0) Or _ ((Wft * 12 + Wins) * 16 < Int(Wft * 12 + Wins) * 16) Then InvalidDimensions = True Exit Function End If ' width If Not IsMissing(Dft) Or Not IsMissing(Dins) Then If (Dft = 0 And Dins = 0) Or _ ((Dft * 12 + Dins) * 16 < Int(Dft * 12 + Dins) * 16) Then InvalidDimensions = True Exit Function End If End If End Function -- Cheers, Ryan -- Dave Peterson -- Dave Peterson |
All times are GMT +1. The time now is 05:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com