Excel Visual Basic (VBA) for Beginners - Part 1 of 4: Buttons and Macros

most of us associate code and computer programming with things more complicated or sophisticated than Excel but if we learn some code it can allow us to do so much more and the basics are actually fairly easy to get to risk with we've created a series of four videos which are going to allow you to get to understand the basics we're going to talk about adding buttons to spreadsheets and attaching macros to them we're going to talk about changing cell values we're going to talk about using offsets of control positioning and we're going to talk about creating basic loops these are for the main building blocks you need for Visual Basic coding so let's get straight into it okay so all I've done is created a brand new excel file then absolutely nothing to it the important thing to understand is that you need to have the Developer tab visible you can see up here the Developer tab is visible however it isn't usually visible in Excel so you might have to go back into file and into options and go to customize ribbon and then on the right you can see all of the things that appear in your ribbon at the top of your excel so if the developer box is on ticked then just tick it and then exit options and you'll be able to see the Developer tab at the top now I'm using Excel 2013 if you're using an earlier version maybe 2010 or 2007 just Google Excel 2010 Developer tab and you'll be able to understand how to get it to display okay so that's the first thing make sure you can see the Developer tab what we're going to do in this video is create a button and attach a macro to that button to do something now we've all seen fancy advanced sophisticated spreadsheets I've created a few myself maybe that have buttons in that won't run routines that do things in the spreadsheet that's what we're going to do we're going to put a button in and attach a macro to it that so the first thing we need to do is open the visual basic editor Visual Basic is the name of the language that underpins Excel and we can edit it and manipulate it to get us to do things for us so let's open the visual basic editor and this is what looks like there isn't a lot there at the moment over here on the left there are tools for structuring your code you structure your code using things called modules so the first thing we're going to do is go to insert at the top and put a module in so not a class module or a user form just put a module in and we can see here we have a module and Excel has taken us to that module okay so we're going to create our first routine now exciteing so we're going to type in sup and then we're going to call this routine say hello okay so sup say hello and then just hit enter you'll go on to on - and - new line and Excel has given us n sub so these routines Visual Basic refers to them as subs and we can see Excel has started the sub and ended the sub there everything we do needs to be within those to the start point the sub and the end point the end sub okay what we're going to type in is msg box which is visual basic language for message box space and then speech marks and we're going to type in hello you could type in any message you want I'm going to type in hello here and then we can click answer again I'm just going to delete the blank spaces so this is our first visual basic routine okay that's great so now we're going to move away from visual basic back into Excel and we just have a blank space spreadsheet what we want to do is create a button put it in here and then link the macro or the sub that we just created to this button so that when we click the button that routine that macro that sub is activated and it executes so to add a button we're going to go to insert at the top and this is the same in Excel 2010 left click and then click on this button here you click once and now you're ready to add a button you can see the cursor has turned into a cross means Excel is ready for you to add a button now a little tip when you're adding buttons I always like my buttons to it to line up nicely I'm a little bit pedantic about that and you can do that using the Alt key so if you hold down the Alt key when you're positioning the cursor Excel will snap that button to the grids to the gridlines which means that the button will be nicely lined up so I'm just going to release and Excel has created the button it's not showing it to us yet but it has given us this dialog box so what's this dialog box doing well Excel is asking us of the routines that you have created which one do you want to trigger when you click this button so it's asking us to assign a macro to this button so can you remember we created the routine say hello we're going to click on that now and that means that when we click on this button the say hello routine is going to run okay we can click OK now and we have our button I like to edit the text I just right clicked on the button and gone to edit text and I'm going to put something mean meaningful in here so as it's important that when we click the button we understand what it's what it's doing and editing that text allows us to do that so let's click the button and let's see what happens okay excellent so we click the button exile has got into visual basic it's found the routine that it's assigned to that button and it's gone through that routine and in the routine we just had one line of code which is show a message box with the word hello in that's exactly what it's done so let's try that one more time there we go feel free to practice it as many times as you like it's always a sense of achievement when you get a macro to work well so definitely have a play with it ok great so you've now created a button you put it in the spreadsheet you've created your first macro in Visual Basic and you've got the macro to work by clicking on the button so that's a great start ok this is the first of four videos in a basic Visual Basic for Excel and I will see you in the next video

Channel: Tiger Spreadsheet Solutions

Views: 1,476,297

Rating: 4.9015989 out of 5

Keywords:

Id: 78GvLEAZecs

Length: 6min 48sec (408 seconds)

Published: Thu Dec 05 2013