Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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
    18,423

    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
    18,423

    Re: Bmi Calculator

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

    VB:
    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
    18,423

    Re: Bmi Calculator

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

    VB:
    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

    VB:
    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. 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,095

    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.
    VB:
    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.

    VB:
    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