"Deadline-Beating Blueprints Trusted by Intermediate and Advanced Excel Users"

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.

Quick tip to make your recorded Excel macros super fast

Here’s the tip.

Turn off screen updating.

Doing this is like jumping to the desired movie scene instead of watching a chain of quick-motion sequences before you watch it.

When you do this, your macro will not spend computing resources to update the screen at every action it performs.

  • Shifting sheets (this does not occur if you implement tip 2 mentioned above)
  • Range selections  (this does not occur if you implement tip 2 mentioned above)
  • Chart creation
  • Table creation
  • and other outputs

How do you do that?

It’s very simple…

Write this instruction at the beginning of your macro (after the “Public Sub ()” line)…

“Application.ScreenUpdating = False” (without quotation marks)

And this one at the end of your macro (before the “End Sub” line)…

“Application.ScreenUpdating = True” (without quotation marks)

How to communicate with other applications using VBA?

There are a lot of benefits of interacting with other applications using VBA language…

  • You can use data generated somewhere in other application, for example: creating new PowerPoint slides using Excel charts and tables
  • You can perform commands that don’t exist in your application, e.g. inserting Excel calculations in a Word document
  • You can expand the functionality of your program, e.g. you cannot make presentations using Excel, so you send the charts, tables and figures to Power Point; sending 100 emails through Outlook using data from a 100-row Excel table
  • and much more

How do you do it?

Let me restate our main question to “How to communicate with other persons in the world using English?”

Can you make any other person in the world act on your instructions?

Well, I am not talking here about influence, I am talking about meaning…my point is, will they understand your commands? You can’t command other people, at least they speak your language (English for our example). The good news is that English is a worldwide used language, non-native-speakers know it so communicating with others is relatively easy.

Let’s get back to our primary question “How to communicate with other applications using VBA?”

You can’t command other applications, at least they speak the programming language you/Excel talks (Visual Basic); Yes, Excel speaks Visual Basic programming language.

The good news is that Visual Basic (VB) is a very popular programming language and many applications speak it. So when Excel talks, VBA-enabled applications will get it, easily.

How is this possible?

Every app has its own objects, e.g. Power Point has slides, animations, etc; Outlook has emails, contacts, etc; Excel has sheets, charts, formulas, etc. You can manipulate those app objects using the user interface, including your mouse and keyboard; e.g. you can add a new slide to an existing PowerPoint presentation, you can add a title to a slide, etc. In Excel, you can add a new sheet, write a new formula in a cell, change the background color of cells, etc.

Ok John, but how those apps make their objects available to other apps?

In a sense, every country makes its citizens available to others through a phone directory, if you know a person’s phone  number you can call it. At the same time, every VBA-enabled application makes its objects available to others in the form of a VBA objects library; for example an Excel sheet is a WorkSheet object, a Pivot Table is a PivotTable object, etc. If you know an object name and syntax you can use it.

Here’s a simple 3-step roadmap to communicate with other applications using VBA…

1) First the first…

1.1. Master the app you want to communicate with

You will hardly control Power Point with VBA if you have never created a presentation. To create efficient VBA code you should be proficient using the real objects, e.g. slides, emails, etc.

1.2. Be sure your app speaks VBA language

Your recipient should be ready to receive your instructions, he/she should speak English. At the same time, your to-be-controlled app should understand VB. Your Office package as well as other Microsoft applications supports VBA. Below is a list of some of the MS programs that are VBA-enabled applications…

  • Office: Word, Excel, Power Point, Outlook, Access, etc.
  • Office for Mac
  • Microsoft project
  • Microsoft Front Page

There are other popular non-Microsoft programs that are VBA-enable applications…

  • AutoCAd – Technical drawings and design; mostly used by civil engineers, mechanical engineers and architects
  • ArcGIS – Geographic information systems; mostly used by environmental engineers, planners, etc.

Here’s the full list of VBA-enabled applications.

1.3. Get the app VBA documentation.

What VBA syntax is needed to open a new Word document? You need to know it; what VBA syntax is needed to add a new slide to an existing Power Point presentation? You need to know it.

You can easily obtain all the VBA information in the Help section of your app; you can also use the objects browser to explore the app objects from within Excel.

These are the main things you can get…

2) Connect with your VBA-enabled application

When you make an international call, the first thing you need to do is entering the country code, e.g. if you want to call to a landline in Ecuador you need the 593 prefix in addition to the phone number.

Dialing 52634232 won’t communicate you with anybody.

While dialing this way will do it…


Think in the country code as a “master key” that opens the gate of every landline phone in the target country.

If you want to communicate with an external VBA-enabled application, you need a key too; this key is activated when you choose the app library in the “References” dialog. Learn more below…

2.1. Activate the VBA-enabled app library

Activate the VBA-enabled app library by launching the VBA interface from within Excel (CTRL+F11), then by going to: Tools>References…

E.g. In the dialog below I activated the libraries of the programs: Access, Outlook, PowerPoint and Word…


You can also reference missing app libraries using the browse button of the “References – VBAProject” dialog. App libraries have a .dll extension.

When you try to access an international phone number without the master key (country code), you get a message like: “Enter the country code first and try again”; the same happens in VBA, you cannot access external app objects without activating its library first. You will get an error like the one below…

User-defined type not defined

Important notice: each VBA-enabled application library is installed at the moment you install the program in your computer.

2.2. Create a VBA-enabled app object

Using the master key (country code) is not enough, you need to dial the phone number so you establish communication with the right house and so with the right person. The same in VBA, you need to create an application VBA master object (phone number) so you can access every VBA object member (persons in the house), do it this way…

This line of code establishes communication with Word.

Set MyWordApp = CreateObject("Word.Application")

This line of code establishes communication with Outlook.

Set myOutlookApp = CreateObject("Outlook.Application")

This line of code establishes communication with Excel (from other application)…

Set MyExcelApp = CreateObject("Excel.Application")

3) Manipulate the VBA-enabled app objects

You are doing it very well, here is the 3-step roadmap…

  1. Enter the country code so you can communicate with it, in VBA terms, enable the app VBA library so you can access the other app
  2. Dial the phone number so you can start talking with the persons in the house, in VBA terms, create the app object so you start manipulating its objects
  3. It’s time to communicate with the persons in the house, in VBA terms, manipulate any app VBA object by using the objects hierarchy, object properties and methods, you can also use any VB command

You can see all these VBA concepts in action below…

VBA macro examples that manipulate other applications

VBA macro example 1 – Create a new PowerPoint presentation slide with an Excel chart

After activating the PowerPoint library, you create a PowerPoint app object (code line 8 below) and then you manipulate PowerPoint objects (code lines >8)…

Here’s the macro code…

Public Sub PasteCharttoSlide()
'Pastes the active chart into a new power point presentation
Dim MyPowerPointApp As PowerPoint.Application
Dim MyPresentation As PowerPoint.Presentation
Dim MySlide As PowerPoint.Slide

'Creates instance of PowerPoint
Set MyPowerPointApp = CreateObject("Powerpoint.Application")
MyPowerPointApp.Visible = True

'Creates a presentation
Set MyPresentation = MyPowerPointApp.Presentations.Add

'Adds one slide
Set MySlide = MyPresentation.Slides.Add(1, ppLayoutBlank)

'Copies active chart as a picture
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture

'Pastes chart into slide
MyPresentation.Slides(1).Shapes(1).Left = 100
MyPresentation.Slides(1).Shapes(1).Top = 100

'Saves the presentation
MyPresentation.SaveAs "C:/MyPPT.ppt"
MyPowerPointApp.Quit ' close Power Point application
Set MyPowerPointApp = Nothing 'frees memory
Set MyPresentation = Nothing

End Sub

This is the end result…

Send any Excel data like tables, Pivot tables, etc.

Download the Excel macro file below…


VBA macro example 2 – Send an email from Excel

After activating the Outlook library, you create an Outlook app object (code line 10 below) and then you manipulate Outlook objects (code lines >10)…

Public Sub SendEmail()
'www.innateexcel.com - "Make Excel your second nature"
'John Franco
'Feb 08 2012

'Sends an email using the data of the current row (name in column A, email in column B,sales in column D)
Dim myOutlookApp As Outlook.Application
Dim myeMail As Outlook.MailItem

Set myOutlookApp = New Outlook.Application 'sets a new instance of Outlook application
'Set myOutlookApp = CreateObject("Outlook.Application")
Set myeMail = myOutlookApp.CreateItem(olMailItem) 'sets a new email item

 SalesAmount = Cells(ActiveCell.Row, 4)

 With myeMail
 .Subject = Cells(ActiveCell.Row, 1) & ", your sales amount..." 'sets the subject line, e.g. "Laura, your sales amount"
 .To = Cells(ActiveCell.Row, 2) 'reads the salesman email from active row
 .body = "Dear " & Cells(ActiveCell.Row, 1) & "Your sales amount is: " & Cells(ActiveCell.Row, 4) 'sets the body of email
 .send 'sends email
 End With

 Set myOutlookApp = Nothing 'frees memory
End Sub

This is the result…

Download the Excel macro file below…



The power of these Excel VBA commands/functions

I just finished a NEW Excel VBA video that shows you:

  • How to find what Excel VBA commands (Visual Basic commands) are available for you to use for a particular situation
  • How to handle the vastness of Excel VBA keywords efficiently
  • And much more…

Stop missing all the Excel VBA tools that you can use today to write your Excel macros faster.

It’s just 12 min long. Watch it below…

And download the list of all the Excel VB commands below…


Excel VBA productivity shortcuts – FREE download

When you want to save time doing little things here and there in Excel VBA Macros, shortcuts are extremely helpful.

The problem with most “Excel VBA productivity shortcuts” is that they are laid out in a way that is difficult to navigate and use.

TODAY you will get a handy and FREE Excel VBA shortcuts report that is laid out in a way that you can clearly see what the shortcut does FIRST, and then see the actual shortcut itself so that you are not tirelessly searching through the list.

This will also empower you to find the VBA shortcuts that you really need INSTEAD of compiling a list of things that you never use.

Click the link below to download my Excel VBA productivity shortcuts report for absolutely nothing:


I know you will enjoy this list and that it will help you greatly. Even if you are not using Macros that much now, you can keep it for future reference.

How to make your R1C1 VBA formulas easier to Read

You hate those horrible R1C1 formulas that your Excel recorder produces, right? Like this one…

Range(“B11”).FormulaR1C1 = “=SUM(R[-9]C:R[-3]C)”

Me too!

And the reason we hate that notation that much is because we are so accustomed to work with “A1” formulas in Excel and because this style of cell referencing is friendlier.

Here’s a nice trick (Convert formula method) to convert R1C1 formulas into A1 formulas easily.

For example: to convert this awful formula =VLOOKUP(RC[-1],R[-2]C[-6]:R[2]C[-4],2,1) into its “A1” equivalent, use the ConvertFormula statement this way…

1) Open the VBA editor by pressing CTRL+F11

2) Launch the Immediate Window

Do it this way…Press CTRL+G or go to View>Immediate Window…
Press CTRL+G

3)In the Immediate Window enter:

debug.print Application.ConvertFormula( formula:=”=YOUR_R1C1_FORMULA_GOES_HERE”, _
fromReferenceStyle:=xlR1C1, toReferenceStyle:=xlA1)

For our example today, enter this…

debug.print Application.ConvertFormula( formula:=”=VLOOKUP(RC[-1],R[-2]C[-6]:R[2]C[-4],2,1)”, _
fromReferenceStyle:=xlR1C1, toReferenceStyle:=xlA1)

You can use any Excel formula here
Immediate window, Application.ConvertFormula

4) and then press Enter

so you the immediate window returns =VLOOKUP(G16,B14:D18,2,1)

Press enter and get the results
Immediate window results

Did I leave something out? Please tell us your ideas in the comments section below…

How to Delete Duplicate Files Using Microsoft Excel

There was a time when a 40 megabyte hard disk was considered luxuriously big, but with today’s consumption of digital media and common file sizes in the gigabytes, modern terabyte hard drives can fill up fast. By filling up our space we don’t only cause a file management headache, if let get out of control it can even harm system performance.

One way we can keep our storage clean and uncluttered is to keep an eye on duplicates. By storing only unique files we can at least know we are not wasting any precious space.

Finding duplicates is not always easy, plus the solution often requires downloading a tool to do it. Well, good news. We have developed a tiny Microsoft Excel spreadsheet that can do the job for you!

Using the Spreadsheet

First, download the spreadsheet from here. You will see there are three tabs. The first lists the files, next we have a backup of the formulas just in case. Last we have these instructions.

  1. CTRL+SHIFT+L to list all the files on your “C:” hard drive in the ‘List of files’ tab.List of files
  2. Once the files are listed and the related data is in place, you can use Excel’s filter function to show files that appear more than once. Column C (labeled ‘how many times this file appears’) shows the count for the number of appearances for the file. Filter the sheet to show rows only where column C is greater than 1 to get a list of just the duplicate files.
  3. Use the standard Excel filter to show all the rows except those with 1 in column C.
  4. When only duplicate files are showing, sort the entire sheet on the filename column (B). You can also find duplicate files for a specific file extension by filtering all other file extensions (column D)
  5. Finally, if you want to delete or rename a file, select the full file path on column A and press CTRL+SHIFT+SDelete a file


Once you start digging into what Microsoft Excel can do you might be amazed. We hope this little tool will help you keep your digital life that bit more organized!

About the author

Yoav is the CEO of Cogniview, a company that creates PDF to XLS conversion software.

Prior to that, Yoav Ezer was the CEO of Nocturnus, a technology-centered software solution company.

For more Excel tips from Yoav, join him on Facebook or Twitter

How to Create Hyperlinks in Excel Formulas and Macros

If you have any spreadsheets where one sheet has some kind of list and the other sheet has some details on the items in the list, you will understand how confusing it can be navigating from one to the other. Microsoft Excel has the ability to create hyperlinks between items in two sheets?

In this article we develop a macro that creates a link between an item in a list and a corresponding worksheet.

To create the example we used two templates found in Microsoft Office Online

  • Employee Profile
  • Employee attendance tracker

We programmatically create the hyperlink from one cell to another cell so that the user can go from the attendance to the profile and back again.

How to Use the Macro

Download our example Excel file from here so you can follow along with us.

  1. In the employee profile sheet select the employee name cell.
  2. Press Ctrl-Shift-L
  3. A link to the the employee’s attendance sheet will be created.
  4. In the employee’s attendance sheet a link to the employee profile sheet will be created
    Link in Employee Attendance Sheet

How it works

The main trick of this macro is in the formula to create a hyperlink:

=HYPERLINK("[File Name]'Sheet Name'! Cell Name","Friendly Name")

Here we used a handy function called ColumnLetter in order to convert the Column number into a Letter. That code was taken from here: Convert an Excel column number to its string equivalent.


By hyperlinking between related information in your spreadsheets you could improve productivity and reduce errors by quite an amount. Anything that removes the hunting and searching process will allow your users to get right to the information they need. How else might you use this macro?

About the author

Yoav Ezer is the CEO of a company that produces PDF to XLS conversion software and also co-author of the technology and productivity blog Codswallop.

For more Excel tips from Yoav, join him on Facebook or Twitter.