UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Formatting Code    
Formatting Code

There are many habits that make up a good programmer, but regardless of the platform one of the most important ones is documentation. In particular, when coding VBA you should strive for clarity, so that if you or someone else looks at your code in the future; it is readable and the intent of the developer is not difficult to ascertain. There are a number of reasons for this, such as:

  • It increases the likelihood that you can remember what your were doing and make adjustments, replicate elsewhere
  • It helps other people who will almost certainly come after you and need to understand your code
  • The chances better that your VBA app can be used as a sound, presentable “model” for another project
  • It becomes much easier to interact with the development community (mostly through online venues), because if you ask for help people can more easily tell what you want to do….And if you are helping others, they will have an easier time understanding your code and duplicating it in their own application
  • Thus, you become much more productive on both an individual and a corporate scale
  • Last but not least, it is actually much easier to code by doing so!

This page will focus on the Formatting aspect of coding, with most attention given to Indentation. VBA code executes sequentially by line, but that doesn’t mean that the relationship between all adjacent lines is equal. In fact, the way that two lines which are a fairly distant from each other relate to each other is often stronger than two lines which are adjacent. Example:


For Each td In CurrentDb.TableDefs
  'Do Something
  'Do Something Else, possibly related but not necessarily dependent on the first thing
  'Do More Things, not directly dependent to the first two things
Next Td

In the above example, the start and end lines For..Next, (which define the outer pieces of the Loop) are very “related” to each other, and in fact if one or the other was incorrect, the VBA project would not even compile, much less run. But quite possibly if one of the middle lines were left out or incorrect, the VBA project might compile and might still run (dependent, obviously, on what specifically was being done to create any dependencies).

Another way to think of the hierarchy of indentation is belonging or boundaries. Which code is like a child under a parent…or which code is like an inner portion of the outer portion? The children are indented under/inside the parent, and the inner portions are indented under/inside the outer boundaries. Or, containers: There are containers, which often have additional containers within them, and so on and so forth, nesting many layers deep sometimes. Each container is a sort of closed-circuit thing. Correct indentation based on multiple layers is demonstrated below:

Sub Foo()

Dim wb As Workbook
Dim ws As Worksheet
Dim fso As Scripting.FileSystemObject
Dim strLine As String
Dim fsoFolder As Scripting.Folder
Dim fsoFile As Scripting.File
Dim ts As Scripting.TextStream

Set fso = New Scripting.FileSystemObject
Set fsoFolder = fso.GetFolder("c:\wiki")

For Each fsoFile In fsoFolder.Files
   If Right(fsoFile.Name, 3) = "txt" Then
       If DateValue(fsoFile.DateLastModified) = DateValue(Now) Then
           Set ts = fso.OpenTextFile(fsoFile.Path, ForReading, False)
           Do Until ts.AtEndOfStream = True
               strLine = ts.ReadLine
               If MsgBox("This line " & strLine & ", do you want to stop yet?", vbYesNo, "  ") = vbYes Then
                   Exit Do
               End If
       End If
   End If
Next fsoFile

End Sub

Most people also prefer to add an extra line of ‘white space’ as a mechanism to indicate when code changes from simple, sequentially executing lines over to the start or end of a block (such as an If/EndIf or For/Next) of code. I’ve left it off here for the sake of reading ease.

You can see how—visually—it’s easier to see which code block is operating as a unit as opposed to operating as quasi-independent, sequentially executing lines. When the code runs, these two lines may turn out to be dependent on each other:


Dim ws as Worksheet
Set ws = Application.Workbooks(“Book3.xlsx”)
(since they will cause a run time error)

But they are not structurally dependent on each other at the time the project is compiled, and they certainly don’t represent pieces where one is inside the boundaries of the other.

If you have an IF statement, then everything inside that IF statement gets indented one tab space (or an appropriate number of spaces). If part of that “inside” stuff is another IF statement, then everything inside THAT gets an additional indent as shown below:


If This
    'some code
    If Something Else
         'some code
         'some code
    End If
End If

These simple indentation rules are commonly applied to:

  • IF / END IF blocks
  • WITH blocks
  • LOOPS (of all kinds – For..Next, Do…Loop, While..Wend)
  • SELECT CASE statements (some people do not do this)

One last piece of advice: When interacting with the online community whether at UtterAccess or elsewhere, try as hard as you can to post your code as a Copy & Paste directly from your VBA project. This way it tends to retain all of the original capitalization and indentation that you’ve carefully set up. Seeing your code this way enables others to understand your code.

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 2,371 times.  This page was last modified 01:56, 7 August 2013 by TheDBguy. Contributions by ipisors  Disclaimers