John Franco 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:
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…
Figure 1: Source data for Pivot Table report
This would be the resulting PT report (a simple one):
Figure 2: simple Pivot Table report
If you do it by hand, this would be the process:
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…
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…
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:
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:
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…
Just for refreshing the location of Pivot Table fields, here’s the PT report layout:
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:
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…
Figure 5: Blank Pivot Table report, before adding fields
It’s time to add stuff to it…
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:
This is the full code I used above…
MyPTReport.AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Units Sold"), "Sum of Units Sold", xlSum
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
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
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"
Download the Excel PivotTable macro practice workbook below:
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. |
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.
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:
I was one of those who was not aware of the psychology and mechanics of Excel Pivot Tables.
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.
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!
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.
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:
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:
Let’s imagine the box contains oranges, apples, lemons and bananas. This is your source data.
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:
Here’s the answer and a nice subtotal:
You can also organize the fruits according to its type: citric and other.
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…
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
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…
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
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
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
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…
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…
Go to the ribbon Insert>Illustrations>Shapes>then, add any of the available shapes. See the picture below…
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.
Right click over the existing shape or object and then choose “Assign Macro…” on the contextual menu. See the picture below…
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…
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…
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…
Have button ideas to share? Let us know at the comments section below…
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…
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…
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…
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:
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:
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…
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…
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…
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…
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…
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)
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…
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)…
This is how your Excel macros are stored inside the VBA editor…
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.
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…
If you want to run a macro from any other workbook, see the picture below…
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…
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
Now you can call your macros using any of the methods below…
I hope this article helps.
Are you asking where the macros in a workbook are?
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…
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…
Launch the VBA editor this way…
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…
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…
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.
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…
Congratulations! You have recorded a dynamic Excel Macro.
Now it’s time to use the dynamic macro you just recorded.
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:
Into table layout like this one:
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.
Disclaimer:
*This site and the products and services offered on this site are not associated, affiliated, endorsed, or sponsored by Microsoft, nor have they been reviewed, tested or certified by Microsoft.
**All product and company names are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.
© 2009-2019 ExcelGuruBlueprints.com. All rights reserved.