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.
Let’s imagine that you find yourself vacationing on Playas De Mallorca, and according to the local weather forecast, high tomorrow is going to be 28 degrees Celsius. This sounds rather chilly, but how cold is it really? Your hotel’s WiFi is temporarily down, but your tablet is equipped with trusty Excel software. All you need to do is perform a simple calculation necessary to convert degrees Celsius into Fahrenheit degrees. This formula is F = C*9/5 + 32. Since you intend to perform this calculation multiple times, you decided to create a new UDF for this task.
Using the syntax above, you can easily do just that, the hard part is coming up with meaningful names for your function and variable(s). Since this function performs temperature conversion from Celsius to Fahrenheit, I’d suggest to call it TempCtoF. Let’s call your input variable DegreesC for Celsius temperature value. Our function becomes the following:
Function TempCtoF(DegreesC) TempCtoF = DegreesC * 9 / 5 + 32 End Function
Plugging in 28 degrees Celsius, the result is 82 degrees Fahrenheit, much better than a place you call home.
While this functions seems to work flawlessly, it actually can be improved by specifying data types for our variables. VBA by default assigns variant data type to your parameters, and this practice has a potential of being slow and using more memory. For this example, our input (degrees in Fahrenheit) is always going to be an integer (a whole number between -32,768 and 32,768.) Our output on another hand can be either integer (if we are not worried about precision of our calculation and all of the decimal digits we are missing), or double, if we in fact want to make sure that our output is 82.4, instead of 82. (To learn more about various VBA data types, head over to the Dummies series site, which has an excellent summary of them.) In addition, documenting our code is always welcome by either you, yourself, 6 months later, or anyone who has to change it down the road. This function cannot get any simpler, but at least let’s describe it in the code:
Function TempCtoF(DegreesC As Integer) As Double 'Celsius to Fahrenheit conversion 'Input is in Celsius, output is in Fahrenheit TempCtoF = DegreesC * 9 / 5 + 32 End Function
In due time, you are back at home, and need to tell your new friends from Spain how cold it actually gets in your neck of the woods at the end of October. The formula that will do this trick for us is: C = (F-32) * 5/9. You have enough knowledge of VBA to create a function for that:
Function TempFtoC(DegreesF As Integer) As Double 'Fahrenheit to Celcius conversion 'Input is in Fahrenheit, output is in Celsius TempFtoC = (DegreesF - 32) * 5 / 9 End Function
Running both of these functions for different values, we can see a following summary:
You recently read somewhere that over 100 million Americans have either Diabetes or Prediabetes. You’d like to find out your Body Mass Index to know if you happen to be one of three people affected by this disease. You found BMI calculation formula on CDC website: BMI = Weight/Height ^ 2, where Weight is given in kilograms, and Height in meters. So, if you are 6 feet tall, your height is going to be 183 centimeters, or 1.83 meters. This function accepts two parameters instead of one, as was the case with simpler temperature conversion functions:
Function BMI_Metric(WeightKg As Integer, HeightM As Double) As Double 'Adult BMI calculation (metric) BMI_Metric = WeightKg / HeightM ^ 2 End Function
This is not too complicated, but since US is using imperial measurement system, calculating your BMI will require three parameters. BMI = WeightLbs / (Height^ 2) * 703, where Weight is measured in pounds, and Height is measured in inches. I wrote three parameters, because you will supply your height twice: in feet AND inches, which this function will subsequently convert into inches only. Since there are 12 inches in a foot, our function becomes:
Function BMI(WeightLbs, HeightFt As Integer, HeightIn As Integer) As Double 'Adult BMI calculation (imperial) Dim HeightInches As Integer HeightInches = HeightFt * 12 + HeightIn BMI = WeightLbs / (HeightInches ^ 2) * 703 End Function
Notice that Dim HeightInches As Integer statement lets us declare HeightInches variable as an integer data type using Dim (Dimension) command.
Playing with different scenarios, our BMI calcuations presents these findings:
Knowing your BMI number is only half the battle, now you need to figure out how healthy you are based on CDC guidelines:
Let’s see if we can incorporate this logic in our VBA code. To start with, VBA allows us to forgo involved nested IF statement that we would normally use in Excel formulas, and instead introduces SELECT CASE command that we can easily adopt for our needs:
Select [ Case ] testexpression
[ Case expressionlist
[ statements ] ]
[ Case Else
[ elsestatements ] ]
End Select
Our new function to calculate BMI and describe our weight uses this code:
Function BMI_Desc(WeightLbs, HeightFt As Integer, HeightIn As Integer) As String
'Adult BMI calculation (imperial)
'Output also includes message whether your weight is healthy or not
Dim HeightInches As Integer
Dim BMI As Double
Dim strMessage As String
HeightInches = HeightFt * 12 + HeightIn
BMI = WeightLbs / (HeightInches ^ 2) * 703
'Determine how healthy this number is
Select Case BMI
Case 0 To 18.49
strMessage = " - Underweight"
Case 18.5 To 24.99
strMessage = " - Normal weight"
Case 25 To 29.99
strMessage = " - Overweight"
Case Is >= 30
strMessage = " - Obese"
Case Else
strMessage = " - Insufficient data to perform this calculation"
End Select
BMI_Desc = Round(BMI, 1) & strMessage
End Function
One more comment I’d like to make about this code is the str prefix I added to our Message variable. There is an ongoing debate whether we need to notate various naming conventions in our code: (s/str) for a string or text data type, (i/int) for an integer data type, etc. to better identify them. I will not attempt to solve this debate here, but do believe that if you find these abbreviations useful you should use them, if not, there is really no need for them. The only rule that you should follow is that you need to stick to your system, which is typically easier said than done. In this example, I know that strMessage variable is going to be a string data type and I shouldn’t expect anything but a text value from it.
By now I am fully confident in your ability to construct a similar function for metric input variables. Should you need any more help, you can download my workbook, complete with the code we just covered.
