Access Visual Basic and Excel VBA Macros – A Comparison of the MS Office Programming Languages

Access Visual Basic and Excel VBA Macros – A Comparison of the MS Office Programming Languages

Similarities between Excel VBA and Access VBA

In theory it should be easy to switch from one MS Office VBA programming language to another, since all the underlying principles are the same. It doesn’t matter whether you’re writing VBA macros within Access, Excel, Outlook, PowerPoint or Word, you’re still using:

– objects, collections, methods and properties

– variables (the DIM statement)

– IF conditions and loops

– message boxes and input boxes

– subroutines and functions

In fact, the only thing which is different is the object and collections defined within the application. So how hard can it be to switch from (say) Excel VBA to Access VBA? The answer, sadly, is… fairly hard. Here’s why!

Differences between Excel Visual Basic and Access Visual Basic

The main differences between the two programming languages are that Access supports two macro languages, doesn’t support recording, has two different ways to create Visual Basic macros, hides a lot of its functionality in the DoCmd object and has two separate object models. If that didn’t make much sense, worry not – the rest of this article will explain each of these points in turn.

Access has Two Macro Languages

This is a red herring. Microsoft Access has two separate languages for writing macros: one called macros, the other called either modules or Visual Basic. If you have any knowledge of VBA, you should ignore the first; it is provided for people who have no programming experience, and doesn’t support proper looping, error-handling and many other structures. So although the Access database window contains a MACROS tab, you should ignore this completely!

Access doesn’t Support Recording

Want to know how to colour a cell red with pink spots in Excel? If you can’t guess the macro command (unlikely), you can just record a macro and have a look at the resulting code. This is a vital aide-memoire even when you’re a VBA guru.

Access, on the other hand, doesn’t support recording – not even in the latest version at the time of writing, Access 2010. This is, to say the least, a shame (interestingly, while Word supports VBA recording, PowerPoint doesn’t any more: Microsoft removed the facility from version 2007 onwards). This means that you’re often forced to resort to Google, Microsoft help or phoning a friend to find out how to do something in Access VBA.

Two Different Ways to Write Macros in Access

Want to create a VBA macro in Access? To do this, you need to go to the VBA code editor. Strangely, you can do this in two different ways: either press ALT + F11 as normal, or click on the Modules tab in the database window and choose to create a new module. Why are there two different ways to do the same thing? Historical reasons, we think.

Access Uses the DoCmd Object for Many Commands

Access VBA is complicated (or simplified?) by the fact that about half the commands begin with DoCmd. For example:

DoCmd.OpenForm – to open a form

DoCmd.Maximize – to maximise a window

Features like this make Excel a far more logical programming language than Access.

Access has Two Separate Object Models

Excel is pretty self-contained (although if you’re creating your own dialog boxes, you’ll be using a separate applications called Microsoft Forms). Access, however, splits into two almost equal parts:

– tables and queries are part of the Access database engine

– forms, reports, macros and modules are part of the Microsoft Access application

Although you’ll probably only hit this complication when you get into advanced programming in Access, it’s another Access feature to muddy the VBA water.

In summary, then, we’d say that Excel macros are far more straightforward than Access ones. Added to this is the fact that it’s much quicker to learn Excel than it is to learn Access, and you get two unequal learning curves!