locked
Using a VBA function in a different VBA function RRS feed

  • Question

  • I am having a problem referencing the VBA function "BMR" in another VBA function "brmActual". Both functions are below. Thank you. 

    Function BMR(Sex As String, Weight As Double, Height As Double, Age As Long) As Long
     If Sex = "M" Then
         BMR = 10 * (Weight / 2.20462262) + 6.25 * (Height * 2.54) - 5 * Age + 5
     Else
        If Sex = "F" Then
        BMR = 10 * (Weight / 2.20462262) + 6.25 * (Height * 2.54) - 5 * Age - 161
     Else
        BMR = "ERROR Must Enter M or F"
     End If
     End If
    End Function

    Function brmActual(Activity As String) As Long

    If Activity = "Extr-active" Then
        brmActual = BMR * 1.9
    Else
    If Activity = "Sedentary" Then
        brmActual = BMR * 1.2
    Else
    If Activity = "Litly-active" Then
        brmActual = BMR * 1.375
    Else
    If Activity = "Mod-active" Then
        brmActual = BMR * 1.55
    Else
    If Activity = "Very-active" Then
        brmActual = BMR * 1.725
    Else: brmActual = "ERROR"
    End If
    End If
    End If
    End If
    End If

    End Function
    Wednesday, April 17, 2013 3:55 PM

Answers

  • The BMR function has 4 arguments: Sex, Weight, Height and Age. You must supply the values of these arguments when you call BMR.

    Regards, Hans Vogelaar

    Wednesday, April 17, 2013 4:38 PM
  • BMR takes four arguments, so you need to pass those arguments to it when you call it. Instead of, for example:

     brmActual = BMR * 1.9

    use

    brmActual = BMR("M", 120, 65, 52) * 1.9

    or use variables that hold values assigned elsewhere

    brmActual = BMR(strGender, dblWt, dblHt, lngAge) * 1.9

    Though why you use long instead of integer for an age is beyond me, unless your ages are in seconds....  ;-)

    You can also define your function using Select Case, like this

    Function brmActual(Activity As String) As Variant
        Select Case Activity
        Case "Extr-active"
            brmActual = BMR(strGender, dblWt, dblHt, lngAge) * 1.9
        Case "Sedentary"
            brmActual = BMR(strGender, dblWt, dblHt, lngAge) * 1.2
        Case "Litly-active"
            brmActual = BMR(strGender, dblWt, dblHt, lngAge) * 1.375
        Case "Mod-active"
            brmActual = BMR(strGender, dblWt, dblHt, lngAge) * 1.55
        Case "Very-active"
            brmActual = BMR(strGender, dblWt, dblHt, lngAge) * 1.725
        Case Else
            brmActual = "ERROR"
        End Select
    End Function





    Wednesday, April 17, 2013 4:43 PM
  • You could use

    Function BMR(Sex As String, Weight As Double, Height As Double, Age As Long) As Variant
        Dim dblBMR As Double
        dblBMR = 10 * (Weight / 2.20462262) + 6.25 * (Height * 2.54) - 5 * Age
        Select Case UCase(Sex)
            Case "M"
                BMR = dblBMR + 5
            Case "F"
                BMR = dblBMR - 161
            Case Else
                BMR = "ERROR - Sex must be F or M"
        End Select
    End Function
    
    Function brmActual(Sex As String, Weight As Double, Height As Double, Age As Long, Activity As String) As Variant
        Dim dblBMR As Double
        Select Case UCase(Sex)
            Case "F", "M"
                dblBMR = BMR(Sex, Weight, Height, Age)
                Select Case UCase(Activity)
                    Case "EXTR-ACTIVE"
                        brmActual = dblBMR * 1.9
                    Case "Sedentary"
                        brmActual = dblBMR * 1.2
                    Case "LITLY-ACTIVE"
                        brmActual = dblBMR * 1.375
                    Case "MOD-ACTIVE"
                        brmActual = dblBMR * 1.55
                    Case "VERY-ACTIVE"
                        brmActual = dblBMR * 1.725
                    Case Else
                        brmActual = "ERROR - Incorrect activity"
                End Select
            Case Else
                brmActual = "ERROR - Sex must be F or M"
        End Select
    End Function

    (If the return type of the function is Long, it can't return a string)

    Regards, Hans Vogelaar


    Wednesday, April 17, 2013 9:05 PM

All replies

  • The BMR function has 4 arguments: Sex, Weight, Height and Age. You must supply the values of these arguments when you call BMR.

    Regards, Hans Vogelaar

    Wednesday, April 17, 2013 4:38 PM
  • BMR takes four arguments, so you need to pass those arguments to it when you call it. Instead of, for example:

     brmActual = BMR * 1.9

    use

    brmActual = BMR("M", 120, 65, 52) * 1.9

    or use variables that hold values assigned elsewhere

    brmActual = BMR(strGender, dblWt, dblHt, lngAge) * 1.9

    Though why you use long instead of integer for an age is beyond me, unless your ages are in seconds....  ;-)

    You can also define your function using Select Case, like this

    Function brmActual(Activity As String) As Variant
        Select Case Activity
        Case "Extr-active"
            brmActual = BMR(strGender, dblWt, dblHt, lngAge) * 1.9
        Case "Sedentary"
            brmActual = BMR(strGender, dblWt, dblHt, lngAge) * 1.2
        Case "Litly-active"
            brmActual = BMR(strGender, dblWt, dblHt, lngAge) * 1.375
        Case "Mod-active"
            brmActual = BMR(strGender, dblWt, dblHt, lngAge) * 1.55
        Case "Very-active"
            brmActual = BMR(strGender, dblWt, dblHt, lngAge) * 1.725
        Case Else
            brmActual = "ERROR"
        End Select
    End Function





    Wednesday, April 17, 2013 4:43 PM
  • Is there a way to call on the BMR that was created in an earlier function. What I mean by that is there a way to make so that when I enter the function in a cell it is looking for the following information bmrActual(M,210,74,25,"Sedentary") in other words bmrAcutal(Sex,Weight,Height,Age,Activity) and I want the function in VBA to look like this. Thank you.

    If Activity = "Extr-active" Then
        bmrActual = BMR * 1.9
    Else
    If Activity = "Sedentary" Then
        bmrActual = BMR * 1.2
    Else
    If Activity = "Litly-active" Then
        bmrActual = BMR * 1.375
    Else
    If Activity = "Mod-active" Then
        bmrActual = BMR * 1.55
    Else
    If Activity = "Very-active" Then
        bmrActual = BMR * 1.725

    Wednesday, April 17, 2013 8:42 PM
  • You could use

    Function BMR(Sex As String, Weight As Double, Height As Double, Age As Long) As Variant
        Dim dblBMR As Double
        dblBMR = 10 * (Weight / 2.20462262) + 6.25 * (Height * 2.54) - 5 * Age
        Select Case UCase(Sex)
            Case "M"
                BMR = dblBMR + 5
            Case "F"
                BMR = dblBMR - 161
            Case Else
                BMR = "ERROR - Sex must be F or M"
        End Select
    End Function
    
    Function brmActual(Sex As String, Weight As Double, Height As Double, Age As Long, Activity As String) As Variant
        Dim dblBMR As Double
        Select Case UCase(Sex)
            Case "F", "M"
                dblBMR = BMR(Sex, Weight, Height, Age)
                Select Case UCase(Activity)
                    Case "EXTR-ACTIVE"
                        brmActual = dblBMR * 1.9
                    Case "Sedentary"
                        brmActual = dblBMR * 1.2
                    Case "LITLY-ACTIVE"
                        brmActual = dblBMR * 1.375
                    Case "MOD-ACTIVE"
                        brmActual = dblBMR * 1.55
                    Case "VERY-ACTIVE"
                        brmActual = dblBMR * 1.725
                    Case Else
                        brmActual = "ERROR - Incorrect activity"
                End Select
            Case Else
                brmActual = "ERROR - Sex must be F or M"
        End Select
    End Function

    (If the return type of the function is Long, it can't return a string)

    Regards, Hans Vogelaar


    Wednesday, April 17, 2013 9:05 PM