Indenting VBA Code In Cells

There's a few addins out there to indent VBA code. I should know this because I made one of them.

But let's say for argument's sake that you can't use any of them. Perhaps you're using a computer somewhere that won't allow you to install what you want. You still have access to the VBE, right?

Here's some code not yet indented.



If you look at the sub's name in the image provided, you might guess what's coming. Yep, I've pasted the code into cells and that's where I'me going to indent it.

And here is the code to indent the code for indenting ;-)

Private Sub IndentCodeInCells()
   Dim rCell As Range
   Dim sCodeString As String
   Dim sCodeStringOffset As String
   Dim sFirstWord As String
   Dim sIndent As String
   Dim sTab As String

   If TypeName(Selection) <> "Range" Then

      MsgBox "Please select a range."

      Exit Sub

   End If

   If Selection.Columns.Count > 1 Then

      MsgBox "Please select a single column."

      Exit Sub

   End If

   sTab = WorksheetFunction.Rept(Chr(32), 3) 'Change number of characters to suit

   For Each rCell In Selection.Cells

      With rCell

         If Not .Value = vbNullString Then

            .Value = LTrim$(.Value)

            sCodeString = CStr(.Value)

            sCodeStringOffset = CStr(LTrim$(.Offset(1).Value))

            Select Case sCodeString

               Case "End Sub", "End Function", "End Type", "End Enum", "End Property"
               sIndent = ""

               Case "End If", "#End If", "End Select", "End With", "Else", "#Else", "Loop"
               sIndent = Left$(sIndent, Len(sIndent) - Len(sTab))

            End Select

            If Left$(sCodeString, 4) = "Next" Then sIndent = Left$(sIndent, Len(sIndent) - Len(sTab))

            If Left$(sCodeString, 10) = "Loop Until" Then sIndent = Left$(sIndent, Len(sIndent) - Len(sTab))

            If Left$(sCodeString, 10) = "Loop While" Then sIndent = Left$(sIndent, Len(sIndent) - Len(sTab))

            If Left$(sCodeString, 4) = "Wend" Then sIndent = Left$(sIndent, Len(sIndent) - Len(sTab))

            If Right$(sCodeString, 1) = ":" Then

               .Value = .Value

            Else

               .Value = sIndent & sCodeString

            End If

            If Left$(sCodeString, 4) = "Sub " Then sIndent = sIndent & sTab

            If Left$(sCodeString, 12) = "Private Sub " Then sIndent = sIndent & sTab

            If Left$(sCodeString, 11) = "Public Sub " Then sIndent = sIndent & sTab


            If Left$(sCodeString, 9) = "Function " Then sIndent = sIndent & sTab

            If Left$(sCodeString, 17) = "Private Function " Then sIndent = sIndent & sTab

            If Left$(sCodeString, 16) = "Public Function " Then sIndent = sIndent & sTab


            If Left$(sCodeString, 5) = "Type " Then sIndent = sIndent & sTab

            If Left$(sCodeString, 13) = "Private Type " Then sIndent = sIndent & sTab

            If Left$(sCodeString, 12) = "Public Type " Then sIndent = sIndent & sTab


            If Left$(sCodeString, 5) = "Enum " Then sIndent = sIndent & sTab

            If Left$(sCodeString, 13) = "Private Enum " Then sIndent = sIndent & sTab

            If Left$(sCodeString, 12) = "Public Enum " Then sIndent = sIndent & sTab


            If Left$(sCodeString, 9) = "Property " Then sIndent = sIndent & sTab

            If Left$(sCodeString, 17) = "Private Property " Then sIndent = sIndent & sTab

            If Left$(sCodeString, 16) = "Public Property " Then sIndent = sIndent & sTab


            If Left$(sCodeString, 4) = "Else" Then sIndent = sIndent & sTab

            If Left$(sCodeString, 5) = "#Else" Then sIndent = sIndent & sTab

            If Left$(sCodeString, 2) = "Do" Then sIndent = sIndent & sTab

            If InStr(sCodeString, Chr(32)) > 0 Then

               sFirstWord = Left$(sCodeString, InStr(sCodeString, Chr(32)) - 1)

            Else

               sFirstWord = vbNullString

            End If

            Select Case sFirstWord

               Case "For", "With", "Select"
               sIndent = sIndent & sTab

            End Select

            If sFirstWord = "If" And Right$(sCodeString, 4) = "Then" Then sIndent = sIndent & sTab

            If sFirstWord = "#If" And Right$(sCodeString, 4) = "Then" Then sIndent = sIndent & sTab

            If sFirstWord = "If" And Right$(sCodeString, 1) = "_" And Right$(sCodeStringOffset, 4) = "Then" Then sIndent = sIndent & sTab

            If sFirstWord = "#If" And Right$(sCodeString, 1) = "_" And Right$(sCodeStringOffset, 4) = "Then" Then sIndent = sIndent & sTab

         End If

      End With

   Next rCell

End Sub
And here's how it looks after. (Indenting your code makes it a lot easier to read for yourself, and anybody else who may refer to it)



Now I simply copy the indented code and paste it back into the VBE.

Okay, how did I indent the code for this blog post then?

With this. (Note the code comments to remove "<br>" if required.)

Private Sub IndentCodeWithHTMLSpacesInCells()
   Dim rCell As Range
   Dim sCodeString As String
   Dim sCodeStringOffset As String
   Dim sFirstWord As String
   Dim sIndent As String
   Dim sTab As String

   If TypeName(Selection) <> "Range" Then

      MsgBox "Please select a range."

      Exit Sub

   End If

   If Selection.Columns.Count > 1 Then

      MsgBox "Please select a single column."

      Exit Sub

   End If

   sTab = WorksheetFunction.Rept("&nbsp;", 3) 'Change number of characters to suit

   For Each rCell In Selection.Cells

      With rCell

         If Not .Value = vbNullString Then

            .Value = LTrim$(.Value)

            .Value = Replace(.Value, "", vbNullString)

            sCodeString = CStr(.Value)

            sCodeStringOffset = CStr(LTrim$(.Offset(1).Value))

            Select Case sCodeString

               Case "End Sub", "End Function", "End Type", "End Enum", "End Property"
               sIndent = ""

               Case "End If", "#End If", "End Select", "End With", "Else", "#Else", "Loop"
               sIndent = Left$(sIndent, Len(sIndent) - Len(sTab))

            End Select

            If Left$(sCodeString, 4) = "Next" Then sIndent = Left$(sIndent, Len(sIndent) - Len(sTab))

            If Left$(sCodeString, 10) = "Loop Until" Then sIndent = Left$(sIndent, Len(sIndent) - Len(sTab))

            If Left$(sCodeString, 10) = "Loop While" Then sIndent = Left$(sIndent, Len(sIndent) - Len(sTab))

            If Left$(sCodeString, 4) = "Wend" Then sIndent = Left$(sIndent, Len(sIndent) - Len(sTab))

            If Right$(sCodeString, 1) = ":" Then

               .Value = .Value

            Else

               .Value = sIndent & sCodeString & "<br>" 'Remove "& <br>" if required

            End If

            If Left$(sCodeString, 4) = "Sub " Then sIndent = sIndent & sTab

            If Left$(sCodeString, 12) = "Private Sub " Then sIndent = sIndent & sTab

            If Left$(sCodeString, 11) = "Public Sub " Then sIndent = sIndent & sTab


            If Left$(sCodeString, 9) = "Function " Then sIndent = sIndent & sTab

            If Left$(sCodeString, 17) = "Private Function " Then sIndent = sIndent & sTab

            If Left$(sCodeString, 16) = "Public Function " Then sIndent = sIndent & sTab


            If Left$(sCodeString, 5) = "Type " Then sIndent = sIndent & sTab

            If Left$(sCodeString, 13) = "Private Type " Then sIndent = sIndent & sTab

            If Left$(sCodeString, 12) = "Public Type " Then sIndent = sIndent & sTab


            If Left$(sCodeString, 5) = "Enum " Then sIndent = sIndent & sTab

            If Left$(sCodeString, 13) = "Private Enum " Then sIndent = sIndent & sTab

            If Left$(sCodeString, 12) = "Public Enum " Then sIndent = sIndent & sTab


            If Left$(sCodeString, 9) = "Property " Then sIndent = sIndent & sTab

            If Left$(sCodeString, 17) = "Private Property " Then sIndent = sIndent & sTab

            If Left$(sCodeString, 16) = "Public Property " Then sIndent = sIndent & sTab


            If Left$(sCodeString, 4) = "Else" Then sIndent = sIndent & sTab

            If Left$(sCodeString, 5) = "#Else" Then sIndent = sIndent & sTab

            If Left$(sCodeString, 2) = "Do" Then sIndent = sIndent & sTab

            If InStr(sCodeString, Chr(32)) > 0 Then

               sFirstWord = Left$(sCodeString, InStr(sCodeString, Chr(32)) - 1)

            Else

               sFirstWord = vbNullString

            End If

            Select Case sFirstWord

               Case "For", "With", "Select"
               sIndent = sIndent & sTab

            End Select

            If sFirstWord = "If" And Right$(sCodeString, 4) = "Then" Then sIndent = sIndent & sTab

            If sFirstWord = "#If" And Right$(sCodeString, 4) = "Then" Then sIndent = sIndent & sTab

            If sFirstWord = "If" And Right$(sCodeString, 1) = "_" And Right$(sCodeStringOffset, 4) = "Then" Then sIndent = sIndent & sTab

            If sFirstWord = "#If" And Right$(sCodeString, 1) = "_" And Right$(sCodeStringOffset, 4) = "Then" Then sIndent = sIndent & sTab

         Else

            .Value = "<br>" 'Remove this line if required

         End If

      End With

   Next rCell

End Sub
If you run the code, you will see the indenting is done with "&nbsp;" which is used instead of normal spaces in HTML. Simple copy the code and paste it in HTML when and where necessary.

See you next time.