13  Diet App

When creating a diet plan, there is a hyerarchy of importance to consider regarding what contributes the most to gain or lose weight.

As shown in Figure 13.1, the most important factor is the total calories consumed, followed by the macronutrient split (protein, carbs, and fat).

In this assignment, we will create a diet app that calculates users’ macronutrient split and diet duration based on their Total Daily Energy Expenditure (TDEE) (read more on What is my TDEE?).

Figure 13.1: Diet pyramid of importance. The base (total calories) is the most important, followed by macronutrient split, and finally, micronutrients.

13.1 Learning objectives

After completing this assignment, you should be able to:

  • Use comparison operators.
  • Use arithmetic operators.
  • Implement conditional statements If/Else/If Else
  • Create parameterized functions.
  • Distinguish different variable types (e.g., String, Double, Long, Boolean).
  • Create Subs to test functions.
  • Read/Write information from spreadsheets.

⭐TIP: Remember to use the Locals window (with breakpoints) and the Immediate window (with Debug.Print) to debug your code!

13.2 Testing instructions ⚙️

Test each function as follows:

  • Create a spreadsheet with the name of the function (e.g., FunctionName)
  • Create a simple form in this spreadsheet:
    • First row = headers matching function parameter names.
    • Second row = synthetic input data (use Excel functions Rand and RandBetween to create data within reasonable ranges for the parameters).

Example of input parameters for functions.
  • Use the function in two ways:
    • Write a sub TestFunctionName that reads the input arguments from the spreadsheet and print the result in column “Result TestFunctionName”.
    • In the Excel spreadsheet, use your function via = to read the arguments and write the result in column “Result FunctionName”.

13.3 Basal Metabolic Rate (BMR)

Create a function:

function BMR( _
    weightInGrams As Double, _ 
    heightInCm As Double, _
    gender As Boolean,
    ageInYears As Integer) As Double

that calculates the Basal Metabolic Rate (BMR) using the Harris-Benedict equation:

\(655 + (9.6 \times \text{weight in g}) + (1.8 \times \text{height in cm}) – (4.7 \times \text{age in yrs}) (Women)\) \(66 + (13.7 \times \text{weight in kg}) + (5 \times \text{height in cm}) – (6.8 \times \text{age in yrs}) (Men)\)

🤔THINK: What other types could the parameter Gender be?

13.4 Activity Level Multiplier

Create a function:

function ActivityMultipler(activityLevel As String) As Double

that returns an activity multiplier given activity levels as follows:

Table 13.1: Activity levels and their respective multipliers.
Activity
Level		 Description Activity
Multiplier
“Sedentary” little to no exercise + work a desk job 1.2
“Lightly Active” light exercise 1-3 days / week 1.373
“Moderately Active” moderate exercise 3-5 days / week 1.55
“Very Active” heavy exercise 6-7 days / week 1.725
“Extremely Active” very heavy exercise, hard labor job, training 1.9

🤔THINK: How to represent ActivityLevel using an integer code?

13.5 Calculating TDEE

Create a function:

function TDEE( _
    weightInGrams As Double, _ 
    heightInCm As Double, _
    gender As Boolean,
    ageInYears As Integer,
    activityLevel As String) As Integer

that returns the Total Daily Energy Expenditure (TDEE), which is equal to: \(\text{BMS} \times \text{Activity Multiplier}\).

13.6 Calculating the diet duration

Create a function:

function DietDurationInDays(StartWeightInGrams As Double, _ 
              targetWeightInGrams As Double, _
              caloricDeficitRate As Double, _
              TDEE As Double) As Integer

that returns the diet duration in days to reach a lower target weight by consuming daily fewer calories at a certain deficit rate (consider 1g fat = 9 calories).

Example:

Someone with a TDDE of 2,000 calories weighing 100 Kg and aiming to weigh 80 Kg by consuming 20% fewer calories daily will have to lose 20 Kg of fat, which corresponds to \(20 \times 1,000 \times 9 = 180,000\) calories. By eating fewer \(0.2 \times 2,000 = 400\) calories daily, the diet will take $ 180,000 / 400 = 450$ days to complete.

13.7 Calculating TDEE

Create a function:

function ProteinCarbsFatTargetGramsArray( _
    proteinRatio As Double, _ 
    carbsRatio As Double, _
    fatRatio As Boolean, __
    TDEE As Boolean) As Integer()

that receives a diet’s macro ratio and the TDEE and returns an array with the grams for each macro based on the TDEE calories.

Macronutrient Cal / g
Protein 4
Carbohydrate 4
Fat 9

13.8 Creating a diet plan

Download nutrition facts data at MyFoodData or access the file here.