Passing Multiple Arguments To A Public Sub

DJL_DASH

New Member
Joined
Jul 8, 2011
Messages
16
Hi,
I am having trouble dealing with multiple arguments when they are passed from one cell to the next.

I have the following public sub:

Code:
Public Sub testFunctOne()
Dim arg1, arg3 as Long, arg2, arg4 as Variant, arg5 as String
arg1 = 10
arg2(1) = True
arg2(2) = False
arg3 = 10
arg4 (1)= 1
arg4(2) = 2
arg5 = "String"

FunctOne arg1, arg2, arg3, arg4, arg 5

End Sub
Which I pass to the following:

Code:
Public Sub FunctOne(ByRef arg1 as long, ByRef arg2 as variant, ByRef arg3 as long, ByRef arg4 as variant, ByRef arg5 as String
Dim something as Integer
Something = 0
End Sub
But I get the following error

Compile Error:
Argument not optional

What is going wrong?

P.S. I am still a bit unclear on the public vs. private sub/ ByRef demarcation.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
P.S. I am still a bit unclear on the public vs. private sub/ ByRef demarcation.
Public (the default) and Private just affect the visibility of the procedure outside the module in which it appears. Procedures outside the module may still invoke Private procedures, but must prefix the module name:

Code:
Module1!FunctOne arg1, arg2, arg3, arg4, arg5

Arguments passed ByRef (the default) are (for scalar variables) a pointer to the variable's address (i.e., the variable itself), so the called procedure can modify them, and those changes will be reflected in the calling procedure.

Arguments received ByVal are a local copy of the variable passed to the procedure; the called routine can modify them at its convenience, but the calling routine will not see the changes.

Arrays are always passed ByRef.

The situation is a little more complex for object variables, but the result is the same.

Chip Pearson explains it all pretty well on his site.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

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