VBE Code Indenter for the Macintosh

I lived in two worlds. My work was done on a PC, but my home computer has been a Mac since the Mac Plus. One thing my work PC had that my Mac never did was a code indenter for prettifying macro code. I’m partial to Stephen Bullen’s “Smart Indenter,” but others exist, such as Andrew Engwirda’s, found over here. Neither one works on a Mac because the Mac doesn’t have the same innards in the editing environment. “Never did” was until a free snowy weekend, when I wrote one in AppleScript. AppleScript exists on any Mac, and this script will indent any VBA routine. It uses a stack (implemented as an AppleScript list.) For an indent you push the stack, and for an out-dent you pop the stack. Two of the trigger phrases are more complicated. Else (and Else If) need an out-dent followed by an indent, or a pop and a push. Case requires an indent for the first one, an out-dent and an indent for the following ones, and then since End Select closes out Case, two out-dents, or two pops.

There’s probably a way to get to the editor’s native elements, but I couldn’t find it. I scripted the GUI instead. Here’s the script. AppleScript is about as self-documenting as it gets.

The script checks for two things: If the stack at the end is not {1}, then you have too many indents. AppleScript won’t complain, and the editor probably showed you it to you any way. On the other hand, if you have too many out-dents, you can’t pop an empty stack, and Applescript throws an error. The script above catches that, and makes no changes.

Stephen Bullen provides some sample code. I stole it, and added some of my own.

Running the script, it looks like this:

I didn’t implement Stephen’s procedure/module/project choice, as I don’t think I can. The whole module page is prettified. And I didn’t reverse engineer his control of comments. Line comments go as the next line of code would be indented, and inline comments are as you put them. This script, suitably and easily modified, will work for MS Word macros, too. I tried to find the hooks that would ensure a code module was the front window in the editor, but I had no luck. If nothing happens, you may have the Project or Properties windows frontmost, and Select All and Copy are grayed out. Just click in your module, and you’ll be all set. If you want to change the category of a trigger phrase, it’s straight forward. You’ll have to pay attention to if the phrase is one word or two. If it’s two, you have to compare as text vice as words. Examples are in the script. And please, if you know the inner mechanisms of AppleScript and the VB Editor, leave a note.


7 thoughts on “VBE Code Indenter for the Macintosh

  1. Michael et al,

    During this year a new version of Stephen’s nice utility will be available for x86 and x64 and targeting Excel 2010 and beyond. Previously versions of Excel works well with the present version.

    It will be created as a managed COM Add-in, completely rewritten in VB.NET. When a beta is available I will call for testers.

    BTW, nice solution for the Apple platform.

    Kind regards,
    Dennis Wallentin
    MVP MS Excel Desktop

  2. I made this one in VBA; maybe you can use it’s method
    Sub M_snb()
    With ThisWorkbook.VBProject.VBComponents(1).CodeModule
    sn = Split(.Lines(1, .countoflines), vbCrLf)
    End With

    y = 1
    For j = 1 To UBound(sn) – 1
    If sn(j) <> “” Then
    c00 = Split(Trim(sn(j)))(0)

    If InStr(“|End|Else|ElseIf|Next|Loop|”, “|” & c00 & “|”) Then y = y – 1
    If c00 = “Case” Then If Split(Replace(sn(j – 1), vbTab, “”))(0) <> “Select” Then y = y – 1

    sn(j) = String(y, vbTab) & Trim(sn(j))

    If InStr(“|For|With|Do|If|Case|”, “|” & c00 & “|”) Then y = y + 1
    If c00 = “If” And InStr(sn(j), ” Then “) > 0 Then y = y – 1
    End If

    MsgBox Join(sn, vbCr)
    End Sub

  3. The code is updated. I fixed a bug in the code where it wouldn’t handle underscores in variable names while aligning the “Dim … As” to tabs. Otherwise, figured out how to make the Code window auto-select, streamlined the AppleScript a bit, and improved the handling of “ElseIf.” Change “Excel” to “Word” three times, and it works in MSWord too.

    … mrt

  4. Hi Michael

    Put it on my list to check out, I always swipe to my Windows VM on my Mac to indent now

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.