Calculate Surface Area Using VBA User Defined Function in MS Excel

Users can design unique functions using VBA userdefined functions to carry out their own tasks. The complex mathematical problem is also evaluated through VBA code. Users could create Macros with the use of VBA to automate tedious activities and quickly create forms, reports, and charts. Before writing the VBA code, you must learn the basic structure and syntax of variables and functions of the VBA program.

To calculate the Surface Area through VBA userdefined function

• Step 1 Open a new worksheet and enter the dataset as shown in the below image

• Step 2 Move to the Developer tab and click on the Visual Basic tab to write the required code

• Step 3 Write the following code in the window box

'creation of a function S_Area
Function S_Area(ra, Vol, ta)
'define variable
Dim gi As Double
gi = 3.14
'Use if else statement
If ta <> 0 Then
   S_Area = ((2 * Vol) / ra) + ((gi * ra * ra) * ((1 / Sin(ta)) - (1 / Tan(ta))))
Else
   S_Area = 0
End If
End Function

Use ctrl + S to save the worksheet and then go to the Excel sheet.

• Step 4 Deployment of Visual Basic Userdefined Function in the worksheet

  • In D2, write the UDF function's name and enter the three parametric values.

  • And then press Enter to obtain the calculated surface area.

Similarly, repeat the similar steps and enter the S_area() function for the next three columns till D5 with the resultant surface area.

Conclusion

The user may learn tricky tips and enhance their VBA programming skills to become proficient in MS Excel. This article provides the step by step demonstration of how to compute the surface area when radius, volume, and theta values are given in the worksheet.