In this article, we present 20 separate coding tips to help you master Excel VBA.
In general, when writing VBA code, it is very good practice to use indenting and blank lines to make the code more readable.
When you press Enter after finishing a line of your VBA code, Excel starts evaluating that line for syntax errors. If no syntax errors are found, the line of code will be reformatted, with keywords and identifiers appearing in different colors. Consistent spaces (before and after an equal sign, for example) will be added, and extra spaces that aren’t needed removed. If a syntax error is found, a pop-up message will be shown, and the line will be displayed in a different color (red, by default). The errors will need to be corrected in order to run the macro.
Tip 1 – Add Comments to Increase Readability
Have you ever opened up VBA code created by a coworker and spent the next hour attempting to decipher what the code is trying to do?
To ensure that you (or anyone else) will be able to comprehend your work in the future, the most crucial aspect of writing VBA code is to write comments. Comments should concisely explain what the portion of code does, and provide other useful information as required.
To add a comment, simply insert a new line of code with ‘ before and after the comment text.
Below is an example of VBA code including a comment.
Tip 2 – Use Easily Understandable Variable Names
Clear function and variable names in VBA simplify understanding the code’s structure and logic.
To illustrate, here are two examples of VBA code; one bad and one good.
The Bad Example
The code below calculates the daily wage from a monthly wage. It declares two variables named x and y, which makes it very difficult to understand what purpose these variables serve at a glance. The x variable extracts the total wage in a month and y will store the daily wage after the calculations, but you’d never guess from their names.
The Correct Approach
In the below example, the variable names explain exactly what purpose they serve, and the structure and intention of the code make sense at a glance. However to avoid unintended results, avoid naming your variables with built-in function names.
Tip 3 – Use VBA for Formatting
Large datasets can be formatted easily by running a simple VBA script in Excel. This is particularly useful if you have a preferred formatting style that you apply to all workbooks, but each time you open a new workbook, it needs to be set up from scratch.
For example, suppose we want to format the range of cells A1:A10 in different formats.
To format the cell values in the default Excel format, use the code below:
Range(“A1:A10”).NumberFormat = “General”
To format the cell values in Number format:
Range(“A1:A10”).NumberFormat = “0.00”
To format the cell values in Currency format:
Range(“A1:A10”).NumberFormat = “$#,##0.00”
Text Alignment
Horizontal
To change the horizontal alignment, we can use the following properties: xlGeneral, xlCenter, xlDistributed, xlJustify, xlLeft, and xlRight. For example:
Range(“A1:A10”).HorizontalAlignment = xlCenter
Vertical
For the vertical alignment we can use xlBottom, xlCenter, xlDistributed, xlJustify, and xlTop, for example:
Range(“A1:A10”).VerticalAlignment = xlBottom
Wrap Text
To wrap the text in the range:
Range(“A1:A10”).WrapText = True
Shrink To Fit
To shrink the cell contents to the column width:
Rows(2).ShrinkToFit = True
Merge Cells
To merge cells in the worksheet:
Range(“A1:A10”).MergeCells = True
Change Orientation
To change the orientation of the text, we can use the properties xlDownward, xlVertical, xlUpward, and xlHorizontal. For example:
Range(“A1:A10”).Orientation = xlHorizontal
Tip 4 – Master Basic Syntax
Mastering basic VBA syntax will greatly increase the speed at which you are able to write VBA code.
Tip 5 – Use the Immediate Window
The Immediate window in the VBA Editor serves a similar purpose to a sandbox. Quick results can be obtained from virtually any VBA statements provided here.
To access it, launch the Visual Basic Editor (by pressing Alt+F11 in Excel) and switch to the Immediate window.
We can run code inside the immediate box instantly, very useful in debugging code and testing variable values quickly and easily.
The below image shows the location of the Immediate window.
Tip 6 – Use Input Boxes Instead of Hard-Coded Values
Using cell references inside code, as shown in the image below, makes the code rigid, vulnerable to future modifications and cumbersome to change inputs.
It’s often more efficient to use input boxes to input values.
The syntax for adding input boxes is:
myValue = InputBox(“Give me some input”).
Here is an example:
Here, instead of hard-coding the value, the value entered into the InputBox is stored in the myValue variable.
Tip 7 – Create User Defined Functions (UDFs)
When the built-in Excel functions are insufficient for the task at hand, we can use VBA code to create bespoke user-defined functions for use either in our code or in worksheets. User-defined functions work in the same manner as other functions.
Here is an example of a UDF that calculates the area of a rectangle:
Function Area(Length, Width)
Area = Length * Width
MsgBox "The Area of your Rectangle is: " & Area
End Function
After inserting the code in the VBA module, enter the following formula in cell D5:
=Area(100,40)
Press ENTER and the area of the rectangle will be calculated from the given inputs and displayed in a message box.
Tip 8 – Plan the Code Structure in Advance
Planning for later modifications in your code is much more efficient than trying to deal with them from scratch later on. It’s much harder to adapt code seamlessly half-way through the process. Long-term time savings will result from planning ahead what structure, goals, loops etc will be required in the future.
Tip 9 – Learn by Recording Macros
The built-in macro recorder in Excel is a useful tool for learning new objects and how to interact with them. A handy approach is to record specific portions of your code and alter the results later. The macro recorder doesn’t always output the best or most comprehensive code, however it provides a solid foundation for how to construct code for a series of actions.
Tip 10 – Avoid Overreliance on Macros
Macros are a great way to learn, however because they are hard-coded and therefore unable to react to possible changes in your sheet structure, they are fundamentally unreliable and not future-proof.
11 – Use Debug.Print
The Debug.Print command instructs VBA to display the specified data inside the Immediate Window. When you need to know the variable value on a specific line of code, but don’t want to save the variable to the workbook or display it in a message box, this feature is very useful.
In the below image, the code performs a Debug.Print of the total_wage variable right after this variable is calculated.
12 – Modularize (Break Your Work into Smaller Parts)
No matter how complicated your task seems, it is likely to be comprised of a collection of related parts. So, divide the task into manageable pieces, a process known as Modularization.
This has 2 main benefits:
- Reusable: The ability to repurpose code is one of the main benefits of modularizing large programs.
- Testable: The process of testing and debugging of code is simplified when it is broken down into smaller chunks.
Smaller code components are simpler to maintain and update, including when updating to newer versions of Excel.
Tip 13 – Adopt an Iterative Build Strategy
Consider the tiniest (but most beneficial) feature that you can add. Implement it, then incrementally add new functionality bit-by-bit. By using this Iterative Build Strategy, turnaround time is accelerated, deliverables are more readily produced, and a sense of control is established.
14 – Use Option Explicit to Prevent Errors
Spelling mistakes in your code can be prevented by starting a VBA source base with “Option Explicit“.
Option Explicit returns a Compile Error when it finds unspecified variables in the code. It alerts us to the issue and draws attention to the undeclared variable. At or near the beginning of your functions and code modules, utilize Option Explicit to be on the safe side.
Tip 15 – Learn to Handle Errors
In Excel VBA programming, there are three different types of error:
Syntax Error
The most common errors, also known as parsing errors, happen while VBScript is being interpreted.
For example, in the code snippet below, there is a syntax error in the Y variable definition.
Function Error_Manage()
dim x,y
x = "Exceldemy"
y = Ucase(x
End FunctionRuntime Error
Runtime errors, also known as exceptions, happen after interpretation and during execution.
Sub add_comment(x As Single)
'This Code will Evaluate if the variable is null
If IsEmpty(x) Then
MsBox "Variable is Null"
Else
ActiveCell.Value = x
End If
End SubThe above code will generate a runtime error because it is trying to call Msbox function, but this function has not been defined.
Logical Error
The most challenging form of errors to identify are logical errors. These mistakes are not due to runtime or syntactic faults, they as result of a logical error in your script, and cause incorrect outcomes rather than error messages.
Tip 16 – Keep Object Selection to a Minimum
If you’re just starting out with VBA and have wondered why your script is so sluggish, an overabundance of selected objects may be to blame, with the selection procedure a likely culprit. When working with vast volumes of data or loops, avoid object selection unless or until absolutely essential. Using the Select method to work with elements in VBA is tedious and rarely useful. Therefore, to master VBA coding, it is crucial that you acquire the skills to recognize and sidestep these techniques.
In the inefficient code below, a specific cell in the worksheet must be selected.
The code selects the sheet, then the range, then the values, then it saves the values in a variable. This process takes almost 4 separate lines to complete.
Instead, we can combine the whole 4 lines worth of code into a single line, making the code neater and faster.
Tip 17 – Avoid Using Common Shortcuts in VBA code
Because Excel doesn’t alert you if you define a shortcut key that is already in use, using keyboard shortcuts in VBA code can be risky. When applying a keyboard shortcut already in use by Excel, the macro’s keyboard shortcut will replace the built-in keyboard shortcut, with unpredictable results
As an extreme example, imagine how shocked your boss would be after loading your macro that uses the Ctrl+C shortcut to delete the current worksheet, then trying to simply copy some values!
The most common keyboard shortcuts are Ctrl+C, Ctrl+V, Ctrl+Z, Ctrl+X, etc. Since they are much less commonly used for built-in Excel functions, use Ctrl+Shift+letter macro button combinations instead of Ctrl+letter combinations, to minimize the potential for issues. If you build a new, untried macro, avoid assigning a shortcut key at all if you are unsure.
Tip 18 – Take Advantage of Macro Sharing Across Workbooks
Contrary to popular belief, macros can work across multiple workbooks at the same time. If you have macros saved in one of your workbooks and open another, then you can use the macros saved in the first workbook in the second. This is a simple way to share macros across workbooks.
Tip 19 – Ensure the Destination Workbook is the Active One
Excel might not only execute macros in the workbook containing the macro code. If you have two active workbooks and run a macro in the first workbook, then utilize the Windows taskbar to go to the second workbook before returning to the Visual Basic editor from which the macro was run in the first workbook, then the macro will also execute in the second workbook, with unpredictable or even disastrous results.
Tip 20 – Test Code Regularly
This advice is pertinent to a wide variety of workplace endeavors, however it is especially pertinent to programming, and hence VBA for Excel. Testing will identify errors and omissions in your code, and any runtime exceptions will be caught by your error handling. Test often, and comprehensively. It’ll save time and headaches down the line.
Read More: How Different Is VBA from Other Programming Languages
