BUILD A CALORIE TRACKER EXCEL WITH VBA

Hi guys, today I’m gonna show y’all how to use Excel VBA to automate certain processes in this calorie tracker excel sheet, use in built excel functions to format certain cells!

It’s really basic so if you’re new to VBA and want to build something you can use on a daily basis, give this a try.

1) Create the main summary sheet (pic 2)

So for me, I named the spreadsheet “Calorie tracking” and I put

Date, total calories, exercise, expected calories, calorics deficit, current weight

As my headers

Reason behind this is so that I can track my total calories consumption in a day, amount I lose through exercising, expected daily calorie intake to achieve weight loss and whether or not I achieved my goal today. If I didn’t, it’ll be in red means I made no caloric deficit and green means I did.

Lastly, measurement of my own weight daily to track if I lost weight

2) Calculate Calorie deficit (pic 3)

To calculate calorie deficit, you simply need to use the formula below

=$D2-$B2-$C2

what’s happening here is that you’re using your expected daily calorie intake to minus the daily total calorie consumption and daily exercise to find out whether you’re in caloric deficit or not

$ in excel means you’re locking this and that this value won’t change even if you drag down the formula

3) Drag down the formula (Pic 4)

Just double click on the bottom right corner of the cell where you see a black cross. Either double or drag down will work and you’ll realise all other cells in that column will have the same formula. You won’t have to copy and paste the formula 1 by 1

4) Apply conditional formatting (pic 5)

To allow yourself to better visualise whether or not you’re in caloric deficit, you can format when the value of cell is below 0, highlight the cell red. When the value of cell is more than 0, highlight the cell green

Click on the entire E row -> home tab (top left corner) -> conditional formatting -> new rule -> copy whatever I did in pic 5

Do it twice, just that the second time put “greater than” and Format with put Green highlight

5) Do a macro to automate creating next day sheet (pic 6)

Instead of manually creating a new sheet everyday, how about doing it with a click of a button?

To do macro, you have to first enable developer tab. You can refer to my other excel post on step by step tutorial or you can just google.

Once you’ve enabled developer tab, click on it and click on visual basics. Double on “This workbook” on the left side of the screen because this workbook is where you want to store your codes

Copy my codes from the picture.

What my code is doing is basically to create a new sheet and name it today’s date. So example 2nd October 2024, I formatted it to 02.10.24

After creating a new sheet, the macro will auto populate the headers to include Date, Food, Calories

And it’ll auto populate cell A2 to today’s date as well

6) Should look like pic 7 after running createNewSheet() function

How to run the macro, go to developer -> macro -> click on the function you want to run

7) Populate your sheet (pic 8)

Fill in the food you ate and the calories intake

8) Copy my codes from pic 9

UpdateSummary function is basically to sum up the total calories you had in a single day and populate it in the calories tracking sheet for that date and under “Total calories”.

9) Copy my codes from pic 10

This is to ensure that every sheet is updated so even if you make changes to a sheet from 20 days ago, it’ll still be updated in the calorie tracking page

Alright, that should be all. If y’all have any questions or faced any difficulties, please lmk. But go and try :)

Stay tuned for part 2 where I make this more interactive.

Example: Excel will ask you for age and weight and recommend you a diet plan

And adding sheets by asking you which date you want Incase you missed certain dates