Excel-VBA Solutions

Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Record a Macro in Excel

Did you know that you can automate repetitive tasks using Excel macros? In this lesson I will teach you how to record a macro in Excel. Record macro is a great tool available in the Microsoft Excel application. It lets the user record all the manual tasks performed. Then that whole process can be executed again and again by one click. So you can understand how much this single tool can save. However this macro recorder tool has some limitations. But if you learn basic concepts of the VBA, then you will have endless opportunities to increase productivity.

Also if you are a programmer, you can use this “Record macro” tool to easily figure out ways to automate some specific tasks. For an example assume that you don’t know how to develop a code to delete a row. Then you can use this tool to find the way to do that. However note that the codes generated by the macro recorder are not always efficient. But it will be a really helpful tool if you are a beginner.

Record macro tool is not only available in Excel but also in some other VBA supporting applications like Microsoft Word and Powerpoint as well.

So now let’s look at how to record a macro in Excel. To do that, first we need to go to the Developer tab. Developer tab is not visible in default settings in Excel. So if you haven’t used the Developer tab before then you will need to show it first. This post explains how to show the developer tab in Excel.

How to show the Developer tab in Excel 2013

Once the Developer tab is enabled you can view all the tools available in that tab.

Now I am going to use the record macro tool for the following process.

  • Create new sheet
  • create a table inside that sheet. (this will be a two column table)
  • Write the headers (subject and marks).
  • Add 11 subjects in the first column.
  • Add the sum function at the bottom of the second column.
  • Format the table.

So this is complete process I am going to record using the record macro.

To start recording the macro we need to click on the “Record Macro” in the “ Code” group.

Once you click on that, a Record macro window will open like this.

Now you can give a name to your macro. Keep in mind that you can not use spaces and other invalid characters for the name. Here are all the rules applied to the macro name.


If you want you can add a shortcut key as well. However this is optional and you can proceed without selecting any key. Also there is a drop-down in the window called “Store macro in”. From that dropdown select “This Workbook” if you want to add the macro only to the current workbook. Then you can add a description from the “Description” text box. This is also optional and you can skip it if you don’t want to add description. Then click the OK button to start the recording.

So this is the sample table I created after clicking the record macro.

Once the process is completed you can click on the “Stop Recording” in the “Code” group to stop the recording.

Now the macro recording is completed. You can view the generated VBA code inside the Visual basic editor.

Code is generated inside Module1.

Don’t know how to open the Visual Basic Editor? Then read this post to learn how.

Getting started with Visual Basic Editor in Excel

Following is the complete code generated when creating the above table in a new worksheet.

Option Explicit

Sub MyMacro()

'
' MyMacro Macro
'

'
Sheets.Add After:=ActiveSheet
Range("A2").Select
ActiveCell.FormulaR1C1 = "Accounting"
Range("A3").Select
ActiveCell.FormulaR1C1 = "Art"
Range("A4").Select
ActiveCell.FormulaR1C1 = "Biology"

Range("A5").Select
ActiveCell.FormulaR1C1 = "Business Studies"
Range("A6").Select
ActiveCell.FormulaR1C1 = "Chemistry"
Range("A7").Select
ActiveCell.FormulaR1C1 = "Computer Science"
Range("A8").Select
ActiveCell.FormulaR1C1 = "English Language"
Range("A9").Select
ActiveCell.FormulaR1C1 = "Geography"
Range("A10").Select
ActiveCell.FormulaR1C1 = "History"
Range("A11").Select
ActiveCell.FormulaR1C1 = "Mathmatics"
Range("A12").Select
ActiveCell.FormulaR1C1 = "Physics"
Range("A13").Select
Columns("A:A").ColumnWidth = 20.86
Range("A1").Select
ActiveCell.FormulaR1C1 = "Subject"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Marks"
Range("A13").Select
ActiveCell.FormulaR1C1 = "Total"
Range("B13").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-11]C:R[-1]C)"
Range("A1:B13").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
     .LineStyle = xlContinuous
     .ColorIndex = 0
     .TintAndShade = 0
     .Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
     .LineStyle = xlContinuous
     .ColorIndex = 0
     .TintAndShade = 0
     .Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
     .LineStyle = xlContinuous
     .ColorIndex = 0
     .TintAndShade = 0
     .Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
     .LineStyle = xlContinuous
     .ColorIndex = 0
     .TintAndShade = 0
     .Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
     .LineStyle = xlContinuous
     .ColorIndex = 0
     .TintAndShade = 0
     .Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
     .LineStyle = xlContinuous
     .ColorIndex = 0
     .TintAndShade = 0
     .Weight = xlThin
End With
Range("A1:B1").Select
With Selection.Interior
     .Pattern = xlSolid
     .PatternColorIndex = xlAutomatic
     .ThemeColor = xlThemeColorDark1
     .TintAndShade = 0
     .PatternTintAndShade = 0
End With
With Selection.Interior
     .Pattern = xlSolid
     .PatternColorIndex = xlAutomatic
     .Color = 12611584
     .TintAndShade = 0
     .PatternTintAndShade = 0
End With
With Selection.Font
     .ThemeColor = xlThemeColorDark1
     .TintAndShade = 0
End With
Range("A3:B3,A5:B5,A7:B7,A9:B9,A11:B11").Select
Range("A11").Activate
With Selection.Interior
     .Pattern = xlSolid
     .PatternColorIndex = xlAutomatic
     .ThemeColor = xlThemeColorAccent1
     .TintAndShade = 0.799981688894314
     .PatternTintAndShade = 0
End With
Range("A13:B13").Select
With Selection.Interior
     .Pattern = xlSolid
     .PatternColorIndex = xlAutomatic
     .ThemeColor = xlThemeColorAccent5
     .TintAndShade = 0.399975585192419
     .PatternTintAndShade = 0
End With
Range("E10").Select
Columns("B:B").ColumnWidth = 10.71
Range("A1:B1").Select
Selection.Font.Bold = True
Columns("B:B").Select
With Selection
     .HorizontalAlignment = xlCenter
     .VerticalAlignment = xlBottom
     .WrapText = False
     .Orientation = 0
     .AddIndent = False
     .IndentLevel = 0
     .ShrinkToFit = False
     .ReadingOrder = xlContext
     .MergeCells = False
End With
Range("A13:B13").Select
With Selection.Font
     .ColorIndex = xlAutomatic
     .TintAndShade = 0
End With
Range("B7").Select
End Sub
Labels: , , , ,

Contact Form

Name

Email *

Message *