Excel VBA Macros Examples
Similarities between Excel VBA and Access VBA
Theoretically, it should be easy to switch from one MS Office VBA programming language to another, as all the underlying principles are the same. It does not matter if you write VBA macros in Access, Excel, Outlook, PowerPoint or Word. You continue to use:
– Objects, collections, methods and properties
– Variables (the DIM statement)
– IF conditions and loops
– Message fields and input fields
– Subroutines and functions
In fact, the only thing different is the object and collections defined in the application. How difficult can it be to move from (about) Excel VBA to Access VBA? The answer is unfortunately … quite difficult. Here is the reason!
Differences between Excel Visual Basic and Access Visual Basic
The main differences between the two programming languages is that Access supports two macro languages, does not support recording, has two different methods of creating Visual Basic macros, hides much of its functionality in the DoCmd object, and has two separate object models. If that did not make much sense, do not worry – 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 Module or Visual Basic. If you have knowledge of VBA, you should ignore the first one. It is aimed at people without programming experience and does not support proper looping, error handling and many other structures. Although the Access database window contains a MAKROS tab, you should completely ignore it!
Access does not support recording
Want to know how to color a cell in Excel red with pink spots? If you can not guess the macro command (unlikely), you can simply record a macro and look at the resulting code. This is an important reminder, even if you are a VBA guru.
On the other hand, Access does not support the recording – not even in the latest version at the time of writing, Access 2010. This is, to say the least, a shame (interestingly, Word supports the VBA recording, but PowerPoint does not). t more: Microsoft has removed the function since version 2007). This means that you are often forced to resort to the help of Google, Microsoft, or a friend to find out how you can do something in Access VBA.
Two different ways to write macros in Access
Do you want to create a VBA macro in Access? To do this, you must switch to the VBA code editor. Strangely, you can do this in two ways: either press ALT + F11 as usual or click the Modules tab in the Database window and create a new module. Why are there two different ways to do the same? Historical reasons, we think.
Access uses the DoCmd object for many commands
This complicates (or simplifies) Access VBA, which means that about half of the commands begin with DoCmd. For example:
DoCmd.OpenForm – to open a form
DoCmd.Maximize – to maximize a window
Functions like these make Excel a much more logical programming language than Access.
Access has two separate object models
Excel is pretty much self-contained (but if you create your own dialog boxes, use a separate application called Microsoft Forms). However, the access is split into two nearly 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 will probably not notice this difficulty until you start using advanced programming in Access, this is another access feature that can tarnish VBA water.
In summary, we can say that Excel macros are far simpler than access macros. In addition, learning Excel is much faster than learning Access, and you get two unequal learning curves!
Andy Brown is a trainer and developer for Wise Owl Training, a company that offers Microsoft computer training in the UK.