VBA (Visual Basics for Applications) is a basic but powerful programming language. It is built into many Microsoft products these days which includes Excel, Word, PowerPoint etc. It comes pre-installed in the latest versions of Microsoft Products. It is accessible using the ‘Developer’ menu in most Microsoft Products which can be activated by going to:
File -> Options -> Customize Ribbon -> Developer ->check box
(in the latest versions of Microsoft Products)
It is basically a programming language that is built into these products to simplify time-consuming or complicated tasks. VBA offers a lot of flexibility to the Microsoft products. It opens a lot of possibilities and is a powerful tool to use.
VBA for Excel
In Excel, VBA can be used to write functions or applications and run complicated processes. It can also be used to make sophisticated workbooks in Excel. Excel VBA has pre-defined parts called ‘features’ which are specifically included to make programming easy in Excel. It helps to integrate user-defined functions into a spreadsheet or workbook. It reduces the complexity of calculations which otherwise are too tedious. It has a series of features that help to simplify complicated operations in Excel.
‘Macro Recorder’ feature
An interesting feature is the ‘macro recorder‘. It creates a code based on recorded actions. Once you start recording using the macro recorder, it records all of your actions till you stop it. Once the recording is done, it creates a code by itself based on the actions that it has recorded. Also, you can modify the code that Excel has created for you using the macro. So, you never need to write a code from scratch. You can get a basic code created by ‘macro recorder’ and modify it according to your needs. To edit the code recorded by the macro, go to:
Developer -> Visual Basic -> Modules -> Project -> Module1 -> Recorded Macro
It will open your recorded macro which you can view and edit according to your requirements.
Another tool of Excel VBA is the ‘Button’. In a nutshell, it is a button to use the macro code in your spreadsheet. It links your macro code and creates a button in the ‘Insert’ menu that will run the code recorded by the macro recorder. The button can be created by going to:
Developer -> Controls -> Insert -> Button
When we click on the ‘Button’ menu, you will be asked to assign a macro code to the button. Select the macro code that you created. After selecting the macro code, a button will be created that will run the assigned macro code when you click on the button. It creates a quick shortcut to run a set of instructions and applies the code in Excel. These two are the most basic features. Many more features can be explored.
Basics of Coding in VBA
Coding in VBA is very simple and convenient. Very basic programming skills are required to write codes in VBA. A simple understanding of how a code is working is enough to be able to write a working code. Some important terms related to coding and Excel VBA are discussed below. This will give an insight into how to write a code in Excel VBA and will also help in the general understanding of how coding in Excel VBA works.
Variables in Excel VBA
Variables are a fundamental component of Excel VBA. A ‘variable’ is a named storage location that has one particular value. The values of variables can change. They help in transporting data through a micro. There are various data types that can be stored in a variable in Excel VBA. Each data type has its own memory size. Some of the data types are:
- Integer: small whole numbers (between -32,768 to 32,767)
- String: text (all words come under text)
- Boolean: value is either ‘True’ or ‘False’
- Single: non-rounded numbers (forty-digit precision)
Variable is usually declared using the ‘Dim’ statement. An example of declaring a variable is:
Dim Age As Integer
Condition and Repeat Statements
Excel VBA supports the use of basic statements like if, then, else, for, next, etc. These condition and repeat statements are very useful in writing codes in Excel VBA. These are the core of any program.
The ‘if… then… else…’ statements are conditional statements that execute an action based on a given condition. If the condition is satisfied, a specified action takes place and if the condition is not satisfied, another specified action gets executed. These conditions can be nested to create desired codes which run according to the conditions required.
The ‘for… next…’ statement is a repeat statement that executes a defined set of code repeatedly many times till a specified condition is achieved.
The ‘while…’ statement runs a set of code while a condition is being satisfied till the condition is no longer satisfied. These statements are a powerful tool which expands the functioning of Excel.
An array is a data structure that can store many variables, given a dimension. It can be thought of as a variable that can store multiple values. An array always has variables of one data type. Array with any dimensions can be declared in the VBA.
Arrays can be Static or Dynamic. Static arrays have a fixed size throughout a VBA code. Whereas, a dynamic array can change its size in a code. An array is declared like a variable except the size is specified in brackets. For example:
Dim Age(4) As Integer (here the size is 4)
Operators are used for building expressions. These operators are generally used to perform mathematical or logical manipulations. In VBA the operators that are built in are mathematical operators, string operators, comparison operators and string operators.
Mathematical operators include operators like addition, division, power, modulus etc. String operators include operators like concatenate. Comparison operators include operators like ‘less than’, ‘equal to’, ‘not equal to’, etc. Logical operators include operators like ‘and’, ‘or’, ‘not’ etc.
A ‘function’ basically takes an input value, performs predefined operations, and returns a value as a result of the operations. Once a function is typed in the visual basic editor, it will be available to be called from other VBA procedures or to be used in Excel worksheets.
To declare a function, four things are needed. They are the name of the function, input variable(s), data types of input variable(s) and data type of the result of the function. For example:
Function OddEven (Input_no As Integer) As String
Errors in VBA
While executing an excel macro, there is a possibility that you will encounter an error. There are three types of errors in Excel VBA – compile errors, runtime errors, and logical errors (bugs).
Compile errors are highlighted before the code runs. These are errors that are not according to the rules of the compiler. They are detected before the code is executed. Common examples are syntax error, variable not defined, etc.
Runtime errors occur during the execution of the code. The execution of the code immediately terminates if a runtime error occurs.
Logical errors are the errors that give the incorrect result or unexpected outcome after the code is run. These errors are very hard to find as the code is executed normally.
Excel VBA is a simple and versatile programming language capable of producing useful additions to Excel’s functional capabilities. It opens a lot of possibilities to compute complicated functions, integrate tedious actions into simple codes and run them over a series of data.
Apart from the above-mentioned common uses, there are many more features of Excel VBA. You can access various Excel Objects and use them in your VBA code or perform actions on them. You can also link code to a specific Excel Event. Such integration opens avenues to possibilities of things that can be done using Excel. It allows to put in user-modified codes into Excel. It has become an integral part of Excel and provides extensive depth in terms of doing tasks such as data analysis, running simulations, create useful functions, statistical analysis, and many more.