Creating user-defined Excel functions (UDFs) – Temperature Conversion and BMI Calculation functions from scratch.
Our last two posts focused on using, or working with somebody else’s VBA code. This article will focus on creating UDF functions from scratch. We will start with a simple temperature conversion from Celsius to Fahreheit, and vice versa. We will then calculate metric Body Mass Index (BMI), graduating to an imperial BMI calculation with 3 variables.
Since we’ve already worked with VBA code, we know some basic syntax rules for our future functions:
1. Start out with Option Explicit statement.
Note : Option Explicit statement forces explicit declaration of our variables, and gives us an error message if we have a typo instead of performing incorrect calculations.
2. Define our function name and its parameter(s)
Function FunctionName(Parameter DataType) As DataType.
Note : VBA makes definining data types optional, but it is a good habit to have.
3. Perform our calculations: Your code goes here.
4. Exit function and return it’s value: End Function.