Something Just for You
If you have kids
who are in college, or if you've recently been in college, you know that
one of the challenges is signing up for the courses you want. Frequently
the class you want is unavailable; it might be full or simply not
offered. In most cases, students will just take a different course. However
the truly clever student will decide that they want a course just for
them. They will do the legwork to design a course then recruit a
professor to supervise the independent study – a course just for them.
Last month, we learned how to take the standard Ribbon and add existing
commands (the equivalent of a course that’s already offered and has an
open slot). This month will be the first of two Wizards that
will show you how to add a command that is just for you (the equivalent
of an independent study).
Our goal is to make a custom routine
written in Access’s programming language (VBA) available on the home tab
of the default Ribbon. Unfortunately, this isn't something we can do
directly; we have to have an intervening macro in order to run our code.
So we need two pieces:
macro to run the code.
For our code we will use the following
function. Note that I use a function rather than a subroutine. The reason
for this is that a macro can call a function, but cannot call a
subroutine. Although this may seem illogical, it is one the quirks of
MsgBox "Hello World."
If you already have a module that is not associated with a form or
report, you can put the function there. Otherwise create a new module by
clicking on the create tab and choosing module on the far right-hand side
of the Ribbon. Once your module is created, you can type the function
above and save the module giving it whatever name you like. If you need a
tutorial on how to create a function from scratch, see the August 2006 Wizard.
As I mentioned above, to run our function from the Ribbon, we have to
take a side trip through a macro. To create a new macro, click on the
create tab of the default Ribbon then click on macro, which is on the far
right-hand side in the macros and code group. That will bring you to a
window that has a green cross and a message inviting you to add a new
action with a downward black triangle. Right click on that downward right
triangle and scroll down until you see run code as shown below.
At this point, you will be invited to enter a function name. Below, you
will see that I typed in the name of my function, MyIndependentStudy.
It is important to that you precede the name of your function with
an equal sign and end with the open and close parentheses ().
Next, save the
macro: Let’s call it My
Macro. Then click the Run button (the red ! at the
left-hand side of the Ribbon) and you should see the message “Hello
World.” If you find this is not the case, make sure that your function is
in a module not associated with a form or report.
Step and Coming Attractions
At this point, we have the pieces of the
puzzle in place: the macro and the function. Next month, we will show you
how to integrate these two pieces so they show up on your Ribbon.