Function Returns Array Excel VBA Macro

Microsoft Excel lets you create your own custom functions, which are generally called user defined functions (UDFs). These are Function procedures inside a module with the VBA code that performs the necessary operations or calculations. They are very similar to the conventional Sub procedures, the main difference being that the Function procedures (and the UDFs) return a value. But they can also return several values. In this post we see how to create a function that returns an array of values.

Macro code:

 Function geometry(d As Single) As Variant 'apply in sheet with Ctr + Shift + Enter
     Dim pi As Single, r As Single, l As Single, a As Single, v As Single, arr(3) As Single
     pi = 3.1416
     r = d / 2
     l = 2 * pi * r
     a = 2 * pi * r ^ 2
     v = 4 / 3 * pi * r ^ 3
     arr(0) = l
     arr(1) = a
     arr(2) = v
     geometry = arr
 End Function
 
 Sub GetCircleGeometry()
     Dim circDiameter As Single, circCircunf As Single, circArea As Single, circVolume As Single
     userInput = InputBox("Enter diameter in cm", "Circle Size")
     circDiameter = Val(userInput)
     If circDiameter <> 0 Then
         circCircunf = geometry(circDiameter)(0)
         circArea = geometry(circDiameter)(1)
         circVolume = geometry(circDiameter)(2)
         MsgBox "Cincunference: " & circCircunf & vbLf & "Area: " & circArea _
         & vbLf & "Volume: " & circVolume, , "Circle Geometry"
     End If
 End Sub
 

 

Macro explained:

The code above consists of two separate procedures. The Function procedure creates a UDF that can be called from any other macro or used in the Excel worksheet as any of the built-in Excel functions. However, this function returns  an array of three values, and therefore, it spans three cells in the worksheet.

Function procedure:

• The function “geometry” accepts the diameter of a circle as argument. The diameter variable is declared as a Single (floating number). That first line of code also shows that the function output is a Variant, because this function returns an array of values.
• The different variables used in the function are declared as Single. The array is declared as a static array with three elements (each of type Single).
• The following lines define the variables that calculate the radius, length of the circumference, area, and volume of the circle or sphere from that circle.
• Next, each element of the array is assigned the corresponding value (circumference, area, and volume).
• Finally, the function returns the array of values. The function can be called from any other macro (see below) or used in the Excel worksheet. When applied to a cell of the worksheet, the keys Ctr + Shift + Enter must be used after selecting the target range (which spans 3 cells).

Sub procedure:

• The macro “GetCircleGeometry” shows how to use a function that returns an array of values. The macro declares the variables that store geometric values of the circle as Single.
• First, the macro asks the user to enter the diameter of the circle using an input box. The variable userInput is a Variant that can anticipate erroneous input from user.
• The variable circDiameter gets the value of the input. The following conditional statement confirms the value is a number different than zero to proceed.
• The following lines get the circumference, area, and volume of the circle for a given diameter using the function created earlier. They get each of the three elements of the array returned by the function.
• A message box displays the circle geometry values for a given diameter that have been taken from the function that returns the array.

 

This is how we create a function that returns an array in Excel VBA.

 

Other macros:

• How to launch a Color Picker Dialog with Excel VBA macros
• How to Export Data As PDF in Excel with VBA macros
• How to Send Meeting Invites From Excel with VBA macros