When building spreadsheets, functions, macros or any other type of complex excel file, it is easy to focus on simply getting things to work and that is what most of us end up doing. What we often forget is thinking with a longer term perspective. It’s often easy to forget that over time, your needs will change and it is more than likely that you will need to modify your file or macro in the future. Even more complex is a situation where other users need to both use and modify the macro. It’s critical to always remember one thing: In a few months/years, you will probably not remember what your macro was intended to do and how it actually worked (steps, etc).

If that happens, you will have 3 options when macros need to be modified (they almost always do eventually):

-Understanding the current macro (painful and very time consuming)
-Start over from scratch
-Patch based on how you believe the macro works

The third option is the one that is generally chosen, it makes macros less effective, more risky (in terms of errors) and macros that include several layers of patches become very difficult to work in. Good macro builders will generally be efficient but not necessarily be the fastest. Why? Like any type of programmer, it is often not about the fact that a macro works but rather about making sure the code is flexible, easy to understand and change. What are ways it can be done?

Writing Clean Excel VBA Code

1-Methodology: When you start using variables for example, it’s a good idea to always define them at the start of the macro, making it easier to understand
2-Leaving notes!!: This will take you additional minutes but believe me that it will save you even much more. Taking the macro that we have been working in the past few weeks, you can see the code is rather difficult to understand. That is generally the case with macros done by recording. Still, you can add comments simply by putting ” ‘ ” in front of a line. That will make the line green but also mean that no code will try to be executed from that line. You can see an example here:

3-Be Consistent: If you build tens of macros, you will certainly improve as a coder but being consistent in the way that you do things will make it easier not only to build but also modify them in the future.

Last week, we published a “how to” guide to building your first macro with the “record” function. The record function is by far the best way to get started because you do not need to know any code, it requires very little time and can be very effective in automating a process. That being said, more complex, efficient macros are usually coded directly instead of being recorded. It does take some time to become familiar with the code but one way to get there is simply to record a macro and start playing around with the macro.

Why Record First?

I think that starting with a blank page can be very challenging and personally, I found that the best way to learn was by doing 2 things:

-Recording macros and looking at the code, modifying it
-Looking at macros found on the web or build my friends to learn a few more lines of code

Obviously, once you start to get more familiar with coding, you will also be able to look on the web, there are thousands of good websites that give code examples or specific knowledge on excel vba coding.

Why Is It Called Excel VBA?

Recording macros is the very simple way to do it but honestly, you are much more limited in what you can actually do. I suggest that you start by opening the spreadsheet that we were looking at in the last post, click here to do that.

As we had showed you, you can see the code simply by pressing Alt+F11. Then, by selecting the module, you will be able to see the code as you can see below:

Understanding Excel VBA Code, One Line At A Time

Then, if you want learn how the code acts, simply put the code window next to the excel one.

Then, select any part of the code and press “F8”, you will see lines becoming yellow. Each time that you press F8, the yellow line command will be “executed”.

In this example, when I select the first line and “execute” it:

You can see the effect on the spreadsheet:

You can simply press F8 for the entire code, one line at a time. That will give you a better understanding of how the code reacts. Ideally, you can also make small modifications to help you test some things. I would just press for a few things.

#1-Save as often as possible: When testing, it’s very likely that you will screw up in some way. Save once in a while to avoid losing too much work.
#2-You can “restart” at any time simply by pressing “stop”

#3-If you want to skip to a specific step, simply click on the left of the line you want to skip to. When you will execute “play”, it will run the macro but stop once it reaches the break point that you specified.

Most important of all, be sure to ask any questions that you might have here, we’ll be more than happy to help!

Last week we took a look at what excel macros are, why using them makes so much sense and some of the ways they can be used. Today, we will look at how to create your first macro. It can be intimidating at first but it’s actually much easier than you could imagine. There are two different ways to create macros in excel. The more advanced one is of course to write the code directly, which is much more challenging when you are a beginner and we do not recommend starting off that way. The second way is to record your macro. What does that mean?

Recording a macro simply means executing a series of tasks and the macro will then do exactly the same thing.

Example: Imagine that a store that sells tennis items wishes to send a summary of the orders from the past month to their 4 clients. Here is the spreadsheet which includes all orders from that month, you can see it here and/or download the spreadsheet here.

In order to do that I will simply start recording by clicking on:

Then, I proceed, so I started by filtering the columns, by selecting them and then clicking on:

Then, I select client #1:

I then copy the columns, create a new spreadsheet and paste those columns. I then saved the file to: C:/client1.xls

I closed that file and did the same thing for the 3 other clients.

Once that was done, I simply “stopped the recording” by clicking on the “stop button”

The result is that I now have a macro that can reproduce those exact steps. If you want to either see it or start, you would simply go back to tools/macro and click on step into:

Here is the code that I see:

Please do not panic. I know that it looks much more complicated than you were hoping for. Believe me, it will get much easier. First off, it’s important to know that code written when recording macros looks much more complicated. Also, the goal is simply to learn a few lines of code every time, not to understand it all from day one.

You can download our spreadsheet here and try the macro for yourself, you will see how it works.

What Is An Excel Macro? difference with functions

On this blog, we have written quite a bit of material that concerns excel functions such as vlookup, sumif, and even combinations of functions such as nested functions (if and or functions combined together). When you start getting a good knowledge of these functions and how to use them, you start to feel as if excel is incredibly powerful… and it is. The possibilities are almost endless.

That being said, functions are only a small part of the story. If you work on excel and use functions, you are tapping into a bigger part of excel than most users. However, there a lot more ground to cover. Excel macros can range from the easiest operation that you could have easily performed with functions but can also include interacting with other files, other windows applications, complex operations with variables, etc. Basically, any task that you perform in excel can be replicated with excel macros. Sounds powerful? It truly is.

-What Does It Look Like In Excel?

When you add functions to a spreadhseet, it is very obvious . One big difference when building Excel Macros is that when you look at the spreadsheet, you will not necessarily know that some macros exist in the spreadsheet. There are a few ways that you can find out. First off, users often insert buttons on their spreadsheets that make it easier to start macros, here is an example:

Another way to see is to look through the menu:

Finally, if while you are in the excel sheet you press on: ALT+F11, this will give you a better view of the document. You can see some of what is going on in the background. Macros are usually found in “modules”, that are “attached” or part of the excel document but they could be found in the background of the sheets themselves. Here is a small view of the structure of my document.

-What Are Some Of The Possible Ways To Use Them?

There are a million (or more) different ways that macros can be used and I think there are many reasons why you would use macros. Some of the possible uses are more complex rules that would be difficult to replicate, combination of different excel fonctions, advanced usage of utilities such as filters, but also trying to either get data from external spreadsheets, saving data to other drives, etc. We will be going through some of the things that can be done over the next few weeks and hopefully you will also be asking any questions that you might have regarding the use of macros.

-Why Use Macros? What Are The Benefits?

Macros are obviously not perfect but they do offer many advantages.

-Executing complex operations that would not be possible with regular functions or would require a lot of time.

-Clean Spreadsheet: Since macros are built in the background, once they are done, it becomes much easier to run without fearing of erasing a function, etc. It is also very easy for outsiders to use the macros.

-Speed: Running macros can make complex operations that take hours for a regular individual be done

-Easy to improve/modify: Well written code can become very easy to modify and improve which is not necessarily the case in heave spreadsheets

-Excel Macro Downsides

Honestly, there are few downsides to using macros, one of them would of course be that they take more to build, especially when you are a beginner. As well, it can be more difficult for a user to understand how the spreadsheet and macro is reacting compared to a more standard and simple spreadsheet. Other than that, I honestly cannot think of big downsides.

-Next Week

In our next post, we will take a look at how to build your first macro, how it should be done and we’ll go from there!