# Using a VBA function in a different VBA function • ### 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

• 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