Hidden & little known features in VBA and the VBE (6 Viewers)

[isladogs]

Prompted by an old thread at Stackoverflow, I thought it might be interesting for members to share features that are not widely known in VBA.
Here's a couple to get started. If the thread gains traction, I may add some more ....

1. Adding Option Private Module in the declarations section is used at module level to indicate that the entire module is private.
This means module-level parts not declared Private are available to other modules in the project, but not to other projects or applications.

This has another benefit in that it can be used to fully hide details of constants, variables and procedures in an ACCDE file.
When an ACCDE file is created, the module code is no longer available to view or edit.

However, a lot of information can still be seen in ACCDE files including a list of procedures and values of any constants that have been defined.
To prevent this, you can add the line Option Private Module to any standard or class module.

For more details, see https://www.isladogs.co.uk/vbe-option-statements/

2. Pressing Ctrl+T whilst in the VBE brings up an archaic Components dialog. Its in all versions of Access though apparently of little use.

Which little known / little used features of VBA would others like to share?

 

[Edgar_]

1. Get the value and type of anything that is currently running by using the debugger:
You don't even have to know the name of what you're looking for, everything is there for you to explore and start using right away, even undocumented stuff (a lot of stuff is undocumented). Avoid those pesky Debug.Print statements that you later have to remove or comment out.

How:
- Open the VBE Editor
- Go to the View menu and choose Watch Window.
- Right click the window and choose Add Watch
- Write application in the input box
- Select All modules and All procedures as context

- Need to see what an open form looks like? Expand Application, then find the Forms collection and choose your target

- You'll get something like this:

Note: just check it out, it even lets you anticipate the error you'll get if you try to access, say, InputParameters: "<You entered an expression th..."
- Need to see a control in that form? You can find those at the end of the Form object or in the Controls collection, just choose your target:

- Need to see a custom collection? load it there, some variable? load it there. Just make sure to select the right context. Most times you just have to drag the variable from your code and drop it there.

This applies to the whole Office suite, you can do this in Excel, PowerPoint, Word, etc. Even non-Microsoft applications. That windows even lets you track value changes or you can write an expression and have the IDE break automatically when that expression is happening so you can evaluate the whole situation at the precise moment.


2. Find out the default member of anything, if it has a default member:
How:
- Open the object browser
- Find your object
- Look for an icon in cyan color

 

[MarkK]

What Option Private Module is to a standard module, so the Friend modifier is a procedure. A procedure can have three possible modifiers, Public (default), Private, and Friend.

When declared as Friend, a procedure is only visible--and only available to execute--within the same project it is declared.

This is useful if you've exposed a class from a library, and you want to hide the methods the library uses to create the class, like Init() or Load(), or other state management members the Library might call or consume internally. This enables you to create and manage the class effectively, but only expose to consumers the members they'll need, and hide all the plumbing.

And since you can't create a type exposed by a Library directly using the New keyword anyway, therefore the Library must expose a method to return an instance. This way you can force consumers to provide parameters the Library validates, then the Library can call a Friend method constructor for the class, return an instance, and that constructor is not visible to the consumer.

This becomes increasing valuable if classes exposed by your library expose or consumer other classes exposed by your library, because using the Friend modifier, all your library-based classes can expose rich functionality to each other--so they can play nice together--but that are not useful, or possibly even hazardous, if users could call them.

 

[MajP]

In Access you can actually use two types of forms. The Access form and the VBA UserForm. If you make Excel applications you may be familiar with UserForms.

1. Need to add the toolbar

2. Now you should see it in the insert menu

3. There are some different things you can do with a Userform that you cannot do with an access form. Building controls on the fly is supportable with a UserForm at runtime. If that is something you need to do a userform may be a better option.

4. Runs like a form but not displayed in navigation pane. Good way to hide a form.

 

[MarkK]

Showing the UserForm toolbar did not enable addition of a UserForm for me, but I was able to export a UserForm.frm file from Excel, and import it directly into the Access IDE. This automatically added an MS Forms 2.0 reference, but the UserForm insert option still doesn't appear.

I am interested in being able to create controls in code, and use drag/drop to/from controls.

Thanks @MajP, this is very cool.

 

[isladogs]

You need to add the UserForm item to the Insert menu manually
Right click on an empty space in the toolbar area and click Customize

Select Insert on the dialog box and drag the UserForm item to the Insert menu

The same process can be done to add /rearrange or remove items to/from the various toolbars

@MarkK
You wrote:

I am interested in being able to create controls in code

This can be done with a standard form e.g.

 ' Create new form
    Set frm = Application.CreateForm
    
    ' Set positioning values for new control.
    intLeft = 100
    intTop = 100
    intWidth = 12000
    intHeight = 9000
 
   'add modern chart control
    Set ctl = Application.CreateControl(frm.Name, acChart, , , "Chart0", intLeft, intTop, intWidth, intHeight)

 

[MarkK]

This can be done with a standard form e.g.

But you have to be in design view don't you? I assume with the UserForm you can do so while it's open, but I haven't checked it out yet.

You need to add the UserForm item to the Insert menu manually

OK, got it, thanks.

 

[isladogs]

But you have to be in design view don't you? I assume with the UserForm you can do so while it's open, but I haven't checked it out yet.

With standard forms, controls can only be created in design view. However, you can give the illusion of doing so by showing / hiding controls at runtime.
You can indeed create user form controls on the fly but AFAIAA any new controls created only exist during runtime. They do not persist

User forms are modal by default and do indeed have features that are not available in standard forms but overall they are far more limited in scope / functionality.

 

[DickyP]

The other real advantage of user forms is that most Active X controls run with all facilities which is not true with Access forms.

 

[isladogs]

The other real advantage of user forms is that most Active X controls run with all facilities which is not true with Access forms.

Can you give examples?