Trending September 2023 # Tips For Writing Vba In Excel # Suggested October 2023 # Top 16 Popular |

Trending September 2023 # Tips For Writing Vba In Excel # Suggested October 2023 # Top 16 Popular

You are reading the article Tips For Writing Vba In Excel updated in September 2023 on the website We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Tips For Writing Vba In Excel

Tips for Writing VBA in Excel

Learn how to write VBA code fast and efficiently

Written by

CFI Team

Published March 27, 2023

Updated July 7, 2023

Tips for Writing VBA in Excel

Listed below are nine tips for writing VBA in Excel. VBA for Excel introduces countless possibilities for custom calculations and automation. Often, code can run into the hundreds or even thousands of lines. As the complexity and size of code increases, it becomes increasingly important to write fast and efficient code that is easily understood by colleagues. Doing so will make you an invaluable asset to your team in maintaining and developing Excel VBA models.

1. Comment your code to improve readability

Have you ever opened a colleague’s VBA model and spent the next hour trying to work out exactly what their code was doing? With that in mind, imagine how professional you’d look if your code was crystal clear to anyone who read it, even if you weren’t there to explain it.

2. Name variables and functions using clear and relevant names

Writing VBA variable and function names that can be clearly interpreted will save users huge amounts of time and allow them to clearly follow the structure and flow of your code. Variable or function names such as “test1” or “first_integer” will create countless headaches for those who try to read your VBA code.

The routine below pastes the US inflation rate into the active cell if it’s available. Notice how much clearer the second example is than the first.

Bad example:

Good example:

3. Plan your work to help create simple, efficient VBA code

It’s not uncommon to get halfway through writing a code project before realizing that you should’ve structured it differently or created a separate function for specific elements of your code. Planning the structure of your VBA, what you want it to achieve, what loops you’ll need etc. will save you time in the long run. It also gives you time to think about potential errors that may arise along the way and how you plan to deal with them.

4. Use macros to quickly work out the syntax

For most people, macros offer the first glimpse into the world of VBA and coding. Even for experienced coders, macros are a great way to quickly understand the VBA syntax needed to execute a particular function or method.

Suppose you didn’t know how to save a file using VBA. By pressing Record Macro, you can carry out the steps manually, and the VBA editor will record those steps in code. Magic! Now, you can reuse and adapt the code in your own routines.

5. Learn to write your own code; don’t rely on macros!

As mentioned above, macros are incredibly helpful in learning how certain pieces of code are written. It’s important to recognize, however, that macros are inherently unreliable and not future-proof because they are hard-coded and cannot adapt to future changes in your worksheet structure.

Suppose you’ve recorded a macro that performs various formatting changes to your worksheet. Part way through the code, it selects column I, and then deletes it. Suppose then that next month, the file includes an additional column; now your macro will delete the wrong column. Learning how to code yourself will allow you to create loops and search for the correct column before correctly deleting it.

6. Use Option Explicit to avoid missing values

Using “Option Explicit” at the start of a VBA code module is a valuable way to avoid spelling errors in your code.

In the left example below, Option Explicit is not enabled. The code runs with no errors, but it always returns the answer of zero. Why? The reason is that the variable MonthIncme is spelled incorrectly, and therefore references nothing. Nothing divided by something is always zero.

When the code in the second example is run, Option Explicit detects that we’ve undefined variables in our code and returns a Compile Error. It highlights the undeclared variable and informs us of the problem. You should always use Option Explicit at the top of each code module before any of your routines.

7. Improve speed by keeping object selections to a minimum

If you’re new to VBA and are wondering why your code is running very slowly, excessive use of the .Select method is a prime suspect. Object selection should be avoided unless absolutely necessary, especially when dealing with large amounts of data or loops. Selecting objects in VBA is incredibly slow and is usually not necessary. Recorded macros often include a lot of .Select methods, so it’s important to learn how to avoid them.

In the first example below, we are trying to find the running total of 1,000 cells in our worksheet. Each time we reference a new cell, we select it, and then take its value. However, the selection of the sheet or cell is not necessary at all. We can simply get the cell value. A timer was used to record the time taken to run each routine, with the second example being 50 times quicker!

8. Re-use common functions to save time

Keeping your most common VBA routines (subs) in one place is a handy way to reduce the time you spend coding or model building. Here’s an example from our “Top 10 VBA Routines” article, which takes the selected cells, unmerges them, and applies the same headings to each cell. You can even assign a keyboard shortcut to the macro to make it lightning fast to use.

Look out for our blog on re-using code (coming soon), which will focus on add-ins and personal code modules.

9. Test your code regularly to avoid run-time errors

The tip above probably applies to most projects at work, but it is particularly relevant to coding and, therefore, VBA for Excel. Testing your code ensures that mistakes and errors are detected and that exceptions that may arise at run-time can be captured with appropriate error catching (see our article on errors in VBA here).

Testing should start with you as the code developer. You should test the code itself, and then test it within a non-live or non-impactful environment.

Once you’re satisfied with it, you should let a colleague test your Excel model, ideally without giving them any clues at all as to what buttons they should press or what inputs they should enter. Your model should speak for itself. The test will be a good indication of what will happen when your code goes live.

Additional Resources

You're reading Tips For Writing Vba In Excel

Update the detailed information about Tips For Writing Vba In Excel on the website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!