- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to calculate the Surface Area through VBA user-defined function in MS Excel?
Users can design unique functions using VBA user−defined 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 user−defined 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 User−defined 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.