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.

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…

593-52634232

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…

References...

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
MySlide.Shapes.Paste.Select
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…

{filelink=7}

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…

{filelink=8}

Resources

The Excel object model demystified

The reason why many Excel VBA beginners fail is that they try to learn Excel VBA without understanding the Excel object model first or even ignoring there’s such as fundamental VBA concept.

If you want to get started with Excel VBA and macros but are still wondering, “what the hell the Excel object model is”, then this simple Excel VBA lesson is for you because today you will finally discover:

  1. Why you need an Excel object model?
  2. What’s the Excel object model?
  3. How to control Excel VBA objects (members) in a hierarchy (Excel object model)
  4. Excel object model resources

Why you need an Excel object model?

You will understand the Excel object model using a simple analogy. In the picture below you see two rivers, 1 and 2, with fishes and the trees 1 and 2 with hanging fruits.

Excel object model - Objects identification and disambiguation

Please identify the fish #1 and fruit #2 in the picture above.

You quickly realize that you can’t find those objects until you know from what tree you want the fruit (tree 1 or 2) and from what river you want the fish (river 1 or 2). This simple problem arises when you deal with a collection of many trees containing a collection of many fruits, and a collection of many rivers containing a collection of many fishes.

To refer to the objects of collections (Excel has the collections of books, sheets, charts, etc.), we need something else. Just think for a moment in a phonebook with just first names…

  • Name, Phone, address
  • Joe, 111111111, Moriarty, NM
  • Joe, 22222222, Shiloh Ranch Rd Lancaster, SC
  • Joe, 23412233, Oakland Dr Lancaster, SC

You could not be sure if any particular Joe is your Joe. You need a sort of disambiguation attribute. In this particular case a last name attribute. We use last names to differentiate every “Joe” from each other.

OK, let’s do it better this time. Please identify the fruit #2 of tree #1 and the fish #1 of river #2 in the picture below…

Excel object model - Objects identification and disambiguation

Now you could do it easily because I specified the collection from which to pick each item.

For now we can conclude that for identifying the element of a collection unequivocally, you need to specify first what collection the item belongs to and then its relative position in that collection.

Referring to any object of the world is easy if we organize all the earth collections and their items in an “earth object model”, all the countries and their items in a “countries object model”, and so on. You can also call it “hierarchy of objects”.

You need an object model to precisely manage the items of collections.

What’s the Excel object model?

Please identify the cell A1 in the Excel spreadsheets below…

Different workbooks and sheets contain an A1 cell

You quickly realize you can’t find the Excel cell object until you know from what Workbook (Book4 or Book5) and Sheet (Sheet1, Sheet2 or Sheet3). The same ambiguity problem arises in Excel because you have a collection of many workbooks containing a collection of many sheets, and each sheet containing one A1 cell.

You should be specific about what A1 cell you refer to. You need a sort of disambiguation attribute. In this particular case the workbook and sheet name.

OK, now please identify the cell A1 of the sheet 2 of the book 5 in the Excel spreadsheets below…

Specifying A1 cell uniquely

Very easy, right? Now you can find any Excel object because you know the exact object from which to pick the cell A1.

Excel macros manipulate Excel VBA objects, which are organized in collections. Here are some of the most important Excel collections…

  • Workbooks collection
  • Sheets collection
  • Charts collection
  • Pivot Tables collection
  • Tables collection
  • Range collection

So guess what?

When you manipulate objects in Excel through VBA code in macros, you need to point to the right objects. You can only control objects by understanding and using an Excel object model or hierarchy.

See a snapshot of the Excel object model below

Excel object model map

How to control Excel VBA objects (members) in a hierarchy (Excel object model)

Here are some VBA code examples:

How to point to a Range Object in Excel?

Workbooks(item).Worksheets(item).Range(item)

  • Workbooks(1).Worksheets(1).Range(“A1”)
  • Workbooks(3).Worksheets(1).Range(“A1”)
  • Workbooks(2).Worksheets(“Sheet1”).Range(“A1:B32”)

How to point to a Chart Object in Excel?

Workbooks(item).Worksheets (item).ChartObjects(item)

  • Workbooks(1).Worksheets(1).ChartObjects(1)
  • Workbooks(3).Worksheets(1).ChartObjects(“Chart1”)
  • Workbooks(1).Worksheets(“Sheet1”).ChartObjects(“Chart1”)

Excel object model resources:

Download an Excel 2003 object model map (pdf) here

Explore more about the Excel object model 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…

{filelink=2}

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:

{filelink=1}

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…