Expert Excel Programmer Resources

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:

– variables (the DIM statement)

– IF conditions and loops

– message boxes and input boxes

– subroutines and functions

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!

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.

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. 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:

With a little bit of training, Excel VBA programming will be as natural as speaking.

Controls and forms

After your training in Excel VBA, your users may not even realize they are using an Excel spreadsheet!

Better looking spreadsheets

Some calculations are simply too complex to fit into a single cell. Sometimes it requires a series of cells in between the data source and the results, each with an intermediate result. – objects, collections, methods and properties

– variables (the DIM statement)

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

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!

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.

DoCmd.OpenForm – to open a form

DoCmd.Maximize – to maximise a window

Copy the code below and paste it in the open window. 

Function Daytitle(InputDate As Date) 

Case 1

Daytitle = “Sunday”

Daytitle = “Saturday”

End Select End Function

Type the date 04/01/1922 in cell a1. In cell b1 type out your function: =daytitle(a1). The result should be “Saturday”.

That is how to use VBA, without knowing VBA.

Find More Fitness Training Programmes Articles