Type Mismatch Error when calling Function

[Ryan H]

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

 

[Jacob Skaria]

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

 

[Gary''s Student]

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.

 

[Ryan H]

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??

 

[Dave Peterson]

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???

 

[Jacob Skaria]

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

 

[Ryan H]

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

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???

 

[Jacob Skaria]

If you get blank entries in any of the text boxes; then to avoid the error

Use
CSng("0" & tbxheightft)

 

[Ryan H]

I changed the declaration for Dft and Dins from Double to Variant. That
seemed to work great. Thanks!

--
Cheers,
Ryan

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?

 

[Dave Peterson]

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.

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?