What is Excel VBA?

Excel VBA is way to automatically perform any Excel task.  VBA is the Microsoft application programming language and can be used to instruct Microsoft applications on what actions to perform.  For example you can write VBA that tells Excel to open Workbook 1 and then filter for U.S. on column N then Copy all the rows and paste into Workbook 2.  Excel VBA is especially useful for automating mundane and repetitive tasks in Excel.   Any task you can manually perform in Excel can be automated through VBA.

VBA syntax is english like as opposed to other programming languages that are more symbolic and cryptic.  This makes VBA one of the easiest programming languages to learn because a lot of the code can be read like a normal english sentence.

For example this VBA code is asking the user which Workbook to open.  When this code is run a popup box opens with the message of “Select which Workbook to Open.” When the user clicks the file Excel opens the Workbook.  You can then have another automated task run after the Workbook is open.  As you can see the VBA code has many english words where you can infer the meaning of what the code is doing.

MsgBox "Select which Workbook to Open"
ipath = Application.GetOpenFilename(filefilter:="", Title:="Open")
Application.Workbooks.Open (ipath)
Set workbook1 = ActiveWorkbook

Here's a list of some of the things VBA can do in Excel.

  • Open/Save workbooks
  • Create/Delete/Select worksheets
  • Perform calculations
  • Copy cells
  • Perform validations on the data
  • Filter rows
  • Basically any function you can do in Excel you can automate with VBA

Here's some advanced functions you can do with VBA

how to create a excel macro

Excel has a macro recorder where it will generate VBA code based on the actions you take in Excel.  This is the easiest way to get started making Excel macros.

  1. Go to View Tab -> Macros -> Record macro.  Then give it a name and click ok.
  2. Then perform the actions you want to automate in excel.
  3.  Once the process is complete click View Tab -> Macros -> Stop Recording
  4. To run the macro you just created click View Tab -> Macros -> Select the macro by  its name -> Click Run
  5. Excel will run the macro which will automatically perform the actions you just performed

The macro recorder uses fixed referencing which is where the cell values are hard coded.  For example if you copy formula down the table it will only copy the formula for how ever many rows you have.  So if the current dataset has 10 rows the VBA will be hardcoded to copy the formula down 10 rows.  But next time if your dataset has 30 rows the recorded macro will still only copy the formula down 10 rows.  This is an example of fixed referencing.  Macros should be dynamic so the formulas copy down to the bottom of the dataset regardless how long the data is.  View the below training for how to make your macros dynamic.

Excel VBA Macro Demo

Demo of what a Excel Macro can do:

Example of what a macro can do.  In this example real estate data is separated by years and then a table with the property value increase rates is created with the rate of change and average change to show the hottest real estate areas.

Basic Operations
  • Open/Save Workbook
  • Filter worksheet
  • Add formula
  • Copy Visible Cells
  • Paste into another worksheet
Advanced Operations
  • Pull Data from Webpage
  • Calculate values
  • Create pivot table
  • Email summary table

VBA Training to learn the basics of how to create Excel Macros.  Learn how to automate business processes in Excel with this free VBA training.