Okay, welcome everybody.

My name is Alistair Matchett.

I was a banker myself at JP Morgan doing oil and gas and financial institutions M&A and then went into the education business and we've got a about an hour, I don't think Wes probably shy of an hour to cover VBA or in, in an introduction to VBA in Excel.

And our aim is to do two main things.

Firstly is to give you an understanding of how VBA works and how to start coding in VBA.

And then the second main thing we want to do is we want to give you three use cases, which I think are very, gonna be valuable as a financial analyst.

The very first one is to understand how to build a print macro.

And that's pretty useful if you have a very large book sheet and there's always sections you want to print.

And instead of having to go to the page, make sure it's all selected.

Instead you can just press a button on the spreadsheet and it'll print that particular section.

You can have like 10 buttons that will print different element, different sections.

That's the first one. The second use case we're going to do is an if statement in a macro and that allows you to build a toggle.

So something that would turn on and off and that could be formatting, it could be a calculation.

It'd just be something where you want to just be able to do a keyboard shortcut, will apply style and then unapply style.

Then the third one, which is a bit more complex, is a loop macro.

And the loop macro will take you through or get Excel to do a repetitive task, but continue to do it until a condition is set.

And we're going to use the iterative formula or the iterative calculation as our use case there.

So what you can do is you can get a macro to build, to do the iterations in the model for you.

So then an LBO model for example, it can iterate through the interest on interest calculation and do that through a macro and then just replace the numbers hard number so you don't get a circularity in the model.

So that's how gonna be our last case and that's pretty more complex case.

And so as a consequence, I'm gonna leave that until last.

So in preparation, what we're going to do first is we're gonna run through some kind of introductory slides.

And I'm gonna share my screen.

So you should be able to see my screen just there we go.

Just to see, we can see what's going on.

So these should be able to get these slides either in the resources section down below or on the website in Felix.

You'll be able to get these slides as well.

What I'm going to do is I'm actually going to be starting with a blank Excel sheet.

And so if you want the answers, we will make sure that they get uploaded to the website after the session.

Okay, so I'm gonna use a, there is a template but I'm actually prefer to use a, a completely blank Excel sheet.

So let's kick off by just starting with the, the Excel VBA environment.

The first thing you need to make sure you have done and you can follow with me, you can actually build these macros with me if you'd like, is you need to have the developer tab activated.

If you don't have the developer tab activated, you're not going to be able to create macros.

So the first thing we're gonna do is put on the developer tab. So let me jump to Excel to show you how to do that because it's not very difficult.

And you can see on my excel at the very top, you've got the menu bar along here.

I'm going to right click the menu bar, menu bar, sorry, I've got actually into Excel.

In my Excel. There we go.

Right click the menu bar and then it says customize ribbon.

And then if you choose that on the right hand side where you've got your main tabs, there is one called developer. Now mine is already checked.

Yours may not be, if it isn't checked, just check it and that means you'll get the ribbon that we can use do the VBA coding with.

So you must have that activated.

And just to remind people, just right click the ribbon customized ribbon and then make sure that you've got developer tick on the right hand side here.

And that means that you can get the VBA menu up to be able to code in it.

Then what you can do, if you look under developer, you see you've got this menu here and let me just explain a few of the things.

So you've got the visual basic editor, which is kind of like if you've ever coded in Python, it's like the Jupiter editor in Python.

So that will bring up the, just that little kind of the area that we will build the code in.

Don't need that right now. Then we've got the list of macros here.

Just the list of macros that you have created, I haven't created any yet.

And then the ability to record a macro, there's actually an easy way of doing that.

And then there's an option here which says use relative references.

This is important to understand if you want to create a macro that is going to work spatially as opposed to from an address.

In other words, you want to macro to kind of go to cells up and then two cells to the right and you don't want it to always do that in the same place.

Then you want to make sure that you have relative references activated.

So you say, I've just clicked that it's kind of highlighted.

If I deselect it, it means it's going to just use absolute references in the cell.

Okay? So it's quite important to understand that.

So that is important to be able to use if you want a macro to always work wherever you are in the spreadsheet.

I've got some just macro security settings there.

Generally speaking, if you go to the macro security settings, I would normally have disabled VBM macros with notification.

Okay? So you always get popup when you open the file.

Then just on the right we've got some add-ins there.

I'm going to ignore those.

Insert will come to, that's how you can attach a macro to a button and we will do that later on.

And then you've got some other items to do with the actual building of the macros like properties, view code, run dialogue, and that's most of what we're going to cover in the macros.

So that's the developer tabs. You do need the developer tab.

So in the visual basic editor, there's three main sections.

There's the kind of the code window, that's when you actually see the code.

Then there's that little testing window, you don't need to have that open.

And then the third window is almost like a kind of window explorer. If you go to Windows exploring, you have all the files and the folders.

That's kind of what's going on here.

So we will be using both number one and number three, but mostly number one as in keyboard shortcuts.

So if you want to stop or start recording, alt LR or stop or start recording. ALT F8 run a macro.

You can also assign a macro to keyboard shortcut, which we will do.

And then ALT F11 opens the VBA editor.

Then when you're in the VBA editor, you've got a number of different shortcuts, which in the bottom row, okay, gonna start by recording a macro.

And the reason we use the recorder is unless you know visual basic code, there's gonna be things you just can't remember.

So it's much, much better, even if you are actually going to do some coding is to record what you wanna do first.

So you get a lot of components.

But one of the things I'd be really clear, clear about here is that the, the macro recorder is I would not use that continuously.

I would always just use that to get some of the initial data in first.

But we're actually gonna be doing, using that as a base to get some of the coding in. And then we going to customize the coding in most cases even where we're just recording ourself doing repetitive thing.

Because that's the whole point of macros. Generally speaking, what they're aiming to do is help you do something just repeatedly.

So what I'm gonna do first is I'm going to create a print macro and even a print macro, you can do it using the recorder, but there's some really dangerous elements when you use the recorder.

If any of you have any questions, please don't hesitate to just put them in the Q&A, this little Q&A icon beneath me.

Put your question there and I'll happily answer them.

So let's go and do a print macro first.

I've got a blank Excel spreadsheet here.

So I've got a complete blank Excel spreadsheet.

And what I'm gonna do is I'm gonna put some data in this because I need to print something.

So I'm gonna go to go to Felix and let me just pull it down to the screen that I'm sharing.

And what I'm going to do is I'm gonna go to, let go to a company and I'm gonna pull in say their income statement. So I'm just gonna pull in their income statement and I'm gonna pop that into Excel.

So I've got a data set here, which is Kellogg's income statement.

I'm gonna widen column A to let's say 30K slides and maybe I'll zoom out a little bit um, so I can see a little bit better. There we go. So what I want to do is I'm to build a macro that will print this and there's a few things I want to make sure happens.

I want to make sure that it comes to this location because the button to print could be on another tab.

So I want to come to this location, select print, and I want to make sure I have my grid line showing my row and column headings showing. I've got some features in the footer that I want to put in.

I want to have one pages wide, one page at all.

So all those really irritating things, it's so easy to forget where you're gonna print.

You go to the printer and you say, oh darn, I've forgotten that you want to have.

So you don't need to worry about it, it'll always be done.

But the first thing to do is we're going to highlight the section that we're gonna print. So I'm gonna just highlight print area and then do control F3. And the benefit of doing a range name that we're gonna create here is that this will give us, give us the ability in the macro to say go to this range name and print the area, which is the range name.

And the nice thing about using range names is that with range names, if you want to change them, it's easy to do right? You can do it in Excel without going to the macro code.

If you build a macro code with cell references every time you want to change the cell references or insert a row or a certain column, you've gotta go into the macro code and change macro code.

So to avoid that wherever possible I would use range names where you want to pick up absolute references.

So I'm gonna choose new here and I'm just gonna do call. This is make it nice and simple, it's referring to that scope of the workbook.

No problem. So I've just called this is which is gonna stand for income statement.

So now what I'm going to do is I'm going to do the macro recorder.

Now there's two ways, two, two kind of main ways that you would use in macro recorder.

One is the keyboard shortcut, alt lr and that would start the recorder or down on the bottom my status bar right at the bottom of the screen.

Can you see where my cursor is? It's got this little icon and this is telling you whether the recorder is recording or not.

Now what I would do here is I would always have this on your status bar because it means that you can see whether you are still recording.

Because sometimes it's actually pretty tricky to remember that.

And the worst thing that happens if you are recording is that then you run a macro while you're recording and then you get this doom loop that doesn't stop unless you press escape.

Escape is like stop the macro.

So in order to get this on just right, click the status bar at the bottom and make sure that you have the macro recording ticked.

Okay, so just right click the status bar and have micro recording ticked.

And let's start the macro just by clicking that.

So if I click that, you can see that allows us to start macro and I'm gonna call this the macro name is print.

There's a few things to be aware of here.

The first is you can't use spaces to macro names because they come into formulas and Excel won't do formulas, spaces, that's the first issue.

The second issue, you can't put numbers the beginning of macro name.

So you can't put like one print, two print, three print, but you can do print one, print two, print three, print four.

So just be aware that sometimes if you don't do a correct name you get an error message.

The next thing we can do, we could assign it to a shortcut key and you can type that into T.

But a lot of these shortcut keys are actually taken away by Excel.

And if like, like if you're like me and I have a little add in which has called shortcut keys, it potentially will overwrite that short, those shortcut keys.

So I'm control And then hold on, I'm actually gonna just hold down shift and hit T and then you can see the shortcut goes from control shift and T.

So I'm gonna sign this to control shift T and the next option is to say where do you wanna save this? And I have three options, this workbook, a new workbook kind of random that you could do.

And lastly, the personal macro workbook.

Well what is the difference If I choose this workbook, the macro code will be attached to this workbook, which means if I email the file to somebody else, they will also be able to run the macro. They'll still have to when they receive the file, have to say yeah I accept that this, there's macros in this file when you open it they'll probably get an error, error message pop up ask.

But it means that the macro moves with the file and that can move obviously for a print macro you definitely want this workbook.

But some macros like the if statement macro, if you want to do say formatting for example, you kind of want that to be available not just for this single workbook but any workbook that you open.

So in that case I would choose personal macro workbook.

Because the personal macro workbook is like your own little cupboard of macros that you can apply to any Excel file that you open.

New workbook is where I guess you just want to put them in a separate workbook.

And I guess if you just want to kind of separate the macros from the sheet, that could be a good reason if you want to email people a file without macros.

But I'm gonna choose this workbook for my print macro and then I'm choose, okay, now I'm recording but don't panic, it's not like video recorder where you know you are eating up storage.

It's only gonna record what we do on the screen and if we make a mistake don't panic because you can edit the code afterwards.

So I would really just kind of take it easy.

So the first thing I'm going to do is I'm going to go to the place where I wanna print.

So if I press F5, you'll notice that I do have the is range name listed there that I created before.

And then I'm looking, okay, so what that does, it does two things. First it goes to the correct place and I could be on another sheet, remember maybe 20 sheets away so it goes to the correct place. Number two, it selects the correct place as well.

So that's the first thing I've done.

Then what I want to do is I'm gonna do control P to print.

And at the moment it's saying print the active sheets.

I'm actually gonna change that to print selection because remember I did that selection and I want to make sure it just prints the selection.

But I've got portrait orientation, that's fine and margins I don't really care about.

But I'm gonna go down to page setup.

I've got some more items that I want to choose here.

I'm gonna fit to one page wide, a one page tool and margins.

I don't really care about hedge on footer, I'm an custom footer.

Let's say I always want to put a copyright notice in there or buy notice and I'm gonna put say let's some pages in there as well.

Could be a logo as well. And then she's on. Okay.

And then go to the sheet tab and I'm gonna check grid lines and row and column headaches. Basically anything that you want to specify, then you need to put it in here.

Then I'm gonna click on, okay, and then what I'm going to do is I'm going to click print and then it will print that thing.

Now we're still recording.

So the macro is recorded every single move that I just made in that macro and now I need to turn off macro.

So I'm now gonna use the keyboard shortcut to do that.

So al alt lr and now macros, the macro recorder has stops. You can see here. Now I guess I could just use that acro, but, so if I can test to see if it works control, oh I do need to ALT L, alt L and then I go to PM for macros, the list of macros.

And then I'm just gonna choose run.

And you can see that that's work, that's print.

In fact, let me just do it. So I'm on another tab that's all the way down to the very bottom right of this spreadsheet.

And then I'll do alt LR and alt LPM and then run that macro and you can see it goes the right place prints.

Now the other thing it's done is that it's still kept that selection.

You may want it to tell, tell it to go to another range.

That's something else you could do.

So you could do select say LA one after you've done it so you don't have that selection. So those are all little things that you want to think about.

But let's go have a look at the code first.

So I'm gonna do alt F 11. So alt F 11.

And that brings up the macro code.

Now you'll see here that you've got a number of different projects here. Projects are like the kind of route to directories of the macros.

But you see that the file I'm working on here is called FE styles one and it's got two sheets in there and it's also got some modules. And the modules is where we're interested in.

So if I open the modules folder and go to modules one, double click it, there we go.

This is the macro that I just recorded.

Now if I can zoom in here a little bit, If I could zoom in, Could zoom in.

Yeah, that's irritating.

Okay, well we're just gonna have kind of slightly short nights short view, but can you see at the very top it says sub, well sub stands for subroutines, that's the beginning with the macro and then it's got the macro name followed by then It's got a series of lines with an apostrophe at the beginning.

What that means is that the apostrophe says don't treat this as code, treat it as text as documentation.

So this is documentation and it's got the name is print macro and then it's also got the keyboard shortcut.

It's a scientific control shift team.

So then what we're going to do is we're just going to make sure that we just tie this up.

So in the application it says go to reference is, and we did want that, we wanted to go to the correct place if you remember.

But then what we're going to do is we've got the application page set up.

I don't want, so I'm gonna take all this out and apart from this item here where it says active sheet page, set up print area.

And you'll notice it's just got open quotes, double quotes.

So what I'm going to do is just type is there because what that will do is it'll set the printer area to that range name is and then application printed connection, false, I'm just gonna take that out.

Then it's saying with active sheet page setup.

So this is where all those options that we had for the page setup have come in.

But can you see it's actually covered every single option almost in that dialogue box.

Now in today's computers there's so far that actually it's not so much of an issue but the old days, if you had a macro like this, it would actually run quite slowly And it's always good practice in my view to slim down your code to only have the minimal amount because what you can do is you can have let's say 10 print macros which print different parts of the sheet and then you can have a macro that runs the 10 macros, if that makes sense.

And that means you can say just print the financial statements or print everything.

So this means that more efficient your code is the faster it will run.

So what I'm going to do is I'm just going to remove anything I don't want these headers I didn't care about but I did care about the footer. If you remember I did put in some information about the footer.

So I want at Alastair Matchett, I want the page numbering.

I don't care about the right footer or the margins.

I did want to print the headings and the grid lines.

I don't care about comments.

I don't care about centering vertically.

I did wanna make sure it's a portrait orientation, all this is garbage but I did want to fit to one page wide and one page tall and then I don't care about errors or that or that or that.

In fact I didn't wanna include any of those.

I'm literally gonna delete all that.

So you can see here I've really reduced the amount of code because A, it's much, much easier if you want to edit the code to have less code there and it will run faster.

So that's gonna end the setup then it's going to be application print communication.

True, yeah because you want to print it.

And then selection print out copies one true, that's fine.

Then what I'm going to do is add another go-to command at the end.

Because I kind of like, I kind of my obsessive compulsive nature is saying I just want to make sure that this is a little bit tidier.

So I'm not gonna go to the IS range.

I'm going to type L1.

Now I know I said don't use cell references but a A1 is coming like and generic, it's gonna be in every spreadsheet, right? So go to reference a one.

So that will just take it to the top left hand corner of the sheet that we have just printed.

So there's a few things I've done there is I've really slimmed down the code and I've done a beauty save at the end of the macro.

So then I'm gonna save the macro here and I need to save the whole workbook if I do that and I need to save the workbook as a macro enabled workbook and I'm just gonna choose this as macro example two, I did another example this morning and then I'm gonna close the visual basic editor and I'm gonna test my macro.

So I'm gonna do ALT L and then PM for macros.

And I'm gonna run is print so you can see, oh I've got a bit of an error there.

Can you see that? So I'm just gonna go and debug and it doesn't like that reference cell A one.

Okay? Because it's cause a problem.

So what we need to do is we just need to find out what the correct reference is to go to cell A1. So let's do that just by using the recorder.

So let me just quickly close that and I'm going stop debugger and then what I'm going to do is just do ALT lr and I'm not going to assign this to macro a keyboard shortcut.

I'm just use macro six.

Then let's just do control home and go to sell a one.

Then I'm gonna stop recording ALT LR and then I'm gonna go alt F11 and can you see range A one select? So that's the correct code to use.

So I'll copy that and I'll just paste it in there.

So that's how I would always do that and I'm just gonna delete this so I don't need that anymore.

And so you can see that's how I would just, you can learn how to do that just like copy something, just remember how to do it.

Then I'm gonna save this again and then I'm gonna go back and see if our macro works now.

So I'll do ALT L and then PM for macros.

And then is print done? It works, it's gone printed and it's done saved to cell 1.

So that is a print macro.

Now if you didn't know there were macros in the model, you wouldn't know that they're there, right? So how would you make it easy for somebody who didn't build this model to activate the print macro? Well that's where we do a button.

So let's go and put a button in the macro.

I'm gonna come up to the developer tab and I'm going to go to insert and I go to form controls.

We don't want the active X controls really frustratingly.

The active X controls look very, very similar to the form controls but they work very differently.

And can you see on the top left it says butter.

So I'm gonna click on that and it brings you up a little skinny little cross there and I'm gonna use that.

I'm gonna drag a little rectangle on the sheet there and when you do that, so I'm going to choose this a print call it print, is or yeah, I'm actually going to, sorry sign, just click is print and then choose.

Okay? And what's happened is that macro has been kind of attached to that button.

So if you click the button you'll lac activate the macro.

You can actually this with graphics as well.

So you can have some fun with that.

But I'm going to click in this and I'm gonna just rename it print income statement.

There we go. And then click away from it.

And you have a button now that you can click and it will print the income statement. And actually if I copy that, I just select it again, right? There we go. And if I copy that, I think maybe I'll just do a copy.

If I go and put that in somewhere else in the spreadsheet and I click on this, yeah, can you see it has copied to another place in the spreadsheet that I can activate that macro from.

So the really nice thing about this is if you have a really big model you can build like macros for each sheet and you have a little button that says print this sheet.

But then what you can do, you can do a central kind of control panel and have lots of buttons where you can click, click, click, click, click, click click.

Or what you can do is you can run a macro that will run all the other macros.

So you're gonna have like print all and that will be a macro that all it does go and activate all the other buttons for your printing.

So this is a print macro, it's pretty simple but the real skill here is making sure that you make the code very simple and you think carefully about ways in which people who are not macro coders or too nervous it gets nervous people out there, about creating a macro code.

Now the next macro we're going to do is a toggle macro.

So I'm gonna open a new tab.

So I'll call this print here.

Then I'm going to call this button and then I'm gonna open a new tab.

And this, if I just zoom in a little bit, this is going to be our kind of toggle macro.

Now the toggle macro, I'm gonna use an example here of applying a style.

Because again, sometimes what you'll find is, for example if I type the letter 10 in this cell, you see that there's actually some inbuilt formatting in this workbook.

We've got hard numbers automatically turning blue.

But what I want to be able to do is turn this into a date and I could use a shortcut to do it, which is in the fe shortcut or sometime I want to apply a style and that's because I'm using a client's formatting and if I do alt hj, you can see in this workbook there are actually three specific styles.

B which is blue D which is date M which is multiple.

So I want to choose D and that will just take the date style and apply it over that cell.

But sometimes what I want, if I'm to remove that, I'll do Alt HJ and I'm gonna choose normal.

But instead of going and doing it through the styles where I have to remember the stars, I then gotta kind of arrow through the styles.

I want a keyboard shortcut that will either flip it to a date if it's a normal style or if it's a date, flip it to a normal style.

So it'll be say if it's date, make it normal.

If it's not date, make it date and we're gonna just use an if statement to do this.

And so it's pretty simple.

But the best way of starting this of course is recording what we just did because you recorded what you did, you'll get all the kind of excel kind of, you know, stuff that you need to know in order to activate stars and I could just type it in for you.

But it's much easier to show you how to actually do this.

So let's go and create a macro, so Alt LR to activate macro.

And then I'm gonna do control shift.

Now I need to be a bit careful here.

This I'm gonna take a risk here.

Maybe I'll just do Oh I wouldn't allow me to do shift T um, do, oh I need to call it macro.

Call this toggles. There we go.

And then shift u, oh let me speak, that won't work.

So I'm thinking what's not used.

So I'll overwrite the last macro because I actually wanna make sure it doesn't flip anything else.

Now again, if I'm using styles then I do want to apply to this macro workbook because the styles are only in certain workbooks.

Okay, so I'm gonna install the macro in this workbook and I've signed a keyboard shortcut to it and then I'm gonna click on, okay, oh it's so it lemme just do R again could show shift R fingers crossed.

So I'm hit enter and I'm recording.

So all I'm going to do now is just do alt hj go and make this a date style and then I'm gonna apply the normal style and then alt HJ and apply the normal style.

Okay, you could actually build like a statement that will just run through the different items.

There's actually potentially different ways of doing that.

But I'm gonna use just a simple if statement first, then I'm gonna stop recording Alt lr stop recording.

So all I did is record myself applying style and then applying style.

You may be asking why don't we just use the like the actual format.

I could have done that. The danger of doing the actual format is that if you use formats then you have to go into the code to change them.

And I sometimes think that if you want the macro to be able to be used and potentially changed a bit by a user, if you use styles and most people know how to change style in Excel, you need to go redefine the style and macro will still work for that style it name or kind of keyboard shortcut.

So that's why like range names with formatting, I do like to use stars because they're easy to manipulate.

So let's go and take a look to see alt F 11 to open the visual basic editor and let's see what this looks like.

And you can see here this is the new sub routine called toggle.

It's called toggle macro and the keyboard shortcut is control shift R and then this is the macro and it's just saying selection style equals D selection style equals normal in quotes.

That's pretty straightforward.

So what do we want to do here? Well we want to turn it into an if statement.

So as you'd expect, the first argument if I just do a new line is if, okay, then we're gonna just type selection because that's what you've selected. And this could be multiple cells rather than just one cell.

If the selection style equals D and I could have just copied it from below actually more efficient.

So if, if, so this is our statement. If you think of an IF statement, this is kind our statements.

If the selection style is D there, okay, so it's just like an F state but in code.

So if the selection style is D, then what we're going to do, we're gonna make the selection style not D, we're gonna make it normal, okay? Then or else otherwise the selection style is going to be D and I could have done that the other way around I could send section style is normal.

Then make the S style D else make it normal.

Okay? So I could have done it the other way.

Now don't forget if you do this to do end if, okay, so you've gotta start with if and then you've also got to end if, okay, so if the statement, if that's true then in this case make the select style of the selection normal, otherwise make the style of the section date, okay? End sub. So then what I'm going to do is I'm just going to save this, I remember we signed it to control shift R and then I'm gonna come back and do hopefully control shift R exit date control shift R and you can go back and forth look at that.

So that's how you can do a toggle macro within this statement.

And there are ways in which you could build them that will, they'll toggle through multiple versions but I want you to give a kind of give you a glimpse into that.

So that's the toggle macro. So it's pretty straightforward, right? It's a really simple little if statement here just saying if this is true then do this, otherwise do that exactly the same as another statement.

Now the next macro that we're going to build here is going to be an iterations macro.

So this is getting into more kind of coding than we have done before, but still pretty straightforward and basic.

So don't panic about this but what I'm going to do, I'm gonna set the spreadsheet up first.

So let me zoom in a bit and what I'm going to do is you really sometimes need to think carefully when you do macros about setting them up so that're easy to change without having to go into the code.

So what I'm going to do is I'm gonna set up a little iterations model.

This is a very simplistic example because what I'm trying to show you how you could apply it to a much bigger model.

So the aim of this in Excel's models we where you have iterations you need to create the iteration, then you can need to go to T tools options and you need to activate iterations and then that will iterate the circularity.

But a lot of people really don't like the circularity and there's a whole load of other issues due the circularity which cause problems.

So what is the solution? Well the solution is using a macro and the macro will run the iterations without causing the so clarity and then just keep a hard number in the interest line.

The danger of course is it won't be dynamically updating.

So you've gotta remember to run the macro before you print or you save the model.

So we'll call this iterations at the very top and we're gonna build a model here which is going to be our beginning debt number.

Then what we're going to do is we're gonna do our interest rate and then we're going to the interest expense.

And then what we're going to do is the ending balance, right? So the beginning of debt, I'm gonna type it in the hard number a hundred there.

Then the interest rate I'm gonna make 10% and then the ending balance is gonna be the addition of the beginning debt plus the interest expense.

So I'm just gonna do that there.

Okay, so I've got my little model, I've not got the interest calculation in there yet.

If I did the interest calculation normally, so you take the interest rate times the average of the beginning and ending balance, you would get your circularity.

And to solve that of course you've gotta go to alt ft files, tools, go to formulas, check iterative calculations.

And if I just increase the decimate slightly so we've got a little bit more accuracy, we get 10.526.

Now I want to avoid creating a circularity and I'm gonna use a macro.

And the macro we're gonna be using is a loop macro.

So the first thing I'm gonna do is just copy the current answer and paste it into the spreadsheet as a hard number, okay? And I'm just gonna increase the decimal there. There we go.

So then what I'm going to do is I'm going to delete that.

So what I'm gonna do first is I'm gonna show you the manual things we want the macro to do.

So I'm gonna do it manually and then what we're going to do is we will get excel to kind of iterate through this.

So the first thing I'm gonna do is calculate the interest expense, but I'm not gonna do it in the interest in column B, I'm gonna do it in column C. So I'll take the interest rate times the average of the beginning and ending balance and you can see of course that that is not the correct answer because the answer should be 10.526313

So what I'm gonna do is copy that and paste it as a hard number alt ESV.

And you could see we've got slightly, slightly closer 10.5.

Now I'm gonna copy that and I'm gonna go back, paste that as a value so it kind of updates it, this kind of doing interest on interest.

Then I'm gonna copy that again and then alt ESV again you can see if I keep on doing this alt ESV.

So I'm actually iterating through, I'm doing the interest on interest and that's all we want the the macro to do, literally just take, take the current answer and sell C4, copy it and paste it into B four.

So why don't we just record the macro doing that.

The problem is we want to make sure that we have range names because if we insert a row in the model, it means that all the sub references be off. So we always want to work with the range names.

So I'm gonna do control F3 and I'm create a new range name and this is gonna be called interest expense and that's fine.

So that cell reference B4 is gonna be called interest expense.

And then I'm create another new range name and the next new range name's gonna be interest calc and that's going to be the 10.5 cell there.

So I've created two range names, interest expense and interest calc. And so what we want the macro to do is copy interest calc, paste it as a value into interest expense.

And so I've created those range names because while I'm kind of thinking about the mechanics of the calculation, it's quite good to actually build the range names then.

So let me delete that and then we're going to go through and record us doing that manual process of copy paste, copy paste, copy paste, copy paste.

So now do alt lr.

And what I want to do here is I'm gonna call this iterate and I'm not going to do it to a keyboard shortcut.

You could do I suppose, but I'm gonna do it to a button in this case then I'm gonna choose, okay so my macro is now recording so I'm gonna copy the interest calc go to interest expense since I'll be for alt ESV for values.

So that's one iteration.

Go back control C, copy alt ESV, paste another iteration copy go back alt ESV paste third iteration copy alt ESV fourth iteration.

And then by about fourth iteration you kind of get to a level that you are pretty confident about.

So what I'm also going to do now is I'm gonna put a little another cell here which is gonna be called difference.

And the difference is going to be the difference between that are four calculation and the hard number in the interest expense line.

And the reason I'm doing this is that if you go back, if I do this calculation again, you can see the moment this difference of 10 with one iteration, that difference gets down to 0.5.

The second iteration it gets down to 0.25 with the third iteration gets down to 0.0125 with four two fourth iteration gets 2.06.

So this is exactly how the, how excel kind of works through the macro.

So what we want our macro to say, so what do we do? We want our macro to say what we want to say first.

Do the following until the difference is less than let's say 0.001, right? So copy the interest calc cell paste as a value in the interest expense cell.

Okay? So what we want excel to do is literally just do that well again and again and again until that different cell is no greater than 0.001 So one, so I'm just gonna create a range name for this.

We call a range name for the difference and I'll just call it difference, I'll call it yeah difference.

So got a name, there we go.

So I've got my mechanics, I've thought about how the macro's gonna work and now what we want to do is, oh I was actually recording all that time so it's garbage, don't panic, just delete it's turn recording off.

See it's, oh my gosh look this because this is actually pretty simple macro.

So what I'm going to do here is I'm going to write some code at the beginning and this is where you do need to start understanding digital visual basic.

Because there's a function called do until which is kind of simple, right? It's a a very nice, very simple but very powerful.

So this is what is known as a loop.

So it's gonna do until and the kind of test is going to be the range which is placed in the spreadsheet.

Open quotes or open sorry, quotes and then type the range name difference quote.

So do until the range and then I'm put a condition the range is less, sorry do until the difference is less than north 0.0001.

I'm just gonna go a little bit north, a bit more detailed than Excel would normally do.

So do the following until the difference is less than 0.01.

Okay? And then what we want to do is we want to go and go to cell c4.

You can see here. So we wanna do this first, I'm gonna move that up.

So the first thing we want to do is we want to go to sell C4.

And if you remember i I range name C4 and I called that interest calc.

Okay? So go to the select the interest calc cell, okay? And then what I want to do is I'm going to I'm gonna go to the selection and copy.

That's the next thing I'm going to do.

So it goes to interest calculation and select it and copies it.

And then what I'm going to do is I'm gonna go and go to the next range, which is range not interest calc, but interest expense, okay? Select. So it goes to the interest calc first and then selection copy and then go to the interest expense range, select that, and then we're gonna paste special.

Now I always can forget these things so I'm gonna go down here.

Can you see it says selection pay, special paste values.

Now there's other options here because in that dialogue box, the pay special dialogue box, there's lots of different options but I don't want them.

And anytime I put those options in, it makes it more complex and it'll range, it'll run more slowly.

So then selection, past special paste values.

So I'm gonna paste values, then I'm gonna type loop.

And so what loop does is say go back to the beginning until continue do this.

Until that test at the very beginning is satisfied.

Okay? Now if you do this, what happens is, well if you copy something in Excel, if I go back to Excels, do Control C, can you see I get those little dotted lines, if you hit escape, they go away.

So what I'm gonna to do is the very end, I'm gonna do lucky what effectiveness of beauty say it's gonna be application, cut copy mode, you can see it, there's lots of suggestions, which is really cool and you're gonna make that false and that will just remove the dotted lines.

Then don't forget to do and sub routine, okay? And then you can just delete everything else to get rid of because if you left this in, what would happen is it was start to run really slowly and someone looking at the code would be really confused because it would have a whole load of stuff that you don't really need in that.

So always try and keep your code really, really tight.

As simple as what like come up building formulas.

In fact building formulas in Excel is the same as flipping coding.

I mean that's reality.

So anytime you can make it simpler and shorter, run faster, it's good.

So I'm now gonna save this and what I'd like to do is I actually kind of sometimes like to be able to check that it runs properly.

So what I'm gonna do is I'm gonna go back and I'm gonna kind of dual screen this and I'm gonna click in the code and then you can see here you can run the code.

So I'm just going to delete that because that's, we want the macro to run.

Then I'm gonna come here, click on the code and I'm gonna click play and it will just, can you see that? Did you see that might still, did you see that you could actually, if you wanted to get it to go up to the very top, so we could just do a range a one select.

So we could actually just do that because that's, lemme just run this again, come back here and then click play.

Oh my gosh, that's cool. I love things like that.

So you can see that that is iterating the formula going back forth, back forth, back forth.

And probably the nice thing to do would be attach it to a button.

So I'm gonna go to developer, it's C control and I'll just put a button at top here

and that was called iterate.

Okay? So now what I can do is I can then just click iteration and fact I just delete that and then choose iterate it will run through that.

And this is a way of avoiding the circularity in a model by using this to iterate A model is macro rather than the circularity in it. Just repeatedly does it.

You've gotta remember to run the macro before you print it out though. That's the key thing. Okay, that's most of what I want to co cover today.

Let me just run through the last couple of slides 'cause there was a few slides. We've done the macro quarter, keyboard shortcuts, we've talked about ribbon, VBA editor and we talked about macro recorder. Macro quarter is great.

But I would not always rely on the macro recorder because if you just rely on the macro recorder, you will create really garbage codes.

You will have lots and lots and lots of code in there that you don't need.

So you can start by using the quarter, but always go through and edit the code afterwards because you just want to see anything you don't need there.

Delete, it'll make it run faster and it'll be much easier for other people to use.

So the other macros you went through is the if state macro and the loop macro.

Okay, and I think that's pretty much it.

So thank you so much for your time today.

Um, you will be able to download the answer file if you look at the recorded session in Felix, you will be able to see it.

So if I just go to Felix, let me come to Felix.

You can see actually the easiest way. If you go to topics and you go down to Felix live, you can see all the sessions that we have run.

So for example, there's one in chat GPT prompting and if there are any downloads you should be able to pick it up down there.

Let just go and see. There we go.

I think here, there was, there we go. You can see, you can see it there.

So we will attach it to the bottom here of the recording.

And of course we're running these sessions on regular basis.

Next Wednesday we're doing M&A modeling, and then next Friday we're doing dives modeling.

So hopefully see you there.

Thanks very much for watching and see you very soon.