A function that returns a VBA array

VBA includes a useful function called Array. The Array function returns a variant that contains an array (that is, multiple values). If you're familiar with array formulas in Excel, you'll have a head start understanding VBA's Array function. You enter an array formula into a cell by pressing Ctrl+Shift+Enter. Excel inserts brackets around the formula to indicate that it's an array formula. See Chapter 3 for more details on array formulas.

It's important to understand that the array returned by the Array function is not the same as a normal array that's made up of elements of the Variant data type. In other words, a variant array is not the same as an array of variants.

The MonthNames function, which follows, is a simple example that uses VBA's Array function in a custom function:

Function MonthNames()

MonthNames = Array("Jan", "Feb", "Mar", "Apr","May", "Jun", _ "Jul", "Aug", "Sep", "Oct", "Nov", "Dec") End Function

The MonthNames function returns a horizontal array of month names. You can create a multicell array formula that uses the MonthNames function. Here's how to use it: Make sure that the function code is present in a VBA module. Then in a worksheet, select multiple cells in a row (start by selecting 12 cells). Then enter the formula that follows, followed by pressing Ctrl+Shift+Enter:

=MonthNames()

What if you'd like to generate a vertical list of month names? No problem; just select a vertical range, enter the following formula, and then press Ctrl+Shift+ Enter:

=TRANSPOSE(MonthNames())

This formula uses the Excel TRANSPOSE function to convert the horizontal array to a vertical array.

The following example is a variation on the MonthNames function:

Function MonthNames(Optional MIndex) Dim AllNames As Variant

AllNames = Array("Jan", "Feb", "Mar", "Apr", _ "May", "Jun", "Jul", "Aug", "Sep", "Oct", _ "Nov", "Dec") If IsMissing(MIndex) Then MonthNames = AllNames

Else

Select Case MIndex Case Is >= 1

Determine month value (for example, 13=1) MonthVal = ((MIndex - 1) Mod 12) MonthNames = AllNames(MonthVal) Case Is <= 0 ' Vertical array

MonthNames = Application.Transpose(AllNames) End Select End If End Function

Notice that I use the VBA IsMissing function to test for a missing argument. In this situation, it is not possible to specify the default value for the missing argument in the argument list of the function because the default value is defined within the function. You can use the IsMissing function only if the optional argument is a variant.

This enhanced function uses an optional argument that works as follows:

♦ If the argument is missing, the function returns a horizontal array of month names.

♦ If the argument is less than or equal to 0, the function returns a vertical array of month names. It uses Excel's TRANSPOSE function to convert the array.

♦ If the argument is greater than or equal to 1, it returns the month name that corresponds to the argument value.

This procedure uses the Mod operator to determine the month value.The Mod operator returns the remainder after dividing the first operand by the second. An argument of 13, for example, returns 1. An argument of 24 returns 12,and so on. Keep in mind that the AllNames array is zero-based and that indices range from 0-11.In the statement that uses the Mod operator, 1 is subtracted from the function's argument. Therefore,an argument of 13 returns 0 (corresponding to Jan),and an argument of 24 returns 11 (corresponding to Dec).

You can use this function in a number of ways, as illustrated in Figure 10-4.

EI

BZ

iHl

X

ABC

E

LJ

<LJ

H I

K

L

h

1

Jan Feb Mar

Apr

May

Jun

Jul

Aug

Sep Oct

Nov

Dec

2

3

1 Jar

Jan [

4

2 Feb

Feb

5

3 Mar

Mar

6

4 Apr

Apr

7

5 May

May

8

6 Jun

Jun

9

7 Jul

Jul

10

8 Aug

Aug

11

9 Sep

Sep

12

10 Oct

Oct

13

11 Nov

Nov

-

14

12 Dec

Dec

15

16

-

> M \Sheetl ' H

J

►I

Figure 10-4: Different ways of passing an array or a single value to a worksheet.

Figure 10-4: Different ways of passing an array or a single value to a worksheet.

Range A1:L1 contains the following formula entered as an array. Start by selecting A1:L1, enter the formula, and then press Ctrl+Shift+Enter.

=MonthNames()

Range A3:A14 contains integers from 1 to 12. Cell B3 contains the following (nonarray) formula, which was copied to the 11 cells below it:

=MonthNames(A3)

Range D3:D14 contains the following formula entered as an array:

=MonthNames(-1)

Remember: To enter an array formula, you must press Ctrl+Shift+Enter.

The lower bound of an array created using the Array function is determined by the lower bound specified with the Option Base statement at the top of the module. If there is no Option Base statement, the default lower bound is 0.

Was this article helpful?

0 0

Responses

  • annemari
    Have a function return an array vba?
    5 months ago

Post a comment