It is not mandatory to follow Microsoft’s rules and conventions regarding name spaces and prefixes. Users could always introduce their own standards, rules and conventions, which will help other users who may need to maintain processes within the organisation.

The following is a guideline to perhaps how authors and users alike could manage the code.

Naming macros, procedures and variables should be meaningful to the process to help clarify the task in hand.

Do not name a macro or procedure ‘MyProcedure1’ or ‘Macro1’ but keep it user friendly to help described the process.

Users can use more than one word provided there are no spaces or invalid characters used (operators). When using more than ‘one-worded’ procedures, consider initially capping each word to help see the name of the procedure clearly.

For example, Sub openallorders() would be better shown as Sub OpenAllOrders().

Variables such as X = 10 would be more helpful if X was named to be more meaningful to the intended process i.e. the number of years and could therefore be shown as NumberOfYears = 10 or NoYears = 10.

Variables and naming conventions are covered elsewhere in this guide – see Variables and Constants.

Do not use keywords when naming procedures or variables, as this will cause potential conflicts and errors.

Indentation

Code should be clearly positioned in a module. Use the tab key to indent logical blocks of code. Users can use as many indentations to emphasis new blocks of code (as nested) if required to show where a block starts and ends. This will help when browsing for long portions of code.

Comments

Commenting your code is important to the author and other users who may need to maintain code fragments. By default, commented lines are coloured green when text is typed following an apostrophe ( ‘ ) or the keyword ‘Rem’ (remark).

As part of the opening signature (either before or after the signature), a brief description of the procedure along with a date and name of the author should be documented.

For example:

Sub ProcessInvoice()

'**************************************************************
' This procedure will validate all entries to the new invoice.
' It will calculate sub total and tax values and post it to the
' data store. ' It will print and close the invoice.
' Author: Ben Beitler
' Date Created: 12/04/2010
' Date Modified: 20/04/2010 '**************************************************************

executed code is entered here

End Sub

Comments can appear anyway in the module provided it is remarked correctly as this type of text is ignored during code execution. Comments should also be added to unusual or difficult lines of code (known as inline comments) to help explain the nature of the action.

For example:

Sub SomeProcedure()
' Comments here.

[executed code is entered here...]

       ' This block is to validate if the field had been
       ' completed using my own custom function
       ' ValidateEntry(field).
       If mField = ValidateEntry(txtDate) Then
            [executed code is continued here...]
       intCounter = 1 'Set the flag back to 1 in order to 'restart counter.

       [executed code is entered here...]

End Sub

Line Breaks

Generally code should not be written beyond the screen/page width as it becomes cumbersome to work with, as users would have to scroll left and right unnecessarily.

Consider introducing a line break for single line code that extends beyond the page width by using the characters ‘spacebar’ and a ‘underscore’ ( _ ).

For example:

Sub MessageTestLineBreak()

MsgBox "This is the first Line." & vbNewLine & _

"This is the second Line.", _
vbInformation, "Message Box Test"

End Sub

Microsoft produced various documents on this subject. For a full list, check out

http://msdn.microsoft.com/library and search for 'Code Conventions’'

More information about conventions regarding variables are covered later in this manual – see Variables & Constants.

Next Topic: Excel objects

Want to teach yourself Access? Free online guide at About Access Databases

VBA Keywords: If...Then, MsgBox, vbNewLine.