Preparing for an Excel Expert Exam

Have you ever written an Excel proficiency exam? Maybe you’ll have some advice or tips for the person who wrote to me this week, asking for help with the Excel Expert 2007 exam.

He’s having trouble with the macros and custom functions that will be part of the test.

It’s been a long time since I wrote the Excel Expert exam, that was part of the old Microsoft Office User Specialist series. The exam has probably changed many times since then, but back then it was a mixture of multiple choice questions and simulated workbooks (if I’m remembering correctly!)

Anyway, I passed, and the certificate is still proudly displayed on my office wall. Well, it’s pinned to the wall, behind the door, but it’s still in good shape! Wow, June 1999 – that was a long time ago.

The Excel Expert Test

The Microsoft website has a list of topics that are covered on the exam, including this section on Managing Macros and User-Defined Functions:

• Record and edit a macro.
  • This objective may include but is not limited to: recording a macro and editing a macro in Visual Basic for Applications (VBA)
• Manage existing macros.
  • This objective may include but is not limited to: moving macros between workbooks, assigning a shortcut key to an existing macro, assigning a macro to a button in a worksheet, and configuring macro security levels
• Create a user-defined function (UDF).

Record and Edit a Macro

There are written instructions and a video on the Contextures website, for recording and testing a macro in Excel. That article briefly discusses macro security levels, and showing the Developer tab.

To see how to edit a recorded macro, you can watch the video on this blog post: Excel VBA Edit Your Recorded Macro. There are written instructions there too, in case you’d prefer to read about it.

Manage Existing Macros

If you need to copy macros into a workbook, or from one workbook to another, there are instructions here: Adding Code to a Workbook

For details on assigning a macro to a worksheet button, take a look at this page: Excel VBA Worksheet Macro Buttons. To see the code and buttons, you can download the sample workbook from that page.

______________