Excel VBA Introduction Part 1 - Getting Started in the VB Editor

welcome to this wise ell tutorial in this video we're going to teach you about working with the visual basic editor application with a name to writing some Excel VBA code in this video we're not going to write any code itself but we are going to do is show you how you can set up and work with the visual basic editor so I'll start by showing you how you can access the VBA deter from whichever version of Excel you happen to be working in we'll talk about how you can switch between the the VBA editor and Excel itself with some nice quick keyboard shortcuts we'll also give you a quick whirlwind tour of the VB screen and explain what the main window is in the VB editor application now for once we've covered that we'll move on to show you how you work with modules which are the places where you're going to write your code so I'll talk about how you can create and remove modules how you can switch between the various ones you've created how you can rename them and finally how you set up the fonts and colors that you're going to use while you're actually programming so let's get started before you can start writing VBA code you first of all need to open up the visual basic editor application and the way that you do that depends on which version of Excel you happen to be using if you're in Excel 2003 you can quite simply head to the Tools menu find the macro option in there and then click the visual basic editor option there too you'll probably find that a guess a bit annoying having to go through this menu every time you want to get to that option so alternatively you could also display the visual basic toolbar in Excel to do that you'd need to right-click somewhere at the top of the screen where all the existing little bars are choose visual basic and then I'm going to drag this and nest it up at the top of the screen like all the little buzz you should find on there that there's a symbol button you can click on to take you to the visual basic editor and there it is to get into the VB editor in Excel 2007 I first of all need to display the Developer tab in the ribbon at the top of the screen and to do that I need to go to the office button at the top left hand corner choose Excel options and then on the popular page of the dialog box that I'm looking at find the checkbox which says show Developer tab in the ribbon check it and then click OK you should find that there's a new tab in the ribbon now called Developer and if you go to that one the very first button on there visual basic clicking it will take you to the VBA editor yet again if you're in Excel 2010 or 2013 you also need to display the Developer tab in the ribbon but the way you do it in these two versions of Excel is slightly different to 2007 what you need to do is right-click on any existing ribbon tab choose customize the ribbon and then on the dialog box which appears find the uncheck box next to the word Developer check it and then click OK once again you'll find the Developer tab appears in the ribbon and if you select it you'll be able to click on the first button there visual basic to take you once again into the visual basic editor whichever version of Excel you happen to be using when you want to switch back into Excel itself you can simply click on the first button on the VB editor toolbar it looks like a little Excel icon if you click on that it takes you straight back to Excel you can also just use the taskbar although you can't see it on my screen if I hover my mouse at the bottom of the screen it will pop up with the two icons related to the two applications so you can simply click on either of those two to switch you between the two applications you might also have noticed while I've been recording this a little bit of the video that when you hover the mouse over certain icons it displays a tooltip so you'll see the Alt + f11 keyboard shortcut will allow you to switch backwards and forwards between the two applications if you hold down the Alt key and then press f11 you know Allison from switching between the two applications so onto the VBA editor itself how does the application work well it's a fairly standard looking Microsoft Office application although it uses the old-school menus and toolbars system you should be reasonably familiar with would be able to click on menus to choose options and come buttons on toolbars to make things happen one thing that is quite different about the VBA editor compared to other Microsoft Office applications is the number of windows that it uses little individual panels which display different bits of information now these two windows you can see at the moment the project Explorer and the properties window but the two that were always displayed by default but there are many more available and you open and close them as and when you want to if you close down a window or two you'll always be able to find them again from the View menu so I choose the View menu I can find my project explorer which I've just closed down and also my properties window there will always be a keyboard shortcut available to open these as well so I have four four properties control an R for the project Explorer it's also possible to move the windows around from their current positions and you can do that by simply clicking and dragging on the title bar window so I click and drag on the properties window you'll see that I get a thin outline border which changes to a thick outline border as I under Commissioner release the mouse I've now got a free floating window which sits can can be positioned anywhere on the screen you can also dock it to different positions so if I start dragging it across to the right and watch for when the thick gray border changed to a thin gray border instead I'll find that I can release the mouse button now and it will sit in and nest itself to another part of the screen so you can feel free to play around with this docking it to various regions until you find a nice layout for the way you want to work it can be a little bit tricky getting that back into its original position in actually so I undock it from the bottom of the screen where it currently is and start to draw like it towards the left-hand edge but I think it should be when it changes from thick to thin if I release the mouse button by default it soon as you go back into its original position so that way you where you release the mouse button is very very important here I've found massive difficulties doing this in the past so for this one let's see if I can get it back in the right position if I drag it down towards the bottom that left hand panel there we go guys from thick to thin there and I've released the mouse button I just need to change the size and the height of that window now by clicking and dragging in between them there we go back to roughly its original position if we want to write some VBA code we need somewhere to write it and the best place for that in the visual basic editor is in something called a module you can create modules in a couple of different ways one method uses the project Explorer window at the top left-hand corner of the screen if you right-click with a mouse anywhere inside the project you're working on you can choose to insert a module when you do so your module will appear as a separate floating window somewhere in the middle of the screen and your project Explorer create a modules folder with a little icon representing the module you've just created another way to create a module is to use the insert menus if you choose insert module that will create a module for you as well so you get another icon representing now module and another separate floating window in the middle of the screen you can close these windows down happily by clicking the crosses in the top right hand corner and you can double click on the module to redisplay it you can also maximize a module by clicking the the middle of the three buttons in the top right hand corner of this window that will give you a bit more space on screen to write your code if you do that for all your modules when you want to switch between them you need to double click to switch between them so you can see the little title bar at the top of the window here shows you what your module you're currently looking at double clicking will switch between the different modules closing the modules down then is a case of clicking the bottom of the lower of the two crosses in the top right hand corner the screen now will close down the individual module windows rather than the entire VBA editor you can also choose to remove a module or delete it now you knew that by right-clicking on the module choose the remove option and then be careful about which button you click on the message which appears the temptation here is always to click yes when if you see one of these sorts of messages from Microsoft but on the button you actually want to click here is no you don't want to export the module before you remove it unless you actually do want to do that we don't want to export our module so if we click no that will delete the module one by one we are exporting it another useful thing to be able to do with a module is to rename it to describe what's going to be contained within it so if I want to do that something to create another new module first so I'm going to right click in my project Explorer and choose insert module and then I need to rename it to give it a sensible label now intuitively using Microsoft products you'll want to be able to right click on that and choose rename but you'll be quite disappointed to find that it the rename option is in there so you can't rename a module by right-clicking on it nor can you rename a module by clicking once on its name pausing and then clicking again I'm waiting for the text cursor to appear and the only way you can rename module is by selecting it and then using the properties window at the bottom of the screen so the properties window displays the properties of whatever object you select if I select the this workbook object I get another longer list of properties if I select the project itself I get a simple name property so Green in my project as well as individual modules within it but all I want to do here to rename the module I'm going to select that name I'm going to call it something like the basics because that's what we're going to display now the names of modules follow some convention some rules you can't use spaces in the name of your modules and many punctuation characters are disallowed as well so if I try to include a space between the word that and basics and if I try to hit enter I'll find I get an error message telling me that's not a legal object name so you'll find some people like me who are quite lazy tended just to miss out the spaces altogether you'll also find another convention is to use the underscore character in place of a space so that is another valid object name the basics now we're nearly ready to start finally writing some code but before we begin there's one last thing that I think is worth while setting up and that's the thumb that you're going to use while you're programming so to modify that head to the Tools menu at the top of the screen choose the options option from within there and then on the dialog box which appears head to the editor format tab and this page of the dialog box gives you all the settings required to modify the fonts you're going to use when you're programming and the sample box at the bottom right hand corner shows you what it will currently look like so you don't like the standard set up you can change things like the font that you're using if you like courier new Westen you can click on the drop down arrow and choose any other font installed on your computer so you can use Times New Roman Arial etc you can even if you really must choose Comic Sans NMS to programming I don't even know whether that's a choice to be brutally honest but you could program in Comic Sans if you must I'm not going to I'm going to revert back to Korea new westin which i think is a much more sensible choice one thing that will help me with the videos is actually to increase the font size as well so I'm going to increase up to 14 which just make it a bit easier if you guys to read where I'm writing on screen then one last thing that's worth mentioning here as well is the ability to change the color of different types of text so the visual basic editor represents different types of code in different colors you can see the list on the left hand side of different types of text you might see so for example if I click on the syntax error text you might be able to see the samplers changed to be a product red in color so whenever you make a syntax error when you're programming they'll be immediately highlighted in red text to pick another example there's comment text as well if I select comment text that sends a darker shade of green now this sounds like a very good idea representing one type of text in green one in red unless you happen to be red-green colorblind in which case that choice is particularly useless so you can actually choose and define your own colors for different types of text you can change the foreground color with a foreground option I don't know I've chosen red again that was the one I'm trying to avoid sorry if I go for yellow perhaps and the background color you can change swirl so go for maybe a blue background color that should make if text stand out to make it easy for you to read I'm going to revert to also for both of these for the time being and then click OK and we're finally ready I think to start writing some code if you've enjoyed this training video you can find many more online training resources at ww-why 0 UK

Channel: WiseOwlTutorials

Views: 1,101,988

Rating: 4.9279122 out of 5

Keywords: excel, vba, vb editor, visual basic, setup, wise owl, macros

Id: KHO5NIcZAc4

Length: 13min 20sec (800 seconds)

Published: Fri May 10 2013