Post
Ask Question

Record A Macro With Excel

Creating Simple Macros in Excel and Managing Macros and Configurations in Excel

This is what we'll cover in this article. Let me start off by introducing macros in Excel. As an overview, I shall quickly dip into the world of Excel VBA followed by demo screenshots.

Let's get started,

Macros are instructions of code that help to automate work. It adds tiny features and enhancements to help you accomplish exactly what you need to do quickly with just a click of a button. Macros are so powerful than standard functions which you do into cells. It is hidden behind the normal user interface.

Option to use macros,

  1. One way is to simply record your actions in Excel to save them as repeatable steps, or
  2. Visual Basic for Applications.

Excel is used in so many work processes. Let's take a scenario. Let's say every week you export and access data from a database to create a report. The only problem is those data exports aren't always in an Excel-friendly format. They are messy and often include far more data than your report requires. This means you have to clean up empty rows, copy and paste data into the right place, and create your own charts to visualize data and make it print-friendly.

Step 1 - Create or Import raw data into Excel

Record Macro with Excel

Step 2 - Option to record macros

  1. Browse the excel
  2. Select view Option
  3. Select Macros -> Record MAcro

Step 3 - Record Macro

There are the certain option can be configured at Macro screen i.e. 

  1. Macro Name
  2. Shortcut Keys - it can help to execute the macro via shortcut key
  3. Store Macro - Macro can be defined for the current worksheets or another worksheet within the same excel file.
  4. Description - short description help to identify the detail about macro, what it is doing.

Let's give our macro a name. Now there are a few conventions you need to follow while naming a macro. The first character of the macro must be a letter, but subsequent characters can be letters, numbers, or underscores. You also cannot use spaces. And it is best to avoid giving the macro the same name as a cell reference. Let's call it center_profit. You can use the Store macro in the drop-down to choose where you want to store the macro.

There are three options available,

  1. Personal Macro Workbook
    This will make the macro available whenever you use Excel. 
     
  2. New Workbook
    This will make the macro available for any workbooks you create during the existing Excel session. 
     
  3. This Workbook
    This will make the macro available only in the workbook you have open. If this is a daily report, I'd be using the Personal Macro Workbook option. 

For now, let's just use this workbook. You can also assign a shortcut key to the macro. Assigning a shortcut key will let you run the macro in Excel at any time by pressing that combination. Remember that the shortcut you assign here would override any existing shortcuts in your workbook. Let's just say Ctrl+Shift+K. To get the Shift key in here, hold Shift while pressing K. And you can also enter a description for the macro if you like. This field is optional, but it's advisable to be as comprehensive as possible so that you and others can see what the macro does in the future. Tables and centers the report & title and then bolds, and also, Deletes the profit column. And then click OK.

While recording a macro, I followed the below steps,

  1. Select the cell and Header as Bold
  2. Select the cell and did center alignment with data
  3. Remove the undesired column i..e Profit
  4. Stop Recording

To validate the record macro. Navigate to Macro -> View Macro -> Edit the selected macro. you will navigate to the below screen.

Sub center_profit()
'
' center_profit Macro
' Merges and centers the report title and then bolds, and also, Deletes the profit column
'
' Keyboard Shortcut: Ctrl+Shift+K
'
 Range("G6:K6").Select
 Selection.Font.Bold = True
 Range("G7:K11").Select
 With Selection
 .HorizontalAlignment = xlCenter
 .VerticalAlignment = xlBottom
 .WrapText = False
 .Orientation = 0
 .AddIndent = False
 .IndentLevel = 0
 .ShrinkToFit = False
 .ReadingOrder = xlContext
 .MergeCells = False
 End With

Step 4 - Output 

When I run the macro on the raw data sheet, it quickly does formating and similar we can do lots more such operations to save lots of time or automate the basic operations.

  1. Header become bold
  2. Content data become center aligned
  3. Unwanted column removal

Appreciate your time to read out and stay tuned with more interesting article.


Similar Articles