The 5 building blocks of every operational Excel macro


Hi,

Thank you for all your comments in the free lesson #1 – The psychology of writing macros. If you haven’t read it yet, go here now.

If you want to turn successful macro writing into a repeatable and enjoyable process, then this free macro lesson #2 is for you!

You can download the structure macro template workbook at the bottom of the lesson.

This macro structure you are about to discover is magic!

How to overcome the technical and psychological problems of developing operational macros?

You and I know that getting an operational macro done is not easy…

…the macro is too long. The macro does not work and one can’t find the source of error. The macro cannot be used in different situations. The macro will take time and effort to complete. The macro is becoming too complex. We forgot the purpose of the code we wrote. One doesn’t know how to get started writing a macro.

The fact is that our macros are never operational because our idea of a macro is not operational from the very beginning. Let me explain…

Most people never know the exact output their macro should deliver. They want to save time, they want to create a dashboard, they want a macro to clean data, etc., but they never think in terms of a measurable and doable output. E.g. a number in C23, a new PivotTable in the sheet “report”, a chart with 3 series, etc.

With a clear output, even your fear of VBA dissapears because once you set a concrete output, you easily find concrete inputs. With a clear output, the rest fall into place.

This is the main secret of the development of operational macros!!! And I will show you in a moment how to conceive operational macros from scratch.

But that’s not the only problem…

The secret of easy Excel macro development…

Just imagine each unit in the car factory would be different. The massive fabrication process would slow down because each assembly task would be new and so it will need much more thought and deliberation. The secret of factory productivity is uniformity. The builders replicate master models.

If every macro is started without a template to follow, each macro will end up different from one another. If the macro development process is not repeatable, you will find the code writing process much more slow and difficult.

We must not forget the purpose of writing macros, which is saving time and effort. Without a macro blueprint, you will be trapped AGAIN being BUSY trying to be unbusy.

You can simplify the macro writing process because every operational macro has a pattern.

The mantra is, “if you have a macro structure, the macro logic will fall into place.”

The 5 building blocks of every operational macro

This macro plan is for those who need to get the macros done in minimum time because the time-sucking and energy-sucking projects are just killing them.

If you want to have fun or if you will only use your macros once, you can go by intuition. But if critical processes depend on your macro or if you macro will be used by other users or clients, or if you simply need to evolve the macro to evolve your career, then you must have a structure in place.

Operational macro building block #1 – Version

If you don’t know the version of your macro, you will never evolve it, you might end up with different versions, you might use an older version each time, etc.

Controlling your macro versions is simple!

Just include the author, date of release, and version number.

The macro CreateDashboard would be 1.0 when you release it. If you later add a change but the functionality remains the same, such as a change in a variable name, you name it 1.1, 1.2, etc. If you add a new functionality such as sending the dashboard by email, you name it 2.0, 3.0, etc.

There’s a whole philosophy of versioning. You can google it.

Operational macro building block #2 – Outputs

A macro is operational when the output of the code is what you want. If you don’t define the output clearly, your code will never become operational.

The output is what makes a macro really operational.

In fact, you should better have an OUTPUT, not OUTPUTS. An output should be concrete, measurable, doable.

  • In the CreateDashboard macro, the output would be a PivotTable showing regions and sales by quarter.
  • In the CalculateSalesCommission macro, the output would be a number (a sales commission).
  • In the ImportTableFromAccess macro, the output would be a table in the current book

If your output is an object (PivotTable, Chart, Sheet, etc), identify it. If your output is a value, define the format (text, number, etc.).

Once you set the output, the rest falls into place.

Operational macro building block #3 – Inputs

To make the macro code delivers the output you want, you need to provide the right inputs and in the right format (text, number, etc.).

In the CreateDashboard macro, the input could be the name of the table and the name of the destination sheet. In the CalculateSalesCommission macro, the input could be the sales amount. In the ImportTableFromAccess macro, the input could be the Access database file name and the name of the table to import.

Define the input’s format and how each input will be passed to your macro, as macro arguments, in cells, through a form.

Operational macro building block #4 – Assumptions

To make a phone call, you need a phone card inside. To start a blender, you need the cable plugged in and the cup locked. Every device has starting conditions to operate and you should know them in advance. You know the conditions reading the user’s manual provided by the manufacturer.

If you don’t define the starting conditions of your macro, the user won’t get it by chance.

Define the starting conditions of your macro. For example, the book containing the table should be open, the folder exists, Power Point is installed on your computer, the table starts in cell A1, the table should be called “table1”, etc.

Clear assumptions also help you to make your macro operational without excessive inputs checking or error coding.

Operational macro building block #5 – Body

This is the code that many see as a jungle of jargon but it’s only the collection and transformation of inputs and the processing of inputs into outputs.

To implement this magic blueprint, you can simply put a block of comments at the beginning of your macros as you see below…

Download the macro blueprint template below…

downloading-from-computing-cloudOperational Macro Blueprint

What’s next?

My Simple Macro Writing System going live in…

-182Days -22Hours -22Minutes -29Seconds

There are other crucial steps you should follow such as naming your macro right, how to code complex instructions, how to debug your macro (even if you plan your macros perfectly, you will get errors along the way), how to distribute your macros, etc. We cannot cover everything here in this free macros masterclass.

In the next lesson, I will reveal the complete and simplified macro writing system to magically write all the macros you dream. With this system, you will finally break free from the chains of time-sucking and energy-sucking projects.

Stay tuned!

Thanks!

John Franco

Previous macros masterclass: The psychology of writing Excel macros

Next macros masterclass: The Simple Macro Writing System fully revealed

Which of these VBA elements was missing in your macros and how their lack affected your macros? Join the conversation below…

3 comments… add one
  • Gautam Shastry

    I understand the value of the first lesson. Will take care. But when we are recording the macro. Even if it long , according to me it should work. Earlier I never had this problem. Please show the way. I am a self learner and has never learnt through any class.

    Thanks in advance

  • Jeff

    I like your formalized structure for macro writing. I have done some of my own macros but it has always been more seat of the pants development.

    • John Franco

      Hi Jeff,

      Imagine Excel developers built the program using intuition and experience rather than a plan or method? Do you think Excel would have evolved till version 16 (which is the current one in 2017)? You must always use your fine intuitive senses but keep in mind that a macro (software) is a very logical thing and the sooner you get the logic right, the sooner you get your code flowing. A macro structure also helps you to keep errors at bay, keep track of changes over time, navigate complex macros easily, etc.

      This macro plan is for those who need to get the macros done in minimum time because the time-sucking and energy-sucking projects are just killing them. If you want to have fun or if you will only use your macros once, you can go by intuition. But if critical processes depend on your macro or if you macro will be used by other users or clients, or if you simply need to evolve the macro to evolve your career, then you must have a structure in place.

      My macro structure is not carved in stone, of course. You can adapt it to your needs and style or you can create your own from scratch.

Leave a Comment