I ran across this bit of very-hidden quite arcane VBA UI wizardry, and it needs to see the light of day.
That's how Mark Burns–no stranger to the far reaches of the Microsoft Access community–began a recent email he sent me. And it got my attention. After all, what feature could be (1) so hidden that Mark had never seen it before and (2) so intriguing that he felt compelled to share it with me?
The wizardry to which Mark referred is the hidden
What is WizHook?
WizHook is a hidden property of the
To discover it for yourself:
- Open the Object Browser ([F2]) in the VBIDE
- Right-click in the header section to open the context menu
- Check the box to "Show Hidden Members"
- Choose "Access" from the library dropdown
- Click on the "Application" class
- Click on the "WizHook" member
- Click on the "WizHook" link to open the WizHook class
Microsoft Access WizHook Reference
The best English-language documentation I have come across is JasonM's PDF, Microsoft Access WizHook Reference.
Jason's documentation starts off with the following disclaimer:
I always noticed [the WizHook object] had some interesting sounding procedure names, but there's almost no reference to it on the internet. This is my attempt at documenting it the best I can. Please understand the vast majority of this is the result of trial and error, so if you find a mistake or have a better interpretation of what's happening, please email me so I can update this document.
WizHook documentation is notoriously sparse.
Outside of JasonM's PDF, some of the best documentation for the WizHook object lives on foreign language forums.
Here are the most useful forum posts I could find:
- German (Thomas Moeller)
- Spanish (Juan M. Afán deRibera)
- French (Charles A. Feine)
- English (Colin Riddington, et al.)
The Secret WizHook Handshake
Oh, I almost forgot the most important part of all!
Before you can call most of the methods of the WizHook object, you have to unlock it by assigning the magic number
51488399 to its
WizHook.Key = 51488399 'It's undocumented magic, just go with it.
Public Function GetTextLength(pCtrl As Control, ByVal str As String, _ Optional ByVal Height As Boolean = False) Dim lx As Long, ly As Long ' Initialize WizHook WizHook.Key = 51488399 ' Populate the variables lx and ly with the width and height of the ' string in twips, according to the font settings of the control WizHook.TwipsFromFont pCtrl.FontName, pCtrl.FontSize, pCtrl.FontWeight, _ pCtrl.FontItalic, pCtrl.FontUnderline, 0, _ str, 0, lx, ly If Not Height Then GetTextLength = lx Else GetTextLength = ly End If End Function
The function can be used like this to AutoFit an Access form/report control:
Public Sub AutoFit(ctl As Control) Dim lngWidth As Long lngWidth = GetTextLength(ctl, ctl.Value) ctl.Width = lngWidth + 40 '40: horizontal buffer; may need adjusting End Sub
UPDATE [2022-10-28]: Added German-language link to Thomas Moeller's website.