Hidden & little known features in VBA and the VBE (1 Viewer)

[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?

 

[DickyP]

Can you give examples?

In general the Forms2 controls, which you can insert into Access forms - especially the Date Picker. Not so useful as they once were in older version as MS has 'enhanced' Access.

 

[isladogs]

I rarely work with user forms or ActiveX controls in Access which is why I was asking for examples.

All the MS Forms2 controls can be used with standard forms. The reference is added automatically.
As far as I am aware, exactly the same functionality is available for these controls whichever type of form is used.
What I'm asking is details of specific functionality for those controls that is only available when used in a user form.

I'm not aware of an MS Forms2 DatePicker control - can't see it in the list nor in the control box from a user form
The old ActiveX DatePicker control was deprecated in A2010 though its possible to reinstate it for 32-bit Access only

 

[DickyP]

I rarely work with user forms or ActiveX controls in Access which is why I was asking for examples.

All the MS Forms2 controls can be used with standard forms. The reference is added automatically.
As far as I am aware, exactly the same functionality is available for these controls whichever type of form is used.
What I'm asking is details of specific functionality for those controls that is only available when used in a user form.

I'm not aware of an MS Forms2 DatePicker control - can't see it in the list nor in the control box from a user form
The old ActiveX DatePicker control was deprecated in A2010 though its possible to reinstate it for 32-bit Access only

Ah but they don't work fully - I suggest you do some extensive usage - some properties of the controls just aren't exposed.

And your right - MS Forms Date picker has now gone (using Access 2021). Most of my usage was with 2003 and earlier and to be honest I just don't have the inclination investigate them all as the exist now.

As well as MS Forms I also use a just a couple of other Active X components and one in particular, a bar code control, which works well enough is far easier on a user form.

 

[isladogs]

Ah but they don't work fully - I suggest you do some extensive usage - some properties of the controls just aren't exposed.

And your right - MS Forms Date picker has now gone (using Access 2021). Most of my usage was with 2003 and earlier and to be honest I just don't have the inclination investigate them all as the exist now.

As well as MS Forms I also use a just a couple of other Active X components and one in particular, a bar code control, which works well enough is far easier on a user form.

From your earlier comments, I assumed you knew some answers to my question.
As I said, I very rarely use ActiveX controls or almost never create user forms.
As for bar codes I just use a textbox with a barcode font on a standard form.

Anyway, I think its time to move on.

 

[D_Walla]

Great thread - The VBA IDE and UserForms are probably my two favourite topics. Some of these will be familiar to many, but I'll jot them down just in case. I should add that, for context, I'm coming from an Excel VBA perspective, but I've double-checked the points below to make sure that they are still valid for Access.

1. Icon Editor

The VBAIDE has its own built-in icon editor - it's rather useless, though, unless you have a burning desire to handpaint every single pixel of the icon, but it's there.

How to find it:

After following the instructions to get to the Customize window in post 6 above, you can right click on any of the icons in in the CommandBar. The following context menu appears that allows for a number of customisation options - Edit Button Image will display the icon editor.

2. Keyboard Shortcuts

This is one from StackOverflow - by adding Accerlerator keys to commandbar buttons, you can execute them with a keyboard command.

For example, it is possible to builk comment out multiple lines of code at once in the VBA IDE by using Alt C (or whatever letter you like)

In the screen capture above, you'll note that the the comment icon has text next to it - this was accompished through the customisation context menu mentioned above. Here it is a matter of selecting Image & Text (or Text Only), and then adding the & character before the letter you want to use as the keybaord shortcut, in this instance, I've used C. And that's it.

3. The Label Control

The MSForms Label control has built-in bitmap transparency. So it will apply 1-bit transparency to the bitmap image based on the color of the bottom leftmost pixel in the image. So in the image below, the left control is an ImageBox control, and on the right is a Label control.

Just picking up on a few of the comments mentioned above:

- the DatePicker control - Colin is right that this was never migrated to a 64bit version by Microsoft for [insert compelling reason here]. It is possible to create one through APIs. The controls themselves are just wrappers for the same set of API calls.

- Generating persisting userform controls - It is true that controls generated dynamically through the standard method do not persist beyond runtime. However, they will persist (and so you can programmatically generate full userforms/controls) by adding a reference to the Microsoft Visual Basic for Applications Extensibility Library 5.3. This then gives access to the Designer, which then allows you to do things like the following - which will generate a Userform and a commandbutton that persists simply by using the Designer object:
Option Compare Database

Sub MakeForm()
  Dim NewForm As Object, NewButton As MSForms.CommandButton, Line As Long

  Set NewForm = Application.VBE.ActiveVBProject.VBComponents.Add(vbext_ct_MSForm)
  With NewForm
    .Properties("Caption") = "New Form"
    .Properties("Width") = 100
    .Properties("Height") = 75
    .Properties("BackColor") = RGB(220, 230, 250)
  End With

  Set NewButton = NewForm.Designer.Controls.Add("Forms.CommandButton.1")
  With NewButton
    .Caption = "Click Me Now!"
    .Left = 10
    .Top = 10
  End With

  With NewForm.CodeModule
      Line = .CountOfLines
      .InsertLines Line + 1, "Sub CommandButton1_Click()"
      .InsertLines Line + 2, "MsgBox ""Hello World!"""
      .InsertLines Line + 4, "End Sub"
  End With

  Set NewForm = Nothing
End Sub

You can actually get away with doing quite a few things with the Designer object that one would normally think isn't possible in the IDE.

 

[isladogs]

Thanks! I'd forgotten about the button editor!

Another couple of fairly obscure things:
Some developers will be aware that you can drag database objects from the nav pane either onto the desktop (to create a shortcut for dropping into another database later) or drop them into the navigation pane of another database.

You can also drag a module from the project explorer of a VBE project to create a module shortcut on the desktop or (as an alternative to the standard import method), you can drop it into the project explorer of another open database . . .
. . . and yes, you can also do both of those with user forms!

 

[DickyP]

From your earlier comments, I assumed you knew some answers to my question.
As I said, I very rarely use ActiveX controls or almost never create user forms.
As for bar codes I just use a textbox with a barcode font on a standard form.

Anyway, I think its time to move on.

I did know a lot more in the past, when the controls were essential. I usually found the problems when I just couldn't to with the controls in Access, what I could do in Word.

As you say time to move on - and thank you for the info about using UserForms directly in Access - wish I known that years back.

 

[isladogs]

One more item from me for today.
You can use the VBE to do bitwise operations. For example:

If that looks like nonsense to you, see https://dev.to/stephengade/mastering-bitwise-operations-a-simplified-guide-2031

 

[Pat Hartman]

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

Best to check that you can do that in all cases especially if you are intending to use the Runtime engine to run the app or to distribute an .accde.