Type Mismatch Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I'm getting an error passing a variable to another sub when calling it.

I've created a user-defined variable. It has multiple variables, which I
reference like: var.item1 = value. They are all Strings.

The definition is in a Module called UserDefinedVariables. When the user
clicks on a button, it gets the name of a file, opens it and places line
values into the custom variable.

I then want to pass that variable to another sub where it will add the
information to the DB. But when I call the other Sub, I get a Type Mismatch
error and I can't figure out why.

The Sub header is: Private Sub InsertDB(data As rptData)
I call the Sub using: InsertDB(newRptData)

I don't understand why there would be a Type Mismatch.

Any suggestions are appreciated.

Thanks,
Jay
 
Alright, I got it fixed, but I'm not sure why it works the new way.

I added 'Call' in front of InsertDB(rptData) and it seems to work properly.
But I don't know why.

Sorry for the wasted post...
 
The "why" is because putting parentheses around the parameter when you're
not using the Call syntax actually has a specific meaning that alters what
you're passing. Unfortunately, I can't remember the exact details, but I
believe it's something like it changes from ByVal to ByRef (or vice versa).
Normally it doesn't matter if you're simply passing a simple variable
(string, Long Integer, etc.), but if can cause problems when passing
objects.
 
I added 'Call' in front of InsertDB(rptData) and it seems to work
properly. But I don't know why.
Click to expand...

A normal call to a Sub does not require brackets round the arguments:

Print myName

db.Execute jetSQL, dbFailOnError

but Call converts it into a function-type call that does need brackets:

Call Print(longString)

Putting brackets round an argument makes VB evaluate it before passing
it, in effect forcing a ByValue rather than a ByRef. Vis:

Public Sub DoubleUp(ByRef SomeValue as Integer)
SomeValue = SomeValue * 2
End Sub

myNumber = 10
DoubleUp myNumber ' myNumber is now 20
Debug.Print myNumber

DoubleUp 10 ' the sub sees the literal value and
' cannot return anything

DoubleUp myNumber / 2
' the sub sees the result of the expression
' as a literal value

DoubleUp (myNumber)
' this is an expression as well, so the sub
' cannot return it


This is only rarely useful. Trouble is that the expression

(MyComplexTypeInstance)

has no value, and causes an error in VBA.

Hope that makes some sense.

Tim F
 
Back
Top