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



The Body Mass Index (BMI) is used by nutritionists and other health scientists, in order to give an indication of the body fat of an individual. It is based on the weight of an individual, in relation to the square of that particular individual’s height. It is worth mentioning that BMI is not a direct measure of fat and is calculated using the following formula:

The BMI calculation is obtained in the metric system, by taking the measurement of the person at hand’s weight in kilograms (kg) and then dividing by the square of the person’s height measured in meters (m).

BMI = weight (kg)/height2(m2)

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

We have already seen in a previous post, how to use the CONVERT function and other combined worksheet formulas, to calculate BMI for both the metric and the US customary unit system. We are now going to create a UserForm and use VBA, in order to create a BMI calculator.

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.

So let’s get started.

Introduction

1) The first thing we are going to do is create a UserForm with all the needed controls and textboxes.

2) Make sure you can access the Developer Tab, by going to File>Options.

3) Select Customize Ribbon and make sure Developer is checked. Click Ok.

4) Go to Developer>Code>Visual Basic.

5) Select the Insert Tab, and then choose UserForm.

6) The empty User form should appear, as shown below.

7) Go to View>Properties Window in order to see the Properties Window and set the properties of the Userform and the controls on the UserForm.

8) Change the Name of the UserForm, in the Properties Window, to frmBMICalculator and the caption to BMI Calculator.

9) Change the height of the Userform to 340 as shown below.

10) We will change the background color of our UserForm, by clicking on the drop-down next to BackColor and selecting this light orange, background color as shown below.

11) We are going to add the following controls to the UserForm:

  • Three labels
  • Two option buttons
  • Three textboxes
  • Two buttons

12) Our first label has the caption, Body Mass Index Calculator. Using the Properties Window, with the label selected:

  • Broadway Font was chosen
  • The font style set as regular
  • The font size was set to 22

The second label has the caption, Enter your weight: . Using the Properties Window, with the label selected:

  • Tahoma font was chosen
  • The font style set was regular
  • The font size was set to 10
  • The background color chosen was a light yellow
  • A border was added to this control

The third label has the caption, Enter your height: . Using the Properties Window, with the label selected:

  • Tahoma font was chosen
  • The font style set was regular
  • The font size was set to 10
  • The background color chosen was a light yellow
  • A border was added to this control

13) The first option button has the name optMetric, the caption Metric Units, and the group name bmiSys. This was all set using the Properties Window.

14) The second option button has the name optCust, the caption US Customary Units and the group name bmiSys. This was all set using the Properties Window.

15)  The first textbox name is set as txtWeight using the Properties Window. The second textbox name is set as txtHeight and the third textbox name is set as txtBMI. The background color of txtBMI was changed to light blue.

16) The first button’s name was changed to cmdCalculate, using the Properties Window and the caption, Click to see your BMI. The second button’s name was changed to cmdCloseUserForm, and the caption, Close UserForm.

17) Right-click the cmdCalculate button and choose View Code as shown.

18) Enter the following code for the click event of the button, which will be initiated when the user clicks on the button on the form.

“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) / (heightOne ^ 2) * 703.0704

bmiCalc = Format(bmiCalc, “0.0”)

 

Let txtBMI.Value = bmiCalc

End If

End Sub”

19) Right-click the cmdCloseUserForm button and select View Code.

20) Enter the following code, in order to close the UserForm when the user clicks the button.

“Private Sub cmdCloseUserForm_Click() 

Unload frmBMICalculator

End Sub”

21) Return to the worksheet and format the actual worksheet, as shown below and insert the free ExcelDemy silhouette man running image, provided.

22) Go to Developer>Controls>Insert>ActiveX Controls and select a Command Button.

23) Draw a command button on the spreadsheet as shown below.

24) Make sure Design Mode is activated and select the button.

25) With the button selected, click on Properties and the Properties dialog box will pop up as shown below.

26) Change the name of the button to cmdOpenForm, and change the caption to Click to Open the BMI Calculator.

27) Close the Properties Window by clicking the little red square in the corner of the window.

28) Right-click the button and choose View Code.

29) Type the code below, so that when the user clicks the button, the BMI Calculator UserForm is shown.

“Private Sub cmdOpenForm_Click()

frmBMICalculator.Show

End Sub”

30) Return to the worksheet, and make sure Design Mode is not activated.

31) Save your workbook as a macro-enabled workbook as shown.

32) When you click on the button in the worksheet, the BMI Calculator will be launched.

33) Choose either the metric or US Customary unit system, input a weight and a height and then click the Click to see your BMI button.

34) And there you have it.

Conclusion

UserForms can be designed and used for many simple and complex calculation requirements, which requires input from users.

Please feel free to tell us in the comments, what you use UserForms for.

Download working file

BMICalculator

Useful links

How to Use the CONVERT Function in Excel and creating a BMI Calculator template

ExcelDemy VBA Resources and Download Sheets

Why UserForms are Necessary in Excel

Body Mass Index (BMI)

How to Use the If-Then statement in VBA

Using option buttons in VBA

Using the CDbl Function in VBA

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.

In her spare time when she’s not exploring Excel or Access, she is into graphic design, amateur photography and caring for her two pets, Pretzel and Snoopy.

You may also like...

2 Responses

  1. wisainin@gmail.com' Wisai says:

    Wonderful!

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve the Math * Time limit is exhausted. Please reload CAPTCHA.