948 Steps to 6 Figures by Learning Excel-VBA and Other Skills
Step 106 - Using the Macro Recorder
| To use the macro recorder, you must know Excel. If you want to automate a chart, Vlookup, or Pivot Table, you do the function in Excel. The Macro Recorder will capture what you do, and generate VBA statements that will allow you to automate your task.
Start the Macro Recorder by pressing the record button. It's on the Developer Tab, left side. Do your work in Excel, and stop recording when you're finished. 
It's better to record small tasks, than record a long macro, because you can lose track of what the recorded code does. Once you've recorded your macro, the next step is to simplify the bloat that Excel includes and make it more useful. You will remove hardcoding, and add comments to make it more readable. The Macro-recorder is active when the status bar has a box in it (a blue arrow is pointing to it below) 
It's easy to start a recording and forget that one is in process. The Statusbar will show And below is some code I recorded. It may look like a weird foreign language to you, but it's the instruction to tell Excel what to do. We will step through this generated macro and discuss what each item means.
Each statement in the recorded macro has an object then a method or property. The format of a statement is Object.Property = value or Object.Method [parameter:=value, param2:=value2, ...] An object is a thing like a Cell, Sheet, Workbook. A method is a verb or action you take on the object like select a cell. And the property is a value that an object has like color, height, Width, Hidden, disabled, etc. The following are some objects that the macro used. Objects are things or nouns. VBA also has Collections of objects that usually end with an "s" The properties have an = sign and the methods have := A property of a person might be their Let's discuss the statement above. I'll be your interpreter. Some of the statements we won't care much about. Sub Macro1() The word Sub denotes that this is a Subroutine, which means you can "call" it to do work for you. Let's say our Macro or sub put nice formatting on our Spreadsheet. We could invoke or call it anytime to format any Sheet. The whole Subroutine ends at the End Sub statement. The Subroutine has a name called Macro1. It's useful to give it a name that describes what it does. For example, what does subroutine Macro1 do and what does subroutine AddBorders do? Exercise: Record a macro that adds borders to a range of cells and call it AddBorders Single Quote A single quote is used to denote a comment. This is the text that VBA will ignore but is for the human to be able to tell what's the purpose of the code. Commenting on your code is an important practice but often neglected. Any text that comes after the ' is ignored and colored Green. Note that the single quote is used other times and it's not a comment which can be confusing to newbies. Range("C2").Select The Range object is passed the name of a cell or cells in A1 format. In this case, the system selects cell C2. Go to the Immediate window and type this statement. It's the same as clicking the cell. For each of the steps below type into the Immediate window and watch the result. Pretty cool. Type the word Range( and VBA shows you the following: range(
You can press Ctrl I or select Edit > Quick Info from the Menu Range("C2:C10").Select The range command is very important and you will use it quite often while writing your code. Note that I will be using "code" and "macros" interchangeably. This statement will select the cells from C2 to C10, as the : (colon) denotes a range like in an Excel Formula. Notice that the C2:C10 is enclosed in a double quote ". This is known as a String and will be contrasted with Numbers, and Variables. Enter into the
You will get an error 
Getting strange error messages will be normal from VBA. It's one of the hard issues to deal with. But with some experience, you'll soon know right away what's causing an error. The one useful rule I've found with dealing with errors is if you get an error assume you did something wrong. Once you have this mindset it's much easier to find the error you made. Yes, you will make hundreds if not thousands of errors. The good thing is that VBA tells you here. VBA is basically dumb and doesn't know your intention. It would be obvious to a human what was meant but not to a computer program. We have a whole chapter on solving the most common errors. If you can't figure it out try to find something that worked, copy it, and change it. The Range object expects a String, Number, or a variable. Strings are enclosed in "quotes." ActiveCell.FormulaR1C1 = "" The Activecell object is used a lot in VBA. It denotes the active cell in your spreadsheet. Active is a common adjective that proceeds many objects like: The property FormulaR1C1 is a bad choice that the Excel developers made. It's not needed here as the statement ActiveCell = "" would do the same thing. The "" is known as an empty or null string. It clears the value that was in the ActiveCell. R1C1 - Denotes a type of cell referencing vs. the A1 type of reference, column A row 1. The formula that's provided will provide this type of cell reference. Range("D3").Select You should be able to guess what the above statement does by now. ActiveCell.FormulaR1C1 = "xcvxvx" This statement does an Assignment statement which is a very common activity in VBA coding. The value on the right gets assigned to the value on the left which in this case is a property of the Activecell. Other times you assign a value to a variable which is just a place for your code to remember something while the program is running and use it later on. Other places you can remember or save information is in a Columns("C:C").Select This statement uses the Columns Collection in this case column C and selects it. Try
With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With Rows("3:3").Select Selection.Font.Bold = True Selection.RowHeight = 51.75 Range("D3").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("E2").Select ActiveCell.FormulaR1C1 = "1" Range("E3").Select ActiveCell.FormulaR1C1 = "5" Range("E4").Select ActiveCell.FormulaR1C1 = "3" Range("E5").Select ActiveCell.FormulaR1C1 = "4" Range("E6").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)" Range("E7").Select Application.WindowState = xlMaximized ActiveCell.Offset(-1, 0).Range("A1").Select Selection.ClearContents Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "=RC[-1]+1" ActiveCell.Columns("A:A").EntireColumn.Select Selection.Insert Shift:=xlToRight ActiveCell.Offset(2, -2).Range("A1").Select Selection.Copy ActiveCell.Offset(0, 3).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, -1).Range("A1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=RC[-1]+1" ActiveCell.Select Selection.AutoFill Destination:=ActiveCell.Range("A1:A4") ActiveCell.Range("A1:A4").Select ActiveCell.Offset(4, -1).Range("A1").Select Selection.Copy ActiveCell.Offset(0, 1).Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False End Sub |
Table of Contents | Send us your feedback | © 2025 All Rights Reserved | Edit