DOCUMENTING MACROS AND CUSTOM FUNCTIONS

Even simple macros and custom functions can be difficult to read. You can make them easier to understand by entering explanatory text in the form of comments. You add comments by preceding the explanatory text with an apostrophe.

For example, the following shows the Discount function with comments:

    Function Discount(quantity, price)
        'Calculate the discount for orders >= 100 units
        'No discount for fewer than 100 units
        'Arguments:
        '   quantity    is the quantity of the item
        '   price       is the price of the item
        'Function returns the discount amount
        'Created by K. Ming Leung, Oct. 28, 2006
        
        If quantity >= 100 Then                     'if more than 100 units
        Discount = quantity * price * 0.1           'calculate a 10% discount
        Else                                        'otherwise
        Discount = 0                                'no discount
        End If
        Discount = Application.Round(Discount, 2)   'round to 2 decimal places
    End Function
Adding comments like these makes it easier for you or others to maintain your VBA code as time passes. If you need to make a change to the code in the future, you’ll have an easier time understanding what you did originally.

An apostrophe tells Excel to ignore everything to the right on the same line, so you can place a comment to the right of a VBA statement. You can also insert comments between the statements in a macro or custom function. For example, you might begin a relatively long block of code with a comment that explains its overall purpose and then use inline comments to document individual statements.

Another way to document your macros and custom functions is to give them descriptive names. For example, rather than name a macro Labels, you could name it RelMonthLabels, where Rel indicates that the macro uses relative references and MonthLabels describes the type of label the macro creates. Using descriptive names for macros and custom functions is especially helpful when you’ve created many procedures, particularly if you create procedures that have similar, but not identical, purposes.

How you document your macros and custom functions is a matter of personal preference. It doesn’t matter which method you use, as long as you document them. Documentation is most important for long and complex procedures, for procedures that you look at only once in a while, and for procedures that will be maintained by other people.