Announcement

Collapse
No announcement yet.

Bmi Calculator

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Bmi Calculator

    Hi,

    I'm trying to make a BMI (Body Mass Index) calculator. I've attempted it already but to no success. My first mistake was to read the values from cells in the spreadsheet which made things more complex than needed. So I started a new macro using a form instead for all data entry. I'm running into multiple problems with this and would like someone else to suggest the most simple code to do this.

    The macro should:-

    -Ask the user which method they prefer (see below formulas).
    -Input the result into a certain cell.

    English BMI Formula
    BMI = ( Weight in Pounds / ( Height in inches ) x ( Height in inches ) ) x 703

    or

    Metric BMI Formula
    BMI = ( Weight in Kilograms / ( Height in Meters ) x ( Height in Meters ) )

    Any suggestions would be much appreciated.

    Many thanks,

    Tom

  • #2
    Re: Bmi Calculator

    It shouldn't make any difference whether it is a spreadsheet or VBA,
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

    Comment


    • #3
      Re: Bmi Calculator

      I just found it difficult and fiddly using cells and think it would be easier doing everything using a VBA window.

      Comment


      • #4
        Re: Bmi Calculator

        Here's a UDF to calculate BMI, just check the results

        Code:
        Option Explicit
        
        Function BMI(wt As Long, ht As Long)
        'BMI = ( Weight in Pounds / ( Height in inches ) x ( Height in inches ) ) x 703
        BMI = ((wt / ht) * ht) * 703
        End Function
        Place it in a Standard Module & use

        =BMI(72,180)
        Hope that Helps

        Roy

        New users should read the Forum Rules before posting

        For free Excel tools & articles visit my web site

        If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

        RoyUK's Web Site

        royUK's Database Form

        Where to paste code from the Forum

        About me.

        Comment


        • #5
          Re: Bmi Calculator

          Place two Textboxes, a label & a button on a UserForm, then try this code

          Code:
          Private Sub CommandButton1_Click()
          Dim ht As Long
          Dim wt As Long
          
             On Error GoTo CommandButton1_Click_Error
          
          ht = Me.TextBox1.Value
          wt = Me.TextBox2.Value
          'BMI = ( Weight in Pounds / ( Height in inches ) x ( Height in inches ) ) x 703
          
          Me.Label1.Caption = "BMI is " & ((wt / ht) * ht) * 703
          
             On Error GoTo 0
             Exit Sub
          
          CommandButton1_Click_Error:
          
              MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CommandButton1_Click of Form UserForm1"
          
          End Sub
          Hope that Helps

          Roy

          New users should read the Forum Rules before posting

          For free Excel tools & articles visit my web site

          If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

          RoyUK's Web Site

          royUK's Database Form

          Where to paste code from the Forum

          About me.

          Comment


          • #6
            Re: Bmi Calculator

            Good stuff, thanks very much. I've found this helpful. I'm working on it at the moment, I will reply back when I get it working. Thanks again.

            Comment


            • #7
              Re: Bmi Calculator

              I think this is the formula you need Roy, otherwise my BMI is almost 150,000 and that would put me in the chubby category

                  Me.Label1.Caption = "BMI is " & (wt / (ht * ht)) * 703


              It is fairly straight forward with formula. See attached.
              Start by select measurement system in C10.


              The vba is only need for the userform example.
              Attached Files

              Cheers
              Andy

              Comment


              • #8
                Re: Bmi Calculator

                Yeah it's ok I noticed that little error and sorted it. I had a look at your way of doing it and that is good too.

                Comment


                • #9
                  Re: Bmi Calculator

                  Fine example Andy. I would recommend that the text of the formula be modified to use the power notation as done in the actual formula. While the formulas are correct, the text of the formulas are off due to order of operation.

                  If one likes UDF methods, this one does both. The 3rd parameter is default to True for metric so it need not be used.
                  =BMI(68,1.65) =BMI(68,1.65,True) resolves to 25.0.
                  =BMI(150,65,False) where (65" = 5'-5") resolves to 25.0.

                  Of course one can make a more simple UDF to eliminate the 3rd parameter by looking at the ratio difference between Metric and Imperial Weight/Height.
                  Code:
                  Function BMI(Weight As Double, Height As Double, Optional metric As Boolean = True) As Double
                  If metric Then
                    BMI = Weight / Height ^ 2
                    GoTo EndNow
                  End If
                  BMI = Weight * 703 / Height ^ 2
                  EndNow:
                  End Function

                  Comment


                  • #10
                    Re: Bmi Calculator

                    Thanks for the help guys, if you are interested then here is the version I made after your help. I am just going to add in some validation and then it's done.

                    Code:
                    Private Sub CommandButton1_Click()
                        Dim ht As Long
                        Dim wt As Long
                        Dim result As Double
                        On Error GoTo CommandButton1_Click_Error
                        
                        
                        ht = Me.TextBox1.Value
                        wt = Me.TextBox2.Value
                         
                         If OptionButton1.Value = True Then
                         
                        'BMI = ( Weight in Pounds / ( Height in inches ) x ( Height in inches ) ) x 703
                        Me.Label1.Caption = " " & (wt / (ht * ht)) * 703
                        Range("B55").FormulaR1C1 = Me.Label1.Caption
                        Range("B55").Select
                        
                        ElseIf OptionButton2.Value = True Then
                        
                        'BMI = ( Weight in Kilograms / ( Height in Meters ) x ( Height in Meters ) )
                        Me.Label1.Caption = " " & (wt / (ht * ht))
                        
                        End If
                         
                        On Error GoTo 0
                        Exit Sub
                         
                    CommandButton1_Click_Error:
                         
                        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CommandButton1_Click of Form UserForm1"
                         
                    End Sub
                    
                    
                    
                    Private Sub OptionButton1_Click()
                    If OptionButton1.Value = True Then
                    Label2.Caption = "Height (Inches)"
                    Label3.Caption = "Weight (Pounds)"
                    End If
                    End Sub
                    
                    Private Sub OptionButton2_Click()
                    If OptionButton2.Value = True Then
                    Label2.Caption = "Height (Metres)"
                    Label3.Caption = "Weight (Kilograms)"
                    End If
                    End Sub
                    
                    Private Sub UserForm_Click()
                    
                    End Sub
                    Last edited by Ozzy88; February 25th, 2007, 01:18.

                    Comment

                    Working...
                    X