Getting Started on VBA Programming in Microsoft® Excel

Last update on 2024-05-02.

Preface

When the topic turned to macros and VBA on any of the training courses I presented, what people were looking for was how to move their skill on from simply recording a macro to a competence in adapting the VBA code they've recorded.

What prompted most such requests was that a recorded macro works under a fixed set of conditions, but the author wanted it to be flexible.

Most attempts to achieve flexibilty through recording had failed, and macros had ended up longer than ever. As a result they had become a nightmare to maintain.

Macros Take Time

If you're intending to take a recorded macro and turn it into something more useful, then the first thing you have to ask yourself is do I the time to devote to venture in to VBA?

The time to:

• Understand the syntax of the VBA language
• Learn the vocabulary for the sort of code I'll be creating
• Plan what each of my revised macros has to achieve
• Render that plan into the code, i.e. program it
• Test, debug and snag-fix and complete this cycle until my progam performs as intended.

If others are going to use the macros, then 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've some control over your time management and you're beginning to appreciate one of the advantages of structured programming.

Planning

To program effectively and efficiently, a plan is essential. Without it you're 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 four macros:

1. Open the workbook
2. Copy and paste
3. Complete the formatting.
4. Sequence the running of the first three.

Executing the Plan

The example plan in the section above points the way to an inside-out, structured 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're 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've 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 constructions this controlling code will be in a macro, call it myControlProgram, that you will write as something like this:

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

Also, by using this structured approach, you'll 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:

By having developed and tested your new macro on its own, you don't need to make the changes to myControlProgram to slot in myCheckData until you're good and ready to do so.

What Next

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

For this reason I recommend that you include error handling in your macros, as explained in How to Include Error Handling in VBA Code for Excel Macros. Failure to do so will almost certainly create problems for the user when an error occurs.

Macros for Applying Range Names to Cells is as example of a macro with code for the interception of, and response to, errors occuring during a macro's execution.

Taking practice further, Macro Sub and Function Procedures goes into structured programming in some detail. It expands on the implementation of the myControlProgram introduced above.

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's 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.

Excel's a very common piece of software in daily use. Lots of people including me are posting relevant topics, which in turn means Artificial Intellegence has much to go on. Hence AI's a useful aid to your studies.

In Conclusion

Using a planned, structured approach to your macros is worth adopting from the outset of any project. You'll 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.

Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

Thanks, in anticipation.