Microsoft Office Tutorials and References
In Depth Information
Function Examples
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
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
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
isn’t 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:
h If the argument is missing, the function returns a horizontal array of month names.
h 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.
h If the argument is greater than or equal to 1, it returns the month name that corresponds
to the argument value.
Search JabSto ::

Custom Search