How do you record a macro and edit it?

At the end of the tutorial you’ll know how to record and edit macros to convert a given date in November 2018 to the correct weekday. It’s not just possible but also easy to learn!

Difficulty: Beginner

«« Previous … | … Next »»

Contents

Click on a link to jump to that section…

#1 Record our first macro
#2 Edit our first macro
#3 Record our second macro
#4 Specifying the sheet to use
#5 Let’s do REAL programming now
#6 Creating a button to start our program
#7 Summary
#8 About the Author

» Back to contents

Let’s record a macro so we can modify the recorded code later (see this article if you need a reminder of how to record macros).

In the downloadable workbook you’ll see that I typed in the 30 calendar days of November 2018. I did this while recording a macro and called it Sub Calendar().

OK, so Sheet1 looks like this after I typed in everything:

I know, it’s not rocket science. What’s more interesting than typing the calendar into Excel?

Well, we’ll first look at the recorded code in Sub Calendar() – and fine-tune some commands to improve that macro. This will show you a simple example of how to improve recorded VBA code.

And then we’ll record a second macro and modify it to tell us what day of the week it is for a given date in November 2018.

This is what the finished macro will do:

We can’t simply rely on recorded actions to do this for us, that’s why we’ll need to modify it. Don’t worry, I will explain everything and keep it as simple as possible.

OK let’s start by fine-tuning our first macro…

» Back to contents
OK let’s edit our first macro. We’ll do this by assigning values to cells with a single line of code instead of the two lines used by the macro recorder.

You can see the full code for our first recorded macro Sub Calendar() in the downloadable workbook.

I don’t include it here because it’s long and repetitive, and you can simply recreate it by recording yourself type in the calendar for November 2018.

The recorded macro has the following commands:

Sub Calendar()

    ActiveCell.FormulaR1C1 = "Monday"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Tuesday"
    …
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Sunday"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("E6").Select
    ActiveCell.FormulaR1C1 = "30"
    …

End Sub

As you can see, I missed out the repetitive parts, and copied only the minimum we need to understand the theory.

The question is how can we make this code more readable, more effective, more “professional”?

Here is my first tip… you can replace two lines of code with a single line of code.

How? Well, when recording a macro if you select a cell first, and then give it a value, there will be two lines of recorded code:

    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Tuesday"

Instead of these two lines, you can use a single command line:

    Range("B1").Value = "Tuesday"

Notice, that the Cell B1 is not selected, we just give it a value. The value is a string now, but that doesn’t bother us.

With this programming method you turn two lines of code and into one line. This saves processing time as well – the result will be faster program execution, and more user-friendly code.

Applying this tiny modification to the rest of our macro cuts it in half from 78 to 39 lines.

Wait, so is shorter code better?

Well, usually YES!

Of course it depends on the task – how short can code be that still does the job – but you should always try to solve the problem as simply as possible.

When we take debugging and support into consideration, simple is better than complex.

Next, we’re going to work on our second macro.

» Back to contents

Going back to our goal, we’re going to record a second macro so we can modify it to tell us what day of the week it is for a given date in November 2018.

We need a function that tells us which cell has our specified date.

For this purpose, we are going to use Excel’s built in search function.

Start a macro recording (see this article if you need a reminder), then Press Ctrl + F on Sheet1, and type in “21”. We want to know which weekday the 21st of November is.

The code should look like this, after you stopped the macro recording:

It’s complicated isn’t it? 🙂

Well, if you look closely, it’s not so bad, there are just a lot of arguments for the Cells.Find command. Most of them are irrelevant now, we could even delete them.

I will explain their function in another article later, but I’m sure you can figure out what “After”, “SearchOrder”, “SearchDirection” parameters do. They are pretty straight forward.

For now, let’s name the recorded macro as Sub SearchDate()

In the following sections we will work on improving it.

» Back to contents

One thing we can improve in the code is to declare which sheet to work on.

Without a specific declaration, macros always run on the active sheet.

If you don’t declare what sheet to work on, you could find yourself with a big headache if you run a deleting macro on the wrong sheet you were working with the whole day 😀

So make sure to declare which sheet to work on!

It’s something you should never forget when you use macros on real, important workbooks! [Remember: there is no “Undo” button for macro actions]

Specifying the sheet to use can be done many ways… for now I will stick to the easiest method.

Type the following command at the start of the macro Sub SearchDate():

    Sheets(“Sheet1”).Select

This is a simple selection command that suits our needs because we use the same sheet in the whole code. As there is no other sheet selected in the macro, it will execute all commands on this sheet.

» Back to contents

You may have heard of variables in the context of programming.

Variables make programming possible, so we need to get familiar with them.

In a later article I’ll list the most common types, but for now I just show you how to use one type.

To achieve our goal and code a program that converts dates to weekdays, we are going to use Excel’s built in object properties and one variable type: the string.

You can think of this as the first step into real programming. 😀

OK, let’s keep working with the “SearchDate” macro. We will edit this recorded macro so it can do the date-weekday conversion.

Variable for date

First we need to declare a string type variable with the name DateNov. The following command creates this variable:

    Dim DateNov As String

Where should we write this in the code?

Always at the start of the macro! It is a common and useful habit to declare all variables and constants at the start.

If you search for macros on the internet, you will almost always see that all declarations happen right after the Sub line. This is done to make the debugging and understanding of the code easier.

Inputbox

After this, we need an “Inputbox”, which helps the user to communicate with the program.

This Inputbox will ask after the specific date you want to convert, and store the date in the DateNov variable.

The command is:

    DateNov = InputBox("Please type in the desired date!", "Date declaration")

The first argument is the prompt (what the message on the screen “asks” from the user) and the second argument is the title of the message.

As you can see, Inputbox is very similar to Msgbox, though it doesn’t just “say” things TO the user, but also takes information FROM the user.

This information will be stored in the variable DateNov so it can be accessed anytime later in the macro. We will use this feature to our advantage!

Find variable instead of 21

Currently in the recorded code, the “Cells.Find” command looks for the value “21”.

We need to make the “Cells.Find” command look for the date stored in the variable DateNov.

To do this, we must change this: Cells.Find(What:=”21″,  to this:  Cells.Find(What:=DateNov,

Note that DateNov doesn’t have double quotes because we only need them for strings, and not for variables (even if the variable is a string-type variable).

Date-Weekday conversion

When you run the edited recording now, it will ask for the desired date and activate the cell that contains the date.

This is a good result, but we are not finished yet. We still need the conversion part of the code.

Here we will use a built-in feature, which is a property of the ActiveCell.

Objects in Excel have many different properties, and two of them are ActiveCell.Row and ActiveCell.Column.

How do we get from the ActiveCell (the cell containing the date) to the top row (the cell containing the weekday)?

Use the following command:

    Cells(1, ActiveCell.Column).Select

This line selects the cell that is in Row 1 and in the same Column as the Activecell, because the weekdays are listed in the first row in every column.

You can think of Activecell.Column as a pre-declared variable – actually it is one, but pre-declared variables are called properties.

Msgbox

Now the program has just to tell the user the result of the conversion – and this is where the Msgbox function comes in the game.

This time the prompt of Msgbox will be a little bit more complex than before:

    MsgBox (DateNov & " of November is " & ActiveCell.Value)

DateNov is the variable containing the date you typed in.

The string “ of November is “ forms the middle part of the prompt.

The third part of the prompt is the Activecell.Value. Remember, that the Activecell is now the cell containing the weekday, because we have selected the top row of the Activecell.Column. We refer to its value using Activecell.Value command.

After all these modifications in the recorded code, your “SearchDate” macro looks like this:

» Back to contents

There’s one last thing it would be nice to do: place a button on the worksheet that starts the “SearchDate” macro.

When you click on it, the cursor will change to a drawing cursor, and you can draw the button in the desired place and size on the sheet.

After drawing the button, a window will appear for you to choose what macro to assign to the button. Choose “SearchDate”:

Now the button is assigned to the “SearchDate” macro, but it’s name does not show it.

We have to change the button’s name manually: Right click on the button and after that when you left click on its name, you can type in a new name (like “SearchDate”).

This is what the finished result looks like:

» Back to contents

We recorded two macros and improved them by editing the VBA code. There are many situations when it’s easier to record and modify a program than write it manually from scratch.

Frequently used functions like Cells.Find and Copy-Paste should be recorded – at least until you know how to handle variables and object properties.

While recording you can learn a lot about the syntax of VBA commands, and by editing the code you can start to think of yourself as a real coder! Basically, that is exactly what we help you to achieve at Launch Excel! 😀

» Back to contents

To boost my processes and spare some time and energy, I started learning Excel Macros. My first teacher was a university professor, who showed me how to get started. I am really thankful to him, because without this knowledge I might have quit my job years ago.

Now I enjoy writing macros for every task I can automate and feel really happy to have learned this skill! Why should we do repetitive things, when our computers can do them quicker for us? We only need to learn how to give them instructions to follow!

Related posts:

1. Excel VBA Tutorial: Get your personal macro code vault (aka Personal Macro Workbook)
2. Excel VBA Tutorial – #3 Running Recorded Macros Step-by-step
3. Excel VBA Tutorial: Unlock the Secrets of Worksheet Code Names
4. Excel VBA Tutorial – #2 Macro recording (including the difference between absolute and relative)

FacebookTwitterGoogle+Share

Hi I'm Victor. I started Launch Excel to help you use Excel and VBA faster. Thousands of people have learned new skills and increased their effectiveness at work with our help. You can do it too! Also I've personally used Excel lots since 2002. I use it mainly in accounting and finance but I also have a Masters degree in Engineering from Cambridge University. I hope you enjoy the site and tutorials!

How do I edit a recorded macro?

Is it possible to edit a macro after recording it?

How do you record a macro and use it?

Can I add to a recorded macro?