Macro improvements in Access 2007
Access 2007 and Macros
One feature that might be surprising to many experienced developers is the re-invigoration of macros in Access 2007. We have done a bunch of work in this area that we think benefits end users and developers in some scenarios. If you are running Access 2007 you will notice macros are used extensively in our templates and the button wizard and switchboard manager creates embedded macros.
History of Macros
Many people think macros were a predecessor to Visual Basic, but it turns out that they were introduced at the same time. However, they have different goals. A large majority of knowledge workers are intimidated and frightened by free-flowing code, but they are more comfortable with using a structured grid such as the macro designer. We found that there are many scenarios where users are very successful "programming" using macros. Macros target a broader audience of Access users that don't have the skills to drop into VBA and write code. Yet, these users are very successful picking an action and setting a couple parameters. This is why many applications built by knowledge workers are loaded with all sorts of macros.
Unfortunately, the Access team has not focused on knowledge workers for a long time. We have traditionally been more developer focused, so our macros have not received much attention. One of our goals for Access 12 has been making the product easier to use by a broader audience (while still delivering features relevant to developers). We think there are many knowledge workers that are tracking information in Excel that could be more appropriately stored in a database. As you can tell from previous posts, Access 2007 has more new knowledge worker features than previous releases. I also think there are some nice enhancements for developers building bigger applications.
There are a number of things we did to improve the macro experience.
Embedded Macros
Just like you can save a SQL Statement in the Row Source or Record Source property, you can now save a macro in any event property. These are called Embedded Macros. Similar to SQL Statements, you can use Save As to create a standalone macro that can be reused. One nice thing about Embedded Macros is that you can copy/paste and "Open Form" button and just change the form name that it opens, and you are done.
When you think about templates and renaming objects--name fix-up becomes really important for users that want to start with one of our templates and modify it to more closely reflect their business. In this case, it is far easier for Access to perform name fix-up operations on macros than try to fix-up code. Think of the scenario of renaming the Contacts table to Employees in the Issues template. Many customers will want to do that—It would be really hard for us to do all the right things if all the logic was expressed in code.
Macro Sandbox
The biggest thing that we added to the macro architecture was the concept of a "sandbox". Certain actions can run even when a database is not trusted. You can think of these macros like expression or formulas in Excel. VBA can never be trusted, so it never runs in an un-trusted database, but you can now use macros to build simple applications that are fully-functional without having to trust the solution. This was our goal for templates. They will leverage these macro that are allowed even when the instantiated template is not trusted.
The Show All Actions button switches the contents of the drop-down list in the Actions column between all actions and only the actions that are allowed in databases that have been trusted. The yellow warning sign in the record selector indicates the action won’t execute unless the database is trusted.
(Click image to enlarge)
Variables and TempVars
We also introduced temporary variable that are accessible via VBA and macros. TempVars are designed to store variant data that is transient in nature. Think of them as global variables that work across the whole product—anywhere there is VBA or an expression. They are exposed as an enumerable collection off the Application object. There are two properties Name and Value and two methods Remove and RemoveAll.
There are four new macro actions associated with TempVars:
(1) TempVars![Name]returns the variant value that was stored.
(2) SetTempVars(Name, Expression) allows macros to set a TempVar to a given value.
(3) RemoveTempVars(Name) removes the TempVar from the store.
(4) RemoveAllTempVars empties the TempVars store.
Here is an example of TempVars in use in a macro:
(Click image to enlarge)
Error Handling
One of the key weaknesses of macros in the past is the inability to handle errors. Well, that is not longer true. We added an OnError action that is similar to the On Error statement in VBA. OnError has three different settings:
(1) 'Next', which is equivalent to "On Error GoTo Next" in VBA, and allows you to keep on running the macro while ignoring AND suppressing all errors.
(2) 'Fail', which is similar to “On Error GoTo 0” in VBA, will revert error handling to the default behavior of having macros fail completely on hitting any errors executing actions.
(3) 'Macro Name' is directly related to the 'Macro name' parameter. This allows you specify a label on the macro to where the macro will jump if any errors are found. This is very similar to the "On Error GoTo " in VBA. You can easily use this to put up your own error messages:
OnError (Macro Name, "Error")
OpenForm (Form, "Form1", …)
Error: MsgBox ("Error: Custom message", …)
Button Wizard
One frequent complain that we hear about Access 2003 is the lousy code the button wizard generates.
For example, if you create a “Record Operations > Print Record” button, this is the VBA it generates:
Private Sub cmdPrintRecord_Click()
On Error GoTo Err_cmdPrintRecord_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.PrintOut acSelection
Exit_cmdPrintRecord_Click:
Exit Sub
Err_cmdPrintRecord_Click:
MsgBox Err.Description
Resume Exit_cmdPrintRecord_Click
End Sub
How do you like the DoMenuItem action? Yeah, I have memorized what the 8 means…
Here is the equivalent embedded macro:
RunCommand (SelectRecord)
RunCommand (PrintSelection)
All the commands in the button wizard will now generate safe macros that execute property in un-trusted databases. We even updated the icons in the wizard!
Macros and Developers
One thing I would be sure to emphasize is the fact that macros behind form controls keep their contents even via copy/paste, whereas VBA events are lost.
As a developer, if you write VBA code to do the majority of the interesting work, consider creating a simple embedded RunCode macro on the buttons that call the VBA code as opposed to VBA events directly behind the buttons. The embedded macro acts as the bridge between the UI and the VBA code. That gives the full functionality of the UI designer with copy/paste as well as the robustness of the VBA programming model.
The new Northwind sample was developed this way where many of the simple open form with a filter functionality is stored in embedded macros. When we needed to do the heavy lifting we used VBA. Turns out there is far less code and the code that is there is more interesting.