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

Why learning how to write macros in Excel is like learning how to write a novel

For now think in a macro like a novel in the sense that is a meaningful composition to the readers. A novel is written in some language and should be meaningful to readers. An Excel macro is written in some programming language (more of this VBA stuff 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 the readers’ language

Could you write a novel in Mandarin? or in Quechua? Knowing how to write a novel is not enough. You need to learn the language first. You can only create a novel of value if you 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.

2 – Mastering novel writing skills

Knowing English is not enough. You need to organize the language to produce the desired result. Your story should have some structure. E.g. how will your story start? in what setting? how it 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.

3 – Designing the novel idea

You can’t start building your house without a blueprint because you might end up raising pillars in the place of walls and walls in the pool area. You need a well defined layout 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 the novel, your idea remains in the realm of imagination without producing any result in the real world. You need to sit down and write.

5 – Proofreading the novel

Be sure the meaning is conveyed before you release your work to the public. You need to check the spelling.

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 for writing macros in Excel…

Let’s get back to our Excel macro question:

“I want to learn how to write macros in Excel, where do I start?”

Here is the simple process you need to follow:

1. Learning the Excel VBA programming language
2. Learning Excel VBA macro writing skills (programming skills)
3. Designing the Excel macro idea and logic
4. Writing your Excel macro code
5. Testing your VBA macro (macro debugging)
6. Publishing your Excel macro (running your macro)

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

Your novel villain can’t appear in the third chapter if he was killed in the second chapter. The events should be organized logically.

You can’t run a macro that reads data form the workbook “Sales.xls” if this is missing in your hard drive or it is named differently. You can’t write a formula on a cell beyond the Excel limits.

If you write macros in Excel without logic, your computer will let you know at every step of the way, presenting errors, crashing, slowing down, etc.

You need to organize the logic of your Excel macro so it produces the desired result every single time you run it. Figure out the requisites that each piece of VBA code needs to work.

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:

• To open a workbook using Excel VBA, you must check if the file exists
• To open an Excel file using VBA code, the file should be in a readable format (xls, xlsx, etc)
• To change the size of a chart using VBA, the chart should exist in your workbook
• etc.

In computer programming, this logic sequence of steps is called algorithm. You can complete a task inefficiently as you see the sequence below:

A) Inefficient traveler’s sequence

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

…you can get what you need doing only what’s needed.

B) Efficient traveler’s sequence

1. Check if you have your passport and ticket in your travel-wallet
2. Go to the airport and
3. Take your flight

Algorithms organize actions to complete a task in the most efficient manner. 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:

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.

6. Apply company standard formatting
7. If printer is set, then Print; otherwise set printer and then Print

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

Want to create dynamic Excel macros that work each 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 the amount of sales. See the picture below…

There are many Excel VBA books but many of them are written from a developer perspective. That’s why I developed an Excel programming course for non-programmers called Master of Macros Blueprint. If you want to learn how to write macros in Excel using simple language you can understand, then this course if for you.

Now it is 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 only write macros in Excel (useful Excel macros) if you know how the Excel VBA programming language works. If you don’t  learn Excel VBA, you will write meaningless macro code that the computer doesn’t understand. When you run a macro containing VBA syntax problems your computer will return compilation-errors and run-time errors instead of results.

Additionally, your creativity increases when you learn Excel VBA because you will be able to talk to the computer fluently. be aware of the VBA functions and objects that you can use at any time.

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, C#
• Java
• Visual Basic and VBA

How to learn Excel 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 for beginners, 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 the 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 Excel VBA first and then focus on macro writing 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 the 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 buy and why, etc. So when you study the camera user manual, you will know the location of each part, their purpose, etc.

The same occurs with learning Excel VBA because 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 the Excel VBA language, you know where any particular 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 fundamental questions to answer…

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

A problem is exactly that, something that has no solution yet, so it requires that you solve it. To solve a problem you need problem solving skills.

The best way to solve a problem is defining the exact solution you want and then reverse engineer the causes of that solution. E.g. If you want to travel to China, you would break down the causes into buy a ticket, get the money for the ticket, package your luggage, etc.

Use an Orr diagram, which consists in conceiving your end-result first and then going back to figure out the causes of that result, using nested brackets. For example, if your end-result is creating a Pivot Table, this would be an Orr diagram solution…

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 journey 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.