This tutorial will demonstrate how to create a body mass index calculator in excel using VBA. The Body Mass Index (BMI) is used by nutritionists and other health scientists, to indicate the body fat of an individual. It is based on the weight of an individual, with the square of that particular individual’s height. With the help of BMI, one can easily understand how to maintain one’s diet or habits to maintain well-balanced health. So, it is very important to learn how to create a body mass index calculator in excel using VBA.
Download Free Calculator
You can download the free calculator from here.
What Is Body Mass Index (BMI)?
The Body Mass Index, simply called BMI, is an easy and affordable approach for determining body fat using just height and weight. BMI is a screening technique that can be used to determine whether a person is underweight, overweight, or obese. The generic formula for calculating the BMI is as below.
BMI = weight (kg) / { height (m) }2
In terms of the US customary unit system of measurement, the BMI is obtained by dividing the person’s weight in pounds (lbs) by the square of their height in inches (in) and then multiplying by the conversion factor.
BMI = weight (lb)/height2(in2) x 703.0704
This BMI calculator will allow the user to choose between either the metric or the US customary unit system. It will then calculate, based on the weight input from the user and the height input, the user’s BMI.
Step-by-Step Procedures to Create a Body Mass Index Calculator in Excel Using VBA
If you follow the steps correctly, you should learn how to create a body mass index calculator in excel using VBA on your own. The steps are:
1. Creating UserForm
In this case, our goal is to create an UserForm to create a body mass index calculator in excel. We can do that by following the below steps.
Steps:
- First, go to Developer > Code > Visual Basic options.
- Second, click on the UserForm option from the Insert option.
- Third, you will get the desired form like the below image.
- Forth, go to View > Properties Window options.
- Fifth, make the desired changes in the properties dialog box.
- Sixth, you will the below result after desired changes.
- Seventh, select the Toolbox option to open the Toolbox dialog box.
- Eighth, select the label option in the Toolbox dialog box.
- Ninth, you will the below result.
- Tenth, select the label, click on the Properties Window option and make the desired changes.
- Eleventh, you can change Font, Font Styles, and Size and press OK.
- Twelveth, you will the below result.
- Thirteenth, select the label option from the Toolbox dialog box again.
- Fourteenth, you will get a label option like the previous step and make the desired changes here.
- Fifteenth, you will the below result.
- Sixteenth, then select the Text option in the Toolbox dialog box.
- Seventeenth, you will the below result.
- Eighteenth, if you do the same previous step again, then you will the below result.
- Nineteenth, go to the Pointer option in the Toolbox dialog box and you will have the desired result.
- Twentieth, make the desired changes and you will have the desired result.
2. Using VBA Code
Now, we will try to create an UserForm to create a body mass index calculator in excel by using VBA code. We can do that by following the below steps.
Steps:
- First, right-click on the Command option and select the View Code option.
- Next, insert the VBA code here.
Private Sub cmdCalculate_Click()
Dim weightOne As Double
Dim heightOne As Double
Dim bmiCalc As Double
If optMetric.Value = True Then
Let weightOne = CDbl(txtWeight.Value)
Let heightOne = CDbl(txtHeight.Value)
bmiCalc = (weightOne) / (heightOne) ^ 2
bmiCalc = Format(bmiCalc, "0.0")
Let txtBMI.Value = bmiCalc
End If
If optCust.Value = True Then
Let weightOne = CDbl(txtWeight.Value)
Let heightOne = CDbl(txtHeight.Value)
bmiCalc = (weightOne) / (heightened ^ 2) * 703.0704
bmiCalc = Format(bmiCalc, "0.0")
Let txtBMI.Value = bmiCalc
End If
End Sub
- Then, right-click on the next command option and select View Code.
- Last, insert the following VBA code there.
Private Sub cmdCloseUserForm_Click()
Unload frmBMICalculator
End Sub
3. Inserting Command Button
Next, our aim is to insert the command button to create an UserForm to create a body mass index calculator in excel. We can do that by following the below steps.
Steps:
- To begin with, return to the main worksheet and select Developer > Insert > ActiveX Controls options.
- In addition, you will the below result.
- Furthermore, right-click on the command button and select the Properties option.
- Afterward, make the desired changes in the Properties option.
- Then, you will the below result.
- Moreover, like the previous step again right-click and select the View Code option and insert the following VBA code.
Private Sub cmdOpenForm_Click()
frmBMICalculator.Show
End Sub
- Finally, you will the below result.
4. Showing Final Result
After all the above processes, we will give a demo of our calculator. We can do that by following the below steps.
Steps:
- Firstly, insert the height and weight.
- Lastly, press the Click to see Your BMI option to see the desired result.
Read More: How to Calculate BMI Percentile in Excel (4 Easy Methods)
Conclusion
Henceforth, follow the above-described methods. Hopefully, these methods will help you to create a body mass index calculator in excel using VBA in excel. We will be glad to know if you can execute the task in any other way. Follow the ExcelDemy website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.
Related Articles
- How to Create a Weight Loss Graph in Excel (with Easy Steps)
- Weight Loss Spreadsheet in Stones and Pounds
- How to Calculate Weight Gain or Loss in Excel (5 Easy Methods)
- Use Formula for Tracking Weight Loss in Excel (2 Examples)
- How to Calculate BMI Z Score in Excel (with Quick Steps)
- Use the CONVERT Function to Calculate BMI in Excel
Wonderful!
Thank you so much.