Hidden & little known features in VBA and the VBE

[Pat Hartman]

Also, don't forget that if you are having to create controls on the fly, there is something very wrong with your design.

 

[MajP]

The JKP Treeview builds a tree dynamically from a Userform and MSForms controls. This is the best example of what you could do with a UserForm and cannot do in an Access form.
See discussion.

Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist

@MajP Works perfectly on my 32 machine It's a very very nice tool, I can already see loads of ways I can make use of it Thanks I'll download it on my 64 machine later but as It doesn't use any API calls it may only be a case of reselecting the reference.

www.access-programmers.co.uk

I have built a tree using this interface with with thousands of nodes and thus thousands of controls built on the fly. This works in an Accde because as I said you can actually build on the fly not like Access where you have to go into design view (bad), and welll beyond the limitations of Access forms. The below tree loads up to 10K nodes. Every line, box, symbol, name is a control.

don't forget that if you are having to create controls on the fly, there is something very wrong with your design.

Yes if someone is building controls because there database is changing, but that is probably not what someone would be doing. More likely a nifty display of data that you cannot do in a native form.

 

[DickyP]

I'd just like to thank Isladogs for starting this thread. Has been extremely useful, especially where it is reminding me of things I know but had forgotten!

 

[DickyP]

Also, don't forget that if you are having to create controls on the fly, there is something very wrong with your design.

Disagree - the feature was very useful at times when using customised Word documents. Multiple tab instances according to need were a deliberate part of the design.

Is probably true in Access, however, where as the site shows most (many of the) problems raised are down to poor initial design, especially of the data.

 

[isladogs]

I'd just like to thank Isladogs for starting this thread. Has been extremely useful, especially where it is reminding me of things I know but had forgotten!

One of the reasons for me starting this thread was also to remind me of things I had forgotten e.g. the button icon editor.
Another is to hopefully find out new things that I never know about.

I have to admit that I wasn't expecting the significant interest in user forms but clearly that has struck a chord with some of those reading the thread.

Hopefully lots more info still to come from forum members!

Earlier today, someone told me about this useful article https://bettersolutions.com/vba/visual-basic-editor/shortcut-keys.htm.
The site appears to have a lot of useful info about the VBE

 

[MajP]

Probably one of the most important features when debugging code especially if building your own classes is here.

Probably most people have break on unhandled errors set.
1. But if you have an error handler and you get the error message you may not know exactly where that code is failing. So you can switch it to break on all errors and have it break without having to comment out the Error handler. Then switch back once fixed. However, if you forget to change back you might think your error handler is not working if "break on all errors" is selected.
2. If you have built a class and your code inside the class is what fails it will not break in the class and show you where it failed. The line calling the class method or property will be where the compiler breaks. You can change it to Break in Class Module to instead fail inside the class so you can find your problem code in the class.

 

[xavier.batlle]

1. But if you have an error handler and you get the error message you may not know exactly where that code is failing. So you can switch it to break on all errors and have it break without having to comment out the Error handler. Then switch back once fixed. However, if you forget to change back you might think your error handler is not working if "break on all errors" is selected.

To fix errors on procedures that have "Error handler", I find the "Extra Resume" technique very useful instead of commenting out the Error handler.
https://nolongerset.com/the-extra-resume/

 

[MarkK]

Another trick you can do debugging is change the Error Trapping setting (indicated by MajP in #26) programatically using...

    SetOption "Error Trapping", < values in 0, 1, or 2 >

Or you can write a little routine that saves you typing, and can return the current setting, like...

Public Enum enErrTrap
    et_Show = -1
    et_All
    et_Class
    et_Unhandled
End Enum

Public Function ErrTrap(Optional Value As enErrTrap = et_Show) As enErrTrap
    If Value = -1 Then
        ' display current setting
        Select Case GetOption("Error Trapping")
        Case et_All:          Debug.Print "All"
        Case et_Class:        Debug.Print "Class"
        Case et_Unhandled:    Debug.Print "Unhandled"
        End Select
    Else
        SetOption "Error Trapping", Value
    End If
    
    ErrTrap = GetOption("Error Trapping")
End Function

If exposed from a standard module...
• when you type "ErrTrap<enter>" in the immediate pane it prints the current setting
• you can set it in immediate pane by typing "ErrTrap ", which opens intellisense to select from enErrTrap
• finally, during code execution you can change the current setting, say at the beginning of process you are debugging set it to break on all errors, and then change it back, also programmatically, to only break on unhandled, like...

Sub StartErrorProneProcess()
    ErrTrap et_All
    ProneToError
    ErrTrap et_Unhandled
End Sub

Sub ProneToError()
On Error Resume Next
    Debug.Print 1 / 0
End Sub

And it's a function, returning the value of the current setting, so you can even test its state during execution, and change it conditionally.

 

[D_Walla]

I've got plenty of things to say on the topic and more tidbits to share, but a lot tend to focus on the UserForm and the UF controls, and I don't think I fully appreciated that userforms weren't as widely used in Access and I don't want to bore anyone so focussing on the IDE, is anyone aware of the Easter Egg that still exists in the IDE? I know Microsoft largely removed all the Easter Eggs from their programs as they got less fun, but given that the IDE is circa 2000BC, I thought there was a fighting change that they've leeft it alone.

I am unable to share links, but there is a video on Youtube from 10 years ago called "Microsoft Visual Basic 6.0 Easter Egg - VB Credits" (video id = z3xO964UIpM) which shows how to access the Easter Egg in the vb6 IDE - which is a simple animation and midi music playing with the credits of the developers. I haven't been able to get it working:

And I've read that it's not just a VB6 thing and is also available from the VBA IDE. So I've actually looked at the contents of the VBE7.DLL file, and the MIDI file is in fact there... I just can't get the window to load...

But anyway, there's this hidden/little known feature of the IDE.

 

[DickyP]

There is really no difference between VB (pre .NET) and VBA: just in the past the versions lagged behind VB 5 / 6 etc before they got into Word, Excel, Access, Visio etc. As an MS Engineer said to me at their offices in Slough years back - they are all VBA but the Application that VB is embedded in is a Compiler.

 

[isladogs]

This is the YouTube link mentioned in post 29:

The instructions in the video description tell you to rename the About Visual Basic Help menu item.
In the VBE, there is an About Visual Basic for Applications menu item but renaming it didn't show the old Easter egg

The IDE has been updated a few times over the years. Nothing dramatic but it would be a surprise if the Easter egg is still there.

 

[Pat Hartman]

they are all VBA but the Application that VB is embedded in is a Compiler.

The "A" stands for application and that is what defines the object model. All versions of VBA have a different object model and different ways of accessing those objects. So, VBA code to access Word objects has to be translated when the code is used in Access. You cannot use the reference method that Word uses.

 

[D_Walla]

I'm surprised too, but the assets are still there - the MIDI file is resource 5432 (RCDATA) in the VBE7.DLL

and Bitmap Resource 5433 looks to me to be the cloud bitmap image in the background of the animation (on its side):

And I'm on MS Office 365.

 

[isladogs]

If you look back at post #1, the VBE also displays an old and apparently redundant VB6 Components dialog box when Ctrl+T is pressed.
Likewise, just because the items still exist for the Easter egg, it doesn’t mean that they can be used.

 

[DickyP]

The "A" stands for application and that is what defines the object model. All versions of VBA have a different object model and different ways of accessing those objects. So, VBA code to access Word objects has to be translated when the code is used in Access. You cannot use the reference method that Word uses.

Can't tell if you are agreeing with me, disagreeing with me, or just commenting! The point I was making is that the VB engine is the same whatever the application, it's just the embedding that changes.

 

[Pat Hartman]

Can't tell if you are agreeing with me, disagreeing with me, or just commenting! T

I was trying to be specific on where the differences lie.