 |
|
|
|
Common Terms used in Excel VBA:
Modules
The grey area of your screen is where your modules (where code is written) are located. As this is a new Workbook we are looking at, there will not be any Modules visible. To insert a Module we go to Insert > Module. You should then see a white background where it used to be grey, this is what's know as a Module, sometimes referred to as a standard Module or code Module in earlier versions. It is within these Modules that we can write VBA code. The code we write is written within a Procedure, a Procedure is a series of statements giving Excel instructions on what to do (a macro). We can have as many Procedures within a Module as we like and we can also have as many Modules within a Workbook as we like, the only restriction is the PC's available memory.
There are 2 types of Modules in the Excel VBE, these are Module and Class Module. The Module (standard Module) is the one we will be using throughout this course. The standard Module can also have what is known as Private or Public modules. By default all standard modules are Public. The Private Module is used in some of Excels other Objects, ie UserForms, Sheets and ThisWorkbook. Again we wont go into any more detail on this yet as we will be covering these later.
One thing you will find yourself doing many times over when writing VBA code is toggling between the VBE and Excels user interface. The simplest way to do this is to either again push Alt + F11 or click on your Microsoft Excel Workbook located on the Task Bar. Let's now look at the Fundamentals of VBA for Excel.
Procedures
Procedures are a named set of statements that are executed as a whole. They tell Excel how to perform a specific task. The task performed can be very simple or very complicated. It is good practice to break long or complicated procedures into smaller sized logically ordered procedures. The two main types of Procedures are Sub and Function. A Sub procedure is what results from recording a macro, while Function procedure must be written manually. We will be looking at Function procedures in a later lesson. All Sub procedures must begin with the word Sub followed by a chosen name and then a set of parenthesis. All Subs must end with End Sub. See example below:
Sub MyMacro()
'<Procedure Code>
End Sub
Keywords
Keywords in Excel VBA are words that Excel has set aside to use in the execution of code. This means we cannot use them for any other purpose. For example, Select, Active, Sub, End, Function etc are all Keywords that we can only use for their indented purpose.
While working in the Visual Basic Environment we can get help on any keyword by simply placing our mouse insertion point anywhere within the keyword and pressing F1. This will force the Excel help to default to the specific topic and saves a lot of hunting around. I urge all users to use this feature to its fullest.
Comment Text
Comment text is what we can use in a Procedure to help explain what our code is doing, or rather why it is doing it. To identify comment text to Excel we must precede it with a single ' (apostrophe). Excel will ignore any text preceded with a '. I strongly suggest using lots of comment text in procedures as it helps greatly when you come back to it later to edit the code. See example below:
Sub MyMacro()
'Place the value 150 in cell A1 of the active sheet.
Range("A1").Value=150
End Sub
You will notice that comment text is a different color to procedure code. This is so it is easily identified as such.
The default color coding that Excel uses for Comment text, Keyword text, Procedure text etc can be changed by going to Tools>Options and clicking the Editor Format tab. don't get too carried away though :o)
Line Continuation Character
The Line Continuation Character is used to tell Excel that more than one line of code is really a single line. It is represented by _ ( space then an Underscore). So to continue a line of code onto the next line in a module you type a space followed by an Underscore. The reason you would use this is so that you would not need to scroll sideways to read a long line of code. See example below:
Sub Find100()
'
' Find100 Macro
' Finds the value of 100 on the active sheet.
Cells.Find(What:="100", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
End Sub
The above example is really one continuos line of code. As you can imagine this would not fit on most PC monitors if we did not use a Line Continuation. Well, not unless you are lucky enough to have an 80 inch screen!
Important Definitions:
Objects
The word Object in the context that we use it is used to describe just about everything in Excel. You will find as you get deeper and deeper into VBA that there are many Objects. Don't confuse the word with its usual use of referring to something tangible. In Excel, an Object can be a range, a worksheet, a chart or even the Visual Basic Editor (VBE) itself. You will find as you learn more about Excel VBA that there are a lot of Objects!
The very least that you need to know about Objects is that there is what is known as the Object Hierarchy. At the top of this order we have the Application Object, Excel itself in this case. Directly underneath the Application we have the Workbook Object eg; Book1.xls. Directly underneath the Workbook Object comes the Worksheet Object. At the Worksheet Object the Object Hierarchy branches off to incorporate all Objects of the Worksheet. The first one you will most likely encounter will be the Range Object. Branching from the Range Object there are again many other objects such as the Areas Object, the Borders Object, Font Object, Characters, etc. To see a complete description, of the hierarchy of Objects go to the VBE for Excel Help and type Microsoft Excel Objects. Don't let this confuse you, as all you need to know at this stage is the following order:
Application
Workbook
Worksheet
Range
When you have a group of Objects that are related, this is then known as a "Collection". So when we use the term Workbooks, we are referring to all open Workbooks, and when we use the term Workbook, we are only referring to an individual Workbook (the active Workbook).
You will notice when reading the above paragraph and in the VBE Help, that the Workbook Object is part of the Workbooks Object. In layman's terms, the Workbook is a member of the Workbooks collection. It is also the same for the Worksheet Object, this is a member of the Worksheets collection. Don't worry too much if you don't grasp this concept immediately, it will become apparent as we progress.
Properties
Properties are an attribute of an Object. They are used to define an Objects characteristics, so to use an example; the Worksheet Object has many Properties, one of which would be its name. So by changing it's name, you are directly changing one of its Properties. Also by making the Worksheet hidden, you are again changing one of its Properties, in this case, the Visible Property. To be able to change the Property of any Object we must first identify the Object of whose Property we wish to change. In the example above we would do this by telling Excel which Worksheet we wish to rename. You will see how this can be done later.
Methods
Like Properties, Objects also have Methods. A Method is simply a procedure that acts on a Object. By this I mean it causes the Object do do something, this might be opening a Workbook (which is an Object) or deleting a Worksheet (another Object) or any one of thousands of other things.
Events
An Event in Excel VBA is as the name implies. For example a mouse click is an event as is the closing or opening of a Workbook, in fact there are hundreds of them. The best part about Events is that we can we can programme Objects to respond to Events. This is covered in detail later.