Type Mismatch Error when calling Function

R

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
 
J

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
 
G

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.
 
R

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

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

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
 
R

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


Dave Peterson said:
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???
 
J

Jacob Skaria

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

Use
CSng("0" & tbxheightft)
 
R

Ryan H

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

--
Cheers,
Ryan


Ryan H said:
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?
 
D

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.



Ryan said:
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?
 

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

Top