20 Practical Coding Tips to Master Excel VBA

Excel is an intricate and sophisticated software that allows users to do incredible tasks without any bearable hassle. Becoming an expert can seem to be a daunting task for many, but not when you know some tricks and tips that can make your journey a lot smoother. If you are interested to learn how to be an expert in the field of excel, then this article can be handy for you. Here, we presented 24 separate VBA coding tips in Excel using which you can make your journey of learning Excel much easier.

When you write your VBA code, remember to write it in such a way that any other user can easily read and understand your code. Even after some months, it might be impossible for you to understand your own code if you don’t use indenting and blank lines to make the code more readable. It is an excellent practice for programmers.

When you press Enter after finishing a line of your VBA code, Excel starts evaluating that line for syntax errors. If no syntax error is found, the line of code will be reformatted, and keywords and identifiers will be in different colors. This automatic reformatting adds consistent spaces (before and after an equal sign, for example) and removes extra spaces that aren’t needed. If a syntax error is found, you receive a pop-up message, and the line is displayed in a different color (red, by default). You need to correct your error before you can execute the macro.


20 Useful Tips That Will Help You to Master Excel VBA

There are numerous tips available. Among them, we presented topmost 24 of them here. Try to use the Excel 365 version to avoid any version or compatibility issues.


1. Add Comment to Increase Readability

Did you ever open up a VBA model created by a coworker and invested the very next hour attempting to decipher what the heck the code was doing? Think about how much more credible you’d appear as a professional if your code was easy to understand for anyone, even if you weren’t around to provide context.

To assure that you’ll be able to comprehend your own work a few months from now, the most crucial aspect of writing VBA code is to write comments. Colleagues will be impressed, and you’ll earn a name for yourself as someone who produces first-rate documentation that helps future users get up to speed quickly.

Below we provided a VBA code example where we will try to understand whether the variable is zero or not. The process incorporates quite a few VBA codes, and the steps can be sometimes hard to understand down the time. So it’s essential to note down the essential information in Excel as comments in the code itself.

To add a comment, you need to insert before and after the code text, as shown in the image below. This line summarises the important info about the code without disrupting the code itself.

Add Comment VBA tips to Increase Readability

Using these tips will certainly increase your VBA coding skill in Excel.


2. Name Variable Should Be Easily Understandable

Clear function and variable names in VBA save users a lot of time and make it easy to understand the code’s structure and logic. Naming your variables and functions things like “test1” and “first integer” will make it very difficult for anyone to understand your VBA code.

Here, we got two separate examples of VBA code to demonstrate, where one example is bad, and another example can be followed.

The Bad Example

Here, the code below is counting the wage per day basis. The variables are named the x and y. It is very difficult to understand what a variable for what is. The x variable here will extract the total wage in a month. And they will store the wage per month after the completion of calculations. But their operation is hard to notice from their name.

The Correct Approach

In the below example, we got the variable name as their operation name. Now the variables are quite understandable and neat. Please keep in mind to not name your variable after any built-in. function name.

Using these tips will certainly increase your VBA coding skill in Excel.


3. Learn to Use Formatting Using VBA

You can do formatting large array of cell formatting by running a simple VBA script in Excel.

Sometimes you have a certain set of preferable formatting, but each time you open a new workbook, the formatting went away. So you need to add the same formatting each time you open the workbook. For this, VBA macros should be ready.

For example, you want to format the range of cells A1:A10 in different formats.

Then you should use the below code:

Range(“A1:A10”).NumberFormat = “General”

To make the cell values in number format, use the code below:

Range(“A1:A10”).NumberFormat = “0.00”

To make the cell values in Currency format, use the code below:

Range(“A1:A10”).NumberFormat = “$#,##0.00”

To change the alignment, enter the following code:

Text Alignment

Horizontal

You can use the following properties: xlGeneral, xlCenter, xlDistributed, xlJustify, xlLeft, xlRight.

Range(“A1:A10”).HorizontalAlignment = xlCenter

Vertical

For the vertical alignment use the below alignment: xlBottom, xlCenter, xlDistributed, xlJustify, xlTop.

Range(“A1:A10”).VerticalAlignment = xlBottom

Wrap Text

Range(“A1:A10”).WrapText = True

Shrink To Fit

To shrink the cell content to the column width, you can use the following code:

Rows(2).ShrinkToFit = True

Merge Cells

To merge cells in the worksheet, use the following code:

Range(“A1:A10”).MergeCells = True

Change Orientation

To change the orientation of the text, you can use the contents,

xlDownward, xlVertical,xlUpward,xlHorizontal,.

Range(“A1:A10”).Orientation = xlHorizontal

The formatting will contain making the cell height 20,


4. Try to Master Basic Syntax

Mastering Basic syntax can heavily increase the speed at which you generally write your code.

Most people’s initial introduction to VBA and coding comes through macros. Even for seasoned programmers, using macros is an excellent approach to quickly grasp the VBA syntax required to carry out a specific function or method.


5. Use Immediate Window

The Immediate window in Excel VBE is a very useful tool. Imagine you’re playing in a sandbox. Quick and easy results can be obtained from virtually any VBA statements you provide here. Try this: launch the Visual Basic Editor (by pressing Alt+F11 in Excel) and switch to the Immediate window.

And you can run code inside the immediate box instantly. Which makes debugging the code extremely helpful. And it also helps to test the variable values quite efficiently.

In the below image, we got the immediate window.

Use immediate window VBA tips

Using these tips will certainly increase your VBA coding skill in Excel.


6. Use Inputboxes to Make It More User Friendly

Using the cell references inside the code is troublesome and makes the code rigid. This code is will be vulnerable to any future modifications and different inputs as shown in the image below.

That’s why it is very important to use input boxes to input values in the worksheet.

The syntax for adding input boxes is

myValue = InputBox(“Give me some input”), as shown in the image below.

Here the value is the variable, and the InputBox is the one that triggers the input box. Whatever the value entered in the input box, will be saved inside the myValue variable.

Using these tips will certainly increase your VBA coding skill in Excel.


7. Learn to Create Functions

Use VBA code to create a function that can help to form a function. This user-defined function is there to carry out different tasks. When the built-in Excel functions are insufficient, these are useful. In these circumstances, you can design your own unique User Defined Function to meet your particular requirements. The user-defined function will work in the same manner compared to the other functions.

Here is an example of a function.

Function Area(Length, Width)
Area = Length * Width
MsgBox "The Area of your Rectangle is: " & Area
End Function

By using the above function we will calculate the area of a rectangle in Excel

To do this, after inserting the code in the VBA module,  you have to use the following formula in cell D5:

=Area(100,40)

The area of a rectangle can then be calculated by pressing Enter after that. This function displays the area of the rectangle in a message box after the user provides it with two arguments (Length and Width).

Using these tips will certainly increase your VBA coding skill in Excel.


8. Plan Early

It’s not unusual to realize you should have organized your code differently or made separate functions for certain sections of it after you’ve written half of a project. Long-term time savings come from planning your VBA’s structure, the goals you desire it to accomplish, the loops you’ll need, etc. Additionally, it allows you time to consider potential mistakes that can occur and how you intend to handle them.


9. Try Recording Macros

The built-in macro recorder in Excel is a fantastic tool for learning new objects and how to interact with them. I frequently use it to record specific portions of my coding and afterward alter the result. Just be aware that the macro recorder doesn’t always output the best or most comprehensive code. However, it provides you with a pretty solid notion of how to construct code for a series of actions.


10. Avoid Overreliance on Macros

The ability to learn how certain bits of code are generated is greatly aided by macros. It’s crucial to understand, however, that because macros are hard-coded and unable to react to possible changes in your sheet structure, they are fundamentally unreliable and not future-proof.


11. Utilize Debug.Print

Debug. The 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 can come in handy.

In the below image, we got the code where we go to the Debug.Print right after the total_wage variable.

Where we got the values of total_wage variables running value in the immediate box. right after the code ran, the value is in the total_wage.

Using these tips will certainly increase your VBA coding skill in Excel.


12. Break Your Work into Smaller Parts

No matter how complicated your work situation or issue seems, it is likely to be comprised of a collection of related issues. So, divide the task into manageable pieces. Modularization is the term for this method of writing code.

Reuse: The ability to repurpose code is one of the main benefits of large modularizing programs.

Testable: The testing and debugging of code are simplified when it is broken down into smaller chunks.

Maintainable smaller components are less of a hassle to keep up. Once you have a newer version of Excel, you can update them without much trouble.


13. Adopt Iterative Build Strategy

Consider the tiniest (but most beneficial) feature that you can have whenever you are trying to automate the job of an entire department. Implement it, then incrementally add new functionality. In this manner, your turnaround time is accelerated, you appear presentable in the eyes of your supervisor, and you have a sense of control. The iterative procedure lets you have the opportunity to stop whenever you can, meaning the control is in your hands.


14. Use Option Explicit to Avoid Errors

Spelling mistakes can be prevented in your code by starting a VBA source base with “Option Explicit.”

Option Explicit returns a Compile Error when it finds unspecified variables in our code. It alerts us to the issue and draws attention to the undeclared variable. Prior to any of your functions, utilize Option Explicit somewhere at beginning of each code module, to be on the safe side.

Use Option Explicit to Avoid Errors as VBA coding tips in Excel


15. Learn to Handle Errors

In Excel VBA programming, there are three different types of error.

Syntax Error

The most common ones, also known as parsing errors, happen while VBScript is being interpreted.

Function Error_Manage()
dim x,y
x = "Exceldemy"
y = Ucase(x
End Function

Runtime Error

Runtime errors, often known as exceptions, happen after interpretation and during execution.

Sub add_comment(x As Single)
'This Code will Evauateifthe variable
If IsEmpty(x) Then
MsBox "Variable is Null"
Else
ActiveCell.Value = x
End If
End Sub

The above code is a faulty one. Because it is trying to call Msbox function but this function is nonexistent.

Logical Error

The most challenging form of faults to find are logical ones. These mistakes are not due to runtime or syntactic faults. Instead, they happen whenever you make a logical error in your script and do not get the desired outcome.

Because the kind of logic you wish to include in your application relies on your business needs, you cannot notice those problems.

Using these tips will certainly increase your VBA coding skill in Excel.


16. Object Selection Must Be Kept Minimum

If you’re just starting out with VBA and have wondered why your script is so sluggish, an overabundance of may be to blame. The selection procedure is a likely culprit. When working with vast volumes of data or loops, object selection is avoided until absolutely essential. Using the select method to work with elements in VBA is tedious and rarely useful. An abundance of is typically found in recorded macros. Therefore, it is crucial that you acquire the skills to recognize and sidestep these techniques.

In the code below, we can see that the code has to select a specific cell in the worksheet.

Below code is a bad example of it, here it selects the sheet, then range, then values. after this, it will save the values in the variable. which takes almost 4 separate lines to complete.

Object Selection Must Be Kept Minimum VBA coding tips in Excel

Here, we got the whole 4 lines worth of code in a single line.

Which makes the code neat and faster.

Using these tips will certainly increase your VBA coding skill in Excel.


17. Avoid Using Common Shortcuts in VBA code

Because Excel doesn’t alert you if you select any shortcut key that Excel is currently using, employing keyboard shortcuts can be risky. When this occurs, Excel substitutes the macro’s keyboard shortcut for the built-in keyboard shortcut. Imagine how shocked your boss will be after loading your macro and pressing Ctrl-C to add a number to half of the cells in his worksheet. One of the most common keyboard shortcuts are Ctrl+C, Ctrl+V, Ctrl+Z, Ctrl+X, etc

Because Ctrl+Shift+letter macro button combinations are far less common than Ctrl+letter shortcut key combinations, you should always utilize them to avoid issues. Additionally, if you build a new, untried macro, avoid assigning a shortcut key if you are unsure.


18. Take Advantage of Macro Sharing Across Workbook

We all had the impression that macros can only run in the workbook that is open. But this is not the case, you 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 macro saved in the first workbook. This way you can share the macro file across the workbook.


19. Make Sure Your Intended Workbook is Open

Excel may not always execute macros on the workbook that includes the macro code, despite what you would believe. The VBA macro inside the open workbook is run by Excel. That is the worksheet that you have recently looked at the most. If you have two active workbooks and utilize the Windows taskbar to go to the second one before returning to that same Visual Basic editor, Spreadsheet will execute the macro in the second workbook.


20. Test Code Regularly

The preceding advice is useful for a wide variety of workplace endeavors; however, it is especially pertinent to programming and, hence, VBA for Excel. Errors and omissions in your code will be found during testing, and any runtime exceptions will be caught by your error handling.

You, the code developer, should initiate the testing process first. The code itself should be tested, as well as tested in a simulated or controlled environment.


Conclusion

To sum it up, how you can different VBA coding tips and tricks using which can make your Excel learning experience much better.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable


Related Articles

Tags:

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo