• Home
 · 
• VB function enhancement
 · 
• Create functions with a variable amount of parameters

Create functions with a variable amount of parameters

Explains how to create a function that will accept a varying amount of parameters

Rate Create functions with a variable amount of parameters

• 1
• 2
• 3
• 4
• 5

(7(7 Vote))

Sometimes 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.