Tips for Writing VBA in Excel
It helps in executing simple and complex tasks in Excel automatically.
VBA, which stands for Visual Basics For Application, is a human-readable and editable programming language developed by Microsoft for Office Applications such as MS Word, MS Excel, etc.
A programming language is a means of communication between humans and computers. It helps users to give a set of instructions or commands for the computer to execute. VBA fulfills this purpose by allowing users to write programs consisting of instructions.
A VBA program helps in executing simple and complex tasks in Excel automatically. Hence using VBA is a very efficient and time-saving method to perform operations on data, especially for businesses and working professionals.
Moreover, VBA also facilitates the creation of user-defined functions to perform various tasks and automate computer processes. This feature enables the users to access functions beyond those available by default in Excel and other Microsoft Office applications.
Hence VBA is an essential tool in the industry for analyzing large amounts of data and creating and maintaining complex financial models. For example, suppose we have a financial model for Nike Inc. It is possible to auto-populate most numbers on the template by clicking a button using VBA.
Hence VBA makes our life easy in many ways while working in Excel, making it an essential tool for financial analysis. It also helps us to access functions beyond the in-built functions in Microsoft Office, allowing users to customize their tasks in Excel by creating their user-defined functions.
Key Takeaways
- Visual Basics For Application (VBA) is a human-readable and editable programming language developed by Microsoft for Office Applications.
- VBA enables the automation of simple and complex tasks in Excel and allows the creation of user-defined functions.
- Most people who work with VBA use macros. A macro is a series of statements that are executable infinitely many times.
- We use the keyword Dim to declare variables in VBA.
- Commenting throughout the program enhances clarity and understanding for others.
- Variables must be given meaningful and relevant names. Doing this makes it easier for us to debug the code. It also makes the code easily understandable to others.
- Do not rely on macros completely to write our code because macros cannot reflect any future changes made in the worksheet structure as they are hard-coded in VBA. Hence macros are inherently unreliable.
- It is important to develop a solid understanding of VBA concepts to write your code.
- Testing your code helps in avoiding run-time errors.
Introduction To VBA
Most people who work with VBA use macros. A macro is a sequence of executable statements the user can repeatedly execute. In other words, a macro is a set of instructions given to the computer that the user can use repeatedly.
The Developer tab > Visual Basic link must be selected to access the VBA interface on the Excel worksheet, as demonstrated below:
If the Developer tab is not already present on your Excel worksheet's Quick Access Toolbar, you can quickly add it. Here's how you can do that:
- Click on the drop-down arrow at the top of the Excel worksheet. Select the More Commands option from the drop-down list, which opens a dialog box.
- In the dialog box, select the Customize Ribbon option.
- Search for the Developer tab in the Popular Commands list on the left.
- Select the Developer tab and click on the Add >> button.
- Click Ok to add the Developer tab to the Quick Access Toolbar.
Doing this adds the Developer tab on the Quick Access Toolbar. Now going to Developer > Visual Basic opens the VBA interface. To start writing your VBA code, go to Insert > Module, inserting an empty module in which we can write our VBA code.
The fundamental building blocks of a VBA are variables and data types. Generally, the variables are declared immediately after naming the macro in VBA. Use the keyword Sub followed by the macro name to start a macro.
For example, writing Sub Sample creates a macro named Sample, in which we can write and execute our program to produce desired results.
The keyword Dim is used to declare variables in VBA. For example, we write Dim Num As Integer to declare a variable named Num of Integer data type. Hence the syntax to declare variables in VBA is as follows:
Dim [Variable_Name] As [DataType]
In programming terms, variables are entities whose values can be changed. Declaring variables is a crucial step in programming as it informs the compiler how much memory or storage to allocate for a program.
The most commonly used data types are integer, single, double, date, and string.
Let us take an example in VBA to understand things better:
In this program, we have declared an integer variable Num and assigned it a value of 567. We then display the value of Num in the message box. The message box is a kind of dialog box in VBA Excel through which you can inform the users of your program or give some message.
To run your code in VBA, click Run > Run Sub/UserForm as shown below:
On running the above code, we would get the following output:
This was a simple program written in VBA. However, using these concepts, we can write many complex VBA programs.
General Tips For Writing Efficient Programs
With the increasing size and complexity of our code, it becomes essential to write programs fast, maintaining the efficiency and accuracy of the code. In addition, our code should be in a simple form so that it is understandable to our clients or someone viewing the program.
Writing efficient and easy-to-understand programs also helps you in debugging and finding errors. Some tips that must be considered while writing programs in any programming language, including VBA, are as follows:
1. Comment on your code snippets.
Suppose you have written a 500-line code. What if you or your client want to revisit the code after a month? By then, you would not remember the logic of the code.
To understand what you have done in the program, you would again have to revisit the entire program and think about the logic you used once again. This very tiring and time-consuming process makes our entire programming journey troublesome.
We can avoid this problem by commenting on the code. Adding comments to your code helps to improve the readability of the program. It makes the code look neat and presentable; anybody seeing the program can easily understand its essence.
Writing comments can accelerate our learning process as we can easily understand our code even if we look at it after a few months. For example, whenever declaring variables, creating new functions, or starting a loop, we can add comments stating the need for the same.
To comment out any line in VBA, place an apostrophe sign(‘) before your text and press Enter. Doing this turns the text green, indicating that it is a comment. The compiler does not execute comments. They are just for the convenience of the users.
2. Use clear and relevant names for variables and functions.
Variables and functions are the fundamental building blocks of any program. They are essential in writing complex codes. Naming variables and functions is essential in programming to uniquely identify different variables and functions.
Giving any random name to the variables would make it difficult for the programmer to recollect the program and for someone viewing the code to understand the logic.
Giving meaningful names to the declared variables and functions enhances code clarity and understanding. It also makes it easy for someone viewing our program to understand the logic and ease debugging the code, saving time and effort.
Suppose we need to enter the length and breadth of a rectangle, and if the length or breadth is negative, display “Not Valid” in the message box. On the other hand, if both length and breadth are positive, we show the area of the rectangle in the message box as the product of length and breadth.
This is a bad example of writing the code, as we gave random variables random names. Any person seeing our code would not understand what the program intends to do. But now let us write the program meaningfully:
The above program is more meaningful and easily understandable to others.
3. Plan your logic before writing the code.
A tip that all good programmers follow is to write the logic of your code on a notebook before actually typing it on the computer. Doing this helps in saving a lot of time as we can finalize the logic of our code beforehand.
All beginners face this common difficulty as they write half of their code and realize they should have proceeded differently.
This planning of the code is essential before we start typing, especially for beginners. It also helps to reflect on the errors that may arise and how to deal with them. Doing this enhances our already learned concepts.
4. Re-using the regularly used functions.
Consider creating reusable functions if you have code snippets that perform similar tasks. Reusing code saves time and effort; you can simply call the function whenever needed. Maintain a library of commonly used functions to maximize efficiency.
5. Test your code at regular intervals.
This tip applies to almost all individuals working on a project. But it is essential for people new to programming because beginners tend to make mistakes while writing their first few programs. So testing your code can help you identify the errors and exceptions that may arise at run-time.
Doing this allows us to correct all the errors before presenting the work to the clients. Hence, testing is important in writing error-free programs in any programming language, including VBA.
These are a few tips you must remember while working in any programming language. These tips are common in all programming languages. Now let’s discuss some VBA-specific tips you must remember while using VBA.
Tips For Writing Efficient VBA Programs in Excel
The tips mentioned below are specifically for the VBA programming language:
1. Use macros to speed up the coding process
Most people working in VBA use macros as it makes things simple and easy to understand. It helps the users quickly understand the VBA syntax for executing any function or method. They are particularly useful for automating repetitive tasks.
If we don’t know how to save a file using VBA, we can do so by pressing the Record Macro option in the Developer tab.
By doing this, the VBA editor automatically records the steps in the code. We can now reuse it multiple times by clicking on Ok.
2. Don’t over-rely on macros! You should know how to write your code
As discussed above, using macros makes our job easy while working in VBA. In addition, it is helpful in writing small pieces of code. However, macros also have some disadvantages.
The macros are hard-coded in VBA and cannot reflect future changes in the worksheet structure, making macros inherently unreliable. Developing your coding skills allows you to create more flexible and adaptable programs using loops, functions, and other VBA features.
3. Using the Option Explicit Keyword
We can use the Option Explicit at the start of our VBA code. This is a very effective way to avoid errors caused by spelling mistakes in VBA.
Suppose we need to take the length and breadth of a rectangle as user inputs and display the area of the rectangle in the message box.
In the program below, Option Explicit is not present. As you can see, we have declared the variables as length and breadth, but while calculating the area, we have mistakenly written the incorrect spelling of length as shown:
If we run the above program, it will not show any errors but will always display the area to be 0 for any input value. This is because we have incorrectly spelled the variable length as lngth, which is not declared. So this variable points to nothing.
On clicking Ok, we can see that the area of the rectangle is displayed as 0.
To avoid this, we can use the Option Explicit at the start of our code before writing the macro. This option detects the presence of undeclared variables in the code and returns a compilation error.
Hence we should always use Option Explicit at the start of each code before writing the macro or any function.
4. Minimizing the Object Selections
Another disadvantage of using recorded macros is that they use a lot of. Select methods which can make the execution of your code slow.
Object Selection is a slow process in VBA and is often not necessary. Object Selection drastically reduces the speed of execution of our program, hence making our program inefficient and time-consuming.
We should avoid it, especially when dealing with large amounts of data. Doing this can accelerate the execution process of our code.
Suppose we want to add the cell values of 4,000 cells in column A of our worksheet. First, we run a loop from 1 to 4000 every time we reference a new cell. Next, we select each cell and then extract its value. We then add the cell values successively and store the total in a variable called sum.
We have also created a timer to record the execution time of the program.
The above program would take time to execute as we select each cell and then take its value. However, this selection is unnecessary as we can get the cell value directly.
On running the first program, we get the execution time to be:
Similarly, on running the second program, we get the execution time to be:
This shows that the execution time for the second code is much less than the first code because, in the first code, we have used the. Select method for object selection. Hence we should avoid object selection in VBA as much as possible.
or Want to Sign up with your social account?