-->

Macros and VBA Programming in Excel

When the topic turns to macros and VBA on any DMW training course, one of the most common requests made by people attending is for 'tips and tricks'.

What people are looking for is how to move their skill on from one of simply being able to record a macro to one of competence in modifying the VBA code they have recorded.

And what prompts most such requests? It's that the recorded macros work under a fixed set of conditions, but the author wants to make them work under wider circumstances.

Most attempts to achieve this through recording have failed because macros have ended up longer than ever. As a result they have become a nightmare to get right.


Macros Take Time

Most people on our courses are using Excel as a business tool in support of their main activities in the office. They are not planning to be professional programmers, or to engage the services of a professional programmer. Yet they appreciate that macros might save them, and possibly their colleagues, a lot of time.

If you are in that position, perhaps the first thing you have to ask yourself is have I the time to devote to my macros? The time to:

  • Understand the syntax of the VBA language
  • Learn the vocabulary for the sorts of macros you will be creating
  • Plan what each of my macros has to achieve
  • Render that plan into the code
  • Test, debug and snag-fix and complete this cycle until your macro performs as intended.

If others are going to use the macros, expect them to find any number of ways of breaking them. So anticipate a prolonged test, debug and snag-fix cycle.

Since programming and testing, once you get immersed in it, can make time fly, you will benefit from breaking down the overall task into manageable chunks. That way you can complete each chunk within a time slot that fits in with all your other work.

In adopting this sort of approach, you have some control over your time management and you are beginning to appreciate one of the advantages of structured programming.


Planning

To program effectively and efficiently, a plan is essential. Without it you are in danger of wasting valuable time and of producing macros that may not work as intended.

To plan, you identify the various steps your program must go through to achieve the ends for which it is to be used. For example, suppose your program has to open a workbook, copy and paste some cells and then format those that have been pasted.

The skeleton of your plan emerges as one for the creation of three macros: the first to open the workbook; the second to copy and paste; the third to complete the formatting.

The final part of the plan is then to sequence the running of these three macros.


Executing the Plan

The example plan in the section above points the way to an inside-out approach to your programming:

  • Create and test your open-workbook macro. Call it 'MyOpenWB', say
  • Create and test your copy-and-paste macro. Call it 'MyCopyPaste', say
  • Create and test your formatting macro. Call it 'MyFormat', say
  • Wrap your macros in a controlling macro.

The advantages to this approach include these:

  • You are not making any attempt to do everything at once. Hence it must be easier to find a time slot to create one of the three than to try and cobble everything together in one big macro
  • You can test and refine any one of the macros on its own without having to worry about its affect on the others
  • You have a suite of macros, anyone of which might be useful in an entirely different project.

With your three macros completed, all you need is some further code to bundle them up so that they will run in sequence. In the most general of forms this controlling code will be in a macro, call it 'MyControlProgram', that you will write as something like this:

  1. Sub MyControlProgram()
  2. Call MyOpenWB
  3. Call MyCopyPaste
  4. Call MyFormat
  5. End Sub

As you familiarise yourself with VBA, you will be able to return to your macros and refine them.

Also, by using this structured approach, you will be able to extend your control program, if necessary.

For example, you might develop an additional macro—call it 'MyCheckData', say—to run some checks on the data prior to copying and pasting them:

  1. Sub MyControlProgram()
  2. Call MyOpenWB
  3. Call MyCheckData
  4. Call MyCopyPaste
  5. Call MyFormat
  6. End Sub

By having developed and tested your new macro on its own, you do not need to make the changes to MyControlProgram to slot in MyCheckData until you are good and ready to do so.


Learning the Language

The boundaries to your ability to construct macros will be set by your knowledge of the VBA language.

The inside-out approach to macros described above assists you in your learning. That is because it encourages developing code in bite-sized chunks, allowing you to progress a step at a time.

Perhaps as a starting point, record a new macro and examine the code that the recorder has provided for you. F1 on a macro term may launch a help screen.

Some well-chosen keywords input into your search engine will almost certainly give you leads to almost anything Excel and VBA.


What next

It is always a possibility that your macro will not work perfectly on all occasions that it is run. This is particulary the case when other people are using your spreadsheet.

For this reason DMW strongly recommends that you include error trapping and handling in your macros.


In Conclusion

Using a planned, structured approach to your macros is worth adopting from the outset of any project. You will benefit in time saving both in the short and long run.

All the more so if it your intention is to expand your ambitions for VBA and its application to your spreadsheets of the future.