I want to learn how to write Excel VBA macros, where do I start?

Let me restate that question for a moment…”I want to learn how to write a novel, where do I start?”

An Excel VBA macro is like a novel

For now think in a macro like a novel in the sense that is a composition with meaning for readers. A novel is written in some language and must convey meaning to readers; a macro is written in some programming language (more of this later) and the macro syntax/code should be meaningful to computers.

What would you do first if you were going to write a novel? Let’s use our common sense…

  1. Mastering novel writing skills – Knowing English is not enough. You need to organize the language to produce the desired result, e.g. how will your story start? in what setting? how will end?, etc. But that’s not all, think for a moment that you will write a novel in a different language like Mandarin; will your novel skills be enough? Nope. So you need to master the language you will write your novel on
  2. Mastering the target language – Knowing how to write a novel is not enough. Could you write a novel in Mandarin? or in Quechua? You couldn’t. You need to learn the language first. You can’t create a novel of value if you don’t know the language properly, you will produce meaningless expressions like “me we play” or “soon will before”. Another benefit of mastering the target language is that you will know the available language tools like verbs, nouns, etc. that you can exploit at any time
  3. Designing the novel idea – You can’t start building your house without a blueprint, you will raise pillars in the place of walls and walls in the pool area; you need a sound plan before you start building something. It’s the same with your novel. Without a plan, you will use the language without purpose, you will redo your work over and over until it makes sense
  4. Writing the novel – If you don’t write it, your idea remains in the realm of imagination, it does not produce any result in the real world. You need to sit down and take action
  5. Proofreading the novel – Be sure the meaning is conveyed before you release your work to the public, you need to check the spelling as well as the sense of the narration
  6. Publishing the novel – Make your work available to others so it produces the intended results

Keep this novel-creation-sequence in mind because you will use it in just a moment…

Let’s get back to our macro question: “I want to learn how to write Excel VBA macros, where do I start?”

Here is the process you need to follow:

  1. Mastering Excel VBA macro writing skills (programming skills)
  2. Mastering the VBA programming language
  3. Designing the macro idea
  4. Writing your Excel macro
  5. Testing your VBA macro (macro debugging)
  6. Publishing your Excel macro
Let’s explore these stages in more detail…

1) Mastering Excel VBA macro writing skills (programming skills)

Your novel villain can’t appear in a chapter if he was killed in the previous one, you need events organized logically. You can’t run a macro that reads data form workbook “Sales.xls” if this is called “Customers.xls”, you can’t write a formula on a cell beyond the Excel limits; your macro won’t work, you need macro logic.

You need to organize the logic of your macro so it produces the desire result every single time you run it, what goes first, what checks to perform, what to do if things does not go as expected, etc.

Would you cure yourself after drinking poisoned water or would you first check if the water is clean and then drink it?

Here are a couple of logical events in Excel macros:

  • You must check if a file exists before you import it
  • You cannot open a Excel 2007 file (.xlsx) from Excel 2003, first, you need to check if is compatible (.xls)

Your macro must not perform unnecessary steps. It is a waste of processing-time for your computer, e.g. without notice, you could easily delete 1000 rows if you don’t import the right amount first, and it’s a waste of time for you because you will need to write additional VBA code to perform those additional Excel tasks, e.g. deleting unnecessary rows, trapping avoidable errors, etc.

In computer programming this systematic sequence of steps is called algorithm. You can complete a task by performing any permutation of actions, like…

A) Inefficient sequence to travel

  1. Go to the airport
  2. Realize you don’t have your passport and ticket
  3. Get back home
  4. Pick the travelling documents
  5. Get back to the airport
  6. Pay the reschedule-fee and
  7. Take the next available flight or

B) Efficient sequence to travel

  1. Check if you have your passport and ticket in your travel-wallet
  2. Go to the airport and
  3. Take your flight
  4. Enjoy you didn’t mess things up – lol

Algorithms organize the most efficient sequence of actions to complete a task; an algorithm requires thinking, sorting things out so at the end you have the least amount of actions that produce the desire result (option B above).

An Excel VBA Macro is made of a sequence of actions/instructions. Here are a couple of sample Excel macros and the actions they perform…

Email commissions macro:

  1. Importing sales
  2. Calculate commission
  3. Send email to salesman
  4. Present the message “Commissions sent”

Or…

Print report macro:

  1. Open “Prices” workbook
  2. Read data from column B of “Prices” book
  3. Close “Prices” workbook
  4. Write a formula that computes a company parameter in each row
  5. Summarize data using a Pivot Table
  6. Apply company standard formatting
  7. Print

But think for a moment that your “Print report” macro fails to print because there is no printer set. Efficient macros take decisions, e.g.

  1. Apply company standard formatting
  2. If printer set, then Print; otherwise set printer and then Print

Every macro contains a smart set of instructions (sequence + decisions); this kind of Excel macros will be reusable, they will produce the intended result on different workbooks and arrays conditions.

Want to create dynamic macros that work each next time?

Learn how to create algorithms.

The logic of algorithms is represented using simple diagrams called flowcharts. Here’s a very simple example…

Here’s another example I took from Wikipedia.

And here’s a simple Excel macro algorithm that calculates a commission based on sales amount, see the picture below…

There are many books about computer programming, most of them are written from a developer perspective, I am conscious of this issue so I developed an Excel programming course for non-programmers.

Now you know how macros are built. It’s time to learn the language Excel macros are written on…

2) Mastering the VBA programming language

What programming language should you learn to write macros in Excel?

Visual Basic and VBA.

You can’t create any Excel macro of value if you don’t know VBA programming language properly, you will produce meaningless expressions that the computer doesn’t understand, when you run a macro containing VBA syntax problems your computer will return compilation-errors instead of results.

Additionally, your creativity is boosted when you learn VBA language because you will be aware of the VBA functions and objects that you can use at any time.

Just so you know, there are nearly 3,000 languages around the globe and there are many programming languages too. There are nearly 600 listed in Wikipedia, give it a look here: http://en.wikipedia.org/wiki/List_of_programming_languages

These are some of the most popular programming languages…

  • C+
  • Java
  • Visual Basic and VBA

How to learn VBA? You can use any learning method: read books, blogs or forums, study macros from others, attend an Excel macros on-site course, enroll in an Excel macros online course, or a combination of these methods. You will gain mastery when you use VBA concepts to solve your own problems with your own macros.

Important notice: there are two Excel macro learning strategies and they are interchangeable…

Excel VBA learning strategy #1 – Learn VBA language first, then programming skills

At this time you might be are thinking, I prefer learning Mandarin first and then acquire novel writing skills, and yes, you are right. You can focus on novel writing skills once you are Mandarin fluent.

But keep this in mind…

These skills are complementary and interchangeable, for example, you learn new Mandarin words as you write your novel and as you write your novel, you develop new novel writing skills like organizing the characters better to produce a different result, etc.

Neither learning the programming language nor the programming tactics will stop, improving your knowledge of any of these fields will improve your macro coding.  So you can learn VBA first and then focus on programming skills, but at the end you need to learn both.

However, if you want to have a better learning experience I recommend the following strategy…

Excel VBA learning strategy #2 – Learn programming skills first, then VBA language

Imagine you don’t know anything about photography and you buy a digital camera, you open the user manual and learn how to operate your camera.

Let me ask you a question: would you be able to grasp how each camera feature really works: lens alignment, frame rate, ISO, zoom, etc.?

And would you be able to take great pictures after finishing the manual?

I bet you won’t, you need some photography background so you understand the use of camera commands wisely and how to use them at the right time, e.g. you need to know how to position yourself to capture the best lightning of the scene, how to set the lens to capture that light, etc.

Knowing photography principles first will make the learning of the camera commands more natural and easier, for example you will be able to visualize what conditions are suitable for using the zoom, what lens to add and why, etc. So when you study the camera manual, you will know where each part fits, what’s their purpose, etc.

The same occurs with VBA, you won’t be able to understand how to use the VBA functions and objects if you don’t know how macros (computer programs) work first. Where will you use IF statements?, what are decision structures?, how to avoid errors, how to trap errors, etc.

If you know how to design algorithms (programs), you intuitively know what programming tools are needed (branching, decision structures, statements, etc.), so when you start learning VBA language, you know where a VBA command fits in the big picture. You will learn what VBA commands exist and why to use them. This is wisdom!

3) Designing the macro idea

Here are three main questions to answer…

How do I start solving a problem I don’t know how to solve?

A problem is exactly that, something that has no solution yet, so it requires imagination and some well-known problem-solving strategies. To solve a problem you need problem solving skills, I particularly recommend the following…

If your end-result is creating a Pivot Table, this would be an Orr diagram solution…

Some of the steps needed would be:

  • Creating a workbook first
  • Import the data
  • Summarizing the data using the Pivot Table command
  • Format your Pivot Table
Here’s a sample Orr diagram for creating a Pivot Table…

There are many other methods that will help you get a better perspective of any problem:

  • Mind mapping
  • Napkin diagrams
  • Daydreaming
  • Combination of all methods

How to plan the Excel macro solution properly?

Once you have the solution clear, then you need to plan and design your macro, you need to use a technique called flow-charting. This technique consists in putting the actions of your macro in sequence with branching that defines the logic.

Basic shapes represent each Excel macro process. These are the three shapes I use to create any complex macro application…

The Process Symbol represents any action and is the most frequently used symbol in flow-charting.

The Decision Symbol is a junction where a decision must be made. A single entry may have any number of alternative solutions, but only one can be chosen.

The connector/arrow establishes the sequence of processes, which one goes after the other.

How do I find the VBA code for my Excel macro?

How do you find the sentences for your novel? There is no such think as a “1000 sentences for your novel” book; the fact is that the more you know about English, the more easily you will find ways to express your ideas.

It’s the same with your Excel macros, e.g. if you know that there is a VBA command called “msgbox” which presents a message based on a title and a text you provide, you can find uses for it in your macros (you can use it for giving instructions to the user, for warning the user about a problem that occurred, and so on)

The flowchart also proposes you (consequently) what VBA code to use, for example if an action of your Pivot Table flowchart says: “Display subtotals”, you need to find the VBA object method that does that; on the other hand, if your flowchart says something like “Adjust row and columns width” you will need to create a new routine that does it because you won’t find such straightforward command in VBA.

Let’s continue…

4) Writing your Excel macro

If you don’t write your macro, your solution is only a dream, it does not produce any results in the real world.

At this stage, you need to sit and type the VBA code of your Excel macro, you have your flowchart as a master guidance and you have a solid knowledge of VBA language but you will also need to research how to use new VBA statements here and there or how to handle Excel VBA objects you have not used before.

Macros are not written in one-sit as you can perceive now. Planning your macro might take you a couple of days or weeks, writing you Excel macro could take you 1 hour to 1 year.

Another important thing to have in mind is that Macros are written in special text windows called Modules, these modules are stored as part of your .xls or .xlsm file, additionally, each macro has its own name so you can later call it (run it). Finally but not the last, Macros can affect Excel workbooks as well as other applications like Word, Outlook, AutoCAD, etc.

5) Testing your VBA macro (macro debugging)

Writing a macro is a very dynamic process, you need to test as you go. Debugging tens or hundreds lines of VBA code is cumbersome, you might abandon some macros if you don’t know where to find the bugs.

The best practice is testing each line/block of macro VBA code during the writing phase. You also test your code during the implementation phase, because you don’t know what stress the end-users will impose to your program; sometimes your macro might go well and no enhancements are needed, however, you create new macro versions as you see room for improvements.

6) Publishing your Excel macro

This is as simple as running your macro the first time. You can do it from a button, from the macro dialog, triggered by an Excel application event, etc. Publishing your macro is also about where you store your macro for future use in other workbooks or how to share your Excel macros across your organization.

Your macro should be released at some point, don’t keep adding more functionality to your macro, release it as “MyMacro-V1.0”; later add new functionalities and release “MyMacro-V2.0”, and so on. Excel 2010 is the 14th version.

Now what?

If you start your Excel VBA macro learning today, it’s very likely that you will be writing your own Excel macros in a few months since now; if you don’t start today, I am 100% sure you will never write any macro.

Did I leave something out? Please share your ideas at the comments section below…