Declaring Multiple Variables in 1 line Causing ByRef Error???

[leishtheman]

Hello

I've been trying to call a function and keep encountering a 'ByRef argument type mismatch' error.

I can't see anything wrong with my code, and the only way I can get round it is by ensuring 'input' and 'output' variables (doubt if these are even real terms) are declared on separate lines. I wasn't declaring multiple variables on a single line caused any issues...does it?

A simplified example of the problem is as below:



Example 1 (doesn't work):
_______________________________________________

Option Compare Database
Option Explicit

Sub tttttttttt()

Dim a, b, c, d, e, y As String

a = "r"
b = "r"
c = "r"
d = "r"
e = "r"

y = WhereBuilder(a, b, c, d, e)

End Sub

Function WhereBuilder(a, b, c, d, e As String)

End Function
__________________________________________________

Example 2 (works fine):
__________________________________________________

Option Compare Database
Option Explicit

Sub tttttttttt()

Dim a, b, c, d, e As String
Dim y As String

a = "r"
b = "r"
c = "r"
d = "r"
e = "r"

y = WhereBuilder(a, b, c, d, e)

End Sub

Function WhereBuilder(a, b, c, d, e As String)

End Function





Many thanks in advance,
Andy.

 

[Fluff]

This line

Dim a, b, c, d, e, y As String

is only declaring y as a string, the rest are all variant, which is why you are getting problems.
You can declare them all on one line, but it need to be like

Dim a as String, b as String, c as string

 

[xenou]

You probably need to think about the parameters too:

Function WhereBuilder(a, b, c, d, e As String)

the above is the same as:

Function WhereBuilder(a as Variant, b as Variant, c as Variant, d as Variant, e As String)

you probably meant:

Function WhereBuilder(a as String, b as String, c as String, d as String, e As String)

 

[leishtheman]

Well I'll be ****ed...I've been using vba for 10 years and genuinely didn't notice this...you learn something new every day lol. thank you for your help.

 

[Fluff]

You're welcome & thanks for the feedback