There everybody. Welcome to this session on Excel VBA and Macro's introduction.

We are going to take you through some key elements of being able to use VBA in an kind of investment banking and finance context.

At the end of the session we will send you the answer file that I'm be working through, but I'm going to give you three what I think are kind of three really useful use cases for visual basic within finance.

The first is going to be building an if step macro and then if instead macro is like a toggle so it turns something on and turn something off.

And you can use that particularly for formatting where you want to have some a switch, we'll turn things on not.

So that's the first one is the is so macro.

The second macro that I'm going to be building is a loop macro.

And what a loop macro does is do something repeatedly and it keeps on repeating until you give it some condition that needs to be met and at that point it will stop.

So it's known as a loop.

And the example I'm going to use for that is where you have an iterative circular reference in the model and instead of using the inbuilt tool that Excel has to iterate interest with the file options formulas and then check iteration, which can cause major issues in a big model particularly where you get ref out, in the model.

Instead we're going to build a macro that will do the same thing so the actual iteration will still be happening.

We'll do interest on interest on interest, but we will do it through the macro process and in a very large model that actually can add, can can mean that it's a lot more stable than permanently having your iterations turned on and seen it used in product finance and larger risk finance models too.

The third use case is more, it's this kind of simple print macro but it's highlighting some of the issues that occur when you, when you use the recording feature in Excel, which is a fantastic feature, it makes creating visual basic very, very easy.

But it is pretty much a blunt tool and I'm going to just go through some of the dangers of using it and how you can clean up your macros so they will be really super fast and speedy.

So before we get started, there's something that you already have to do and that is making sure that your Excel is set up to be able to use macros and the default setting in Excel is not to allow you to use macros.

So I'm going to share my screen and I'm going to run through a couple of slides really quickly, but I'm actually going to focus mostly on using Excel here as well.

So I've gone through the kind of the introduction already, um, of what we're going to cover, but excuse me, we need to make sure that our developer tab on the ribbon is activated because it's not a standard setup in Excel. So the very first thing I'm going to do is show you how to do that.

So now I'm going to hop over to Excel and we will do that.

So I've got a blank Excel spreadsheet in front of me and you'll notice that if you look on my ribbon at the very top I do have a tab called developer.

And that tab allows us to use macros.

If you don't have that tab, just right click the ribbon or the many buzz at the top doesn't matter.

And then go to customize the ribbon.

And when you do that, you get into the Excel options dialogue box. You can actually, I think do this from the file menu as well.

But on the right hand side you can see that you've got main tabs, okay, and you probably will see the developer tab deselected.

So all you need to do is select the developer tab and what that will do is add a new tab in Excel, which is a developer.

And unless you have that turned on, you won't be able to follow through in the, in this webinar or actually in the future be able to use visual basic macros.

So once you've done that, click on okay and you'll see on the menu bar you've got the developer icon.

So let me just show you that ALT L for developer.

So I do ALT L and it pops open the windows, the the ribbon, sorry, the components you've got here visual basic, that's a visual basic editor.

That's where once we record in a macro we're going to go in and edit the code, which we have to do for all three examples I'm going to give you, which is number one, the macro number two, the loop macro, and number three, the print macro.

Then you've got pm which is just a list of macros, the ability to record a new macro.

Then this item which is used relative references that's quite important for you to understand.

When you create macros, you do need to really think about not just your own use of them but other people's use of them.

And when you are recording a macro, what Excel will do by default is record the absolute references of any selections you're making in the spreadsheet. So it won't say two cells to the left of me and one cell up.

It will sell, it will see, it will say cell A23 and that's a very specific cell reference which of course means that the macro is very inflexible because it will always go to that cell.

So this means if you want Excel not to use absolute references, but to use spatial recognition within the spreadsheet i.e. two cells left two cells up.

Then you need to use relative references before you do the recording and you can turn that on and off during the recording process and it should pick up relative or absolute references.

Quite important to understand that.

The next item is macro security.

And in macro security you can have settings.

So when you open a file it will disable VBA macros but it will tell you first, it will say, Hey, this file has got a VBA macro attached to it.

Do you want to activate the macro or as default we won't do that.

You can as well have trusted locations.

If you save your macros in a specific location, Excel will always trust them and that's useful if you are using a lot of macros.

So if I go back to my developer tab the other item we're going to use this insert, which I'll come to later.

There's one issue or one kind of structural thing that you want to think about when you are developing macros.

Most people when they see macro code kind of freak out. Certainly, certainly most people finance they freak out because it's coding and they don't feel comfortable with it.

So you want to try and build macros, particularly if you're using them in a kind of banking context that are flexible and you can adjust what they do by doing things like changing range names or changing styles rather than going into the visual basic code.

So all the three, the two cases that I'm going to cover, I'll show you how to help avoid the situation where when you hand over the file to somebody else that they will have to go into the visual basic code. They want to make adjustments. So we're going to use range names and we're going to be using styles to avoid that problem.

And that's quite an important thing to think about.

Okay, so I think we're kind of ready now.

I have to think any more slides I want to go through.

Yeah, we're going to , we are gonna actually start our first process with recording.

The reason I use recording a lot is that frankly I, you know, I don't use visual basic every single day so you forget things and the recorder is a really helpful way of just recording a series of actions so you kind of get the mechanics of what's going on and then you can code around it.

Chat GPT is a also a fabulous tool, but again with chat GPT when it comes up with a code, often if you don't understand visual basic you can actually make mistakes.

So be quite careful with chat GPT.

I do use it a lot but do be slightly careful if you have a large language model generating code, you need to really check it carefully.

So when we create a macro, we will have the ability to save it in the current workbook.

So you attach it to the workbook or you can save it on a separate workbook, which I think is less useful.

I guess if you put all your macros in one file, that'd be fine.

Or alternatively use the personal macro workbook and the personal macro workbook is a workbook which is, it's kind of sitting, I'm sitting on the sidelines and if you save it to your personal MacBook it will be available for any file that you open in Excel.

So that's kind of pretty useful to have.

And when you save the file you have to save it as a macro enabled file.

So that's dot XLSM so you don't save the file as a non macro workbook when you've created a macro in it.

Okay, so let's get started.

I think with our first little macro use case. So I'm going to go to my blank Excel spreadsheet and the first thing I'm going to do is just create a style.

And the reason I'm creating a style is that I want to do a macro that will format a cell as a date.

Okay? But if the date, if the cell is already formatted, I want it to format it to the normal style. So I want to be able to toggle between the two.

So the very first thing I'm going to do is create a style called date.

The reason I want to do that is I could code the date style within the VBA code.

The difficulty is if someone wanted to change the date style, they'd have to go into the VBA code to do it.

If you do a style, it means that all people have to do is going to amend the style rather than a macro code.

And that means that someone who's not comfortable with VBA would be able to easily change the um, date macro without actually going into the code and doing it. And that's why I'm using that process.

So let me, if I type in the number 10 in a blank blank cell, what I'm going to do is I want to create a date style.

And when you create a date style underneath it is an index number and number one is the first January, 1900 and it counts up from that point.

So 10 will be the 10th of January, 1900.

So I'm going to go to alt HJ for cell styles and I'm going to create a new cell style.

So type N for new and I'm going to call this D for date and I want to be what to be as flexible as possible.

And then I'm going to choose format and I'm going to go down to the category which call custom and under type I'm going to give it a specific date format.

And my date format is going to be DD two digits for dates M and M, three digits per month, dash YY two digit, two digits for year.

Hit enter and then tab down to okay and then hit enter.

So now we're going to apply the style of HJ just to check if it's okay.

Yes, it's taken that 10 and it's made it into a date format.

So I've got a style in Excel in this workbook that is formatted to my date.

I'm now going to record myself doing the set of keystrokes that I literally just did.

So I'll type another number in the cell, let's make it 12 this time.

And I'm going then going to turn on the recorder and I'm going to do the Alt L for the developer ribbon and then just R or record, funnily enough I'm going to type that.

Now the macro name, you've gotta be slightly careful with names.

You can't use really generic things like print or macro or date because those are too generic and I think Excel gets confused in code.

If you use really generic terms.

You can't also use a name which has a number as a suffix, you can't use a name with spaces you have to use underscore or capitalization.

So there are quite a lot of restrictions but you can usually figure it out.

So I'm going to put date, but if I had you date you will give me an error message.

So I'll do date macro and that should be fine.

Now we can assign it to a shortcut key and you'll notice that it's asking for control and a letter and and a lot of people's desktops you'll have a fax set add in a capital IQ add in or maybe a financial edge add in.

And that means that sometimes you may have a problem with using a keyboard shortcut because another macro will conflict with it.

So you can use the shift key.

So if I hold down shift on the letter, that gives me a little bit more scope because it's we control plus shift and I'm going to choose the letter M for mother.

So if I hold down shift and type M, you'll notice that in the dialogue box it goes from control plus shift plus letter, I'm going to store it in this work macro workbook.

I could choose a personal workbook or another workbook.

Personal work macro workbook will work in will will be available, won't necessarily work but will be available in any open spreadsheet.

And this is going to be a macro which will match the cell either as a date or a normal style I should say style or normal style.

It's quite good but documentation, just to make things super clear, then I'm going to click on okay and we are recording and you can see down at the status bar at the very bottom that little icon, there's another um place you can activate recording down at the bottom.

And it just says we're recording. So all I'm going to do is apply that style alt HJ and I'm going to apply the date style.

That's all I wanted because I wanted to know how excel, what it calls the style, rather than actually kind of learn the visual basic to do that.

So that's the first thing I'm doing.

Next I'm going to turn off recording. Don't forget to do that. Sometimes when you first do VBA coding you forget to turn off the recorder and then you can, you can record yourself actually running the macro, which is creates this nightmare loop.

If you are in that nightmare loop and it's like jumping out of an aircraft freestyle and you want to open your parachute, just hit the escape key because the escape key will stop the macro running.

So I'm going to go to the ER tab and R to stop recording.

Now you'll notice that I got a a most recent version of Excel and it's saying, oh gosh, do you want to use anfa script which is more cloud-based or do you want to use VBA? I'm going to stick with VBA because most of our clients are actually still on a kind of VBA basis. They're not on a cloud basis.

So I'm going to just close that down and I'm going to go into the developer ribbon Alt L and I'm going to go into visual basic V and this opens up a new tab which has just gone onto my other screen, which I'm going to pull back into this screen.

There we go. Let me just see if I can pop that up. Here we go. Perfect. And let me just explain this.

So this is like a file manager here and at the moment we've got three items here.

You may only have two.

We've got the current workbook, which is called book one.

We've got the financial edge macro suite which is added in.

And we've also got our personal macro workbooks.

If we've had macros that we want to save that are always available, we can put them there.

But I'm just going to focus on the top one. This is the one we've created.

And then you've got your workbook at the top, but actually it's the modules we're most interested in. So I'm hit into and I've selected module and on the right screen you'll see this is where our coding screen is.

So you've got the project, the kind of file manager on the left, and then you've got the coding screen on the right at the very top it says sub, which stands for subroutine and then the macro name followed by the parentheses open and close premises underneath it's got some green text and green text is the way you can document macro so you can add to this.

So this is a macro and you'll see that it goes green while I do that.

Nice people put little space, there we go.

But it goes green when I'm finished.

If it's green, it means that the code isn't, it's going to ignore Excel. We'll ignore that and just see it as as documentation.

Now you'll see here that all this is doing is taking the selection and applying the style date.

So this actually could work as a format.

So if I go into another cell type 14 and then do control shift n, it creates a date format.

So we have done a really basic macro, but as I said to you, I want to make this a little bit more exciting and we want a toggle macro that will turn the font into date if it's not date.

And if it is date, I want it to turn it back to normal style.

But the good thing is that we can see that the way it applies the style is takes a selection first and then says style applied date.

And you'll notice here it's not using any code about the formatting, it's just using the style D The good thing about that is that somebody could then change the style D and this macro would still work with their new style definition.

So I'm now gonna start by putting an if statement in, I going to hit a hard return.

Because I would like to put it on a separate line or actually I don't need to do a hard return.

Let me just do a nick on this.

So if capitalize it and if the selection of the style is equal to D.

So if that is true, so think about next statement in Excel you have three components, the statement, what to do if it's true, what to do if it's false.

This first bit is a statement.

So if the selection of the style of the selection is date format, then we're going to tell Excel what to do If it is date format.

And remember this is a toggle so all we want to do is take the style of the selection and make it normal.

So this is kind of easy because you've kind of seen what we've done before.

So now what I'm going to do is I'm going to do selection style equals normal and that's the normal style there.

So that's the statement, what to do if it's true next by head enter we're going to do else.

And what else does is say, well if that's not true, now I'm gonna tell you what to do.

If that's not true, I'm going to do selection style equals D.

Okay? Because if it is not date that makes a normal, but if it is date, we want to if sorry, if it if the date, if the style is date, then we want to make it normal, but otherwise we do want to make it date because we wouldn't toggle on and off.

Then we need to end the stents to do end if at the um, very bottom and then end sub ends the sub routine.

So this is, you can see it's a pretty simple straightforward little lift statement.

Now when you build macros, particularly as they get more complex, sometimes you make mistakes or sometimes they don't work, you actually don't really want to know that when you go into the spreadsheet it's actually good to do it actually in the code.

So I'm gonna use this little icon at the top, which is just run sub-routine.

And if I click this, if I get no error messages, you're good to go, let me make an error.

If I put a space in else and then I run, you can see it stops and it says error, error, you've made a mistake.

So I'm then going to click on okay and get rid of that and then I'm gonna save this and then I'm gonna go back to Excel.

And now when I asked to save it, it says well hold on a moment, you're saving this in the file so you need to save the file.

So here I'm gonna go and put this into, let me just take a look on my desktop and I can't save it as an Excel workbook.

I need to save it as an ex Excel macro enabled workbook.

So I'll choose that and I'll call this macros 5:00 PM There we go, hit enter.

And I've saved that.

Now let's go and test this actually for real and I'm going for that debug to stop.

So now I'm gonna test this control shift M, control shift M date control shift M number and it it flits between the two.

So that's an if state macro really useful as a little tool that you want to total on or off.

Okay? Now our next macro, I'm just gonna name this tab, this is going to be style.

I'm gonna open a new tab and in this new tab we're going to do an iteration macro and iteration macro.

This is where you want to be able to build a macro.

Then instead of using the tools, options, iterations or tools, options, and go to formulas and then iterations, we can do this using a macro.

So I'm gonna build a little model first, let's call this for title iterations here.

And I'm going to do my interest rate.

An interest rate I'm gonna make, I'm gonna make let's say 10% and then I'm gonna do my beginning debt.

Yeah. And I'm gonna make that 100.

And then we're gonna have a line, I'm just gonna be the interest expense and then we're gonna have an ending deadline.

Okay? And so the ending debt is gonna be the beginning debt plus the interest expense, okay? Then I'm gonna calculate interest expense as we would normally do in a model.

And if I take the interest rate and I multiply that by the average I get the beginning and any balance, I get the error saying you've got a circular reference and this is how we normally deal with circularity.

So then we go to alt ft, go down to formulas and enable iterative calculation.

And I'm going to just doing this because I want to get an answer using the Excel because then we can compare it to the answer that we get.

So I just move this over here, this is going to be Excel's answer here and that's just our little reference so we can check whether our macro works.

Next I'm gonna go through actually how you can do this manually, this iterative process.

So I'm actually going to move this calculation down below here and I'm gonna delete this and you'll notice circle reference is gone.

This is still calculating interest here and this is, I'm going to call this interest calc.

Okay? I'm now going to turn off iterations because I don't need that and I don't like to it on if I don't need it, but I'm gonna show you how to do this iteration manually.

Because what we now want to do is get Excel to do this manual process for us.

Okay, let's go ahead and do this.

So if I copy this control C and I go up to the interest expense line, I'm gonna paste it as a value, Alt ES V for values and you'll see that I paste it as a hard number.

But actually now the interest has changed because you get interest on interest.

So I'm gonna do another paste of value Alt ESV hit enter and that's the second one. And notice that actually the interest cal got slightly larger and then Alt ESV again hit enter.

And can you now start to see if you look at the interest calculation in the left and the Excel's on, so we're getting closer alt ESV for values you enter and now with the same.

So it took us about four goes to get the same answer as Excel as our interest calculation just by iterating through copying, pasting, copying, pasting, copying and pasting.

And that's essentially what we want Excel to do.

But we want Excel to continue to do this until it reaches a level of accuracy we're cal we're comfortable with.

So I'm going to now calculate the difference between the interest expense in the model minus the interest expense in the calculation.

And you can see we actually do have a pretty small difference there.

That's a really small number and it's negative now.

But if I change this and make say the interest rate 5%, we're going to have to oh 5% or 50%, we're now going to have to do the same iterations.

So copy that Alt ESV, Alt ESV, Alt ESV and you can see we get a really small number.

Now the problem is that difference can be negative or positive.

If I now make the interest rate 8%, you can see now that difference is negative and we are going to go closer and closer to zero but still negative.

So when you are coding, it's actually quite difficult to code something where you have a difference which is either positive or negative.

So it's actually easier if I wrap this answer with a function called ABS which is the absolute function and instead giving me a positive or negative number, it will just gimme the difference as a positive number and absolute reference.

So that means my macro can always look at that difference as a positive number and that would be much easier for our coding.

So I'm next going to make this as flexible as possible.

If you are coding, you don't want to use absolute references here.

If you use absolute references in this model, if you insert rows or columns, the macro won't work and that would be kind of real pain in the neck to do.

So what do you do in that situation? Well what we're going to do is we are going to range name the key cells.

So what the macro can do is it can go to that range rather than go to that cell reference.

And that means if we insert rows or insert columns, it will still work.

So the first thing I'm gonna do is do control F three interest expense and I'm gonna create a new range name called interest expense.

Then I'm going to do another one, initial calculation control F3, create a new range name interest under sort calc and then lastly the difference control F3 put a new range name for difference.

So I've got three range names in this file.

Now one for the actual interest expense in the model, one for the interest calculation and one for the difference.

Now I'm going to go and do the calculation and or do that kind of iterative process, but in this case I'm going to record myself doing it.

So let me change this to say 3% and I'm gonna come down to the interest calculation and I'm going to do Alt LR but record macro and this is gonna be called iteration.

I'm not going to assign it a keystroke, I'm gonna show you something a bit different for that.

And I'm going to hit enter and then it's recording. So I'm gonna copy this Control C, go up to interest expense Alt ES and V, Alt ESV, Alt ESV, Alt ESV, and I think that's probably a level of accuracy I'm comfortable with, oh I'll just do one more.

So that's the process iteration.

Now I've got to turn off recording Alt LR which turns off recording.

I'm going to ignore that.

And then I'm going to go into the visual basic editor because this is where it gets a little bit more detailed.

Gotta get into the code.

So I'm gonna do Alt L for the rope ribbon and then V the visual basic.

And this opens up my visual basic window.

And you can see here what we have on the iteration macro, we've got that recording.

So it's gone to the range B4 and you don't really want to use B4 because that's gonna be problematic because it's a cell reference. If we insert rows, macro won't work then it's gone through, it's gone.

Pay special, pay special, pay special, pay special.

Now I guess we could do this using pay special multiple times, but actually I'm gonna give you a code which will be a bit smarter and we'll tell it to do that action, that pay special action until a certain condition is true.

And the condition that's gonna be true is that that difference cell, that range then called difference is no greater than 0.001 similar to kind of how Excel deals with it as well.

So we need to do some coding around this and I'm going to use a loop macro and a loop Macros always starts with the word do do something.

So do until and then we're going to do the range and I'm gonna type the range name diff or difference in you mind.

Okay, so do, I'm gonna spell until wrong so little um, problem with my macro code there.

So until, until the range difference is less than not 0.001.

So that's kind of the level of accuracy that Excel is comfortable with.

So we're going to tell it to continue to do something until the range length difference is less than 0.001. That's the level of of accuracy.

So what do we actually want it to do? Well I want to go and I'm gonna get rid of that.

I'm gonna do I'm going to go into the range and currently it's got B4 but we don't want to go to cell reference because if you in certain rows that would be a problem.

Instead I want to go to the interest calc under score calc because that's where we calculated the interest and it's gonna select that then that selection, I'm going to choose selection dot copy.

I could record myself do that.

We're going to ask it to copy.

So it will go to the range then import interest calculation and copy it.

Okay then we need to tell it once it's copied it we want to go to the interest expense which is actually in the model.

So I'm going to go range open parentheses, open quotes, interest expense do select.

Okay? So it will go to the interest expense select, then we can do the pay special.

So let's put a little bit of an error there because I haven't put another quotes after that and just fix that.

Oh it's from the outside.

So um, select and then selection pay, special pay, special values.

Now it's also got some other options here.

These are other options in that dialogue box and Excel's giving values for them.

I could leave them in. It would still work.

However, my view when you're building macros is you want to make them simple and as short as possible because the more simple the macro is, the faster it will run. Number one, it's always good but number two, the easier it is for you to look at the code and kind of understand broadly what's going on.

So I'm going to just get rid of everything else and because this is a loop, I don't need to specify how many times you do it because Excel is going to do this until it actually has met the condition of the difference being no greater than 0.001.

So the very end I'm gonna type loop and that tells Excel that that's the end of the loop.

So once that's done, one of the other things that we should do is we should remove off the clipboard that copy.

So I'm going to, and that's kind to good practice.

It's like being in the Olympics when you've done an amazing gymnastics routine and you hop off the horse and you're going to go like that but you stumble.

It's like at the last hurdle stumbling.

No instead I'm going to say okay in the application.dot cut copy mode, notice it's prompting and you can just hit tab equals false and this means that it will just make sure that the copy of the clipboard is removed.

Now this is nice little short code, lemme just check everything. That all looks pretty good.

So the next thing I want to do is I want to run this because it's always good to check that this works and if I click the run, no error messages looks good.

Okay, so then I'm going to save this and I'm gonna go back to my model and I can just reduce that and let's go and type 4% here and then I'm going to run the macro Alt LPM macros and I'm going to run the iteration macro.

Hit enter. There we go. Look at that. Amazing.

So we could have attached this to a keyboard shortcut.

The problem with the keyboard shortcut is that often when people get given the model you need to actually document where that is and they wouldn't necessarily see it.

So instead we're going to create a button on the spreadsheet and do something a bit fun.

I'm going to go to the insert menu and in the insert menu I'm gonna go to icons NS and I'm going to call type run.

Okay? And I'm gonna choose one of these, yeah, this guy is running so I'm gonna set him into the model.

So this is all graphic.

It actually could be any graphic, it could be a picture instinct.

And you've got a little person running here.

I'm really doing this for fun because it's probably been unintuitive having a runner there.

But if I right click the runner and activate it, you'll notice that there's an sign macro to that.

So I'm going to sign a macro to this and of course macro I'm gonna sign, gonna be the iteration macro.

So I'm gonna select that, click on, okay then if I click on the sell on the model, I'm then going to be able to click that icon and if I click that icon, the model will run. So let's give it another skip of 15%, it's quite a big difference.

Then I'll click the runner and you can see it iterates through the model.

So this is a very good way where you have a very, very large model and you want to really finesse it down the iterative calculation to a macro rather than the Excels inbuilt macro, which option be problematic.

So that was the second use case that we had. So we've done a little loop macro there.

The last case is going to be nice and straightforward and easy I hope, but there's some really important issues in this last use case.

And to get the last use case, I need to get some data.

So I'm going to call this print because this is gonna be a print macro and I'm gonna go to our Felix platform and hopefully I'll get and pull one of these in and let me go to the um, how many page.

Okay? And I'm gonna go and pull in this balance sheet, copy this and let's get this into Excel.

Just jump to Excel and I'll just paste that in.

Okay, so I've got some data there.

Widen that but even wider characters wide.

Okay, let's make that.

So I'm gonna zoom out a little bit as well just so you can see 60%.

So we got data set.

Now when you're creating a print macro, again you want to make the macro easy for people who don't want to go into visual basic and code.

So the best thing to do is select the arrow that you want to print and give it a range name.

So I'm going to do that first.

I'm going to select the balance sheets and I'm going to do control F3.

And I'm going to call this new and I'll call this print underscore bs.

Okay, so I've created a range name for print bs. The benefit of doing this is that if you use that range name in your macro code, somebody doesn't know visual basic can go in the code no sorry, can go into the range name definition, change that without having to go in the code, but the macro will still work.

So I'm not going to record myself printing that section.

So I'll do Alt LR and I'm going to call this print underscore BS.

I'm not going to assign it to a shortcut key.

And now it's recording. So here we go.

First thing I'm going to do is I'm going to go to the area.

So press the F5 key and I'm going to go to the print BS range name and that does the selection as well, which is actually quite a nice little technique.

Once you've done that, then I'm going to go into Control P and I want to go down to page setup.

Now when you go to page setup, you'll notice there's lots of options here. There are four tabs and each tab has a number of options.

So I'm just going to focus on the ones I really care about.

I want to portrait orientation, I want to fit one page wide, one page tall.

I don't really care about margins, I don't really care about the header or footer.

I could do, you could actually get it to put some head around photo in.

And then on the sheet I do want grid lines and I do want row and column headings.

Then I'm gonna click on, okay and I want to make sure that we don't print the active sheet, we just print the selection.

Okay? And yep, that all looks good.

And then I'm just gonna click print and it will print that.

Now I also like to do control home when I finish that so I don't have that big selection activated.

Now let's turn the recorder off and let's go into the code just to see what we can see there.

Oh my gosh, look at the length of that code.

It's got every single flipping thing in the macro code.

So we are going to go through this code and we're going to literally edit it.

We want to reduce Anything we don't necessarily need.

So application, go to reference print balance sheet. Yeah we do print communication.

I actually don't want that.

Print title rows and columns.

Yeah we did want that.

Active page set up.

I actually got that twice. Print page, set up print area.

I'm going to go get rid of that.

Page up left header, right head.

I don't want any of the headers or footers, I don't want any of the margins.

I do want to print the grid lines.

I don't want to print comments towards center.

Center vertical.

Historically don't vertically enough horizontally, don't care about that.

I wanted portrait orientation, not anything there.

I did want a bit one pages wide and one tool.

I don't care about the errors or the headers or footers, all this stuff is garbage so I'm removing all the stuff that I don't need.

I actually can probably move this, let me just edit this.

If I move that to there, that should actually work better because I then can get rid of those two lines as well.

So you can start to see print area.

Okay, the print area actually page of that print area.

We actually do want to put print score BS there because we do want the print area to be print BS.

And then with active sheet page set up print rows and titles, grid lines, portrait orientation, one page one more print or good.

Then we want to print. That's good.

And then go to cell A1. That all sounds good.

So that huge long code I've gone down and I've literally reduced, I've deleted anything I don't need.

And the reason you want to do that is you want to make sure that your code is short and sweet, the macro will run much faster.

Let's test to see if this actually works.

I'm going to run the macro now.

Looks pretty good to me and I'm going to close this down.

Save my file voila. With print macros you generally want to have a button on the spreadsheet and I'm going to go to the developer tab and I'm going to go to insert and under insert we have a section called form controls, irritatingly form controls and active X controls look very, very similar.

So I'm going to choose the form control button and at the very top you can notice my cursor has gone to a little x or cross and I'm just going to drag That on the spreadsheet and then let go.

And it's asking me what do I want to assign here? And it's going to be print balance sheet macro and I'm going to rename this print balance sheet there.

And then if you click on the spreadsheet then you can now click the button with the mouse.

If you want to edit this, just right click the button.

But now if I click on a button it will print that in one fell swoop and you can actually create a series of buttons where you can print individual components of the model.

And then you can also have a macro that records you printing multiple buttons.

So you can actually really quite quickly build really quite neat little print macros in a model like this.

But be really careful when you are recording and you go into a dialogue box with lots and lots of options and things like format sales or the page setup.

We've got tons of options that will just kill your code and you'll find that your code code will run really slowly if you don't do that.

And it will just waste time and time is money.

Okay, that's pretty much all I wanted to cover.

There's three use cases, an if statement macro that's a toggle, a loop macro that will continue to do something which in our case was iterations and then a print macro to make sure that your macro code is as efficient as possible.

And we are done. So I hope you have a really wonderful weekend.

Thanks very much and hopefully I'll see you Felix live again soon.