This week I was looking at my VBA code indenter addin.
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.)
