Category: Visual Basic Editor

Setting the Tab Order of Userform Controls Programmatically

Posted on by Dick Kusleika

I hate designing userforms. I mean the part where I’m lining up controls, renaming controls, and all the other super-fiddly parts of making a form look and act right – like setting the tab order. I always wait until the very end so I don’t have to do it twice. And that Tab Order dialog? Forget about it.

When I’m finally ready to set the tab order, I find the first control and click Move Up until it’s at the top. Next, I find the second control and click Move Up until it’s just below the first control. Then I go to Whammyburger and force them to serve me breakfast. Then I go back and do the rest of the controls. It’s maddening. I decided finally to do something about it.

First I set a reference to the VBA Extensibility Library.

Then I put this code in a standard module

This is pretty brute force, but it only take a few seconds to run, so who cares. The i and j variables move through the userform point-by-point, left to right, top to bottom (that’s the typographical point, not the agenda items point). For every point on the userform, it loops through every control to see if that control starts at that point. If it finds one, it sets the controls TabIndex to the next number. At the end it just prints out the control names and tabs.

Even if that’s not exactly how I want my tab order, it gets it close and I can make other modifications manually.

If you have a super-wide or super-long form, it will take longer. The right way to do this is to put all the controls in an array and sort them by their Top and Left properties. If this method is too slow, you should write that up. Then send me a copy.

Tidying Up the Project Explorer

Posted on by Dick Kusleika

You know that pressing Ctrl+R in the VBE takes you to the Project Explorer. Of course you do. I’m not going to sit here and insult your intelligence by pretending that you don’t. But that means you are probably also just as confused as I am about which projects the VBE “chooses” to expand and which stay closed.

I have a lot of add-ins and that makes my Project Explorer very messy. Ideally I want the active project to be expanded and everything else collapsed. But I can’t have that. I decided I’ve had enough and added the following hotkey to my AutoHotKey VBE.ahk file.

^+r::
Send ^r
Send {End}
Loop 20 {
Send {Left 2}{Up}
}

When I’m in the VBE (an IfWinActive condition in VBE.ahk) and press Ctrl+Shift+R, the focus goes to the Project Explorer, goes to the last project, then closes up to 20 of them. Two left arrows will collapse an expanded project and has no effect on a collapsed project. The up arrow goes to the next project. Genius, you say? I humbly agree.

I don’t keep my modules in Folders (Toggle folders using the button at the top of the Project Explorer). If you do, you’ll need to change the {Left 2} to {Left 4}.

Listing Calling Procedures

Posted on by Dick Kusleika

I have this awesome machine with 64-bit Office sitting under my desk. I don’t use it to code because MZ-Tools doesn’t work on 64-bit Office and I need that (and a few other things) to be productive. I only use a few features from MZ-Tools, so I think I’ll just write them in VBA. I took my first stab at the Procedure Callers feature.

Public Sub ListProcedureCallers()

Dim vbProj As VBProject
Dim vbModule As VBIDE.CodeModule
Dim vbComp As VBIDE.VBComponent
Dim i As Long
Dim lActiveLine As Long
Dim sProc As String

'get the name of the current procedure
Application.VBE.ActiveCodePane.GetSelection lActiveLine, 0, 0, 0
sProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(lActiveLine, vbext_pk_Proc)

'only look in the active project
Set vbProj = Application.VBE.ActiveVBProject

'loop through the code modules
For Each vbComp In vbProj.VBComponents
Set vbModule = vbComp.CodeModule

'print the procedure for any line that contains the name of the active procedure
If vbModule.CountOfLines > 0 Then
For i = vbModule.CountOfDeclarationLines To vbModule.CountOfLines
If InStr(1, vbModule.Lines(i, 1), sProc) > 0 And vbModule.ProcOfLine(i, vbext_pk_Proc) <> sProc Then
Debug.Print vbComp.Name, vbModule.ProcOfLine(i, vbext_pk_Proc), vbModule.Lines(i, 1), i
End If
Next i
End If
Next vbComp

End Sub

I just wanted to get something down and not be too worried about how well it works. This procedure just prints to the Immediate Window rather than a fancy userform that let’s you go directly to one of the procedures.

One of the things I don’t like about MZ-Tools is that it searches for callers in all open projects. I can see that value in that, I just personally have never needed it. And for procedures with common names, it shows a crap ton of stuff. I made my procedure only search the current project.

One of my property procedures in one of my class modules is named Active. When I looked for its callers, I got every procedure that uses ActiveWorkbook or ActiveSheet. My code does not discriminate – if the name of the procedure appears in the line of code, it’s a hit.

How do I avoid that? For the Active property, all I have to do is look for a space after the word Active and I should be good to go. Except for comments, perhaps. That’s fine for a property with no arguments, but if it has arguments or is a method with arguments, there won’t be a space after it but a parenthesis. Can I search for either a space or a paren? Seems like it, but I’ll have to think it through.

Another thing I don’t like about MZ-Tools is that it doesn’t care what class module you’re in when you look for calling procedures. Every one of my Collection Classes has an Add method. When I search for procedure callers for Add, I get every call to every Add method in every class.

That’s a little tougher proposition. I could be very opinionated, as I am, by looking for clsPlural.Add rather than just Add. I always name my class instance variables clsXXX. That would work for me, but wouldn’t be very general purpose. While I’m a well-known selfish prick, I do still care about you, dear reader. Even if I were so inclined, I’d have to still look for With blocks. I can’t just look for clsPlural.Add, I have to also look for .Add, then I have to search up the lines of code for a With before I hit an End With, then I have to determine the variable… My goodness that sounds like a lot of work. This is probably why MZ-Tools doesn’t care which Add method I’m looking for – it’s just not worth it.

Here’s some things I’d like to do:

  • Find actual callers, not just the procedure name
  • Omit finds in comments
  • When I’m in a class, only find properties/methods from that class
  • When I’m on a Property Get, don’t return Property Let assignment statements
  • Go to the first caller automatically, but still list the rest somewhere
  • Other stuff I haven’t thought of

What say you?

AutoHotkey in the VBE

Posted on by Dick Kusleika

I downloaded AutoHotkey recently for something not Excel related. Well, it was kind of Excel related so I guess I’ll tell the story. I’ve been using the Save As feature of my Kwik Open add-in and really enjoying the lack of folder navigation. But it has sewn the seeds of discontent. When I’m doing a Save As in other programs, like a PDF viewer, I don’t get the keyboard love. I got AHK so I could quickly jump to some of my favorite folders without leaving the keyboard.

But then I got to thinking about other ways I could use this tool. One of my pet peeves about the Visual Basic Editor (and there are many) is that typing “endif” will auto-expand to “End If”, but typing “endwith” just sits there like an insolent child. I thought maybe I could fix that with AHK. And I could. But that wasn’t enough. Check out these two AHK scripts I wrote.

#IfWinActive ahk_class wndclass_desked_gsk
::with::With{Enter}End With{Enter}{up 2}{end}
:*:thenn::Then{Enter}{Enter}End If{Enter}{up 2}t

The first line tells it to only work in the VBE window and it comes with a windows spy utility to find out the ahk_class of whatever window you like. The second line monitors for when you type the word “with”. When you do that, it replaces it with With and End With – even better than expanding endwith. Then it moves the cursor back up to the With line so you can continue coding. If you type “with” inside a comment, it’s trouble. So that’s something to work on.

The third line looks for when you type “then” and press Enter. It replaces it with an If block and puts the cursor in the middle, ready for more code.

Watch the video to see it in action. I’m not sure why the video is blurry at the start nor do I know how to fix it.

As you might guess, I’m going to love this.

An MSForms Treeview

Posted on by jkpieterse

If you have ever used the Treeview control from the “Additional controls” section, then you know what a versatile control this is to show hierarchically organized data.

There are a couple of problems with this Treeview control:

  1. Compile errors due to a difference in how the control libraries are registered in 32 bits Windows’ System32 and 64 bit Windows’ SysWOW32 folders. If you distribute a file that was saved in 64 bit Windows, containing one of the “Microsoft Windows Common Controls 6.0” (The Treeview control is one of them) and with the reference set to “mscomctl.ocx”, people using 32 bit Windows will almost certainly have problems. At best it could entail removing both the control and the reference and replacing both, but at worst the user’s Excel can crash when trying to load the file and run the app.
  2. The standard Treeview control, like all non built-in ActiveX controls, cannot be used in 64 bit versions of Office.

Especially the second point convinced me it is time to develop a custom-made Treeview “control”, that only uses the native Office forms controls. I started building this a couple of weeks ago and after some time I tricked Peter Thornton into helping me with it :-)

The screenshot below shows both our new Treeview (left) and the Windows one (right) side-by-side:

Not bad, right?

Both Treeviews allow for checkboxes:

And both allow icons (windows Treeview not shown here):

You can also edit a node:

And expand and collapse nodes and navigate the tree using your arrow keys.

We built the custom Treeview using just two class modules. Using it in your project will require nothing more than copying the two classes and adding a bit of plumbing to your userform: some code and an empty frame which will hold the Treeview and possibly a frame with pictures for the icons.

We’re currently doing some cleaning up (like removing obsolete debugging stuff, adding comments and the like), so the “control” is not quite ready to be released to the outside world. Keep an eye on this blog, once we’re done we’ll post here.

Well, what do you think, is this useful or what? What functionality would be critical for you? Let us know!

Regards,

Jan Karel Pieterse

VBE Keyboard Shortcut

Posted on by Dick Kusleika

To put your cursor in the Immediate Window, press Control+G. We all know that. To get it back to the code pane, I use Control+R, then Enter. Control+R puts the focus on the Project Explorer pane with the last active module highlighted. Enter just “opens” that module.

A few months ago, I learned on StackOverflow that F7 will take you to active code pane from the Immediate Window. Brilliant. Months later, I’m still using Control+R and Enter. Old habits…