Excel VBA
Introduction To VBA: Macros And Automation Part 1
Language Basics
Additional Keywords
Keywords can be broken down into the types of tasks they perform within VBA. Some are infrequently used when developing smaller, less complex macros, others are essential to every macro. We will start by covering the essential keywords, and subsequently cover non-essential but frequently used ones.
Essential Keywords
These keywords are used in the vast majority of all macros, and are essential tools in VBA. They will quickly become a part of your vocabulary for how often they are repeated.
Module
While not something you will necessarily write out when working with VBA, the code object you will be writing in will most likely be a module. It specifies the source file/path your code can be located in, so that the system can retrieve it when needed.
Sub
Short for subroutine, a Sub is essentially a procedure which can be referred to by name. It is the first line of any macro and will make an appearance again in at least one similar form later on in your code. After using the sub keyword, you can define the name of the subroutine, as well as any parameters it has, prior to the code which makes it up.
End
A VBA prefix which is responsible for ending the portion of code defined by the keyword placed after it. For example, ‘End Sub’ is used to close the code block that is the subroutine. It is also used with a multitude of other keywords like ‘if’, ‘function’, ‘module’, ‘select’, ‘while’, or ‘with’ among others.
Dim
This keyword begins the process of defining a variable. It stands for the word dimension, which is used to describe the process of explaining the limitations and restrictions being placed upon a variable. The Dim keyword is always followed by a string which will become the name of the variable, and then subsequently the data type it is being assigned.
As
One of the most used clauses within VBA due to its close relation to the dim keyword. Once a variable name has been defined, the As keyword is required to link the variable name, to its data type. The as clause is also used in a multitude of other clauses and statement types, although those do not fall under our classification of essential knowledge.
If Statement Keywords
If
The most basic comparative function in Excel, whose VBA counterpart operates in a very similar manner. The statement starts with a logical comparison between two or more things, and moves on to detail what should be done if the criteria are met.
Then
A follow-up to the If keyword, Then is used to direct to the system what is to be done should the conditions of the If clause return a value of TRUE. Everything following the Then clause will be executed until the If statement ends, or when a new logical comparison is presented.
Else
Continuing with the If statement, Else is the keyword used to identify which block of code should be executed when the result of the logical comparison is FALSE. This keyword is, however, not necessary for the complete construction of an If statement block.
ElseIf
This keyword brings in the functionality of the IFS function from Excel. In the event that the initial logical comparison does not return a value of TRUE, and you would like to specify additional comparisons to validate (with the intention of having different code running on each result), ElseIf allows you to add these comparisons. Always placed before the final Else keyword, ElseIf comparisons also require the use of the Then keyword.
End If
Bringing back one of our essential keywords, the End prefix is used in conjunction with the If keyword to signal that anything beyond that line of code no longer a part of the statement we had been working on, and to resume running through the code normally.
And
Another keyword familiar to those in the Excel environment, And allows multiple logical comparisons to be made where only under the situation that every comparison results as TRUE will the final statement resolve to TRUE.
Or
Similarly to the And keyword, Or should be familiar to those who have an existing proficiency within Excel. It allows multiple comparisons to be made, where if any of them evaluate as TRUE, the final statement will resolve to TRUE as well.
AndAlso
This keyword functions very similarly to the And keyword, with one notable exception. A traditional And keyword will evaluate all arguments before comparing them. AndAlso will only evaluate the subsequent comparisons if the previous one evaluated to TRUE. This makes it consume less execution time, especially with more complex instructions.