Welcome along.

Okay, so hopefully you can see the link that I posted into the chat.

If you follow that link on the very bottom right there is a file to download and a a workout file Excel.

It's not absolutely essential to be honest, because what we're gonna do, you can do that in a blank Excel, but just so we're looking at the same screen, if you wanted to download that, then please feel free to do so.

I'm not gonna spend ages doing intros because you've only got a short session and you might notice me continually looking down.

I'm looking down at the clock, so I'll keep an eye on the time as we run through it.

I've gotta slide deck that I'm gonna use that isn't available as a download, but this session is being recorded and you can watch it back if you want to.

So the intention is for this to be an introductory session.

I'm gonna show you guys macros how macros work, how we record macros. We're gonna have a look at the VBA editor.

We'll even have a look at a little bit of code.

And what I wanna do is kind of give you a platform to sort of go forward and maybe explore the VBA in a bit more detail.

And there's some, there's some good VBA content on Felix as well you can look at to augment the work that we are doing here today.

Okay? Right. I think we're probably going, gonna get going.

So just to be sure, because people are joining as I'm chatting, there's the link for the materials.

If you jump into that web address on the very bottom right, we have a link to an Excel file which you can download.

And if you can't get that to work, I wouldn't worry about it.

What we're gonna do, you can replicate in a blank Excel.

Okay? So if I scroll down to the slides, we're gonna talk about the VBA environment.

So we've got Excel as an application, and then there's a separate application.

We've got the visual basic, VBA environment, visual Basic Editor.

So they're two separate applications, but they work very much symbiotically.

So the object sitting in Excel kind of populate the Visual Basic Editor and Visual Basic, the Visual Editor kinda looks and, and manipulates stuff in Excel. So they really do work together.

It's just that the vi the Visual Basic editor is kind of behind the scenes and you don't really perceive it.

In the day to day, we're gonna have a look at some keyboard shortcuts.

I love some keyboard shortcuts, so we'll have some fun looking at those.

And then the sort of big thing I wanted to do which I think is reasonable to achieve within the next 27 minutes, is to record a macro and start to pull apart some of the code to understand how it's working.

If you've just joined, because I notice people are still joining as the link in the chat box to the materials.

Okay, I'm gonna scroll down to the slide deck.

Okay, so the first thing is that I'm gonna mess around in the developer ribbon.

Now, strangely, and I don't know why this is the case, but strangely, Microsoft don't enable the developer ribbon di by default in Excel.

So it is hidden, everyone has it.

You don't have to install anything to gain access to it, but what you do need to do is to unhide it and make it visible.

So to do that, apparently we can click on file in Excel, we can go to options, and then we'll end up with what we're seeing here on the screen.

So something that looks like this.

And then if you look kind of down the left hand side, customize ribbon.

So if we click on Customize Ribbon, the developer, if you do this, and I recommend you do do this, the developer Ribbon by default is not visible.

So it's not checked. So all we need to do is check it.

Now, file options and cus customized ribbon is not the only way we can achieve this. There are a number of ways to do that.

Let me jump into Excel and do that. Now.

I'm just gonna have a blank Excel opening.

It doesn't really matter, you know, you can have a, the workout file we're going to use or some other model or a blank Excel. It doesn't really matter if I click on file on the top left.

And if you look having clicked on file at the very bottom, you've got options.

So I'm gonna click on options, and then we get this dialogue box that pops up and down the left there's a menu with different sections.

And what I wanna do is look at customized ribbon.

So if you've done file options, customized ribbon, you should land at somewhere, something that looks like this and let's just collapse home.

There's a whole load of ribbons with checks next to them down the side.

And I have a feeling that your developer ribbon will look like that.

It will be unchecked. So I'm gonna click on it to check it.

And then if we click okay, then we have the developer ribbon that appears.

Okay, so for me, I've got file home insert, et cetera.

And if you scroll maybe about two thirds of the way to the right we've got a, a ribbon called developer, which you can click on and it's got a whole bunch of macro stroke VBA type stuff in it.

And it just isn't available to you by default unless you select it.

As I'm chatting away, more people have joined. And if you've just joined, welcome along.

I'm just gonna pop in the chat box link to the materials.

But for this session, I'm actually not convinced you really need those materials because you could achieve what we're gonna achieve, just using a blank Excel.

Okay, so back to my slide deck.

Hopefully we've all done that. Hopefully we've all enabled the developer ribbon.

And, if you haven't done it, if you could do it, it would be good because then you'll be able to follow along with other things that we're going to do if we scroll forward in the slides.

Let's have a look at that developer ribbon in a little bit more detail.

There's loads of stuff in there, some of the stuff is useful and we we're gonna make use of it in this session.

So on the developer ribbon and on the very far left, you've got an icon for Visual Basic.

So if you click on that, that opens up the visual base can, it's kind of there already, but it's sort of under the skin of Excel.

So by clicking on it, you sort of make it visible.

So you've got those two programs open at the same time.

I actually, I'm doing that in my hands because on the screen I actually love to tile them.

So I'll have one side by side.

So if we write some code or we run a macro, we've recorded, it's really nice to see what it's doing in Excel.

So I'd recommend we do that. We'll do that in a second.

There's a button next to that also on the left called macros.

And so if we've recorded some macros or if we've coded some macros, if we've created some just by coding in, uh, directly into the Visual Basic editor, they will appear here.

We can record a macro. So it's not the only way of achieving it, but there is a button on the developer ribbon that enables us to record a macro which is very useful.

So the great thing about macros, I mean, you don't need to record a macro, you could just go straight into the visual ba basic editor and start coding in a sub procedure without making reference to a macro.

But, you know what with VBA, we're talking about a language and, and often in any language, I'm English, even in English, sometimes I think, what is the right way to say this? You know, how, how might I word that and, and phrase it? And of course, for us, visual basic is VBA is a foreign language.

It's not our, certainly not our first language.

And so if you're trying to create some code and write something, you might understand what you're trying to achieve, but not understand the way to say it.

So it's super useful to use the visual base to use the macro recorder to record some record some actions and then go and look at the code that's been created and that enables you to understand how to say things in VBA.

We've also got something called use relative references.

So think about Excel. Okay? In Excel, Excel uses relative references by default.

So if you go into any given cell and you say equals the cell above, then if you copy that formula out to the right and to the right and to the right, what it'll do by default, it will reference the cell directly above each respective cell.

You can override that of course in Excel, you press F4, it puts dollar signs around the reference and it locks it.

So by default in Excel, all formulas are built using relative references.

Now, this is a bit weird, but in VBA the opposite is true.

So by default, when you are writing code in VBA, it uses an absolute reference, so it's locked to a cell or it's sort of anchored to a specific cell, unless of course you tell it to do otherwise.

So it is almost like the inverse of pressing F4. If you select use relative references, when you start to record macros and build code, it will look relatively.

We've also got insert, so just something very, very quick on that.

When we record a macro, we need some way of running it.

Okay? And what you can do is click on macros number two to the side, get your list of macros and run that.

It's not very user friendly, is it? What you could do is sign a keyboard shortcut.

For the most part, I'm not a big fan of keyboard shortcuts for macros.

Now I say that I actually do have a particular macro that I built that beauty saves my spreadsheets and I use shift control and S so I've assigned a shortcut to it.

And so there are some, some circumstances when shortcuts for macros can be, I think, quite effective, but for the most part they're a bad idea.

And that's because there are already loads of shortcuts in Excel, right? So if I said, Hey, let's print something, you'd probably say, Jonathan Control P.

If I said make that sell bold, you'd say Control B.

So there are a lot of shortcuts that already exist in Excel, and if we create a shortcut for our macro, it will stamp over the top of those existing shortcuts, which is kind of a pain.

So I'm not a massive fan of that.

Talk about that in the next slide a bit.

So rather than do that, we can use the insert section of the ribbon to insert, for example, a button.

And what you can do is you can assign a macro to a button.

So insert button, click on it and it will run the macro.

It's a really great way of running macros in spreadsheets.

Okay, let's scroll down.

So the next slide is on the VBA environment.

We should definitely go and have a look at that.

So to gain access to the VBA environment, if we scroll back, we would click on the developer ribbon on the visual basic section of that developer ribbon, and you'd be presented with a screen that looks like this.

Let's do that in a second.

On the, in the middle of the screen you've got the code window.

So in the code window you can just type in code, or if you record a macro code will get populated there.

As I say, really useful to run that code and have Excel open and see what it's doing.

We've also got the immediate window.

Absolutely love the immediate window, rather than having to build a sub procedure, you can type in little bits of code to see if it's behaving how you think it'll behave.

So type it in and strike enter.

And then in Excel it'll run just that individual line.

So it's really great as you kind of like incrementally building a building some code. Very useful.

On the left we have the Project Explorer window.

So when we write, when we write a macros, when we create code, I might call them sub procedures I want you to think of those as files.

So when we create a macro or we write a sub procedure, so a little bit of code I want you to think of that as a file that we've created.

And you know, in Windows files are stored in folders, will in visual basic VBA macros or sub-procedures are stored in modules.

So down the side there's a bunch of different modules here.

Actually there's one module, it says there, module one, think of that as a folder and that will contain lots of little macros or sub procedures that have been created. They're really one in the same thing.

And they're effectively like files that are stored in that folder.

Okay, why don't we actually have a look at this.

So if we go back into Excel, and I've got that developer ribbon enabled on the far left, there's a section here for Visual Basic, it says Alt F 11.

So you know, maybe useful to know the shortcut, but I'm gonna use my mouse.

So I'm gonna click on that. And we've got, let me maximize that.

So we've now got what we saw on the slide.

So we've got the visual basic editor in the middle here. We've got the code window, but it looks kind of blank because we don't have any code open.

Uh, on the left here we've got the we've got the area where modules would be stored, but we don't have any yet.

And down the bottom we've got an immediate window.

I really love having the visual basic editor open and having Excel open side by side.

I think that's an extremely useful way to build code and to run macros.

But we probably want to sort of move forward and actually build something.

So if we scroll forward, there are some shortcuts.

We probably can't do much learning shortcuts in this very, very short session, but they are available.

I'm not gonna use them because I'm gonna use my mouse so you can see what I'm actually doing and where I'm clicking.

If we scroll forward, what about the macro recorder? So massively powerful, slightly inefficient and I'll explain those two points.

So it would probably be a stretch for us to just jump in and start creating code for a language that's completely unknown to us.

So what we're gonna do is let the macro recorder do some heavy lifting here.

If you look at the developer ribbon, we've got that we saw in the previous slide. We've got visual basic, we've got macros and we've got record macro.

So presumably if we click record macro, then it will open up this dialogue box that we see down here.

And the first thing it'll say is, Jonathan give you macro a name.

So I know that when I have files, I might call it Jonathan's space file Space four space class for example.

But unfortunately in visual basic, no spaces or special characters.

So when we're creating the name of a macro, you could use something like underscore I think can be really effective.

So you could call it Jonathan's or Jonathan underscore Macro or you could just put it all together and have no, you know, no spaces. Perhaps you could capitalize Jonathan and capitalize M for macro for example.

But you can't have any spaces or any special characters.

Unfortunately it does give the option to assign a shortcut.

And I said I wasn't a big fan of that.

So controlling something, it says over here that in Excel 2009, J, M, and Q are not assigned shortcuts.

But that's probably not very intuitive.

If you were gonna say create a macro to beauty save your work, for example, which I'm gonna talk about at the very end, then you know, the obvious thing would be like B for beauty save or maybe S for save.

So control S is already save in Excel control B is bold, so we can't use those.

I'm not a big fan. But what you can do, and I think this is useful to know, is you can use shift.

So you can hold down when you assign the shortcut, you can hold down shift control and press the key.

And I don't think there are any or there, no, that's not true. There are very few shortcuts in Excel that use the combination of shift and control.

There are some, certainly there are, but there are very few.

So that's a really, really good tip.

When we create a macro, it will bind default, save it in the workbook you are working on.

And I'm gonna proceed on that basis.

But let's just think about why that might be a bad idea and why it might be a good idea.

I might write a macro, as I've said to Beauty Save.

So whatever file I've got open actually doesn't matter what I've got open any file I open for me, I hold down shift control and S and it will put the cursor to sell a one and it'll go to the first sheet and it'll do a beauty saving.

That's all great. And I really want to use that macro irrespective of what file I've got open.

So I wouldn't wanna save the macro in the file I'm working on because it would only be available in that file.

That's no good for me. Okay? And in that instance, what I'd save it in is my personal macro workbook.

So this is really kind of under the radar, but when you open up Excel, it always opens up your personal macro workbook.

You just never perceive it.

It's always hanging around in the background and probably there isn't anything in there.

But if you've created if you've created you know, a macro and saved it in there, but then that will sit within that, you might use a plugin, say like a FactSet plugin or various other plugins and they will certainly be sitting in your personal macro workbook.

What about if I create a macro in a file and I want everyone that opens that file to be able to use that macro.

So all of you guys, there's loads of people in the room on that file.

I want all of you individually to be able to run it. There's not much point saving it in my personal macro workbook 'cause you won't be able to run it.

We should save it in the file itself.

So imagine that the file's got some data in it and we wanna clean the data in some way by pressing a button.

We'd want that macro to sit in the file.

So there are pros and cons for each approach.

There's also the option to put a little description in and that's probably quite useful.

I'm gonna do that.

Let's just have a, a quick look then.

So, again, for the background recorder there's an example here of something we might record.

And it's showing that if you look at Visual Basic, which is kind of overlaid the visual basic editor, it's showing some codes been created.

Let's try and do that now for the guys that Have arrived after I started talking, I'm just gonna copy and paste this here.

So for anyone that arrives after I started chatting away, there's the link.

I've got a question in my Excel. It's not shown The left and the bottom immediate field.

That's okay. We can have a look at that.

It's there are certain things that may or may not be visible and we can turn them on and off.

We won't really need them now, but I'll show you how to do that. Anyway, anyone that's just joined if you jump onto the web address on the very bottom right, there's a link there to something to download.

If you can't download it, it really doesn't matter that much.

I'm gonna open it up, I'm gonna open it up.

Let's just close that visual basic edit for a minute and sort of maximize this, and not that one. There we go. So I'm gonna open up that file and if you guys jump from the welcome sheet to the record macro sheet, That would be good.

The question on making things visible in VBA, I'll definitely show you that in a minute, don't worry.

I'll show you that when we when we jump in there.

So we've got a little example here.

Really just something blank.

What I'm gonna do is I'm gonna go into sell, I'm gonna go into cell B8 and enter my name.

Okay, that's not very profound thing to do, but let's create a little macro to go into B8 and enter my name.

Now I'm gonna click on cell A1.

Not that that's that important, but I'm gonna click on a one and then we're gonna record a macro.

There are two ways of doing this.

What we can do is go to develop a ribbon And record macro, Okay? Which if you wanna use a shortcut, let's just click on the home ribbon is Alt L for developer and quite sensibly R for record macro.

And it opens up a little dialogue box here.

So you can do it that way.

Or If you look at the very bottom left, see I'm not sure how well this will come through on the screen share, but let's magnify this a bit.

If you look on the very bottom left, there is one I think is a little icon.

I think it's supposed to be like some code with a kind of record button on it. You know, like the circle is kind of a universal sort of symbol for record.

So you can also click on the bottom left and if we do that, it brings up that same dialogue box.

So it doesn't really matter how you do it, let's all click on record macro.

I'm gonna give it a name.

I'm gonna call it something like enter name, enter name or maybe enter capital name, enter name.

I'm not gonna give it a shortcut.

I mean if I gave it something like B, that would be a problem because it would override making things bold.

What I could do is I could hold down shift and press B control shift and B, so you, you, you know, it is probably always worth using the shift key if you do want to assign a shortcut, but actually I'm not gonna bother.

Okay, we're definitely gonna store it in this work book I am gonna put a little description in because I wanna show you that in the code.

So, this macro enters my name in B8.

Okay, so it's, that's just a little description. So we can see that coming through in the code.

Now if I click okay, it looks like nothing profound has happened, like that was happening here. Nothing's happened. Well actually we are now recording so Anything I do Gets recorded, which can be very frustrating because if you mis click on something or mistype something, it kind of enters into your code, you might ultimately wanna delete that macro and start again.

Anyway, let's be Careful.

I'm gonna go into B8, I'm gonna click on B8.

It's just recorded me doing that incidentally.

And I'm gonna type my name Jonathan And hit enter.

And that's actually all I want to do.

Now I wanna stop the macro. What we could do, can you See in the developer ribbon, that Record macro button is now turned turn to stop macro, that's sort of Universal like media player sort of sort of square button for stopping recording.

And also if you look on the bottom left down here as well, we've also got a square.

So I'm gonna stop recording, Right, what happened? Well I tell you what, do we have a macro there? If we're in the developer ribbon and we click on macros, I expect it'll bring up a dialogue box and it will show me my macro.

Let's click on that. Okay, it does, okay, there's a macro called end to name.

I'm just gonna cancel that.

If I delete my name out of B8 and perhaps I go back to A1 macros end to name, let's run it. Okay, So it goes to B8 and enters my name.

Now I wonder if I delete that and I go to some other random cell, will it kind of go down a few cells and across Across one, across one column.

So down a few rows and across one column, will it sort of like go down and enter my name down here because I'm now in a different starting position.

I'm pretty sure I know the answer to this one and probably you do as well, but let's just try it.

Macros enter name run. Oh No, it goes back to B8 and that's because by default Excel uses absolute referencing, not relative references.

Okay, let's just delete that and go back here. Now, what I'd like to do is I would like to see that code working.

So if we click on macros to bring up that dialogue box, let's not click run, we could edit the macro, we could go and look at some options and assign a shortcut to it, et cetera.

I'm not gonna do any of that. Can you see? It's a step into why don't we magnify that there.

So right on sort of second from the top it says step into this is a really cool feature.

So if we click step, step into that macro, it will open up the visual basic editor.

I'm gonna tile these two screens. So they're side by side.

Can you guys see that? We've got a yellow arrow here and it's highlighted in yellow.

Okay, I'm just gonna sort of click on this here you can see Excel. My curser is in A1, there's nothing in cell B8.

Now what we're, what we've done is we've stepped into the macro and we are in break mode.

And this is really cool.

It allows us to slowly step line by line through the macro and see it running in the background.

Okay, so in order to step through the macro, you need to press the key F8.

Okay, F8. We'll step through.

You might notice that at the very top it says sub and then end to name open back it close back.

So this is like, it's a bit like a file in Windows, but it's a little program, a sub procedure, okay? And uh, the, it's got a name, I've given it a name, I called it end to underscore name.

And then can you see we've got some, a post apostrophe here and some text in green.

That text is not gonna run as part of the macro.

If it's got a comma in front of it, apostrophe perhaps in front of it it's a comment and so it won't form part of the code and then it turns green.

You can see that I put some description and I said this macro enters my name in B8.

I could type something else in like, I like this macro or you know, whatever you wanna whatever you wanna say, I like this macro.

And if you press enter on that, you know, it, it turns it green and it becomes a comment.

Oh that's all very cool.

Really useful to thread into the code.

Some comments to explain what the code is doing.

Now I've said that that won't run as code so presumably if I press F8 it will skip past all of that because they're just comments.

It selects the first cell, it hasn't run that code yet, but look at it.

Range B eight select. So it's probably fairly intuitive.

A range is like an array in Excel.

It could be a whole load of cells, but it isn't, it's just cell B8.

So the code is say go to cell B8 and select it.

If we press F8, now look at, look at Excel here.

If I press F8 for a second time, it actually does that, it hasn't entered anything in there yet.

Look at the next line active cell.

So it's selecting the active cell formula, row one, column one. So there's no offsetting here at all.

It's just gonna grab that row in that column and it's gonna make it equal to Jonathan because it's a text string.

It's in double quotes.

Now there's nothing in Excel here, but if we press F8 again, it will run that and then it gets to the end of the sub procedure if we press F8 again.

So what you could do, I've referenced a few times that I've got this beauty saved macro and what you could do is you can go into Excel, we're not gonna do it now, but I'll leave you with a a few thoughts go into Excel.

You could say developer record macro and then when you are recording a macro, which I'm not yet doing, you could say something like you could say something like control home.

So that takes you to cell A1.

Now I don't know how to code that in VBA a or perhaps I don't perhaps, I dunno how to code it in in VBA, but if I don't know how to code it then I could use the macro recorder to create the code.

I can go and look at it. If you wanted to create a macro that went to cell A1 and then went to B8 and wrote your name in the cell, then you could copy and paste those two sub-procedures.

You could put them together.

So you, the macro call is super, super useful for doing that.

Okay, we've got a minute left.

I did get asked,I asked, was asked a question.

I really want to respond to that.

So, if you go into, if you go into the VBA editor, there's a menu at the top. It's not got this sort of ribbon layout that we are used to.

Now in office. It's still a bit of a throwback, but you've got view and you can select things like the immediate window object browser, locals, window, et cetera.

So you can make, if it doesn't look like mine, you can make it look like mine, right? It was only a short session.

What I hope to do is just to wet your appetite and sh give you some tools to play around with. There's some awesome content on Felix so you can pick this up and have a bit of a play around.

I know you're gonna find it useful, like visual basic VBA is a massively useful tool.

So thanks so much for being dialed in. It's really nice to see you guys on the loads of people on the call. Really appreciate that.

And look forward to seeing you in a future session.

Thanks guys. Cheers.