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

leishtheman

New Member
Joined
Oct 1, 2007
Messages
32
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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
This line
Code:
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
Code:
Dim a as String, b as String, c as string
 
Upvote 0
You probably need to think about the parameters too:


Code:
Function WhereBuilder(a, b, c, d, e As String)
the above is the same as:
Code:
Function WhereBuilder(a as Variant, b as Variant, c as Variant, d as Variant, e As String)
you probably meant:
Code:
Function WhereBuilder(a as String, b as String, c as String, d as String, e As String)
 
Upvote 0
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.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top