Summary: How do you make a macro recording more user friendly? Let’s say you want to get rid of all those “Activecell” and “FormulaR1C1” and similar lines of VBA code. In article #4 of this series we’ll turn a recorded macro into something that looks like it was written by a VBA pro.
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!
Download the Sample Workbook
et’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…
#2 – Edit our first macro
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.
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.
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():
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.
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.
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.
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!
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).
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:
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.
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:
There’s one last thing it would be nice to do: place a button on the worksheet that starts the “SearchDate” macro
o do this, click on the following icons on the Developer Tab: “Insert” -> “Button (Form Control)”
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:
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!
Daniel Lajosbanyai – I work as a controller and accountant for a company with a bunch of international subsidiaries. In my daily job I work A LOT with Excel and my tasks are quite repetitive (and sometimes boring!)
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!
Victor expertly teaches Microsoft Excel to people all over the world. He has millions of views of his popular Excel explainer videos on YouTube. These show time-saving shortcuts and real-world applications explained with easy-to-follow visuals.
Victor has over 20 years of experience using Excel as a professional for Big 4 Audit Firm Deloitte and two global tech companies. He knows firsthand that being more productive with Excel can lead to greater job satisfaction and career growth.
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.