Calculating the BMI (Body Mass Index) in Excel VBA

The formula for BMI is:

BMI=Weight (kg)/(Height (m)*Height (m))

Steps to Create the VBA Code

1. Open the VBA Editor:
   • Open your Excel file.
   • Press Alt + F11 to open the VBA editor.
   • In the editor, click Insert in the menu, then select Module to create a new module.
2. Add the VBA Code:

In the module, paste the following code:

Sub CalculateBMI()
    ' Declare variables
    Dim weight As Double
    Dim height As Double
    Dim bmi As Double
    Dim message As String   
    ' Ask the user to enter the weight (in kg)
    weight = InputBox("Please enter your weight in kilograms:")   
    ' Check if the weight is a positive number
    If weight <= 0 Then
        MsgBox "Weight must be a positive number.", vbCritical
        Exit Sub
    End If
    ' Ask the user to enter the height (in meters)
    height = InputBox("Please enter your height in meters:")   
    ' Check if the height is a positive number
    If height <= 0 Then
        MsgBox "Height must be a positive number.", vbCritical
        Exit Sub
    End If   
    ' Calculate the BMI (Weight / (Height^2))
    bmi = weight / (height ^ 2)   
    ' Determine the BMI category
    If bmi < 16 Then
        message = "BMI: " & Round(bmi, 2) & vbCrLf & "Category: Severe Thinness"
    ElseIf bmi >= 16 And bmi < 16.9 Then
        message = "BMI: " & Round(bmi, 2) & vbCrLf & "Category: Moderate Thinness"
    ElseIf bmi >= 17 And bmi < 18.4 Then
        message = "BMI: " & Round(bmi, 2) & vbCrLf & "Category: Mild Thinness"
    ElseIf bmi >= 18.5 And bmi < 24.9 Then
        message = "BMI: " & Round(bmi, 2) & vbCrLf & "Category: Normal Weight"
    ElseIf bmi >= 25 And bmi < 29.9 Then
        message = "BMI: " & Round(bmi, 2) & vbCrLf & "Category: Overweight"
    ElseIf bmi >= 30 And bmi < 34.9 Then
        message = "BMI: " & Round(bmi, 2) & vbCrLf & "Category: Obesity (Moderate)"
    ElseIf bmi >= 35 And bmi < 39.9 Then
        message = "BMI: " & Round(bmi, 2) & vbCrLf & "Category: Obesity (Severe)"
    Else
        message = "BMI: " & Round(bmi, 2) & vbCrLf & "Category: Morbid Obesity"
    End If   
    ' Display the result in a message box
    MsgBox message, vbInformation, "BMI Result"
End Sub

Explanation of the Code

• Declarations:
  • weight: Stores the user’s weight in kilograms.
  • height: Stores the user’s height in meters.
  • bmi: Holds the calculated BMI value.
  • message: Contains the message that will be displayed with the BMI result and category.
• User Input:
  • The InputBox function is used to ask the user for their weight and height.
  • If the user enters an invalid value (like a negative number or zero), an error message appears, and the code stops execution.
• BMI Calculation:
  • The BMI is calculated using the formula weight / (height ^ 2).
• BMI Categories:
  • Based on the BMI value, the program categorizes it into different groups, following standard BMI classifications:
    • Less than 16: Severe Thinness
    • 16 to 16.9: Moderate Thinness
    • 17 to 18.4: Mild Thinness
    • 18.5 to 24.9: Normal Weight
    • 25 to 29.9: Overweight
    • 30 to 34.9: Obesity (Moderate)
    • 35 to 39.9: Obesity (Severe)
    • 40 and above: Morbid Obesity
• Displaying the Result:
  • The result is shown in a message box, including the calculated BMI and the corresponding category.

How to Use the Code

1. Once you’ve added the code to the VBA editor, you can run the macro by pressing F5 within the editor.
2. A prompt will ask you to enter your weight and height. After entering the values, the result will appear in a pop-up message.

Notes

This code works in Excel with VBA enabled. You can further adapt the code for more complex use cases, such as working with data in an Excel worksheet. However, this example provides a straightforward approach for calculating BMI with user inputs.