# Visual Basic for Applications/VBA Code Indenter
## Summary

* This admittedly long code module is used to indent or format any VBA code that it finds on the clipboard.. It uses fairly standard code indenting rules, examples of which can be seen in the formatting of the code on this page. Commercial formatting utilities are now available for VBA editors in 64-bit Windows, but the code below runs, not as an add-in but as a simple VBA code module. Within reason it will work for all recent versions of Windows and Excel, and with other Microsoft Office applications that can run VBA. It is fast enough for even the most critical user.
* It places the indented text back on the clipboard to replace the original text. The user can then paste it as he pleases.
* The first effort has few options.
** The depth of indenting can be set. This is done by setting the number of spaces per tab.
** The number of consecutive blank lines can be set. This is limited to none, one, or left as they are found.
** Existing line numbers can be preserved or removed, though no renumbering facility has been provided.
** Shared label lines can be split onto their own lines.
** The code routinely replaces comments that are continuations with comments on their own lines, though users can elect to avoid changes to comments altogether. If comment changes are permitted:
*** Rem comment style is optionally replaced with the apostrophe style.
*** Users can choose to insert one space after the comment apostrophe or to remove it where it already exists.

## VBA Code Indenter-Formatter Module

A few points bear mention:
* The code runs as one complete standard module. Run the procedure Indenter to indent the clipboard content.
* The user might also consider an optional user form to preview the indented output string (sRet in procedure Indenter) prior to Paste.
* Select code with complete pairings to make best use of the indenter. For example, so that there are no obvious If's without End If's. That said, snippets of code, procedures, or whole modules can be indented. In fact any text with recognized VBA keywords and line breaks, will get indented. Users should set the VBA editor error settings to Break for Unhandled Errors to avoid unnecessary interruptions from the intentional raising of errors.
* Readers who find the matter interesting might consider adding any bug reports to Discussion, and I will look at them when I can.

### Work Method Used

All working is done in a module-level string array. The process is:
* Get the clipboard string first. The method used to get the string is the same as the one listed elsewhere in this series. See Clipboard VBA, for the DataObject clipboard methods. This method replaces an earlier method using a dummy user form.
* Load an array with the string as a set of lines. Load only the code part without any line number that may be present. Then remove the existing indentation from every line. This means any leading and lagging spaces and tabs.
* Re-connect lines broken with continuation marks. This process avoids many line identification problems, especially from follow-on comment lines that have been folded without their own continuation marks.
* Identify and mark the array with line types. The line types that come in closed structures, like For...Next or Sub...End Sub pairs, are especially important for indenting. These are marked as start and end line types respectively. Initially, it is not important which structures they are, just whether or not they are starts or ends. Middles such as Else also need to be identified, as well as comment, blank lines, and a large set of so-called other lines.
* Match up corresponding start and end lines. It works like this: Starting at the top of the code; select the first start line and count it as one, then move down, incrementing both start and end counters until the two counters are equal; the matched end line is then found. After resetting the counters, move down to the second start, and repeat the process until all of the start lines have been matched. The array start lines are marked with the row numbers for the corresponding end matches.
* Check the pair counts. If the code does not contain at least one start-end structure, or the start and end totals do not match, the user is invited to proceed or exit. A user assigned error is raised to achieve exit.
* Assign the indents and outdent counts for the main structures. Starting at the top of the code lines, go to the first line marked as a start. Add one indent count for all of the lines that lie between that start and its corresponding end line. Move down to the next start line and repeat the process until it is done. Now, for any line anywhere in the array that is marked as a middle, outdent, that is, subtract one indent count. Indent counts are converted to spaces using an indenting option.
* Join indent spaces to the code lines and make one string from them all. Although users can set a spacing option, four spaces to each indent unit seems to be the most useful, much as in the VBA editor itself.
* Upload the indented string to the clipboard, then advise that it is there, ready for paste.

#### Code Module Modifications

15 Dec 2018: Code modified to add error when clipboard not text. 14 Dec 2018: Code modified to use DataObject copy and paste methods. 29 Mar 2017: Minor edit to GetClipboard function comment.