Passing several arguments to sub

user_3250452 0
Viewed 78

After searching, it seems nobody ever needs to pass 3+ arguments to a subroutine.

To my knowledge, VBA subroutines (Which are void) can only have up to 2 parameters (Error when I try 3+), and functions (Which can accept more than 2 parameters) must return a value to whatever called them.

I have been using a little hack to get around this for years, but wondered if there is a better way to pass 3+ arguments to a sub, or to call a void function. Example of what I currently do:

Private Function Foo(str1 as String, Optional str2 as string = "", Optional str3 as string = "") as Boolean
    [Do something with parameters, like send an email with programmatic parameters 'to', 'body', and 'subject']
End Function

Public Sub Bar()
    Dim dummyVal as Boolean
    [Do something]
    dummyVal = Foo("CustomerX@mail.com","Report [Today's Date]","Hello [Customer Name]...")
    [Do something]
End Sub

This could also be done by using

If Foo(args) Then
End If

These both work, but is probably confusing if someone tries to read my code. Is there a way to pass 3+ arguments to a VBA sub, or to get rid of the "dummyVal" syntax?

syntax vba excel
2 Answers

There is no 2-parameter limit for a sub:

Sub Runner()
    Test 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
End Sub

Sub Test(a, b, c, d, e, f, g, h, i, j)
    Debug.Print Join(Array(a, b, c, d, e, f, g, h, i, j), ",")
End Sub

Output:

1,2,3,4,5,6,7,8,9,10
user_4788840

Tim Williams' answer is right.

However, when dealing with a row of data, I find it useful to define a record, and pass that to any sub or function. This has the advantage of not having to re-code every parameter list when something changes - changing the record structure once is sufficient. For example:

Public Type typPerson
    GivenName As String
    Surname As String
    ShoeSize As String
    NoOfPets As Integer
End Type
Sub SavePersonDetails(recPerson as typPerson, ByVal dblRow as Double)
    Activesheet(dblRow, 1).Value = recPerson.GivenName
    Activesheet(dblRow, 2).Value = recPerson.Surname
    Activesheet(dblRow, 3).Value = recPerson.ShoeSize
    Activesheet(dblRow, 4).Value = recPerson.NoOfPets
End Sub

If you wanted to add a field such as "HasCar As Boolean" to the record structure, the parameter list for "SavePersonDetails" wouldn't change.

So perhaps, if the number of parameters is getting out of hand, a record might be useful.

Also, even if there was a difference such as you described between using functions and subs, you could use a function anyway. There's nothing that says you have to use the value that a function returns. For example:

Function ExplodeApp() as Boolean
    ExplodeApp = True
    Application.Explode
End Function

could be called by:

ExplodeApp

without anything actually accepting the value that it returns. A function is just a sub that returns a value; a sub is just a function that doesn't return a value. Otherwise, they are interchangeable. Try it - re-code a sub to be a function. (Also, that's a good way to reduce the number of names that show up in the list of macros that you can run from the Developer tab, since it only shows subs. But you can still run the function if you type in its name.)

user_129477510
Related