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.


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.

Opening USerForm to Create a Body Mass Index Calculator in Excel Using VBA

  • Second, click on the UserForm option from the Insert option.

Selecting UserForm to Create a Body Mass Index Calculator in Excel Using VBA

  • Third, you will get the desired form like the below image.

  • Forth, go to View > Properties Window options.

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

  • Fifth, make the desired changes in the properties dialog box.

  • Sixth, you will the below result after desired changes.

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

  • Seventh, select the Toolbox option to open the Toolbox dialog box.

Opening Toolbox to Create a Body Mass Index Calculator in Excel Using VBA

  • Eighth, select the label option in the Toolbox dialog box.

Selecting Label option to Create a Body Mass Index Calculator in Excel Using VBA

  • Ninth, you will the below result.

 

  • Tenth, select the label, click on the Properties Window option and make the desired changes.

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

  • Eleventh, you can change Font, Font Styles, and Size and press OK.

  • Twelveth, you will the below result.

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

  • Thirteenth, select the label option from the Toolbox dialog box again.

Add Label to Create a Body Mass Index Calculator in Excel Using VBA

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

Adding Text to Create a Body Mass Index Calculator in Excel Using VBA

  • Seventeenth, you will the below result.

Adding text to Create a Body Mass Index Calculator in Excel Using VBA

  • Eighteenth, if you do the same previous step again, then you will the below result.

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

  • Nineteenth, go to the Pointer option in the Toolbox dialog box and you will have the desired result.

Inserting Pointer Button to Create a Body Mass Index Calculator in Excel Using VBA

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

View COde Make the desired changes and you will have the desired result.

  • 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

Insering CodeMake the desired changes and you will have the desired result.

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


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.

Taryn N

Taryn N

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS), and biofuels. She enjoys showcasing the functionality of Excel in various disciplines. She has over ten years of experience using Excel and Access to create advanced integrated solutions.

2 Comments

Leave a reply

ExcelDemy
Logo