Excel Visual Basic for Beginners – Part 1 of 4 Buttons and Macros

  • by

Most of us associate code and computer programming with things more complicated or sophisticated than Excel without exception Visual Basic for beginners. but if we learn some code it can allow us to do so much more, and the basics are actually fairly easy to get. 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 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.

Excel Visual Basic for beginners

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. There isn’t a lot there at the moment over here on the left there are tools for structuring your code. Your code structure 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, so we’re going to create our first routine now exciting. So we’re going to type in sup and then we’re going to call this routine. 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. 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, this is the first of article in basic Visual Basic for Excel.

Leave a Reply

Your email address will not be published. Required fields are marked *