Attention: The VBA code indenter has recently been replaced by AET VBE Tools. Download them here.
First thing I did was make some improvements to the menu code. Some people have mentioned that they have trouble getting the menu to show, and I think I found the reason and fixed it.
I also added an option to indent single procedures. Just place your cursor in the procedure you want to indent, and select the appropriate option.
I don't know how useful this is (why not just indent the whole module?), but I guess there are legitimate reasons. And I also did it because I wanted to see if I could! A productive learning experience in learning more VBE related code to be sure. To those interested, the project is password protected, but use "password" to unlock it and have a look at the code if you want.
You can download the new version here.
Now regarding the Visual Basic Editor itself. You can make various changes to make your code more visible on the Editor Format tab on the Options dialog box (click Tools, Option on the top menu of the Visual Basic Editor). The forecolour of your Comment Text is probably set to green by default. Another change I like is to make Keywords Text blue. Keywords include Sub, Function, If, Then, etc.
Another thing I'd really like to do though is to dynamically highlight "matching" text, and everything in between. What do I mean by this? Pictures are probably an easy way to explain.
Last time I looked at indenting VBA code in cells instead of the Visual Basic Editor.
As far as I know, you can't change the colour of VBA code in the VBE, apart from Tools, Options, Editor Format mentioned above. (Am I wrong? Let me know!).
But code in a worksheet is a different situation. You can use a Worksheet_SelectionChange event to do all kinds of things!
Before
After
Useful? Maybe, if you have lots of code and it's driving you crazy to see which code matches what. You might have lots of nested code, or code off screen. Yes, we can break code down into smaller subroutines or functions, but that's a job in itself, and how about if the code is not ours to change? I think highlighting code like this is a possibly useful curiosity in any case.
Here's the highlight code. In the VBE, paste it into the Sheet module that corresponds with the sheet where your code is to be highlighted.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rCodeCheck As Range Dim rCell As Range Dim lIndentSpacesCount As String Dim sFirstWord As String Dim sKeyWordsRGB As String Dim sOtherTextRGB As String Cells.Interior.ColorIndex = xlNone sKeyWordsRGB = RGB(255, 242, 204) 'Change to suit sOtherTextRGB = RGB(221, 235, 247) 'Change to suit With Target If .Column <> 1 Then Exit Sub If .Cells.Count > 1 Then Exit Sub If .Value = vbNullString Then Exit Sub Set rCodeCheck = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)) If Intersect(Target, rCodeCheck) Is Nothing Then Set rCodeCheck = Nothing Exit Sub End If lIndentSpacesCount = Len(.Value) - Len(LTrim$(.Value)) If InStr(LTrim$(.Value), Chr(32)) > 0 Then sFirstWord = Left$(LTrim$(.Value), InStr(LTrim$(.Value), Chr(32)) - 1) Else sFirstWord = vbNullString End If Select Case sFirstWord Case "Select", "For", "Do", "With" .EntireRow.Interior.Color = sKeyWordsRGB For Each rCell In rCodeCheck.Cells If rCell.Row > .Row Then With rCell If Len(.Value) - Len(LTrim$(.Value)) = lIndentSpacesCount Then .EntireRow.Interior.Color = sKeyWordsRGB Exit For Else If .Value <> vbNullString Then .EntireRow.Interior.Color = sOtherTextRGB End If End With End If Next rCell Case "If", "#If" If Right(.Value, 4) = "Then" Then .EntireRow.Interior.Color = sKeyWordsRGB For Each rCell In rCodeCheck.Cells If rCell.Row > .Row Then With rCell If Len(.Value) - Len(LTrim$(.Value)) = lIndentSpacesCount Then .EntireRow.Interior.Color = sKeyWordsRGB If Left$(LTrim$(.Value), 6) = "End If" Then Exit For If Left$(LTrim$(.Value), 7) = "#End If" Then Exit For Else If .Value <> vbNullString Then .EntireRow.Interior.Color = sOtherTextRGB End If End With End If Next rCell Else .EntireRow.Interior.Color = sOtherTextRGB For Each rCell In rCodeCheck.Cells If rCell.Row > .Row Then With rCell If .Value <> vbNullString Then If Len(.Value) - Len(LTrim$(.Value)) = lIndentSpacesCount Then If Left$(LTrim$(.Value), 6) = "Select" Then Exit For If Left$(LTrim$(.Value), 3) = "For" Then Exit For If Left$(LTrim$(.Value), 2) = "Do" Then Exit For If Left$(LTrim$(.Value), 4) = "Loop" Then Exit For If Left$(LTrim$(.Value), 4) = "With" Then Exit For If Left$(LTrim$(.Value), 2) = "If" And Right$(.Value, 4) = "Then" Then Exit For If Left$(LTrim$(.Value), 3) = "#If" And Right$(.Value, 4) = "Then" Then Exit For .EntireRow.Interior.Color = sOtherTextRGB Else Exit For End If End If End With End If Next rCell End If Case "End", "#End", "Loop" 'Do nothing Case Else .EntireRow.Interior.Color = sOtherTextRGB For Each rCell In rCodeCheck.Cells If rCell.Row > .Row Then With rCell If .Value <> vbNullString Then If Len(rCell.Value) - Len(LTrim$(.Value)) = lIndentSpacesCount Then If Left$(LTrim$(.Value), 6) = "Select" Then Exit For If Left$(LTrim$(.Value), 3) = "For" Then Exit For If Left$(LTrim$(.Value), 2) = "Do" Then Exit For If Left$(LTrim$(.Value), 4) = "Loop" Then Exit For If Left$(LTrim$(.Value), 4) = "With" Then Exit For If Left$(LTrim$(.Value), 2) = "If" And Right$(.Value, 4) = "Then" Then Exit For If Left$(LTrim$(.Value), 3) = "#If" And Right$(.Value, 4) = "Then" Then Exit For .EntireRow.Interior.Color = sOtherTextRGB Else Exit For End If End If End With End If Next rCell End Select End With Set rCodeCheck = Nothing End Sub
Now I have to say something. The Visual Basic Editor has seen little change over a long period of time. It certainly doesn't have the rich interface functionality of other code editors. And trying to find code samples to manipulate the interface is not an easy task.
Will this change? There's been many changes to the front end of Excel and other Office applications. The Visual Basic Editor is wanting, at least as the interface is concerned.
Microsoft, I'm sure there's many programmers besides myself who would like to see improvements. Our numbers may pale in comparison compared to ordinary users, but Office is not cheap. I would definitely like to see something better, coming soon.
Opinions? I'd like to see your feedback in the comments. (and like I wrote above, I'd also like to see a way of highlighting code within the VBE if it can be done.)
All these years, hell decades, and I never noticed I could highlight keywords?!? Brings my IDE up to the mid-2000s.
ReplyDeleteThe generally-accepted wisdom is that the VBE will never be updated. I can't think why that would change, except to support a tool that must be used by hundreds of thousands, if not millions, of Office users at some point or other.
I wasn't aware either Doug. I had played around with other settings before, but only came across this functionality last week :-)
DeleteYeah, about the VBE, I'm not optimistic but who knows. It would certainly be good PR if Microsoft did improve a few things just to let us developers know we are not forgotten (I'm guessing you've seen Steve Ballmers's "Developers" video on YouTube!).
Anyway, I'm going to dig around and do some research. Maybe I can come up with something.