Don’t let the short length of this article fool you. When I discovered how to add my custom code routines to Access’s intellisense feature, I discovered a coding technique that I have used every day since! Think about it. Have you ever wondered how you could get your custom VBA code routines to show up in an Intellisense list that you could easily pick from? Well, you can, and here’s how.
Tip #1: The ”Friend” class scope declaration in forms and reports: First, if you don’t know about the “Me” object in Access, it refers to the form or report that your VBA code page is attached to. Forms and reports are class objects. In your VBA code, you can simply type “Me.” (without the quotes but with the “period”) and get a list of all the controls and properties in the object’s IntelliSense listing. Pretty handy, eh?
Now, let’s see how we can add our custom “sub” and “function” procedures to the list. We can do that by simply declaring them using the “friend” keyword. Instead of starting our procedure with the usual:
Sub ProcedureNameHere()
We add the “Friend” keyword at the beginning:
Friend Sub ProcedureNameHere()
Now, when we type “me,” we see that the name of our procedure appears in the IntelliSense listing! We no longer have to try and remember what name we gave our procedures; the names are now on the IntelliSense list!
Tip #2: Adding the name of the basic module to your calls to public procedures: Are you tired of having to look up the names of your custom procedures stored in the basic modules? Just type the name of the basic module first, then the dot, and see a listing of all the public procedures pop up in the IntelliSense listing. For example, instead of:
Call FunctionName try Call basModuleName. with the dot on the end.
This will not only display all public procedures in the IntelliSense listing, but it will also eliminate any “ambiguous procedure name” compile errors if you happen to have the same procedure name in more than one code module. (Even so, I would not recommend having the same public procedure name in more than one code module.)
That’s all there is to it. Happy VBA coding!
If a person uses the “Private” and “Public” tags for Subs and/or Functions, where is the “Friend” tag placed? Before or after the Public/Private tag? E.g. Public Friend MySubI(), or Friend Public MySub()?
Todd, neither. The keywords
Public,PrivateandFriendare all mutually exclusive. TheSub MyProcedure()is actually equivalent toPublic Sub MyProcedure(). For clarity, I recommend against omitting the optional access modifier keywords.However, a
Friendprocedure can only appear on a class module, not a standard module.More information can be found here and here.