Dear Readers,
VBA stands for Visual Basic for Applications an event-driven programming language from Microsoft.
The Birth story of VBA
VBA was derived from Visual Basic. The story of the birth of VBA began in 1991 when Microsoft partnered with Alan Cooper from Tripod under the project code named as “Ruby“. Microsoft decided to combine Ruby with the BASIC language to create Visual Basic. The Ruby interface generator provided the “visual” part of Visual Basic and BASIC provided the “programming” interface. Visual Basic 1.0 was introduced in 1991 and VBA in its current form originated in 1993.
This means that I was just 8 years old when our VBA brother arrived and it took more than 14 years for me to unite with this amazing programming language.
VBA has been the primary tool for automating a wide range of Microsoft applications. VBA has saved thousands of man hours for me which would have been wasted otherwise in messing up manually with notorious platforms like Excel, PowerPoint, Outlook and Internet Explorer.
Accessing VBA Editor
- In Excel window, press “ALT+F11“. A VBA window opens up as shown in the following screenshot.
2. Alternatively, You can select Visual Basic Icon from the Menu Bar.
Open MS Excel>> Select Developer menu>>Visual Basic
In order to view the code editor window in VBA enviorment, you can press shortcut key F7 or alternatively select View>>Code from the menu bar.
Further, enable the “Properties Window” via shortcut key F4 or alternatively select View>>Properties window from the menu bar.
VBA Data Types
VBA is majorly driven by variables and to declare a variable, one should be familiar with the data types it allows. Below is a the summary of all major data types allowed by VBA. The most frequently used are Integer, Long, Date, String and Variant. Use Integer for storing small numerical values and use Long for larger numerical values.
If you omit the data type, VBA applies the Variant data type to your variable. This gives flexibility but at the same time may take a hit on the memory, so choose the data types wisely basis the usage and memory. When not sure about the size of data to hold, better use Variant.
VBA Variable Declaration, Subroutines & Functions
Variable – Variables are used to store information to be referenced and manipulated in a VBA program. They also provide a way of labeling data with a descriptive name, so our programs can be understood more clearly. In VBA, a variable is declared using a dimension statement. For Example:- Dim i As Integer declares a variable i of Integer data type.
Subroutines and Functions– The main difference between a VBA Function procedure and a Subroutine procedure is that a Function procedure returns a result, whereas a Sub procedure does not.
Therefore, if you wish to perform a task that returns a result (e.g. summing of a group of numbers), you will generally use a Function procedure, but if you just need a set of actions to be carried out (e.g. formatting a set of cells), you might choose to use a Sub procedure.
Below is the detailed list of options available for declaring variables, subroutines and functions.
| Term | Description | Syntax/Example/Comments |
| Option Explicit | If Option Explicit is On (the default), the compiler requires a declaration for every variable you use.
If you turn Option Explicit Off, every undeclared variable defaults to the Object Data Type. The recommendation is to keep it On as a good programming practice and save memory. |
Option Explicit |
| Dim (Dimension) | Declaring a variable. | Dim i As Integer
Dim j As Long, Dim name As String, Dim myarray(10) As Integer |
| Redim | Redim Statement is used to re-size an array. Re- sizing an array would erase the elements in it. | ReDim myarray(5) |
| Redim Preserve | Resizing an array without erasing elements in it. | ReDim Preserve myarray(12) |
| Static Variable | Static variabale retains its value even after the macro has been executed. Its value expires only when the application is closed. | Static Total As Integer |
| Public Variable | Public Variable can be used in any of the modules within the project. It should be declared before the subroutine. | Public status As Boolean |
| Private Variable | Private variable are visible only to code inside the module. | Private status As Boolean |
| Private Sub | A VBA Private Sub can only be called from anywhere in the Module in which it resides. | Private Sub Workbook_Open() |
| Public Sub | A VBA Public Sub can be called from any of the Module.
A Public Sub in the objects, ThisWorkbook, ThisDocument, Sheet1, etc. cannot be called from anywhere in a project. |
Public Sub Exercise() |
| Static Sub | Aside from Private and Public, you may also declare a Subroutine as Static.
If you declare a Sub as Static, all the variables declared within the Sub will retain their values when the Sub is exited instead of the default behavior, which is that they become Empty and their value “out of context” Thus, their value is not preserved. |
Static Sub one() |
| Private Function | A VBA Private function can only be called from anywhere in the Module in which it resides. | Private Function FunctionHalf(ByRef y as Double) as Double
FunctionHalf = y / 2 End Function |
| Public Function | A VBA Public function can be called from any of the Modules.
A Public function in the objects, ThisWorkbook, ThisDocument, Sheet1, etc. cannot be called from anywhere in a project. |
Public Function Sum(ByRef Number1 As Double, ByRef Number2 As Double) As double ‘Return the result by writing it to a variable having the same name as the function Sum = Number1 + Number2End Function |
I hope this article helps you to understand basics of VBA and could be the foundation stone for your VBA programming journey. Stay tuned for the part-2 of this VBA basics series.
Still have questions?? Reach out to me via Contact form.
Enroll now in VBA training course here
Feel free to send your feedback ,suggestions and questions.
Follow me on Linkedin, Medium, GitHub for more stuff like this
Categories: Data Analytics, Data Science
Thanks for this introduction to VBA.