Simple VLOOKUP Writing System

If you want to follow a proven system to always write perfect VLOOKUP/INDEX & MATCH formulas, then this free crash course is for you!

In this crash Excel course, you will learn:

  • How to understand and remember the VLOOKUP/INDEX & MATCH syntax
  • How to set up your data table for perfect matches
  • How to get rid of #N/A errors once for all using the TIF blueprint
  • And much more!

Download area

How to create a Pivot Table using VBA

You can generate a Pivot Table report with a mouse click. You can do it using Excel VBA and macros!

Let’s suppose you want to generate a Pivot Table report based on a given Excel table created; see below…

Excel table with macro-button to generate a Pivot Table using VBA

Figure 1: Source data for Pivot Table report

This would be the resulting PT report (a simple one):

Summarize with Pivot Table

Figure 2: simple Pivot Table report

If you do it by hand, this would be the process:

  1. Creating the PT report by going to: Table Tools>Design>Summarize with Pivot Table (assuming the Excel table is already created)
  2. Setting row fields (“Products”)
  3. Setting data fields (“Units Sold”)

Overall, the same steps should be executed using VBA, see the Excel macro code below…

Public Sub CreatePivotTable()
'developed by John Franco
'excelgurusacademy.com
'last update: Mar-03-2012

Dim MyPTReport As PivotTable 'a pivot table report object
Dim MyPTCache As PivotCache 'the pivot table data

On Error Resume Next

'adds a new sheet
Sheets.Add

'creates a pivot table based on Table1. You can change your table name as you like

' Create PivotTable cache and report.
Set MyPTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="Table1")

Set MyPTReport = ActiveSheet.PivotTables.Add(PivotCache:=MyPTCache, TableDestination:=Range("A1"), _
TableName:="PivotTable1")

'add row fields
    With MyPTReport.PivotFields("Product")
        .Orientation = xlRowField
        .Position = 1
    End With
'add data fields
    MyPTReport.AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Units Sold"), "Sum of Units Sold", xlSum

End Sub

That’s all! You can create a Pivot Table report each time without effort.

Download the Excel Pivot Table macro practice file at the bottom of this page.

Now let me explain the Excel VBA macro code in more detail…

Adding a new Pivot Table using VBA

To create a new Pivot Table in a workbook you need to be familiar with the hierarchy of Pivot Tables objects in the Excel object model, see the picture below…

Pivot tables collection, pivot table object

Figure 3: Pivot Table object model

Basically you need to specify the unambiguous path that points to your PT, e.g.

Workbooks(“YOURBOOK”).Worksheets(“YOURSHEET”).PivotTables(“YOURPIVOTTABLE”).”PT method or property here”.

You can make your Excel VBA code shorter when you are referring to the active workbook, replace the entire VBA objects hierarchy: “Workbooks(“YOURBOOK”).Worksheets(“YOURSHEET”)” by this one: “ActiveSheet()…”

Well, to create the Pivot Table invoke the “Add” method of the “PivotTables” collection.

This is the full syntax:

expression.Add(PivotCache, TableDestination, TableName, ReadData, DefaultVersion)

“expression” represents a PivotTables object.

Where the three most important arguments are:

  • PivotCache – this provides the data for the report
  • TableDestination – the cell where the PT report will be inserted, e.g. “A1”, “G2”, etc.
  • TableName –  the name of the Pivot Table, e.g. “Region sales”, “Labor hours summary”, etc.

Here’s the full code I used above:

Set MyPTReport = ActiveSheet.PivotTables.Add(PivotCache:=MyPTCache, TableDestination:=Range("A1"), _
TableName:="PivotTable1")

You first need to specify the source data of your future Pivot Table (cache). To create a “PivotCache”, use the “Create” method of the “PivotCaches” collection.

This is the syntax:

expression.Create(SourceType, SourceData, Version) or you can also use expression.Add(SourceType, SourceData, Version)

“expression” represents a “PivotCaches” object.

Where the two most important arguments are:

  • SourceType – can be either: xlConsolidation, xlDatabase, or xlExternal
  • SourceData – this is the data source of your Pivot Table, e.g. Range(“A2:F22”), Sheets(“Sheet3”).Range(“F30:G40”), “Table1”, etc.

This is the full code I used above:  

Set MyPTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="Table1")

Now is time to add the respective fields…

Adding row fields to a Pivot Table using VBA

Just for refreshing the location of Pivot Table fields, here’s the PT report layout:

Drag and drop the fields to the right fields area

Figure 4: Pivot Table fields’ layout

To add row fields use the “PivotFields” collection of the “PivotTable” object.

Specifically, use the “Orientation” property of the “PivotField” object.

This is the syntax:

expression.Orientation

expression represents a “PivotField” object.

Specify the orientation (field type) using the respective “Orientation” property value:

  • xlRowField
  • xlPageField
  • xlColumnField
  • xlDataField

This is the full VBA code I used above…

With MyPTReport.PivotFields("Product")
 .Orientation = xlRowField
 .Position = 1
 End With

At this time you have defined a blank PT report, see below…

Before adding row fields, data fields, column fields or page fields

Figure 5: Blank Pivot Table report, before adding fields

It’s time to add stuff to it…

Adding data fields to a Pivot Table using VBA

You need to use the “AddDataField” method of the “PivotTable” object.

The syntax is:

expression.AddDataField(Field, Caption, Function)

“expression” represents a “PivotTable” object.

Where the main parameters are:

  • Field – what field you refer to, it should be one of the fields contained in the source data (cache). For this example the fields are: “Product”, “Salesman”, “Units Sold” or “Price per unit”
  • Caption – how your field will be presented in the Pivot Table report, e.g. “Sum of Sales”, “Average of price”, etc.
  • Function – to sum use xlSum, to average use xlAverage, etc.

This is the full code I used above…

MyPTReport.AddDataField ActiveSheet.PivotTables( _
 "PivotTable1").PivotFields("Units Sold"), "Sum of Units Sold", xlSum

Additional VBA Pivot Table techniques

Adding column fields using VBA

Use also the “PivotFields” collection, see the full VBA code below…

'define column field
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product")
    .Orientation = xlColumnField
    .Position = 1
End With

Adding page fields using VBA

Use also the “PivotFields” collection, see the full VBA code below…

'add page fields
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product")
        .Orientation = xlPageField
        .Position = 1
    End With

Converting the current region to a table

If you want to create the Excel Table on which your Pivot Table report will be based on (“Table1” for this example), you need to use the “Add” method of the “ListObjects” collection. See the full VBA code below…

'creates a table based on current selection
ActiveSheet.ListObjects.Add(xlSrcRange, Range(Range("$A$1").CurrentRegion.Address), , xlYes).Name = "Table1"

Excel VBA Downloads

Download the Excel PivotTable macro practice workbook below:

How many Excel functions are in Excel 2016?

According to this Office article, there are 477 functions in Excel 2016. They are distributed this way:

Category Amount
Statistical 112
Math and trigonometry 80
Financial 55
Engineering 54
Compatibility 38
Text 33
Date and time 25
Lookup and reference 24
Information 20
Database 12
Logical 11
Cube 7
Web 3
Add-in and Automation 3
Total 477

The new Excel 2016 functions are shown below:

Function name Category Description
IFS Logical Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
SWITCH Logical Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned.
FORECAST.ETS Statistical Returns a future value based on existing (historical) values by using the AAA version of the Exponential Smoothing (ETS) algorithm
FORECAST.ETS.CONFINT Statistical Returns a confidence interval for the forecast value at the specified target date
FORECAST.ETS.SEASONALITY Statistical Returns the length of the repetitive pattern Excel detects for the specified time series
FORECAST.ETS.STAT Statistical Returns a statistical value as a result of time series forecasting
FORECAST.LINEAR Statistical Returns a future value based on existing values
MAXIFS Statistical Returns the maximum value among cells specified by a given set of conditions or criteria
MINIFS Statistical Returns the minimum value among cells specified by a given set of conditions or criteria.
CONCAT Text Combines the text from multiple ranges and/or strings, but it doesn’t provide the delimiter or IgnoreEmpty arguments.
TEXTJOIN Text Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.

The functions that were renamed and moved to a different category are shown below:

Function name Category Description Note
BETADIST Compatibility Returns the beta cumulative distribution function In Excel 2007, this is a Statistical function.
BETAINV Compatibility Returns the inverse of the cumulative distribution function for a specified beta distribution In Excel 2007, this is a Statistical function.
BINOMDIST Compatibility Returns the individual term binomial distribution probability In Excel 2007, this is a Statistical function.
CHIDIST Compatibility Returns the one-tailed probability of the chi-squared distribution  In Excel 2007, this is a Statistical function.
CHIINV Compatibility Returns the inverse of the one-tailed probability of the chi-squared distribution In Excel 2007, this is a Statistical function.
CHITEST Compatibility Returns the test for independence  In Excel 2007, this is a Statistical function.
CONFIDENCE Compatibility Returns the confidence interval for a population mean In Excel 2007, this is a Statistical function.
COVAR Compatibility Returns covariance, the average of the products of paired deviations In Excel 2007, this is a Statistical function.
CRITBINOM Compatibility Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value In Excel 2007, this is a Statistical function.
FDIST Compatibility Returns the F probability distribution In Excel 2007, this is a Statistical function.
FLOOR Compatibility Rounds a number down, toward zero In Excel 2007 and Excel 2010, this is a Math and trigonometry function.
FTEST Compatibility Returns the result of an F-test In Excel 2007, this is a Statistical function.
GAMMADIST Compatibility Returns the gamma distribution In Excel 2007, this is a Statistical function.
GAMMAINV Compatibility Returns the inverse of the gamma cumulative distribution In Excel 2007, this is a Statistical function.
HYPGEOMDIST Compatibility Returns the hypergeometric distribution In Excel 2007, this is a Statistical function.
MODE Compatibility Returns the most common value in a data set In Excel 2007, this is a Statistical function.
NEGBINOMDIST Compatibility Returns the negative binomial distribution In Excel 2007, this is a Statistical function.
NORM.INV Compatibility Returns the inverse of the normal cumulative distribution  In Excel 2007, this is a Statistical function.
NORMDIST Compatibility Returns the normal cumulative distribution In Excel 2007, this is a Statistical function.
NORMSDIST Compatibility Returns the standard normal cumulative distribution In Excel 2007, this is a Statistical function.
NORMSINV Compatibility Returns the inverse of the standard normal cumulative distribution In Excel 2007, this is a Statistical function.
PERCENTILE Compatibility Returns the k-th percentile of values in a range In Excel 2007, this is a Statistical function.
PERCENTRANK Compatibility Returns the percentage rank of a value in a data set In Excel 2007, this is a Statistical function.
POISSON Compatibility Returns the Poisson distribution In Excel 2007, this is a Statistical function.
QUARTILE Compatibility Returns the quartile of a data set In Excel 2007, this is a Statistical function.
RANK Compatibility Returns the rank of a number in a list of numbers In Excel 2007, this is a Statistical function.
STDEVP Compatibility Calculates standard deviation based on the entire population In Excel 2007, this is a Statistical function.
TTEST Compatibility Returns the probability associated with a Student’s t-test In Excel 2007, this is a Statistical function.
VAR Compatibility Estimates variance based on a sample In Excel 2007, this is a Statistical function.
VARP Compatibility Calculates variance based on the entire population In Excel 2007, this is a Statistical function.
WEIBULL Compatibility Calculates variance based on the entire population, including numbers, text, and logical values In Excel 2007, this is a Statistical function.
ZTEST Compatibility Returns the one-tailed probability-value of a z-test In Excel 2007, this is a Statistical function.
EXPON.DIST Statistical Returns the exponential distribution In Excel 2007, this is a Statistical function.
FORECAST Statistical Returns a value along a linear trend In Excel 2016, this function is replaced with FORECAST.LINEAR as part of the new Forecasting functions, but it’s still available for compatibility with earlier versions.

 

Pivot Table Excel

This short story shows how I gained the respect and trust of 3 departments in the big company I worked for.

Before I knew how Pivot Tables in Excel worked, I found a big opportunity to become valued at my office…

…I noticed that one single Excel table contained all the data that the Planning, Cost, and Engineering department needed.

This was a huge realization at the time…

…in a way, the data and its insights were dormant!

The Planning department needed the length of pipes by diameter and by zone, the Cost department needed pipes length by diameter for suppliers, the Engineering department needed the total length of pipes to plan the work of drafters.

And nobody was providing these insights in a timely manner!

I knew Pivot Tables were good for mining data but I didn’t understand how they worked yet.

And the table was monstrous…

I have always known the benefits of trying new things so I created a Pivot Table report out of the table, and started playing with it.

I asked to each department head, what do you want to know about this data?
The table contained tens of fields but I just focused on the fields they care about: Pipe length, Pipe zone, Pipe Diameter, etc.

For example, Mirian, from the Planning department wanted to know the distribution of pipes by zone, so I dropped those fields into the Pivot Table Excel canvas. And patterns started emerging. I did a lot of trial and error until the pattern she wanted was established.

Do it now!

Pick your company data and see how you can help different people, maybe your boss and the boss of your boss. Ask them what do they want to get out of this data? And they will tell you because people is craving for more understanding.

Drag and drop the most relevant category fields (Department, Pipe diameter, Name, Year, etc.) to the Row area of your PT report. Drag and drop the most important data fields (Length, Sales, Amount, etc.) to the Value area of your PT report. If you are not satisfied with the layout, move the Pivot Table Excel fields around. This is the way I did it and it worked because any layout you create is right (maybe not needed) and you can play safely until you find something that is needed.

The worst part was when they started asking me for new reports. My trial-and-error approach was a huge limitation because it was embarrassing playing with data when they were looking over my shoulder.

Hopefully I stumbled upon a simple grocery analogy that revealed the mechanics of Pivot Tables in Excel and you can grasp it in minutes from now.

If you’re looking for an “insider” shortcut guide to finally get comfortable with Pivot Tables in Excel, creating insightful Pivot Table Excel reports (consistently) that will supercharge your data mining and presentation skills, then you’ve got to sign up for my free Excel Pivot Table Tutorial.

How to do a Pivot Table in Excel

The reason why many Excel users never learn how to do a Pivot Table in Excel is because they don’t understand why they should learn Pivot Tables neither what is a Pivot Table.

If you are still afraid of Pivot Tables or you want to establish a solid foundation to your data analysis journey, in this free Excel Pivot Table tutorial, you will learn the fundamentals first:

  1. Why you should learn how to do a Pivot Table in Excel
  2. What is a Pivot Table
  3. Learn how to do a Pivot Table in Excel

I was one of those who was not aware of the psychology and mechanics of Excel Pivot Tables.

Why you shoud learn how to do a Pivot Table in Excel

But I was determined to definitely get it because I realized Pivot Tables were a form of fast Excel dashboarding and fast data analysis and understanding.

Create Excel dashboards fast

You can create summarized tables out of big tables with a couple of clicks. You can add several views of the same data set (e.g. by department, by quarter, by region, sum, average, count, etc). You can also add charts and dashboard controls such as slicers and timelines  without using VBA, without writing any single vlookup or index and match formula.

I created the plain dashboard you see below in a matter of 5 minutes!

Dashboard using a Pivot Table

Analyze and understand data

For example you can turn a big 10K rows Excel table into a tiny Excel table packed with meaningful information because a Pivot Table finds patterns and show them to you. For example, in the table below, the thousands of “Delivery Truck” entries become a single one with a subtotal.

Simple Pivot Table example

 

When you learn how to do a Pivot Table in Excel, you become able to include in your analysis only the data that matters most to you. For example, if you are a manager, you can focus on departments or products or on both categories at the same time. You can focus your analysis even further by looking at a date range only. If you are a salesman, you can focus your analysis on the product you sell and narrow the date range to your sales period.

If you want to look at your data from another vantage point, you can do it easily because you can drag and drop any data field you want and change your Pivot Table report instantly.

Well, if you don’t know how to do a Pivot Table in Excel yet, then you need to learn what is a Pivot Table first. If you want to get Pivot Tables explained to you in a meaningful manner, you might find this explanation useful:

What is a Pivot Table

You are about to discover a simple analogy that will make Pivot Tables easier to learn and use.

If you don’t know what is a Pivot Table, if you are afraid of learning and using Pivot Tables, if you have only a basic idea of Pivot Tables in Excel and don’t know what to expect when you drop the fields to the PivotTable report, or if you just want to understand more about Pivot Tables, then keep reading…

…because this grocery analogy I stumbled upon will show you how to do a Pivot Table in Excel because it will make PTs simpler for you, and it will boost your reporting and dashboard skills, starting today.

It’s a shame…

I used to create Pivot Tables reports through trial and error but I was afraid my coworkers or boss would ask to me: how did you that? because I didn’t know exactly how my Pivot Table reports were generated, how the fields affected the layout of the report, and so forth.

I never had confidence in using Pivot Tables because it was a sort of hope-reporting. Every column, row, page, or value field I dropped into the fields’ area, produced unexpected results.

Everything changed when I discovered the Pivot Table relationships . Pivot Tables Paradigm!

A Pivot Table report is like a box. See below:

How to do a Pivot Table in Excel - Pivot table report explained

Let’s imagine the box contains oranges, apples, lemons and bananas. This is your source data.

How to do a Pivot Table in Excel - Pivot Table explained using a simple example

With this bunch of fruits as your source data, you can ask questions like: how many oranges, apples, lemons and bananas are in the box? You can achieve this by counting each type of fruit.

To better find the answer, you organize the box by fruit type as you see below:

Pivot Tables explained - Counting

Here’s the answer and a nice subtotal:

 

Pivot Tables explained - Counting

You can also organize the fruits according to its type: citric and other.

Pivot Tables explained - Counting and grouping

If each fruit have a price tag, then you will be able to answer questions like: how much the oranges, apples and bananas are?, how much the box is?, how much the citrics are?

To answer the question: how many green and mature oranges contain the box?, you would need to compartmentalize the box by fruit state: mature and green oranges.

Very simple, right?

To answer the question: how many big or small green/mature oranges are in the box?, you would need to compartmentalize the box by fruit size.

Would you compartmentalize the entire box again? Or just the existing compartments?

You would separate green oranges into small and big and mature oranges into small and big.

In other words, you compartmentalize the already compartmentalized box.

This simple principle applies to Excel Pivot Tables…

Learn how to do a Pivot Table in Excel

You simply group the data source on your Pivot Table report by any of the fields you choose.

The first item you add to your Pivot Table fields area (box) is what’s in your box (data source), the second field is how you compartmentalize the existing box, the third field is how you compartmentalize the existing already compartmentalized box, and so on.

For example, if you have a table with these fields: Department, Brand, Model, Salesman, Date, Units sold, Amount, and you add the “Department” field to the fields area, then you will answer questions like how much sales by department.

To answer the question how well each brand performed by department?, then you would add “Brand” to the fields’ area so you compartmentalize “Department”. If you add Brand, then you compartmentalize department by brand, if you add salesman, you split the brand compartment by brand, and so on.

You now should be able to drag and drop the Pivot Table report fields with confidence.

If you are more comfortable with using Pivot Tables in Excel, and want to get deeper into using Pivot Tables, you can definitely learn how to do a Pivot Table in Excel in my Free Excel Pivot Table tutorial

Or you can explore these good Pivot Table tutorials:

http://www.contextures.com/CreatePivotTable.html

http://www.excelfunctions.net/Excel-Pivot-Table-Tutorial.html

How to reference cells dynamically using VBA

The best way to go through cells dynamically is using the Cells object inside a loop. This way you can refer to a different cell on each pass of the loop.

Use a simple counter variable (you can name it whatever you want), which is increased or decreased on each loop pass. Here are some examples…

Using a ForNext loop

Use For Next when you know the starting row/column as well as the last row/column.

For counter = 2 To 5

Cells(counter, 5).Value = Cells(counter, 3).Value * Cells(counter, 4).Value

Next

Using a DoWhile loop

Use Do While when you either don’t know the starting row/column or the last row/column.

counter = 2

Do While Cells(counter, 1) <> ""

Cells(counter, 5) = Cells(counter, 3).Value * Cells(counter, 4).Value

counter = counter + 1

Loop

Using a DoUntil loop

Use Do Until when you either don’t know the starting row/column or the last row/column.

counter = 2

Do Until Cells(counter, 1) = ""

Cells(counter, 5) = Cells(counter, 3).Value * Cells(counter, 4).Value

counter = counter + 1

Loop

How to run a macro from a button

You can run an Excel macro by clicking any Excel object you can imagine: text box, shape, picture, chart, etc. See below the main Excel objects you can use…

Chart, text box, smart art, picture, etc.

After storing your Excel macro for future use, you can attach your macro to any button inside any workbook.

Here’s the 3-step process to do it…

1) Create the button in your sheet

Go to the ribbon Insert>Illustrations>Shapes>then, add any of the available shapes. See the picture below…

Add any shape to assign a macro to

You can also insert any other type of illustration like: Picture, Clip Art, SmartArt, etc. You can use any already-created button. Additionally, you can also use other Excel objects like charts, equations, etc.

Once the shape/object is in your sheet, you are ready to link an Excel VBA macro to it.

2) Assign a macro to your button/object

Right click over the existing shape or object and then choose “Assign Macro…” on the contextual menu. See the picture below…

Assign macros to shapes, clip art, smart art, charts, etc.

Then, specify the source location of your macro in the “Macros in:” drop-down list of the “Assign Macro” dialog (“This Workbook”, “Personal Macro Workbook” or any specific workbook). See the picture below…

Choose the location of the macro and click OK

Important notice: if your macro is not stored centrally in the Personal Macro Workbook or as an Add-in, you need to open the macro source-workbook before you assign those macros to any button. Read “how to store Excel macros for future use

Then, select the Excel macro you want to be attached to the button, and then click OK.

Your Excel macro is now attached to your button!

You can also create a new macro that will be automatically attached to the current button. Just click “New” or “Record…” in the “Assign Macro” dialog.

You are almost done, just a final step to make your Excel macro button more reusable…

3) Name your Excel macro button meaningfully

Don’t use names as “Click here” or “Macro 1”, use meaningful names that describe the macro purpose, for example: “Delete selected columns”, “Insert financial formula”, etc. This way you will use the button naturally and without hesitation.

To do it, proceed this way…

Right click over the shape (Text Box, Shape, Equation), then click “Edit text”, then enter your text.

In the case of pictures and charts you can add a Text Box to describe the assigned-macro, it could be something like “click the picture to format columns”, etc.

And be creative with the layout of the button, have fun. Select the shape and then go to Drawing Tools>Format>Shape Styles, there you can change the shade, color, borders, etc.

Here are some examples of Excel macro buttons…

Format your macro buttons in Excel using colors, borders, 3d effects, etc.

Have button ideas to share? Let us know at the comments section below…

How to store the Excel macros you create or record for future use

If you acquire a brand new car and ignore the location of the keys, you couldn’t open the doors to get in, nor drive the car. In a sense, when you don’t know the location of your recorded Excel macros or written Excel VBA macros you won’t be able to get in the VBA editor to edit the Excel macro or run the macro.

Knowing the location of your Excel VBA macros is essential if you want to create, edit and run your Excel macros!

If you ask some of the questions below, then this Excel VBA article is for you…

  • Will my macro be there if I close my workbook or Excel?
  • How can I share my Excel macros with others?
  • How to use the Excel macros from others?
  • How to use a macro I wrote or recorded before?
  • How to make my macros available on other Excel VBA workbooks?
  • How to create a macro in Excel?
  • How to write a macro in Excel?

It’s very simple!

You just need to be aware of some details I will share with you here…

You can think on a workbook as a box with two compartments, one for spreadsheet-data and the other one for VBA-data. In other words, an Excel VBA workbook might contain spreadsheet-data and VBA-data, see the illustration below…

Excel files contain data and vba code

Now, you might ask, “if an Excel macro is stored inside a VBA workbook, will it be available to other workbooks?”

That’s a good question.

The answer is NO, a macro is only available to the same workbook where it was created, but you can make it available to other workbooks, you need some tricks you will learn in just a moment.

First let’s explore the difference between…

Local Excel macros vs. central Excel macros

Let’s suppose you create a macro in Excel VBA, which is a loan calculator app and it works this way: first you enter the arguments like loan amount, annual interest rate, loan period in years, etc, then you hit calculate, finally the Excel macro generates the monthly payments in the same sheet.

You don’t want to run this macro on other workbooks because the relevant data is entered in the given loan sheet. This is a local macro.

Some examples of local macros might be:

  • Fixed-layout constrained macros. For example, a macro that works upon a fixed table scheme or report
  • Sheet-specific macros. For example, a macro that uses the data of a particular sheet or sheets
  • One-time macros
  • Etc.

Now think for a moment in a macro you created in Excel to format a raw sales table you receive every week. The macro deletes some columns, sorts the table by invoice number, adds some headings here and there, etc. Every time it does the same to the file you receive weekly.

You want to use this macro each time you receive new data. This macro is required to be centrally stored so is available on each upcoming sales workbook.

Some examples of central macros might be:

  • Dynamic macros that work on different sheet and arrays and layouts. E.g. a macro to format a report, write custom formulas on columns with dynamic rows, etc. You can create dynamic macros if you use loops and conditional statements. You can also record dynamic macros if you learn how to place the cursor and use relative references
  • Repetition routines like: converting date format, inserting a given formula in the current cell, etc.
Below you will explore how to make the macros you create in Excel (or record) available to the same workbook or to any other workbook (local or central)…

Location of recorded Excel macros

If you know how to record a macro in Excel, then you know that when you hit stop, your recorded macro is somewhere in your hard drive and this location depends on what you specified in the “Record Macro” dialog, see the picture below…

Store macro in: this workbook, personal workbook
Record macro dialog

Important notice: by default, your recorded Excel macros are named Macro1, Macro2, MacroN and stored in Module1, Module2, ModuleN of the specified workbook.

Many Excel users think that once they learn the art and science of writing macros in Excel, the Excel recorder is not needed anymore, but knowing how to record a macro in Excel is critical in some cases. Here are the guidelines for taking full advantage of your recorded Excel macros…

Make your recorded macros available to the same workbook only (local)

1) Choose “This Workbook” or “New Workbook” in the “Store macro in:” drop down list of your “Record Macro” dialog (see “Record macro dialog” picture above).

2) Then record your Excel macro as normal.

Run that macro by specifying the source workbook where you recorded the macro (it should be open). See the picture below…

All the macros in the current workbook
Macro dialog

Make your recorded macros available to any workbook (central)

1) Choose “Personal Macro Workbook” in the “Store macro in:” drop down list of your “Record Macro” dialog.

2) Then record your Excel VBA macro as normal.

You don’t need to open the source file to run your central macros because the “Personal Macro Workbook” is always loaded. See the picture below…

See all the macros that are in the personal workbook

What’s the Personal Macro Workbook?

It’s a hidden Excel 2007 Binary File workbook (.xlsb) that is always open so you can run its macros all the time. See the picture below…

The personal macro workbook is always loaded

If you edit any macro of the “Personal Macro Workbook” and close Excel, you are presented with the message below (This is a handy proof this book is open behind the scenes)

Save the changes you made to the macros of the personal macro workbook

The “Personal Macro Workbook” is stored by default in this location: C:/Users/Administrator/AppData/Roaming/Microsoft/Excel/XLSTART

Important notice: show system folders this way (system folders are hidden by default). In the Windows explorer window, go to  Organize>Folder and search options>View>Hidden files and folders>Show hidden files, folders, and drivers. See the picture below…

Show hidden files

Location of the Excel macros you create

You are free to put the Excel macros you create or write using VBA anywhere inside your Excel VBA compartment (VBA editor). You can store your Excel macros in any module and in any Sub procedure. For example: you can create a module called MyFormulas and create the macros (procedures) MyAdvancedFormula1, MyAdvancedFormula2, MyFinancialFormula1, etc. Or you can create a module called MyCommonRoutines and write the macros: FormatMyReport, DeleteMyColumns, etc.

Here’s an illustration of the location of your macros inside the VBA editor (compartment)…

Excel macros are stored this way in a workbook

This is how your Excel macros are stored inside the VBA editor…

Excel macros are stored in modules and procedures

You can also put the created macros in the “ThisWorkbook” VBA module or in any VBA Sheet module (under the folder Microsoft Excel Objects) of the VBA Project explorer pane.

Important notice: Sub procedures must be Public in scope so you can call them from outside the source module, e.g. from another module, from a button, shortcut, toolbar, ribbon, etc.

Make your written Excel macros available to the same workbook only (local)

Just write your macros in any module of your VBA editor (workbook compartment). It will be available next time when you open that workbook.

Your local macros will be listed in the Macro dialog, after you select “This workbook”, “All Open Workbooks” or any specific workbook in the “Macros in:” drop-down list. see the picture below…

Choose what macro to show in "macros-in" list, thisworkboo, all open workbooks, personal workbook, specific workbook

Make your written Excel macros available everywhere (central)

If you want to run a macro from any other workbook, see the picture below…

Assign macros to ribbon commands

You can run a macro in Excel in several ways…

1) Write your macro in the Personal Macro Workbook, it will be available next time on any workbook you open.

This method is not recommendable because you can’t control the location of the master macros book and you can’t organize your macros in meaningful files., e.g. a workbook for formatting macros, another one for calculations, etc.

The best way to do it is by storing your workbook as an add-in. Here’s how to do it…

1) Write your macro in any given VBA module of a new or existing workbook

2) Save your book as an Excel Add-in, go to Office button/File>Save As>Save as type:>set the file type as add-in, see below…

  • .xlam – Excel Add-in workbook format in Excel 2007+
  • .xla – Microsoft Excel Add-In format in Excel 97-2003

3) Go to Office button/File>Options>Add-Ins>Manage:>Excel Add-ins>Go

4) Then in the Add-ins dialog activate the desired add-in file, if is not listed here, choose “Browse…” and locate your add-in file

Load any Excel addin or browse it

Now you can call your macros using any of the methods below…

  • Macro dialog
  • Shortcut
  • Button
  • Toolbar or Ribbon
  • Triggered by event
  • UDF (User defined function)

I hope this article helps.

How to find the code of a VBA macro in a workbook

Are you asking where the macros in a workbook are?

CTRL + F11, show the VBA interface

Well, you should know that Excel stores VBA stuff: macros, modules, user forms, etc. as well as spreadsheet data inside workbooks.

How does this work? Here’s a quick explanation…

Excel files contain data and vba code

To access your macros VBA code you need to open the workbook compartment that contains VBA data, this is the VBA interface.

There you will find the macros VBA code (procedures) in modules, see the picture below…

Sub procedures

Launch the VBA editor this way…

Excel 2007/2010 users…

The fastest way to show the VBA interface is by using the shortcut ALT+F11, type it when you are in the workbook environment.

You can also use the Ribbon. Excel 2007/2010 brings the “Developer” tab hidden by default. Activate it this way…

If you are using Excel 2007 go to the Microsoft Office button>Excel Options>Popular>”Show Developer tab in the Ribbon”>OK

If you are using Excel 2010 go to the Microsoft Office button>Excel Options>Customize Ribbon, and then activate the “Developer” tab, see the picture below…

VBA interface, Excel options, customize ribbon

Once the Developer tab is shown in the Excel ribbon, activate it and click on the “Visual Basic” toolbar.

Another handy way to show the VBA editor is by right clicking over any sheet tab of your workbook and choosing “View code”.

You can also go directly to any given macro by going to the Ribbon: Developer>Code>Macros>Select your macro>Edit. See the picture below…

Select the macro and click edit to show the VBA code

Excel 97-2003 users…

Click ALT+F11 or go to Tools>Macro>Visual Basic Editor.

The same right click technique applies to Excel 2003, just right click over any sheet tab of your workbook and choose “View code”.

You can also go directly to any given macro by going to the Tools>Macro>Macros>Select your macro>Edit.

How to record a macro in Excel that works dynamically on different array sizes

You want to record dynamic Excel Macros that work on variable-size arrays, right?

Maybe you recorded/created an Excel VBA Macro and worked awesome this time, but you are wondering if it will work the same way the next time.

In just a moment you are about to learn how to record a dynamic Excel Macro using relative references and some mouse techniques.

If you still don’t know how to record a macro in Excel don’t be afraid because recording Excel Macros is simple!

Here’s how to do it…

  1. How to record a macro in Excel that works dynamically on different array sizes and layouts
  2. How to use a dynamic macro you recorded in Excel

How to record a macro in Excel that works dynamically on different array sizes and layouts

  1. Put your cursor at the starting position of your raw data (Don’t include earlier mouse movements in your Macro)
  2. Launch the Record Macro dialog
  3. Assign a shortcut to your Macro (so you can run it repeatedly easily)
  4. Click Ok on the Record Macro dialog
  5. Use relative references (On Excel 2003 do it this way: click the Relative Reference button on the Stop Recording toolbar. On Excel 2007 do it this way: go to View>Macros> Macros>Use Relative References. On Excel 2016 go to Developer>Code>Use Relative References). Do this before you perform any movement of the Excel cursor around your spreadsheet
  6. Record one instance of the dynamic Excel Macro (if you have one thousands blocks of data, record just one)
  7. Stop the Macro when your mouse is at the starting position of your next block of data (corresponding position to the location you started on step 1 of this tutorial)

Congratulations! You have recorded a dynamic Excel Macro.

Now it’s time to use the dynamic macro you just recorded.

How to use a dynamic macro you recorded in Excel

Use your dynamic Macro this way:

Put your cursor at the starting position and then press the Macro shortcut. If you want to process N blocks of data, press CTRL + YOURKEY N times, or keep your shortcut pressed and the Excel Macro will be repeated effortlessly.

For instance, you can use this dynamic macro recording technique to convert raw data like this one:

How to record macro in Excel dynamically

 

Into table layout like this one:

How to record a macro in Excel that works on different array sizes

Your recorded Macros will work for one row or for one million rows, for one column or one hundred columns.

Want to go to the next level?

Then you need to learn how to write Excel Macros from scratch.