Very recently, a comment on a past post with a question turned out to be a very interesting challenge. Without further wait, here is the question:

“Is it possible to do a “ceasar cipher” of three over letters (input A and output is D)

(A=D, B=E, C=F, D=G, … X=A, Y=B, Z=C)
ABCDEFGHIJKLMNOPQRSTUVWXYZ
DEFGHIJKLMNOPQRSTUVWXYZABC

e.g. I LOVE CARS = L ORVH FDUV
e.g. WKDQN BRX = THANK YOU

Please help me, I am having difficulties in doing this in excel vlookup.”

As is always the case, there are many different ways to get this done. I personally felt it would be easier with vba than using vlookups. Since I would likely not be dealing with hundreds of different messages at once, using simple loops. Here is how I represented the issue:

As you can see, I wanted to be able to both uncode a message as he had asked but also use the same one to code something else. Here is the code that I used for the first part:

“Sub decode()

End Sub”

I did very small modifications for the second part. You can see the end result here:

And as always, feel free to download the spreadsheeet here.

Often, while running excel macros, especially ones that take a while to run, we turn off the screen updating function in excel. That is done quite simply by adding the following into a macro:

With Application
.ScreenUpdating = False
End With

Then, at the end, I simply re-activate the screen update feature by adding the following:

With Application
.ScreenUpdating = True
End With

The main benefit is that this simple line of code can make a major difference in the speed of the macro.

The downside however is that you no longer see what’s going on in the code. For macros that take some time to run, this can be a bit worrying. One way to get around this is having the macro give an update or message. There are two different ways this can be done.

#1-Message Box:

MsgBox “The macro has finished running”

#2-Status Bar Updates

One alternative is to have the message displayed in the status bar. The advantage is that no click is necessary to move to the next step, here is a sample code:

Application.ScreenUpdating = False
‘ turns off screen updating
Application.DisplayStatusBar = True
‘ makes sure that the statusbar is visible
Application.StatusBar = “Please wait while performing task 1…”
Application.StatusBar = False
‘ gives control of the statusbar back to the programme

This is what it would look like:

Both methods are generally good ways to get updates without needing to see every single step that the macro is doing.

One of the critical reasons why many including myself decide to use excel macros is that it can help make some processes much smoother and faster and it also gives more options.

A lesser known but also very useful function in excel macros is having a specific macro run automatically. There are many different ways to do this but you could have a macro start running at a specific time, on the opening of the file or even have a macro run every X minutes. Today, I wanted to give an example where I want a specific macro to be ran every 5 minutes on my computer.

Why would I need this? There are many different reasons and it could be changed depending on your specific need but I thought it would be interesting to look at this example. There are a few things to know about this before going further:

-This is a fairly advanced excel macro functionnality but I think it’s a good example of how powerful excel macros can be
-Obviously, both computer and the excel file need to be open for such a macro to work
-A macro that goes on automatically can be used to do a lot of different things. However, it does not work well if you are doing other things at the same time

If you are new to excel macros, I’d invite you to look at my introduction post here!

Step #1-Defining variables

Some built-in functions in excel make this macro work more easily, you simply need to “call them” with these variables:

Step #2-It can screw up things if you start several such macros at once. To avoid this, I create a cell that indicates if it’s already running and does not start if it is:

Step #3-When the auto macro is “off”, I reach the auto P&L:

The first line is the name of the macro that I want to execute (or you could even put the actual code)
The 2nd line helps me write down the exact time when the macro was last ran
The 3rd line starts the “timer”

Step #4-There isn’t much to do here, I simply start the timer, write down the time of the “next run”, and also write the status of the automatic macro (“on”):

Step #5-It’s important to also have a stop button:) Here is mine:

Here is what the file looks like:)

You can of course the download the spreadsheet here

Hi everyone, today I’m thrilled to be able to publish a new example of how powerful Excel macros can be. This is an email that I received from a reader (would love to get yours as well, simply contact us) and I decided to take up the challenge. First, here is the email:

“Hello.. Random e-mail. I stumbled onto your blog and found the tutorials suprisingly easy to read and understand! which isnt normal for me! I was wondering if you could just show me how to work this spreadsheet..
I work for a chauffeur company and i’ve recently made a spread sheet with the customers name, date of travel, place they travelled, e-mail address and whether the travel was for business or leisure.
What I need to do is to be able to filter the customers.. Say if they travelled to Heathrow in march 2011. And e-mail all of those customers. Do you see what I mean? It would be great if you could e-mail back with some help!

Laura”

So basically, this can be done in a number of different ways. I did think about using an advanced filter but in the end I decided to simply take a quick macro. First, you can take a look at what the database looked like (don’t worry, you can download the spreadsheet later in this post):

Basically, I wanted to give the option and finally decided on doing a simple loop. Sure, if the database had a few tens of thousands of lines, this could slow it up by a few seconds for this specific task, it looked like a perfect solution. If you would like a quick introduction on building an excel macro, I recommend that you go here!

So basically the steps in my opinion are as follow

1-determine the criteria
2-go through each line of the database to determine if it meets all 6 criterias
3-if it does, simply copy the line

Here is the code that I wrote:

As you can see, it is very simple, the only line that is more complicated is the verification of each line. Basically, I make sure that either the result in that cell meets the criteria or that the criteria is empty.

Here is the result as you can see it:

You can also download the spreadsheet here.

I love getting questions as they often give me ideas for new excel tutorials, etc. Recently, I received a question for a seemingly simple problem. Using a variable in such a case should make it easy to understand and will also give more flexibility. Why? Let’s first take a look at the question:

“You are doing wonderful job at posting easy excel tutorials. Continue the great work! Can you please post a macro tutorial that copy data from one tab and paste into another tab? For instance, the macro copies column A & B from sheet1 and paste into sheet 2, then copies column A & C from sheet1 and paste it into sheet3, and so forth?

Can you please help with this? I have attached my excel spreadsheet, which includes the data and simply macro – that is not working.”

Here is the screenshot of the file:

Many would have the reflex to simply record a macro that does this exact task. That would work fine. However, what if the next time there are more rows? Or you would like to change a few things? Using an excel macro with a variable is a great way to get it done.

Step #1-Define the problem: I want the macro to go column by column starting at column B until there is an empty column. For each column, I want the macro to create a sheet, copy both the first column and that column to the new sheet. Let’s first take a look at my code:

Name of the macro:

Add the variable “I” that will be the column number. And also select the sheet named “Detail” where the data needs to be.

Then, I add a loop that will make it possible for the macro to go from column to column:

Then, for each column I want to add a sheet, and rename it to the correct name:

Finally, I want to paste the information on each tab so I add a few more lines

You can also see the result after running the macro and of course download the spreadsheet here.