How to Create a Body Mass Index Calculator in Excel Using VBA

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.

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.

How to Create a Body Mass Index Calculator in Excel Using VBA: Step-by-Step Procedures

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 a 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 see the below result after the 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 see 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 see the below result.

• Sixteenth, then select the Text option in the Toolbox dialog box.

• Seventeenth, you will see the below result.

• Eighteenth, if you do the same previous step again, then you will see 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 a 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 Click to See Your BMI 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 Close UseForm 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 a 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 CommandButton 1 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 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 see 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.

Download Free Calculator

You can download the free calculator from here.

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. 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.

<< Go Back to BMI | Formula List | Learn Excel