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 have a head start on understanding VBA's Array function. You enter an array formula into a cell by pressing Ctrl+Shift+Enter. Excel inserts curly braces around the formula to indicate that it's an array formula.

CROSS- See Chapter 3 for more details on array formulas.


Note 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 (without the braces) and press Ctrl+Shift+Enter:

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


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 Dim MonthVal As Long

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


Select Case MIndex

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

Note 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. Keep in mind that the AllNames array is zero-based and that indices range from 0 to 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-5.




1 J





•uf Jkf

Sjpp Oct




i ir












t Mf|<


1 lufl




IB 11

** in


J.) Oil



11 S y,






■ *



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

Figure 10-5: 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 (without the braces), and then press Ctrl+Shift+Enter.

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:


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

Range F3 contains this (nonarray) formula:


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

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

CD- A workbook that demonstrates the MonthNames function is available on the companion ROM CD-ROM. The file is named month names . xslm.

0 0

Post a comment