 |
|
|
|
INTRODUCTION
In this first lesson we will discuss the basics of VBA for Excel. I purposely do not include screen shots or pictures, as I believe by omitting them it forces the student to explore and become more familiar with the environment in which they will be working. This may seem a bit inconvenient at first, but the long-term gain will far outweigh the short-term pain. The basics are probably not very exciting, but definitely necessary so you have a good understanding of the Application (Excel).
VBA is short for Visual Basics for Applications. This is the standard Macro language used in most Microsoft Office products. The word "Applications" can represent any one of the Office products it is used within eg; Excel, Access etc. The main ones being Excel, Word, Access, Powerpoint and is now, in Office 2000, moving into Outlook. The VBA language is a derivative of Visual Basic (VB), which in turn is a derivative of the language Basic. The fundamental difference with VBA from VB is that VBA is (as the name implies) used within an Application. By far the most mature of these Applications when it comes to VBA is Excel. You will find as we delve deeper into VBA for Excel that we can modify the Application so it will behave in almost any way possible.
The purpose of VBA is to enable programmers to customise and extend the functionality of the Application in which it is used. The VBA we will be talking about in all lessons will be VBA for Excel. While the VBA language is generally the same throughout other Applications, their Object model can differ significantly. Unless you are already familiar with a programming language I would recommend not trying to learn too much too soon. I believe it is far better to gain a good understanding of one small aspect of VBA for Excel than to gain a superficial knowledge of a broad aspect. I can honestly say (without hesitation) that I may not know every single aspect of VBA for Excel, but what I do know, I know well. Please at anytime throughout the course never feel that any question is a silly question and if you do not understand an answer I supply for a particular question then it is important you say so. Trust me I have immense patience! One of the most important things about the Basic VBA for Excel course is that you have an understanding of each lesson before moving on to the next lesson.
Getting Started - The Look of the Visual Basic Editor
To write any VBA code (not record), we need to go into the VBE (b>Visual Basic Editor). There are many ways to do this, but by far the easiest is by pushing the shortcut key Alt + F11 (hold down the Alt key and push F11). You can also access the Visual Basic Editor by going to Tools > Macro > Visual Basic Editor from within Excel.
Below is a summary of arguably the most important components of the Visual Basic Editor. I would like you to work through this, so open a new workbook within Excel. Open the Visual Basic Editor using one of the ways described in the previous paragraph (Alt + F11 is the fastest). It is important that you use a new Workbook so that all names and terms we refer to are the same. The positioning of the windows I refer to may be different in your view, but I will use the headings also so you shouldn't get lost.
At the very top of the VBE you have what is known as the Menu Bar. From this one menu bar it is possible to access most functions of the VBE. If you right click on the grey area just to the right of Help on the Menu Bar a shortcut menu should appear. Select Customize... Here you will see the names of all the Toolbars that can be available to you. As a general default you will have the Menu Bar and the Standard Toolbar showing. To make sure we are looking at the same view, go to View > Project Explorer, then back to View > Properties Window. Or to use the shortcut keys, Ctrl + R for Project Explorer, and F4 for the Properties Window. You should now have visible the Project Explorer and the Properties Window. If you go up to the top Menu bar and click on any menu item you will notice that many of the functions available have their associated short cut keys written next to them. Get to know these well and working from within the VBE will become mush easier.
Project Explorer and Properties Window
Within the Project Explorer (the small window with the heading Project - VBAProject) you will see at the top VBAProject (Book1). This window and it's folders are very similar to the folders you would see in Windows Explorer in that you will be able to expand and collapse folders by clicking on the + or - signs to expand and collapse them. Expand VBAProject (Book1)! Once the VBAProject (Book1) is expanded, you should see a folder called Microsoft Excel Objects. Expand this folder and you will see the Objects that the Workbook contains. In most cases this will be Sheet1 (Sheet1), Sheet2 (Sheet2), Sheet3 (Sheet3) and ThisWorkbook. It is not necessary to know anything more about these at this stage, but I will be showing you how they can be used in a later lesson. But as you have no doubt guessed, the Sheet1, Sheet2 etc refer to the Worksheets in the Workbook, while ThisWorkbook refers to the Workbook itself. If you now click on Sheet1 in the Project Explorer you will see in the Properties Window (the window probably directly beneath) a list of all the Properties for a standard Worksheet. Don't be too concerned with what Properties are at this stage, we will go into the detail later.