Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Bmi Calculator

  1. #1
    Join Date
    14th February 2007
    Location
    Lincolnshire, England
    Posts
    12

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    19,293

    Re: Bmi Calculator

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

    Roy

    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.

  3. #3
    Join Date
    14th February 2007
    Location
    Lincolnshire, England
    Posts
    12

    Re: Bmi Calculator

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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    19,293

    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

    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.

  5. #5
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    19,293

    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

    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.

  6. #6
    Join Date
    14th February 2007
    Location
    Lincolnshire, England
    Posts
    12

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    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

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

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Cheers
    Andy


  8. #8
    Join Date
    14th February 2007
    Location
    Lincolnshire, England
    Posts
    12

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    2nd November 2005
    Location
    Tecumseh, OK
    Posts
    1,283

    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

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    14th February 2007
    Location
    Lincolnshire, England
    Posts
    12

    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 at 01:18.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Holiday Pay Calculator
    By kevinm12 in forum EXCEL HELP
    Replies: 2
    Last Post: May 21st, 2007, 21:54
  2. Vba Calculator
    By ka0sx in forum EXCEL HELP
    Replies: 3
    Last Post: January 29th, 2007, 09:41
  3. Calculator
    By chrisxs5 in forum EXCEL HELP
    Replies: 4
    Last Post: September 27th, 2005, 06:25
  4. Calculator
    By digikiwam in forum EXCEL HELP
    Replies: 2
    Last Post: May 21st, 2003, 23:57

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno