Intro to Excel Macros and Visual Basic Programming

I am sure that many of you work in routine processes and work with Macros, or have at least heard of Macros. The most common type of Macro is an Excel based one, but you can use Macros in any application in the Microsoft Office Suite.

A Macro is defined by Microsoft as an action, or a set of actions that you can run whenever you want. Macros can be created by recording them or by coding them directly into your application.  The code behind the Macro is commonly referred to as VB or VBA.


Getting Started

Before getting started with Macros we will want to add the “Developer” tab to your excel Ribbon. To do this, go to:

  1. File
  2. Options
  3. Customize Ribbon

On the right Panel you will see “Developer“. Click the checkbox for it and hit OK


Recording a Macro

The easiest way to understand Macros and the code behind them is to record one yourself.  You can find the option to record a macro under your developer tab, labeled “Record Macro

Excel_Record_Macro

For this Macro Recording, we are going to:

  1. Click in Cell A5
  2. Type “Hello World” into cell A5
  3. Copy A5
  4. Paste it into A10
  5. Bold A10
  6. Highlight A10 as Yellow

Open your Excel File and Turn on “Record Macro“.
Click “OK” to start recording and perform the above steps.

When you have performed the above steps, click “Stop Recording”

 

 


Running the Macro

To see how our macro works, delete all of Column A so that we have a blank spreadsheet. On the Developer Tab, click on “Macros” and run your Macro.  You should hopefully see the steps you did earlier, automatically recreated for you.

The below GIF shows a recreation of all of these steps.
Excel_Record_Macro1


Coding Behind it all

If you wanted to see the code behind your Macro, choose “Step Into” instead of “Run“. This will bring up a small code window showing you the steps that were recorded in your macro.

You can press F8 to step through each individual step in your Macro to see how it works.
Most of the programming here should make sense as you read it. VB/VBA is considered a “Logic Based” coding system and the understanding of each line of code can be pretty easily derived as you read it.

For example,  the below is selecting cell A5 and setting the cell value to "Hello World"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Hello World"


This concludes our introduction into Visual Basic and Excel Macros.  We will be including more detail on Excel and Access VBA programming very soon.



Categories: Excel, VBA

Tags: , , , , ,

1 reply

Trackbacks

  1. Why you Should Not Rely on Recorded Macros

Leave a Reply

Show Buttons
Hide Buttons
%d bloggers like this: