How to save maximum time with minimum VBA knowledge

"have you ever wanted to break free from repetitive and boring work, but the silly excel recorder and complex VBA, has always prevented you from getting started?"

Liberated!: a proven 5-stage blueprint to Record smart Excel Macros that work over and over...it works even for people with basic knowledge of VBA

From: John Franco, Excel author

At my desk, Tuesday, 10:25 AM

Dear Excel friend,

If you want to quickly and easily develop Excel macros to beat repetitive work that kills our souls everyday like cells formatting, columns deleting, filtering, chart creation, etc. and be ahead of deadlines, then read this short letter I have prepared for you…

…because I developed a 5-stage macro blueprint that anyone can follow to start taking full advantage of the dumb Excel macro recorder.

Excel comes with a dump tool that will do the dumb tasks for you!

It is the Excel macro recorder but it is underutilized because many think they need to be VBA experts to take advantage of this tool.

That’s why today you will discover that automating boring tasks with Excel macros for being much ahead of deadlines is not complicated if you know some crucial VBA and macro recording principles…

For example, you can take advantage of a voice recorder even if you are not a sound engineer and here’s why…

…using a sound editing software you can cut silences here and there, add some background music. These simple changes will have a significant effect on the output. Similarly, you can take advantage of the macro recorder even if you are not a VBA expert because you can cut some VBA excess here and there and add some VBA touches here and there.

If you want to learn how to record smart Excel macros that work for variable array size and layout, then, you will love my new program:

How To Save Maximum Time In Excel With Minimum VBA Knowledge And Macro Coding 

What Are Others Saying About The 'How To Save Maximum Time In Excel With Minimum VBA Knowledge And Macro Coding' Program?

Hi John, I accessed your program 'How To Save Maximum Time In Excel With Minimum VBA Knowledge And Macro Coding ' last night and found the information great... your information was much more helpful than anything MS had online - it was great
Thanks again for helping me and everyone else. Kind regards
Kylie
USA

What Are You Going To Learn In the 'How To Save Maximum Time In Excel With Minimum VBA Knowledge And Macro Coding ' Course?

Included in this practical Excel VBA program are macros and VBA concepts many Excel users never get it such as…

  • What is an Excel macro
  • How the Excel recorder works
  • The single VBA programming concept that will help you understand 90% of Excel VBA syntax. Understand what the macro recorder produces and feel confident to change the code and adapt it to your needs
  • The 3 crucial VBA concepts that are used 90% of the time
  • The 3 types of VBA loops to make your recorder macros smarter. Turn some minutes of macro recording effort into a powerhouse of repetitive operations automation
  • The 2 kind of VBA decision structures that will make your macro smarter
  • How to merge two or more macros
  • The 5-stage macro recording blueprint that will help you produce operational macros with ease. Learn how to record a macro step by step, how to plan your macro recording to minimize fat VBA code and trial and error
  • How relative references are used to create dynamic macros that work on different size arrays. 
  • Where you should put the cursor before the recording process starts so your macros work on different size arrays and to minimize fat VBA code
  • How to alter the Excel VBA Macro code that is created when you Record Excel Macros so your macros work on different size sheets and conditions
  • How to easily run a Macro from buttons, shapes, graphs, shortcuts, Ribbons, etc.
  • How to consistently get rid of fat VBA code produced by the Excel macro recorder. Make Excel Macros more efficient (shorter, faster and smarter)
  • VBA practice file so you see how the concepts work. Enjoy the freedom to tweak the sample code and see what happens
  • How to alter the Excel VBA Macro code on the go while the Excel macro recorder is on
  • The most common macro troubleshooting scenarios so you keep your macro running
  • And much more

What's different from other Excel macro courses out there?

Most of the Excel macro courses are all about VBA theory. The program ‘How To Save Maximum Time In Excel With Minimum VBA Knowledge And Macro Coding’ will show you how to record smart macros step by step using the most essential knowledge and techniques.

No VBA jargon. No complex explanations. I use simple language anyone can understand.

What proficiency level should you have to get the most out of the 'How To Save Maximum Time In Excel With Minimum VBA Knowledge And Macro Coding ' program?

Basic to intermediate Excel users will get the most out of this program because the program starts from scratch and follows a practical down-to-earth approach. The program only covers the VBA fundamental concepts everyone needs to get started and be successful with the Excel macro recorder.

You can record macros to automate the most common Excel tasks and move gradually into more complex VBA projects.

You can ask for a full refund if you find the course is not for you.

But if you are a seasoned expert, you might use this program as a resource to face any kind of automation project. If you are stuck in the macro mud, you might find here a practical approach to recording macros that will help you complete bigger projects easier and faster with minimum coding.

You can also use the concepts as a refresher or for covering some VBA voids you might have.

Also, you can ask for a full refund if you find the course is not for you.

Here's how to get this macro foundational material our community needed!

With a $67 product, you can add a 10%, 20%+ increase to your salary because VBA is a highly in-demand skill in today’s data-driven economy and your increased self-worth will be reflected in the promotions and bonuses you are going to get or in the new and better jobs you can prospect and get.

What does a 10% increase mean to you, dear friend? Hundreds even thousands more in your pocket each month?

And if you are VBA freelancer or looking to become one, these done-for-you macros will help you complete projects faster and take new projects with confidence. Just one macro gig can put hundreds of dollars in your pocket.

This course won’t cost you $300.

This course won’t cost you even $100.

If you enroll today, you get access to this essential macros library for only…

USD 67.00

With a small investment, Excel Macros will pay you back again and again.

If you don’t learn macros, you will keep redoubling your time and effort for completing assignments.

You risk nothing

If within 30 days of purchase, you follow the VBA fundamental lessons and implement the 5-stage blueprint and cannot record your own macros for automating your work, or if you simply didn’t like the material, just ask a refund and you will get your money back, no questions asked.

I have been selling information products online since 2011 to thousands of professionals from around the world.

Just contact us (use the subject ‘refund’). I will take care of it.

Enroll now and start learning how to do the most basic Excel things in VBA.

You are covered by my 30-day money back guarantee.

What do you get when you enroll in the 'How To Save Maximum Time In Excel With Minimum VBA Knowledge And Macro Coding ' program?

After you enroll, you get instant access to:

Macro building blueprint

Make meaningful progress in reasonable time by following a proven blueprint that will make your macros operational

2+ VBA workbooks

More than 2 practice Workbook files in xlsm format (the same ones used in the program)

Forum

Post your projects on the forums and get feedback from me and other members on your same journey. Also, hone your skills by helping others in their Excel guru journey

User and password

Email with a username and a password to access the private members' area

25+ practical VBA lessons

Concepts explained using annotated graphics, simple language and analogies everyone can understand

Email support

One year of email support

One year access

Learn at your own pace during an entire year. No need to follow rigid schedules

Free updates

Get free access to updated and new lessons, bonuses, for one year

Access anytime, anywhere

Access the members' area on any device (Android, iOS, Windows, Mac).

What else do you get when you enroll in the 'How To Save Maximum Time In Excel With Minimum VBA Knowledge And Macro Coding' program?

Practical bonus #1 - Excel Object Model Blueprint

You and I know that without having the big picture of the Excel object hierarchy, finding the exact syntax for our macros or understanding the syntax is cumbersome. And we also know that Microsoft has wiped out this information from its help system. You don’t get a map view of the objects anymore but only an encyclopedia of links where one never sees the big picture.

I knew this material was crucial to macro success and compiled it before it disappeared!

I have synthesized everything in a convenient pdf you can easily explore, zoom in, and zoom out.

If you want to immediately have clarity about the whole Excel object model, then this rare pdf is for you!

  • What are the main Excel objects and how to access them in a macro
  • What are the available collections you can use in your loops
  • Why some macros look the way they do
  • How to access any aspect of Excel using VBA code
  • and much more

You get this secret VBA syntax building blueprint for free if you order the program ‘How To Do More Than 150 Fundamental Excel Tasks In VBA’.

Practical bonus #2 - Dynamic Macro Recording Formula

dmrf-video-player

This practical video lesson reveals the 5 VBA dynamic techniques to easily make your recorded macros work for variable array size and conditions.

If you want to reuse your recorded macros, then this macro recording formula is for you.

Includes a VBA workbook practice file!

Practical bonus #3 - Recorded Macro Performance's Optimization Formula

rmpof-video-player

You know the macro recorder fills your macro with garbage (unnecessary selections, scrolling, sheet shifting, etc.) and they become painfully difficult to edit and adapt to your situation.

And recorded macros are slow and silly.

Learn how to take your recorded macros to an optimal level. This practical video lesson reveals a 3-step VBA optimization formula to clean all the unnecessary VBA code and improve your recorded macros’ performance, readability, and usability.

If you want to synthesize your recorded macros and make them perform at their best, then this macro recording formula is for you.

You are covered by my 30-day money back guarantee.

You are literally minutes away from following a proven 5-stage macro recording blueprint that will help you record more macros than you will ever need.

Thanks!

FAQ

How much time should I devote to learning?

To complete the ‘How To Save Maximum Time In Excel With Minimum VBA Knowledge And Macro Coding ‘ program, you should follow the 5-stage blueprint and complete at least 10+ lessons. But you don’t need to study the entire program in one sitting. 

You can read the lessons at your own pace and you can also skip the areas you are familiar with or that you want to study later.

No pressure teacher. No rigid schedules. Study at your own pace.

Click here to get started

What payment methods do you accept?

All major credit cards including Visa, MasterCard, and American Express are accepted. We also accept PayPal, Amazon Pay and Wire transfer.

Click here to get started

When does the course start and finish?

The access to the course starts now and ends whenever you like because is a self-paced online course. You decide when you start and when you finish.

The course site remembers your progress, so you can pick up right where you left off on any device.  That means you can study a few lessons at work, then continue right where you left off on your tablet or home computer.

You can access the course site from your work or home computer, tablet, smart phone or any other device with an internet connection.

Click here to get started  

How long do I have access to the course?

You have lifetime access to the course.

Click here to get started

What Excel version do I need?

The Excel workbooks are saved as macro-enabled books (.xlsm). You need Excel 2007+

Click here to get started

Can I access the lessons on my mobile device?

Yes, you can log in with your smartphone or tablet (Android or iOS) and read or download the material. If you want to manipulate the workbooks, you might need an Excel app installed.

Of course, you can also log in with your computer or laptop (Windows or Mac).

Click here to get started

I don't understand VBA yet, can I use the macros in the library to save time in my work?

You will get the most of this material if you already understand the fundamentals of VBA. But even if you have never record any macro, you can quickly follow the fundamental lessons and the 5-sstage macro recording blueprint to get results that will get you off the ground, such as closing all the open books or opening all the books in a folder, creating a line chart for your weekly report, etc.

Also, this essential VBA library is the perfect companion if you are already started learning macros or if you plan to do it in the future.

Click here to get started

Who is this Excel macros library for?

Basic to intermediate Excel users will get the most out of this program because the program starts from scratch and follows a practical down-to-earth approach. The program only covers the VBA fundamental concepts everyone needs to get started and be successful with the Excel macro recorder.

 

You can record macros to automate the most common Excel tasks and move gradually into more complex VBA projects.

 

You can ask for a full refund if you find the course is not for you.

 

But if you are a seasoned expert, you might use this program as a resource to face any kind of automation project. If you are stuck in the macro mud, you might find here a practical approach to recording macros that will help you complete bigger projects easier and faster with minimum coding.

 

You can also use the concepts as a refresher or for covering some VBA voids you might have.

 

Also, you can ask for a full refund if you find the course is not for you.

Click here to get started

The Essential Library Of Excel Macros For Busy Professionals

"Imagine...You Can Be Ahead Of The Next Arbitrary Deadline Just By Pressing Some Buttons!"

To busy professionals and consultants who want to automate the data collection, data analysis and data formatting of daily and weekly tasks (without the hassle of trial-and-error needed to make macros work)

From: John Franco, Excel author

At my desk, Tuesday, 8 AM

Dear Excel friend,

If you want to use macros that will finally break you free from the chains of ‘time & energy sucking projects’ but the lack of time to figure out the exact VBA syntax, the complex syntax itself and the lack of relevant VBA examples, have always prevented you from getting started, then this is the most important message you will ever read…

…because today you will discover that getting started with Excel macros or finishing your macro projects for being much ahead of deadlines is not complicated if you have the essential VBA code at hand.

It is embarrassing for an Excel guru like me to admit it but the truth is that…

…finding the essential VBA syntax that will get the job done is more about STEALING than creativity…

…I really mean it…

…learning and using VBA have given me freedom and professional prestige beyond all my wildest visualizations but still today, with all my understanding of the VBA language and macro programming techniques, I don’t know how to do all the most basic Excel things in VBA. I know how VBA works like I know my mother tongue but fact are facts…

…finding a truthful and concise macro expression that does the job, requires the hassle of trial and error (a lot of editing, testing, tweaking, debugging, etc.), not just knowledge.

Don’t get me wrong. Excel VBA helps us express creativity but when it comes to getting things done, I prefer taking advantage of proven macro code and adapt it to my needs because of three main reasons…

#1 finding the right code for your macros requires long cycles of thinking, writing, rewriting and debugging.

Even if one makes a coding decision, the VBA syntax is prone to errors and one ends up doubting our judgment and that’s not fun.

If our macro module is blank, the source of every new piece of the rightful macro code is unknown to us. And each potential VBA solution brings fear because one doesn’t know if it will work.

#2 the VBA language is like English, you can say something but using too many words. 

For example, to select the entire sheet, I would write ActiveSheet.Range(A1:XFD1048576).Select, not Cells.Select.

Is it the current book, sheet or the current window? Is it Range(A1:XFD1048576) or simply Cells?

Researching and testing VBA is tricky and requires a lot of trial and error (and imitation). This is true because VBA is a full-fledged language and has many hidden workarounds for the newcomers and even experts like me.

#3 translating the mouse movements into code is not so straightforward

For example, copying and pasting a range doing a literal translation of the mouse movements leads to this cumbersome and slow way:

ActiveSheet.Range(A1:D22).Copy

Workbooks(Target).Range(A1).Select

ActiveSheet.Paste

or to this other crappy way

Selection.Copy

Windows(Target).Activate

Range(A1).Select

ActiveSheet.Paste

But the simplest and most professional way is this one:

Worksheets(Source).Range(A1:D22?).Copy Destination := Worksheets(Target).Range(A1)

How one finds an essential piece of VBA code?

Well, after years of writing macros, I figure it all out!

Writing Excel macros to automate repetitive tasks is really cumbersome if you don’t have a library of proven macros at hand.

If you know that Excel has hundreds of VBA statements and objects but when it comes to writing operational macros you are faced with questions like how to copy formulas down to the last row?, how to merge books in a folder?, how to create a PivotTable from scratch, how to loop through cells, sheets, chart series, PivotTable fields, PivotTable items?, how to import an Access table/query into the current sheet?, etc…

…or if you have unfinished macro projects because you don’t know how to code some Excel tasks.

And want to become comfortable and at ease with writing VBA code, then this is great news for you!

…I decided to open my library of essential Excel VBA code I have synthesized and curated over the years:

If you want to use macros that will finally break you free from the chains of ‘time & energy sucking projects’ but the lack of time to figure out the exact VBA syntax, the complex syntax itself and the lack of relevant VBA examples, have always prevented you from getting started, then this is the most important message you will ever read…

…because today you will discover that getting started with Excel macros or finishing your macro projects for being much ahead of deadlines is not complicated if you have the essential VBA code at hand.

…I decided to open my library of essential Excel VBA code I have synthesized and curated over the years: 

The Essential Library Of Excel Macros For Busy Professionals – How to do more than 150 fundamental Excel tasks with VBA.

This is the insider VBA library John wishes was around when he was starting out with macros earlier in his career. Learning even one or two of these essential VBA techniques would have short-cut his own success by oh five maybe five years. Probably more.

What Are Others Saying About 'The Essential Library Of Excel Macros For Busy Professionals'?

John, I have just been able to look over your program quickly, but it looks good!! This is the type of VBA foundation building information that I believe many of us need. Thank you John, this is going to be great!
Dave Fields
USA
I got your 150 Fundamental Excel Tasks In VBA course today, and am reading through some of the macros with delight. Thanks, Tom
Tom F.
John, I have been able to use the information from "How To Do More Than 150 Fundamental Excel Tasks In VBA".
I, like so many others just jumped into VBA without ever really getting a foundation and the course has provided me with many of the foundation questions I have had and I have a better understanding of VBA because of it.
Dave Fields
Hi John, Yep, so far, so good thanks.
I haven't had as much time to run through it as I would like but what I have seen is exactly what I'm looking for. Having bought a few of your courses in the past, I'm not surprised that my VBA learning expectations have been exceeded.
The materials are exactly at the level I'm looking for and the way they are presented will make it easy for me to apply the learnings to my job. I'm looking forward to spending time developing my vba knowledge and skills. Keep up the good work! Best regards, Barry
Barry Lawson
USA
Hi John I speak french so sorry for my mistakes 🙂

However I want to thank you for the megaformula course, it is one of the best investments I have made. I am no longer apprehensive when approaching complicated formulas. My understanding changed my vision of Excel. This is why I did not hesitate on Macros For Busy Professionals.

Have a good day
Michel Tremblay

What Are You Going To Get In 'The Essential Library Of Excel Macros For Busy Professionals'?

Included in this simplified VBA code library are VBA techniques many Excel users never get it such as: 

Module 1 – How to move data between cells, sheets, and workbooks

  • How to copy data from one range to another sheet
  • How to copy data to a new workbook and save it
  • And much more!

Module 2 – How to write formulas in cells and ranges

  • How to write a COUNT formula
  • How to write a SUMIF formula
  • How to write a SUM formula
  • How to copy formulas down to the last row
  • And much more!

Module 3 – How to merge cells, sheets, books, etc.

  • How to merge two cells that match certain criteria
  • How to merge many sheets
  • How to merge books in a folder
  • And much more!

Module 4 – How to open, save and export workbooks and sheets

  • How to save a book with a different name
  • How to close all open books
  • How to open the books in a folder
  • How to send the active sheet as pdf by email
  • How to save with a custom stamped name

Module 5 – How to do Vlookups

  • How to write a Vlookup formula in Excel
  • How to use the power of Vlookup inside a macro (without writing any formula)
  • How to write an Index & Match
  • And much more!

Module 6 – How to print

  • How to print the books in a folder
  • How to print the selected sheets
  • How to print a range of pages of the active sheet
  • And much more!

Module 7 – How to create and set up PivotTables

  • How to create a PivotTable from scratch
  • How to rename the PivotTable totals
  • And much more!

Module 8 – How to create and set up charts

  • How to create a line chart
  • How to create a pie chart
  • How to set the chart title
  • How to set the chart axes
  • And much more!

Module 9 – How to format cells and ranges

  • How to set the cell font styling
  • How to set the range borders
  • How to merge and unmerge cells
  • How to set the cells number formatting
  • And much more!

Module 10 – How to loop (how to iterate through cells, books, charts, PivotTables, etc)

  • How to loop through the cells of a column
  • How to loop through the cells of a row
  • How to loop through books, charts, etc
  • How to loop through PivotTables
  • How to loop through PivotTable fields and items
  • And much more!

Module 11 – How to work with cells and ranges

  • How to hide columns, rows, etc
  • How to write variable values to cells
  • How to store a cell value in a variable
  • And much more!

Module 12 – How to get information about cells and ranges

  • How to store a range in an object
  • How to find the last used row
  • How to get the size of a range
  • How to enter to the next occupied cell
  • And much more!

Module 13 – How to create and set up UserForms

  • How to create a User form
  • How to add the most common controls (text boxes, command buttons, etc.) to user forms
  • How to send user form data to the last row of the active sheet
  • How to send user form data to the last row of any given sheet
  • And much more

Module 14 – How to connect Excel with Access

  • How to import an Access table/query into the current sheet
  • How to open Access from Excel
  • And much more…

Module 15 – How to work with directories and files

  • How to get the path of workbooks and files
  • How to create a filesystem object that allows you accessing to any directory using VBA
  • How to create a file on a folder
  • And much more…

Module 16 – How to work with tables

  • How to convert a range into a table
  • How to convert a table back to a range
  • How to add rows and columns
  • How to clear contents
  • How to filter a table (single and multiple columns)
  • How to remove duplicates
  • How to format a table
  • And much more…

Here's how to get this macro foundational material our community needed!

With a $67 product, you can add a 10%, 20%+ increase to your salary because VBA is a highly in-demand skill in today’s data-driven economy and your increased self-worth will be reflected in the promotions and bonuses you are going to get or in the new and better jobs you can prospect and get.

What does a 10% increase mean to you, dear friend? Hundreds of dollars more in your pocket each month?

And if you are VBA freelancer or looking to become one, these done-for-you macros will help you complete projects faster and take new projects with confidence. Just one macro gig can put hundreds of dollars in your pocket.

This course won’t cost you $300.

This course won’t cost you even $100.

If you enroll today, you get access to this essential macros library for only…

USD 67.00

With a small investment, Excel Macros will pay you back again and again.

If you don’t learn macros, you will keep redoubling your time and effort for completing assignments.

You risk nothing

If within 30 days of purchase, you follow and implement the essential VBA pieces of code and cannot write your own macros for automating your work, or if you simply didn’t like the material, just ask a refund and you will get your money back, no questions asked.

I have been selling information products online since 2011 to thousands of professionals from around the world.

Just contact us (use the subject ‘refund’). I will take care of it.

Enroll now and start learning how to do the most basic Excel things in VBA.

What's different from other Excel macro courses out there?

Think of a desperate small business owner that calls a technician to fix the server that runs the factory. He is losing money and clients, he cannot even sleep, workers are idle.

If you were that business owner, what kind of technician would you want to hear?

One that starts teaching you about the operating system running the damn server, that gives you scenarios A, B and C.

Or one that says to you, “just push the little orange reset button at the back of the server.”

That’s the difference between insight and information!

Now think of yourself near the deadline with unfinished job and someone comes and give you the theory of time saving Excel technology…it is called VBA, this is the syntax, this is the object model, the best coding practices, and blah blah blah.

And other guy says, if you want to get the report done, “press this macro-linked button”

Or…

Want to format a table? Use this macro.

Want to create a pivottable? Use this macro.

Want to move cells to another book? Use this macro.

And so forth.

Who would you choose?

Well, today, I am that person that comes with insight…

…because I will give you more than 150 macros that will do the heavy lifting for you.

No guessing.

No hassle of trial and error writing, testing, debugging, etc.

No theory.

Most of the Excel macro courses are all about VBA theory. The essential VBA library program will show you more than 150 proven done-for-you macros that will do the most essential Excel tasks.

Find inside a comprehensive library of curated VBA code you can copy and paste into your macros or adapt them to bigger macro projects.

You can also learn how the VBA theory is implemented and how good-enough macro code is written.

Who is 'The Essential Library Of Excel Macros For Busy Professionals' for?

Basic to intermediate Excel VBA users will get the most out of this program because operational VBA code is key to having a good VBA learning and coding experience.

You can use the macros provided to automate the most common tasks and move gradually into more complex VBA automation projects.

You can ask for a full refund if you find the course is not for you.

But if you are a seasoned macro expert, you might use this Macros Library as a resource to face any kind of automation project. If you are stuck in the macro code mud, you might find here a bit of rightful VBA code to point you in the right direction.

You can also use the macros as a refresher or for covering some voids you might have.

Also, you can ask for a full refund if you find the course is not for you.

You are covered by my 30-day money back guarantee.

What do you get when you enroll in 'The Essential Library Of Excel Macros For Busy Professionals'?

After you enroll, you get instant access to:

160+ practical done-for-you macros

Curated and proven macros. Some of the scenarios are explained using annotated screen captures

18+ VBA workbooks

More than 10 practice Workbook files in xlsx format (the same ones used in the program)

Forum

Post your projects on the forums and get feedback from me and other members on your same journey. Also, hone your skills by helping others in their Excel guru journey

User and password

Email with a username and a password to access the private members' area

Macro building blueprints

Make meaningful progress in reasonable time by following some proven blueprints that will get your complex macros done

Email support

One year of email support

Life-time access

Learn at your own pace at any time. No need to follow rigid schedules

Free updates

Get free access to updated and new lessons, bonuses, for one year

Access anytime, anywhere

Access the members' area on any device (Android, iOS, Windows, Mac).

What else do you get when you enroll in 'The Essential Library Of Excel Macros For Busy Professionals'?

Practical bonus #1 - Excel Object Model Blueprint

You and I know that without having the big picture of the Excel object hierarchy, finding the exact syntax for our macros or understanding the syntax is cumbersome. And we also know that Microsoft has wiped out this information from its help system. You don’t get a map view of the objects anymore but only an encyclopedia of links where one never sees the big picture.

I knew this material was crucial to macro success and compiled it before it disappeared!

I have synthesized everything in a convenient pdf you can easily explore, zoom in, and zoom out.

If you want to immediately have clarity about the whole Excel object model, then this rare pdf is for you!

In this practical and hard-to-find VBA syntax blueprint, you will learn:

  • What are the main Excel objects and how to access them in a macro
  • What are the available collections you can use in your loops
  • Why some macros look the way they do
  • How to access any aspect of Excel using VBA code
  • and much more

You get this secret VBA syntax building blueprint for free if you order the program ‘How To Do More Than 150 Fundamental Excel Tasks In VBA’.

Practical bonus #2 - Dynamic Macro Recording Formula

dmrf-video-player

This practical video lesson reveals the 5 VBA dynamic techniques to easily make your recorded macros work for variable array size and conditions.

If you want to reuse your recorded macros, then this macro recording formula is for you.

Includes a VBA workbook practice file!

Practical bonus #3 - Recorded Macro Performance's Optimization Formula

rmpof-video-player

You know the macro recorder fills your macro with garbage (unnecessary selections, scrolling, sheet shifting, etc.) and they become painfully difficult to edit and adapt to your situation.

And recorded macros are slow and silly.

Learn how to take your recorded macros to an optimal level. This practical video lesson reveals a 3-step VBA optimization formula to clean all the unnecessary VBA code and improve your recorded macros’ performance, readability, and usability.

If you want to synthesize your recorded macros and make them perform at their best, then this macro recording formula is for you.

You are literally minutes away from grabbing a gallery of 160+ indispensable macros to do the most wanted Excel things Thanks!

FAQ

How much time should I devote to learning?

To complete the MMF program, you should complete 20+ lessons. But you don’t need to study the entire program in one sitting. 

You can read the lessons at your own pace and you can also skip the areas you are familiar with or that you want to study later.

No pressure teacher. No rigid schedules. Study at your own pace.

Click here to get started

What payment methods do you accept?

All major credit cards including Visa, MasterCard, and American Express are accepted. We also accept PayPal, Amazon Pay and Wire transfer.

Click here to get started

When does the course start and finish?

The access to the course starts now and ends whenever you like because is a self-paced online course. You decide when you start and when you finish.

The course site remembers your progress, so you can pick up right where you left off on any device.  That means you can study a few lessons at work, then continue right where you left off on your tablet or home computer.

You can access the course site from your work or home computer, tablet, smart phone or any other device with an internet connection.  

Click here to get started

How long do I have access to the course?

You have lifetime access to the course.

Click here to get started

Can I google these Excel macros?

Searching and finding are two different things. You can google all your needs but can you actually find what you are looking for?

Sometimes, one cannot find the exact search words to find the exact piece of code. At other times, the solutions are provided by developers, with advanced techniques that you don’t understand yet and with more information than you need at your current stage of understanding.

And the solutions are scattered all over the place, in different blogs, forums, etc., with different styles and hidden agendas. If you don’t want the hassle of organizing all that, what you need is everything in one single location, curated and readily available.

Click here to get started

What Excel version do I need?

The Excel workbooks are saved as macro-enabled books (.xlsm). You need Excel 2007+

Click here to get started

Can I access the lessons on my mobile device?

Yes, you can log in with your smartphone or tablet (Android or iOS) and read or download the material. If you want to manipulate the workbooks, you might need an Excel app installed.

Of course, you can also log in with your computer or laptop (Windows or Mac).

Click here to get started

I don't understand VBA yet, can I use the macros in the library to save time in my work?

You will get the most of this material if you already understand the fundamentals of VBA. But even if you have never written any macro, you can quickly use the pieces of VBA code to get some practical results that will get you off the ground, such as closing all the open books or opening all the books in a folder, creating a line chart for your weekly report, etc.

Also, this essential VBA library is the perfect companion if you are already started learning macros or if you plan to do it in the future.

Click here to get started

Who is this Excel macros library for?

This library of essential pieces of VBA code is for you if you are getting started with macros and want to have a reference source of VBA code to perform the most basic functions.

You will love this library if you want plenty of relevant VBA examples.

This library is also for you if you don’t know what property or method to use for certain tasks and want to see how the most common objects are used for doing the most essential Excel tasks.

Even if you are an expert, you might find some surprises to increase your VBA arsenal of techniques. If you find increasing challenging scenarios and you are constantly improving, you know that there’s never enough of VBA techniques to dispose of.

Click here to get started

How to reference cells dynamically using VBA

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

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

Using a ForNext loop

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

For counter = 2 To 5

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

Next

Using a DoWhile loop

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

counter = 2

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

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

counter = counter + 1

Loop

Using a DoUntil loop

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

counter = 2

Do Until Cells(counter, 1) = ""

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

counter = counter + 1

Loop

How to run a macro from a button

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

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

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

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

1) Create the button in your sheet

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

Add any shape to assign a macro to

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

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

2) Assign a macro to your button/object

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

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

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

Choose the location of the macro and click OK

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

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

Your Excel macro is now attached to your button!

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

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

3) Name your Excel macro button meaningfully

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

To do it, proceed this way…

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

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

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

Here are some examples of Excel macro buttons…

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

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

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

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

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

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

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

It’s very simple!

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

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

Excel files contain data and vba code

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

That’s a good question.

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

First let’s explore the difference between…

Local Excel macros vs. central Excel macros

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

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

Some examples of local macros might be:

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

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

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

Some examples of central macros might be:

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

Location of recorded Excel macros

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

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

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

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

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

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

2) Then record your Excel macro as normal.

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

All the macros in the current workbook
Macro dialog

Make your recorded macros available to any workbook (central)

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

2) Then record your Excel VBA macro as normal.

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

See all the macros that are in the personal workbook

What’s the Personal Macro Workbook?

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

The personal macro workbook is always loaded

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

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

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

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

Show hidden files

Location of the Excel macros you create

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

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

Excel macros are stored this way in a workbook

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

Excel macros are stored in modules and procedures

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

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

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

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

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

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

Make your written Excel macros available everywhere (central)

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

Assign macros to ribbon commands

You can run a macro in Excel in several ways…

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

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

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

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

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

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

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

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

Load any Excel addin or browse it

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

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

I hope this article helps.

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

Are you asking where the macros in a workbook are?

CTRL + F11, show the VBA interface

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

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

Excel files contain data and vba code

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

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

Sub procedures

Launch the VBA editor this way…

Excel 2007/2010 users…

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

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

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

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

VBA interface, Excel options, customize ribbon

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

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

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

Select the macro and click edit to show the VBA code

Excel 97-2003 users…

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

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

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

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

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

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

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

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

Here’s how to do it…

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

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

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

Congratulations! You have recorded a dynamic Excel Macro.

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

How to use a dynamic macro you recorded in Excel

Use your dynamic Macro this way:

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

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

How to record macro in Excel dynamically

 

Into table layout like this one:

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

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

Want to go to the next level?

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

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(&quot;Word.Application&quot;)

This line of code establishes communication with Outlook.

Set myOutlookApp = CreateObject(&quot;Outlook.Application&quot;)

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

Set MyExcelApp = CreateObject(&quot;Excel.Application&quot;)

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(&quot;Powerpoint.Application&quot;)
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 &quot;C:/MyPPT.ppt&quot;
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 - &quot;Make Excel your second nature&quot;
'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(&quot;Outlook.Application&quot;)
Set myeMail = myOutlookApp.CreateItem(olMailItem) 'sets a new email item

 SalesAmount = Cells(ActiveCell.Row, 4)

 With myeMail
 .Subject = Cells(ActiveCell.Row, 1) &amp; &quot;, your sales amount...&quot; 'sets the subject line, e.g. &quot;Laura, your sales amount&quot;
 .To = Cells(ActiveCell.Row, 2) 'reads the salesman email from active row
 .body = &quot;Dear &quot; &amp; Cells(ActiveCell.Row, 1) &amp; &quot;Your sales amount is: &quot; &amp; 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: