A function with an indefinite number of arguments

Some Excel worksheet functions take an indefinite number of arguments. A familiar example is the SUM function, which has the following syntax:

SUM(number1,number2...)

The first argument is required, but you can have as many as 29 additional arguments. Here's an example of a SUM function with four range arguments:

Here's a function that can have any number of single-value arguments. This function doesn't work with multicell range arguments.

Function Concat(string1, ParamArray string2()) ' Demonstrates indefinite number of function arguments Dim Args As Variant

' Process the first argument Concat = string1

' Process additional arguments (if any) If UBound(string2) <> -1 Then

For Args = LBound(string2) To UBound(string2)

Concat = Concat & " " & string2(Args) Next Args End If End Function

This function is similar to the Excel CONCATENATE function, which combines text arguments into a single string. The difference is that this custom function inserts a space between each pair of concatenated strings.

The second argument, string2(), is an array preceded by the ParamArray keyword. If the second argument is empty, the UBound function returns -1 and the function ends. If the second argument is not empty, the procedure loops through the elements of the string2 array and processes each additional argument. The LBound and UBound functions determine the beginning and ending elements of the array. The beginning element is normally 0 unless you either declare it as something else or use an Option Base 1 statement at the beginning of your module.

ParamArray can apply to only the last argument in the procedure. It is always a variant data type, and it is always an optional argument (although you don't use the Optional keyword). Figure 21-2 shows this function in use. Examine the figure to see how the results differ from those produced by the Excel Concatenate function, which doesn't insert a space between the concatenated items.

Figure 21-2:

Using the Concat function.

Figure 21-2:

Using the Concat function.

Microsoft Excel - custom furictions.xls

!- <iö\m

Iii

£ile fil it View Insert Format

Toots Data Window Help

_ s

x

; j J A j, j 1 ^ Al 7 SL IJt ^ s, ■ J1-) - ■[% £

t? m

I

: Arial

|B I D|=

Ï-MJM.MI » % . i! i

! ïr «►

. A .

fl

D2

r-

=concat(ffi',a2)C2J

A

B

5

D E

s» l »

H

1

Tille

First

Last

2

Mr.

Jim''

Smith.

Mr. Jim Smith II

Dr

Tina

Pierson

Dr. Tina Peterson

4

Ms

Jane

Doe

Ms. Jane Doe

S

Mr

Frank

Franklin

Mr. Frank Franklin

Fi

Mr

Willie

prison

Mr. Willie Mielson

7

Mrs

Steve

Mafks

Mrs. Steve Marks

S

9

10

11

12

13

14

15

h *

f ,K'[\ Sheet!/Sheet2

\Sheet3ji

M

ji _j

0 0

Post a comment