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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 |
set TabSize to 3 -- spaces per tab. Adjust to suit set AlignDimAsToTabs to true -- Adjust to suit set PutDimAsAtTab to 7 -- Adjust to suit set OldCode to "" set NewCode to "" set templine to "" set Tabs to 0 set Stack to {1} set CaseStack to {} set ThisLine to {} set NoTabs to {"Option Explicit", "Sub", "Private", "Public", "Function", "Type", "Enum", "End Sub", "End Function"} set OneTab to {"Dim", "ReDim", "Static", "Stop", "Debug", "#If", "#Else", "#End"} set PushTabs to {"For", "With", "Do", "Select", "Case"} set IfTabs to {"If", "Else", "End If"} --Push, Pop & Push, Pop set PopTabs to {"Next", "End With", "Loop", "End Select"} set the clipboard to OldCode tell application "System Events" tell application id "com.microsoft.Excel" to activate tell application process "Microsoft Excel" --scripting the GUI set myList to (get name of windows) repeat with i from 1 to count of myList try if last word of item i of myList is "Code" then exit repeat end if end try end repeat perform action "AXRaise" of window i end tell keystroke "a" using command down delay 1 keystroke "c" using command down delay 1 --if nothing happens, make sure your code window is frontmost. end tell set OldCode to the clipboard set C to count of paragraphs of OldCode set AppleScript's text item delimiters to space repeat with N from 1 to C try set WasFound to false set LineOfCode to paragraph N of OldCode if length of LineOfCode > 1 then repeat while character 1 of LineOfCode is space if length of LineOfCode = 1 then exit repeat set LineOfCode to text 2 thru -1 of LineOfCode end repeat repeat with i from 1 to (count of characters of LineOfCode) - 1 set char1 to character i of LineOfCode set char2 to character (i + 1) of LineOfCode if not (char1 is space and char2 is space) then set templine to templine & char1 end if end repeat set templine to templine & char2 set LineOfCode to templine set templine to "" set char2 to "" end if set L1 to length of LineOfCode if L1 > 1 then repeat with i from 1 to count of NoTabs set ItemText to item i of NoTabs set L2 to length of ItemText if L1 ≥ L2 then if ItemText = text 1 thru L2 of LineOfCode then set WasFound to true exit repeat end if end if end repeat if WasFound is false then repeat with i from 1 to count of OneTab set ItemText to item i of OneTab set L2 to length of ItemText if L1 ≥ L2 then if ItemText = text 1 thru L2 of LineOfCode then if AlignDimAsToTabs is true and (ItemText = "Dim" or ItemText = "Static") and ¬ LineOfCode does not contain "(" then set j to 1 repeat with i from 1 to count of characters of LineOfCode if character i of LineOfCode is space then set ThisLine to ThisLine & text j thru (i - 1) of LineOfCode set j to i + 1 end if end repeat set ThisLine to ThisLine & text j thru i of LineOfCode set x to (length of ItemText) + (length of item 2 of ThisLine) + 2 set y to TabSize * (PutDimAsAtTab - 1) --take one away, add it back below set txt to "As" repeat with i from 1 to (y - x) set txt to space & txt end repeat set item 3 of ThisLine to txt set LineOfCode to ThisLine as string set ThisLine to {} end if set Tabs to 1 set LineOfCode to pad_the_line(LineOfCode, TabSize * Tabs) --add it back set WasFound to true exit repeat end if end if end repeat end if if WasFound is false then repeat with i from 1 to count of PushTabs set ItemText to item i of PushTabs if ItemText = first word of LineOfCode then set Tabs to last item of Stack if ItemText = "Select" then set CaseStack to CaseStack & (false) --push the stack if ItemText = "Case" then if last item of CaseStack is true then set Stack to pop_the_stack(Stack) if last item of CaseStack is false then set the last item of CaseStack to true end if set Tabs to last item of Stack set LineOfCode to pad_the_line(LineOfCode, TabSize * Tabs) set Stack to Stack & (Tabs + 1) --push the stack set WasFound to true exit repeat end if end repeat end if if WasFound is false then repeat with i from 1 to count of IfTabs set ItemText to item i of IfTabs set L2 to length of ItemText if L1 ≥ L2 then if (ItemText = text 1 thru L2 of LineOfCode) and (last word of LineOfCode is not "Then") and (last character of LineOfCode is not "_") and (ItemText = "If") then --If... set Tabs to last item of Stack set LineOfCode to pad_the_line(LineOfCode, TabSize * Tabs) set WasFound to true exit repeat else if (ItemText = text 1 thru L2 of LineOfCode) and (ItemText = "Else") then --Else, Else If set Stack to pop_the_stack(Stack) set Tabs to last item of Stack set LineOfCode to pad_the_line(LineOfCode, TabSize * Tabs) set Stack to Stack & (Tabs + 1) --push the stack set WasFound to true exit repeat else if (ItemText = text 1 thru L2 of LineOfCode) and (last word of LineOfCode = "Then") then --If...Then set Tabs to last item of Stack set LineOfCode to pad_the_line(LineOfCode, TabSize * Tabs) set Stack to Stack & (Tabs + 1) --push the stack set WasFound to true exit repeat else if (ItemText = text 1 thru L2 of LineOfCode) and (last character of LineOfCode = "_") then --If..._ set Tabs to last item of Stack set LineOfCode to pad_the_line(LineOfCode, TabSize * Tabs) set Stack to Stack & (Tabs + 1) --push the stack set WasFound to true exit repeat else if ItemText = text 1 thru L2 of LineOfCode then --End If set Stack to pop_the_stack(Stack) set Tabs to last item of Stack set LineOfCode to pad_the_line(LineOfCode, TabSize * Tabs) set WasFound to true exit repeat end if end if end repeat end if if WasFound is false then repeat with i from 1 to count of PopTabs set ItemText to item i of PopTabs set L2 to length of ItemText if L1 ≥ L2 then if ItemText = text 1 thru L2 of LineOfCode then if ItemText = "End Select" then set CaseStack to pop_the_stack(CaseStack) set Stack to pop_the_stack(Stack) end if set Stack to pop_the_stack(Stack) set Tabs to last item of Stack set LineOfCode to pad_the_line(LineOfCode, TabSize * Tabs) set WasFound to true exit repeat end if end if end repeat end if if WasFound is false then set Tabs to last item of Stack set LineOfCode to pad_the_line(LineOfCode, TabSize * Tabs) end if end if set NewCode to NewCode & LineOfCode & return on error error_message number error_number if error_number is -1728 then --Can’t get last item of {}. set alert_string to "Extra Code Block End" as string set AppleScript's text item delimiters to " or " set message_string to ¬ ("There looks to be an extra code block end line of either \n{" & PopTabs as string) & ¬ " or End If}\nin your code, as the Stack is empty {}. Ending. Your code is not changed." display alert alert_string message message_string as warning buttons {"OK"} set AppleScript's text item delimiters to "" return end if end try end repeat set the clipboard to "" delay 0.5 set the clipboard to NewCode tell application "System Events" tell application id "com.microsoft.Excel" to activate keystroke "v" using command down end tell if last item of Stack is not 1 then set alert_string to "Extra Code Block Start" set AppleScript's text item delimiters to " or " set message_string to ¬ ("There looks to be an extra code block start line of either \n{" & PushTabs as string) & ¬ " or If}\nin your code, as the Stack did not return to {1}." display alert alert_string message message_string buttons {"OK"} ¬ giving up after 10 end if set AppleScript's text item delimiters to "" on pop_the_stack(this_list) set this_list to reverse of this_list set this_list to rest of this_list set this_list to reverse of this_list return this_list end pop_the_stack on pad_the_line(ThisLine, pad) repeat with j from 1 to pad set ThisLine to space & ThisLine end repeat repeat while last character of ThisLine is space set ThisLine to text 1 thru -2 of ThisLine end repeat return ThisLine end pad_the_line |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
Option Explicit 'Example Procedure Sub ExampleProc() 'Smart Indenter '(c) 1998-2004 by Office Automation ltd. Dim iCount As Integer Static sName As String If YouWantMoreExamplesAndTools Then 'Visit http://www.oaltd.co.uk Select Case X Case "A" 'If you have any comments or suggestions, _ or find valid VBA code that isn't indented correctly, #If VBA6 Then MsgBox "Please comment below." #End If Case "Continued strings and parameters can be" _ & "lined up for easier reading, optionally ignoring" _ , "any operators (&+, etc) at the start of the line." 'Not implemented in Applescript. Debug.Print "X<>1" End Select ' Case X End If 'More Tools? End Sub Sub Proc() Dim mrt As Double Select Case Row Case 1 Select Case Col Case 1 action 1 Case 2 action 2 End Select Case 2 Select Case Col Case 1 action 3 Case 2 action 4 End Select Case Else action 5 End Select End Sub |
Running the script, it looks like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
Option Explicit 'Example Procedure Sub ExampleProc() 'Smart Indenter '(c) 1998-2004 by Office Automation ltd. Dim iCount As Integer Static sName As String If YouWantMoreExamplesAndTools Then 'Visit http://www.oaltd.co.uk Select Case X Case "A" 'If you have any comments or suggestions, _ or find valid VBA code that isn't indented correctly, #If VBA6 Then MsgBox "Please comment below." #End If Case "Continued strings and parameters can be" _ & "lined up for easier reading, optionally ignoring" _ , "any operators (&+, etc) at the start of the line." 'Not implemented in Applescript. Debug.Print "X<>1" End Select ' Case X End If 'More Tools? End Sub Sub Proc() Dim mrt As Double Select Case Row Case 1 Select Case Col Case 1 action 1 Case 2 action 2 End Select Case 2 Select Case Col Case 1 action 3 Case 2 action 4 End Select Case Else action 5 End Select End Sub |
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.
…mrt
©¿©¬
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
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
Next
MsgBox Join(sn, vbCr)
End Sub
Appreciate this site.
Having probs w/ my computer.
Is Ddoe not D, or is it still me?
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
Hi Michael
Put it on my list to check out, I always swipe to my Windows VM on my Mac to indent now
Code updated yet again. Two bugs killed.
… mrt