by Ciarán Walsh (1 Submission)
Category: VB function enhancement
Compatability: Visual Basic 5.0
Difficulty: Intermediate
Date Added: Wed 3rd February 2021
Rating: 
(6 Votes)
Explains how to create a function that will accept a varying amount of parameters
VBcodersSometimes it can be very useful to have a function that will accept any number of parameters. For example, say you wanted to join a number of strings into one string, but with a semicolon in between each one. You could do it like this:
JoinedString = String1 & “;” & String2 & “;” & String3
However, if you were doing the same thing more than once, it would be easier to use a function to do the job, like this:
Function Join(Seperator As String, String1 As String, String2 As String, String3 As String) As String
Join = String1 & Seperator & String2 & Seperator & String3 & Seperator
End Function
Unfortunately, this won’t do the job if you want to join a different number of strings, since you can only pass it three strings to join. You could pass the function an array with the strings you want to join, but then you would need to build up the array before you could call the function.
To create a function that can have a varying number of parameters, you use the ParamArray keyword when you declare a function. The syntax is like this:
Public Function Join(Seperator As String, ParamArray Strings()) As String
This makes Strings an array which can hold any number of parameters. When you use the ParamArray keyword, the variable following it must always be a variant array. It is also optional, so you don’t need to pass any parameters for it if you don’t need to.
Here’s an example for the Join function from before:
Public Function Join(Seperator As String, ParamArray Strings()As Variant) As String
Dim aString As Variant
For Each aString In Strings
Join = Join & aString & Seperator
Next
End Function
This can be called like so:
Joined = Join(";", "a", "b", "c")
This passes the Join function a semicolon as the Seperator variable, and “a”, “b”, and “c” will populate
the Strings array. Then the join function loops through the array using a For Each loop, and adds the Seperator and a string from the array. Unfortunately, since the ParamArray variable is a Variant, the string used to loop round must also be a Variant.
