Writing Solid Code
"Writing solid code" means writing Microsoft® Visual Basic® for Applications (VBA) code or script that performs as expected and is reusable, easy to understand, and easy to maintain. Many developers focus all their time and effort on just getting code to work. If you have ever struggled to understand code, whether written by you or someone else, or rewritten the same procedure to work in different applications, then you understand how writing solid code can make life easier.
One of the most important considerations for an effective Microsoft® Office application is how well it is designed. If your application has a user interface, you should be aware of consistency and ease-of-use issues every time you create a dialog box or add a custom toolbar. You also must be aware of the design and structure of the code you write. The way you design your code, from how you name variables to when you use class modules, can make a big difference in the maintainability and usability of your code.
In This Section
The benefits associated with writing reusable, understandable, and maintainable code can be realized whether you are writing script or Microsoft® Visual Basic® for Applications (VBA) code.
There are many formal naming conventions and each has its adherents and detractors. You can adopt one of the existing conventions or create one of your own.
Structuring and Formatting Your Code
How you use structured coding conventions directly affects how easy your code is to understand and maintain.
All procedures and functions should begin with a brief comment describing the functional characteristics of the procedure (what it does).
Designing Code to Be Used Again
Reusable code is code that can be used, without modification, to perform a specific service regardless of what application uses the code.
Scripting languages are "loosely typed" and, therefore, all variables used in script have a Variant data type. In addition, script is written directly into the HTML code behind a Web page, and there are no modules used to contain code as there are in VBA and other "strongly typed" languages. Finally, scripting languages do not require you to expressly declare variables before you use them.
Given these unique characteristics, it still makes sense to talk about a naming convention in the context of writing script. The naming conventions and other coding guidelines apply to script in an HTML page as they do to Microsoft® Visual Basic® for Applications (VBA) code in a Microsoft® Office application. The benefits associated with writing reusable, understandable, and maintainable code can be realized whether you are writing script or VBA code. In fact, there is a great deal of work to be done persuading script developers to pay attention to issues of code reuse and maintainability. There are just as many benefits to writing solid script as there are to writing solid code.
Although script is written directly into the HTML code of a Web page, questions of visibility and lifetime are still important. Variables and constants declared within a procedure are local to that procedure and have a lifetime that lasts only as long as the script within the procedure is executing.
Variables and constants declared in script outside a procedure are visible to any script contained in the current HTML page. These variables have the equivalent of the module-level scope described earlier. Variables and constants declared in Visual Basic Scripting Edition (VBScript) by using the Public keyword are visible to all script in the current HTML page and to all script in all other currently loaded pages. For example, if you have an HTML page that contains multiple frames designated by a <FRAMESET> tag pair, a variable or constant declared with the Public keyword will be visible to all pages loaded within all the frames specified by the <FRAMESET> tag.
In addition, although all script variables have a Variant data type, it is important to remember that the Variant data type encompasses many different data types and can coerce a variable to the most appropriate data type in a particular context. Although you cannot declare a variable as a specific data type, you should name your variables as if you could. Naming script variables as if they were strongly typed will not prevent you from assigning an integer value to the strCompanyName variable, but it will force you to think about how the variable is used and the data subtype it will contain. You declare VBScript variables by using the Dim statement and Microsoft® JScript® variables by using the var statement.
Note Unlike VBScript, JScript is a case-sensitive language; if you name a variable strCompanyName but refer to it as STRCompanyName, you will encounter errors.
Using the Option Explicit Statement
Neither VBScript nor VBA requires you to declare variables before using them. The default behavior in both languages makes it possible for you to create variables by simply using a variable name in an assignment statement. However, the failure to use the Option Explicit statement to force explicit variable declaration can be a serious mistake. Using undeclared variables can introduce subtle, hard-to-find bugs into your code that are easily avoided by using this simple technique.
To force VBA to insert the Option Explicit statement in every module you create, open
the Visual Basic Editor, click Options on the Tools menu, and then click Require Variable
Declaration on the Editor tab.
To force variables to be declared in VBScript, type Option Explicit immediately after the first <SCRIPT> tag in your HTML document. For example, <SCRIPT> Option Explicit </SCRIPT>.
1.Using a Naming Convention
There are many formal naming conventions, and each has its adherents and detractors. You can adopt one of the existing conventions or create one of your own. The important points are that you adopt some convention that would be self-evident to another Office developer and that you apply it consistently.
At a minimum, a useful naming convention will identify and distinguish variable type (object variable, control variable, and data type), variable scope, constants, and procedures, and it should be simple to understand and use.
1.1.Naming Variables and Constants
Naming conventions typically use a lowercase prefix or suffix to specify the variable's type and scope. The variable itself should have a meaningful name that describes what it is or what it does. Multiple-word names are concatenated, the first letter of each word is capitalized, and the underscore character is not used. If you used a variable-name template, it would be in the form prefixNoun or prefixNounVerb.
Constant names should also be descriptive names in the NOUN or NOUN_VERB format. Constant names are uppercase and use an underscore character to separate words. Although there is nothing technically wrong with adding characters to constant names to specify data type and scope, it is not done often. A constant is really the same thing as a variable in the sense that both are symbolic representations of data. The difference is that variables can change and constants remain the same.
Both variable and constant names can contain up to 255 characters; however, names that exceed 25 to 30 characters can become unwieldy. Besides, 25 or 30 characters should be plenty of room to provide descriptive names that clearly convey the purpose of the variable or constant.
1.1.1.Variable Names
Variable names use the mixed-case form (Noun or NounVerb), specifying what the variable is and what it does. The mixed-case form is used as the descriptive portion of the variable name where the first letter of each word is in uppercase and the rest is in lowercase.
Variable names also have a two- or three-character prefix used to specify the variable's data type. For example, the following statements declare variables with a prefix that specifies the variable's data type:
Dim strRecipientName As String
Dim intItemsProcessed As Integer
Dim blnContinueProcessing As Boolean
The two-character prefix is used typically to specify an Office Application object type. For example:
Dim xlApp As Excel.Application
Dim olNameSpace As Outlook.NameSpace
Dim wdNewDoc As Word.Document
Use the "obj" prefix when declaring a generic, or object variable. Use this prefix even when you are creating a late-bound object variable that represents a Microsoft® Office application. For example:
Dim objXLApp As Object
Dim objWDDocument As Object
Dim objOLMailItem As Object
Global and module-level variables use an additional single-character prefix to specify their scope. The scope of a variable defines its lifetime and visibility. Global and module-level variables have a permanent lifetime. That is, the memory allocated to the variable remains allocated as long as the application is running. Variables declared within a procedure are visible only within the procedure where they are declared and have a lifetime that lasts only as long as the code within the procedure is executing. The exception to this is when they are declared by using the Static keyword.
Global variables have a lowercase "g" prefix and are declared in the Declarations section of a module by using the Public statement. They are visible to all procedures in all modules in an application. For example, Public gstrPathToDataSource As String would be a global variable that contains a string that is the path to the data source used in the application.
Variables always should be defined using the smallest scope possible. Use global variables only when there is no other way to share the data they contain. Global variables can make your code hard to understand and difficult to maintain. If you find you are using more than a few carefully chosen global variables, you might want to redesign your code to eliminate them.
Module-level variables have a lowercase "m" prefix and are declared in the Declarations section of a module by using the Dim or Private statement. They are visible to any procedure within the module in which they are declared. For example, Dim mrstCustomerRecords As ADODB.Recordset would be a module-level object variable for customer records. In class modules, module-level variables that are declared with the Private statement have a "p_" prefix. Public module-level variables in class modules appear as properties of the class and should not have any prefix to indicate their data type or scope.
Procedure-level variables are created within a procedure by using the Dim statement. For example, Dim intCurrentMailItem As Integer would be a procedure-level variable used as a loop counter. In addition, procedure-level variables can be declared by using the Static keyword. Static variables retain their value even after the procedure in which they are declared has finished running. Static procedure-level variables have a lowercase "s" prefix. For example, Static scurTotalSales As Currency would create a procedure-level static variable used to keep an accumulating total in a procedure that calculates current sales.
User-defined type variables are declared in the Declarations section of a module by using an all uppercase type name with "_TYPE" appended to the type name. You could declare a user-defined type in the following manner:
Type EMPLOYEEINFO_TYPE
strFullName As String
lngEmployeeID As Long
datStartDate As Date
strDepartmentCode As String * 4
curSalary As Currency
End Type
You declare a module-level variable of type EMPLOYEEINFO_TYPE by using a "udt" prefix. For example, Dim mudtEmployeeRecord As EMPLOYEEINFO_TYPE.
Array variables have a lowercase "a" prefix and, unless the variable is a variant, are followed by a pair of parentheses. An array is a variable that can contain multiple values. Array variables are declared by using the Dim statement; for example, Dim alngNum() is an array variable of type Long. Arrays are useful when you must store a number of values of the same type, but you do not want to create individual variables to store them all.
The following are some examples of variable names that use the general naming guidelines described earlier.
| Variable | Data type | Scope |
| strCompanyName | String | Procedure |
| rstCurrentOrders | Object | Procedure |
| intCurrentRecordCount | Integer | Procedure |
| wdWordApplication | Object | Procedure |
| varClipboardData | Variant | Procedure |
| curAmountPastDue | Currency | Procedure |
| blnProcessNextRecord | Boolean | Procedure |
| molOutlookMailItem | Object | Module |
| mcolCurrentUsers | Object | Module |
| gcnnDBConnection | Object | Global |
| gstrLogonID | String | Global |
| gastrContactNames() | String (array) | Global |
| molOutlookMailItem | Object | Module |
| mcolCurrentUsers | Object | Module |
| gcnnDBConnection | Object | Global |
| gstrLogonID | String | Global |
| gastrContactNames() | String (array) | Global |
1.1.2.Constant Names
Constants use multiple-word descriptive names in all uppercase letters with an underscore character between each word. Constants are declared by using the Const statement along with the name of the constant, its data type, and its value. For example, the following constant could be declared in the Declarations section of a module to provide the path to the data source used by an application:
Public Const DATABASE_PATH As String = "C:/Solutions/Source/AppData.mdb"
Note By using the Public keyword to declare the constant, that constant can be used by any procedure in any module in the application. If the Public keyword is not used, the constant has a module-level scope, meaning that it is available only to procedures within the module in which it was declared. If the constant is declared within a procedure, it is available only to the code in the procedure and only as long as the code in the procedure is executing.
Here are some examples of constant names that use the general naming guidelines described earlier:
ACCESS_CONNECTSTRING
API_MAX_STRINGBUFFER
SQL_STRING
Note If you create public enumerated constants in a class module, you can use a different naming convention to distinguish them from other constants.
In addition to the constants you declare yourself, Microsoft® Visual Basic® for Applications (VBA), Visual Basic Scripting Edition (VBScript), and each of the Microsoft® Office applications contain built-in, or intrinsic, constants whose values are predefined. Intrinsic constants always should be used in place of the values they represent. As with user-defined constants, the advantage to using intrinsic constants is that they make your code more understandable. For example, compare the following two code samples, where one sample uses intrinsic constants and the other does not. See if you agree that intrinsic constants can make a big difference in how easy the code is to understand.
If MsgBox("Proceed Now?", 48 + 512 + 3 + 16384, "Continue?") = 7 Then
DoCmd.OpenForm "Customers", 0, , , 1, 3
End If
If MsgBox("Proceed Now?", vbExclamation + vbDefaultButton3 + _ vbYesNoCancel +
vbMsgBoxHelpButton, "Continue?") = vbNo Then DoCmd.OpenForm "Customers", acNormal, , , acFormEdit, acDialog
End If
For a complete listing of intrinsic constants available through VBA and each of the Office applications, open the Object Browser, select the appropriate type library from the Projects/Library dialog box, type the appropriate constant prefix in the Search text box, and then click Search on the Object Browser toolbar. The following table is a sampling of the complete listing of intrinsic constants.
| Application/type library | Constant prefix |
| Access | Ac |
| Excel | Xl |
| FrontPage | Fp |
| Office | Mso |
| OfficeBinder | Bind |
| Outlook | Ol |
| PowerPoint | Pp |
| Word | Wd |
| VBA | Vb |
1.2.Naming Objects and Controls
Objects and controls, and variables that represent objects and controls, should be named with a prefix that identifies the item and a mixed-case name that clearly identifies the item's purpose. In this context, the term objects refers to object variables that represent items such as documents, workbooks, forms, reports, recordsets, the application itself, and other items exposed through a Microsoft® Office XP application's type library.
When you create a new module or form or add a control to a document, form, or report, the Visual Basic Editor creates a default name for the object, such as Module1, Form3, or TextBox5. You should avoid using these default names in your code. Develop the habit of specifying a meaningful name for an object as soon as you add it to your project. That way, you will not have to revise your code to rename objects later. The name should include a prefix that specifies what the object is and a name that identifies its purpose. For example, you could use modDataAccessCode, frmCustomers, and txtLastName to represent a module, a form, and a text box control. A three-character prefix is preferred, but the important point is that the prefix should be adequate to clearly specify the control type.
Note When you are designing custom object models, you should use object names without prefixes and instead use names that indicate the purpose of the objects in the model. Custom objects are designed to be used by other developers and are exposed through the Object Browser; therefore, prefixes do not make sense in this context.
When you create HTML objects and controls, you must specify a name by using the object's ID parameter. If you use a tool to add controls to an HTML page, the tool often will insert a default name for an object or control, the same way that the Visual Basic Editor does. For example, if you add a Microsoft® Forms 2.0 CommandButton control to an HTML page by using the Microsoft® ActiveX® Control Pad, the control's ID parameter is given the name CommandButton1 by default. These objects and controls should always be renamed according to the guidelines discussed in this section.
HTML element names (tags) should be entered in all capital letters. Although HTML is not case-sensitive, using this convention will help create a visual distinction between HTML elements and other items on the page. You might think of this technique as being equivalent to Microsoft® Visual Basic® for Applications (VBA) keywords being highlighted in the Visual Basic Editor. For example, if you examine the HTML code in the following example, the use of uppercase HTML element names clearly distinguishes them from the other items on the page:
<HTML>
<HEAD>
<TITLE>
Developing Office Deveoper VBA and Workflow Solutions, Formatting HTML Elements
</TITLE>
<STYLE>
.CenterThisRed {position:absolute; left:40%; top:220; font:bold; color:red}
.BoldAndBlue {font:bold; color:blue}
</STYLE>
<SCRIPT LANGUAGE="VBSCRIPT">
<!--
Option Explicit
Dim strMessage
Sub ShowAMessage(strMessage)
' Display strMessage in a message box.
If Len(strMessage) = 0 Then
strMessage = "You need to enter some text in the " _
& "'Enter Text Here' text box before you can " _
& "see it displayed here!"
End If
MsgBox strMessage
End Sub
Sub cmdMessage_OnClick()
ShowAMessage(frmSampleForm.txtMessage.Value)
frmSampleForm.txtMessage.Value = ""
End Sub
-->
</SCRIPT>
<BODY>
<CENTER>
<H1>Enter HTML Elements Using
<BR>
<SPAN CLASS = "BoldAndBlue">
ALL CAPS
</SPAN>
</H1>
</CENTER>
<HR>
<DIV ID="ItemsList" CLASS="CenterThisRed">
<OL>
<LI>Item One</LI>
<LI>Item Two</LI>
<LI>Item Three</LI>
<LI>Item Four</LI>
</OL>
</DIV>
<CENTER>
<FORM NAME="frmSampleForm">
<DIV ID="divTextBoxLabel"
STYLE="font:bold;
color:green">
Enter Text Here:
</DIV>
<INPUT TYPE="Text" NAME="txtMessage" SIZE=50>
<BR>
<INPUT TYPE="Button" NAME="cmdMessage" VALUE="Display Text">
</FORM>
</CENTER>
</BODY>
</HTML>
1.3.Naming Functions and Subroutines
A well-written procedure performs a single specific task and is named to identify the task performed. If you find it difficult to give a specific name to a procedure because it is performing more than one task, consider breaking the procedure down into multiple procedures, so each discrete piece of functionality can be identified clearly.
When naming a procedure, you should use the NounVerb or VerbNoun style to create a name that clearly identifies what the procedure does. It is not necessary to use a prefix or suffix to specify the data type of the return value. Keep in mind that when you store related procedures in the same module, the Procedures box in the Code window will display those procedures alphabetically. If you stored all your data access code in a module named modDataAccessCode, you could use the NounVerb naming style, so related procedures are listed together. For example, the CustomerAdd, CustomerDelete, and CustomerUpdate procedures would all be displayed together in the Procedures dialog box.
When you are creating procedures that use arguments, use argument names that adhere to your variable-naming convention. For example, the following procedure uses arguments consisting of three strings, an integer, and a Boolean value:
Function RemoveString(ByVal strSource As String, _
strStart As String, _
strEnd As String, _
Optional intEndCount As Integer = 0, _
Optional blnReturnChunk As Boolean = False) As String
As _
String
.
.
.
End Function
When you are calling a built-in or custom method or procedure that accepts optional arguments, always use named arguments instead of positional arguments. Named arguments make your code easier to understand, debug, and maintain. A named argument is an argument name followed by a colon and an equal sign (:=), followed by the argument value. When you use named arguments, you do not have to include placeholders for optional arguments not passed to the procedure. The first line in the following example shows how to call a custom procedure using positional arguments. The second line shows how to call the same procedure using named arguments.
strModifiedString = RemoveString(strOriginalString, strStartHere, _
strEndHere, , True)
strModifiedString = RemoveString(strSource:=strOriginalString, _
strStart:=strStartHere, strEnd:=strEndHere, blnReturnChunk:=True)
The following example shows how to use named arguments to call the Open method of the Word Documents collection. The Open method accepts up to 10 arguments, but only the FileName argument is required.
Application.Documents.Open ReadOnly:=True, FileName:="AUTOSHAPE.DOC", _
Format:=wdOpenFormatAuto
If an argument uses a value that represents a built-in enumerated constant, declare the argument's data type by using the enumerated constant name. For example, if you have an argument that is used to specify one of the many Outlook item types, declare the argument As Outlook.OlItemType rather than As Integer. Using this technique means you do not have to validate the argument that is passed to the procedure, because by definition the argument value can contain only an existing Outlook item type. For example:
Function CreateNewItemB(intItemType As Outlook.OlItemType, _
Optional strName As String = "")
Dim olApp As New Outlook.Application
Dim olNewItem As Object
Select Case intItemType
Case olMailItem
Set olNewItem = olApp.CreateItem(olMailItem)
Case olAppointmentItem
Set olNewItem = olApp.CreateItem(olAppointmentItem)
Case olContactItem
Set olNewItem = olApp.CreateItem(olContactItem)
Case olTaskItem
Set olNewItem = olApp.CreateItem(olTaskItem)
Case olNoteItem
Set olNewItem = olApp.CreateItem(olNoteItem)
Case Else
End Select
.
.
.
End Function
2.Structuring and Formatting Your Code
How you use structured coding conventions directly affects how easy your code is to understand and maintain. General principles of applying a structure to your code have effects at the application level, the module level, and the procedure level. The corresponding use of formatting - line breaks, white space, and indentation - helps reveal the logic and structure of each procedure.
2.1.Structuring Your Code
At the application level, your code is contained in one or more standard modules or class modules and in modules behind forms, reports, or documents. You apply structure to your code at this level by organizing your code logically within these components in your application. Within any module, the procedures should have some relation to each other. For example, you could keep all data access code in a single module. Form, report, or document modules should contain only code that applies directly to the form, report, or document or to controls it contains.
At the procedure level, applying a structure to the code means breaking up large procedures into smaller ones and using line breaks, white space, and indentation to organize and illustrate the logical structure of the code. Any general-purpose procedures called by code in these objects should be contained in a separate module. In addition, you should add comments at the module level to provide information on the nature and purpose of the procedures contained in the module.
You should use these principles whether you are writing Microsoft® Visual Basic® for Applications (VBA) code or script in an HTML page. You can think of an HTML page as being similar to a VBA application for the purposes of structuring your script. You can think of blocks of script within <SCRIPT> tags as being similar to VBA procedures contained in a module. The script and procedures used between <SCRIPT> tags should be related to other script within the same set of tags. Any general-purpose procedures called from the script in an HTML page should be grouped together within their own pair of <SCRIPT> tags or kept in a scriptlet.
2.2.Formatting Code
Some developers believe that although formatting code might make it look pretty, it is not really worth the time. However, properly formatting code has nothing to do with appearance and everything to do with how easy your code is to understand and maintain. The basic techniques used to format code are line breaks, white space, and indentation. In addition to making the code easier to read, these formatting techniques help document the code by showing the logic and flow of a procedure and by grouping logically related sections of code.
Formatting VBScript vs. VBA Code
Even developers of the most feebly written Microsoft® Visual Basic® for Applications (VBA) code usually attempt to name things consistently and add comments and perhaps some white space where appropriate. However, something very different is happening on the Web. It seems there is no attempt to use naming conventions or formatting techniques to make script easier to understand and maintain; in fact, just the opposite appears to be happening. Perhaps it is the forgiving nature of an HTML page as a scripting environment, or perhaps it is because script in an HTML page is viewed easily by others, and the easier it is to understand, the easier it is for someone to borrow.
Line Breaks
In VBA and Visual Basic Scripting Edition (VBScript) code, you break a line by using the line-continuation character - an underscore (_) preceded by a space. You use line breaks to make sure that your code does not extend beyond the right edge of the Code window (usually about 60 characters).
For example, line breaks have been used in the following code, so the entire string can be viewed in the Code window without having to scroll to the right:
Dim strMessage As String
strMessage = "Fourscore and seven years ago our fathers " _
& "brought forth, on this continent, a new nation, " _
& "conceived in liberty, and dedicated to the " _
& "proposition that all men are created equal."
MsgBox strMessage
Note how an additional tab character is inserted for all lines following the initial line break. This creates the visual cue that the indented text remains a part of the portion of the string that comes before the line break.
If the line following the continued line is indented as much as the continued line would be, add one more tab to the continued line to distinguish it from the next line. For example:
If ActiveSheet.ChartObjects(1).Chart.ChartTitle = _
ActiveSheet.Range("a2").Value Then
MsgBox "They are equal."
End If
Be careful when you are using line-continuation characters in strings. If you must divide the string into two or more strings, place the line-continuation character between the strings, and then concatenate them using the ampersand (&). It is important to preserve all spaces in the string when it is concatenated. For example:
Sub LongString()
' This will form a correct SQL string.
strSQL = "SELECT LastName, FirstName FROM Employees WHERE " _
& "(BirthDate > #1-1-60#);"
' This one will be missing the space between WHERE and (BirthDate).
strSQL = "SELECT LastName, FirstName FROM Employees WHERE" _
& "(BirthDate > #1-1-60#);"
End Sub
Use the ampersand (&) for all concatenation operations; never use the plus sign (+).
In HTML code, you create a line break by entering a carriage return. The browser will ignore these line breaks when it renders the page. For example, text in this HTML page will break only where the <BR> element appears:
<BODY>
<CENTER>
<H2>Office Programmer's Guide
<BR>Programming Concepts
<BR>HTML Sample Page: Line Breaks</H2>
<HR>
<H3>To see an example, click Source
on the View menu.</H3>
<BR>
<BR>
</CENTER>
Fourscore and seven
years ago our fathers
brought forth, on this
continent, a new nation,
conceived in liberty, and
dedicated to the proposition
that all men are created equal.
</BODY>
White Space
Use blank lines to separate logically related blocks of code, introductory (header) comments from the first variable declaration, and the last declared variable from the code itself. Precede all comments with a blank line.
Indentation
Indent code and comments within a procedure by using a two- to four-space tab stop. (The Visual Basic Editor uses a four-space tab stop by default.) As with white space, indents are used to organize code logically and make it visually appealing.
The following list contains some general guidelines regarding where, when, and how to use indentation correctly to make your code more readable and maintainable:
Indent all code and comments within a procedure at least one tab stop. The only code lines that are not indented are the beginning and ending of the procedure and line labels used in connection with your error handler.
If you use line breaks to format a procedure's argument list, use tabs to indent the arguments and their data-type declarations, so they are aligned with the first argument in the list.
Indent declared variables one tab stop. Declare only one variable on a line.
Indent control structures at least one tab stop. If one control structure is embedded within another, indent the embedded structure one tab stop. Indent code within a control structure one additional tab stop.
If you use a line-continuation character to break a line of code, indent the new line one extra tab stop. This creates a visual cue that the two (or more) lines belong together. If the line following the continued line is indented as much as the continued line would be, add one more tab to the continued line to distinguish it from the next line.
Indent comments to the same level as the code to which the comment refers.
Look at how these general techniques are applied in the following procedure:
Function GetFileList(strDirPath As String, _
Optional strFileSpec As String = "*.*", _
Optional strDelim As String = ",") As String
' This procedure returns a delimited list of files from the
' strDirPath directory that match the strFileSpec argument.
' The default delimiter character is a comma. By default, the
' procedure returns all files ("*.*") from the designated
' directory.
Dim strFileList As String ' Used to collect the file list.
Dim strFileNames As String ' The full path and criteria to search for.
Dim strTemp As String ' Temporarily holds the matching file name.
' Make sure that strDirPath ends in a "/" character.
If Right$(strDirPath, 1) <> "/" Then
strDirPath = strDirPath & "/"
End If
' This will be our file search criteria.
strFileNames = strDirPath & strFileSpec
' Create a list of matching files delimited by the
' strDelim character.
strTemp = Dir$(strFileNames)
Do While Len(strTemp) <> 0
strFileList = strFileList & strTemp & strDelim
strTemp = Dir$()
Loop
If Len(strFileList) > 1 Then
' If there are matching files, remove the delimiter
' character from the end of the list.
GetFileList = Left(strFileList, Len(strFileList) - 1)
Else
GetFileList = ""
End If
End Function
3.Commenting Code
If you follow the guidelines discussed in Using a Naming Convention and Structuring and Formatting Your Code, you are using a naming convention that identifies objects, variables, constants, and procedures, and you are using a prefix to indicate each variable's data type and scope. In addition to naming conventions, structured coding conventions, such as code commenting, can greatly improve code readability.
All procedures and functions should begin with a brief comment describing the functional characteristics of the procedure (what it does). This description, preceded by an apostrophe ('), should not describe the implementation details (how it does it), because these often change over time, resulting in unnecessary comment maintenance work, or worse yet, erroneous comments. The code itself and any necessary inline comments will describe the implementation. For example:
' This is a comment beginning at the left edge of the
' screen.
Text1.Text = "Hi!" ' Place friendly greeting in text
' box.
Note Comments can follow a statement on the same line or can occupy an entire line. Both are illustrated in the preceding code.
3.1.Using Comments Effectively
The purpose of adding comments to code is to provide an understandable description of what your code is doing. Comments should provide information that is not otherwise available from reading the code itself. Good comments are written at a higher level of abstraction than the code itself. Comments that only restate what is already obvious add nothing to the code and should be avoided. In addition, if your comments speak to how the code works, instead of to what it does, you have created an additional code-maintenance problem, because comments that describe how code works must be revised whenever you change the code. Failing to maintain these comments along with the code creates a risk that the comments will no longer describe the code. Some developers often write "how" comments that merely restate what is already obvious from the code itself; for example:
' Make sure the length of strSource is not zero and it contains
' a ".txt" extension.
If Len(strSource) > 0 And InStr(strFileName, ".txt") > 0 Then
' If strSource does not contain a ":" or a "/" then
' return False.
If InStr(strFileName, ":") = 0 Or InStr(strFileName, "/") = 0 Then
SaveStringAsTextFile = False
Else
' Get the next available file number.
intFileNumber = FreeFile
' Open the file in Append mode.
Open strFileName For Append As intFileNumber
' Write data to the file on disk.
Print #intFileNumber, strSource;
' Close the file.
Close intFileNumber
End If
Else
' Return False.
SaveStringAsTextFile = False
End If
These comments add nothing that is not evident from the code itself. The following is the full version of this procedure that lets the code speak for itself and uses comments that describe only what the code is doing:
Function SaveStringAsTextFile(strSource As String, _
strFileName As String) As Boolean
' Save the string in strSource to the file supplied
' in strFileName. If the operation succeeds, return True;
' otherwise, return False. If the file described by
' strFileName already exists, append strSource to any
' existing text in the file.
Dim intFileNumber As Integer
On Error GoTo SaveString_Err
' Assume that the operation will succeed.
SaveStringAsTextFile = True
If Len(strSource) > 0 And InStr(strFileName, ".txt") > 0 Then
If InStr(strFileName, ":") = 0 Or InStr(strFileName, "/") = 0 Then
' Invalid file path submitted.
SaveStringAsTextFile = False
Else
' Save file to disk.
intFileNumber = FreeFile
Open strFileName For Append As intFileNumber
Print #intFileNumber, strSource;
Close intFileNumber
End If
Else
SaveStringAsTextFile = False
End If
SaveString_End:
Exit Function
SaveString_Err:
MsgBox Err.Description, vbCritical & vbOKOnly, _
"Error Number " & Err.Number & " Occurred"
Resume SaveString_End
End Function
At a minimum, you should add comments at the module level to describe the group of related procedures in the module. Add comments at the procedure level to describe the purpose of the procedure itself. For example, the following module-level comments document the public and private procedures (called "methods" in a class module), the properties and their data types, and information about how to use the class as an object:
' This class provides services related to creating and sending
' Outlook MailItem objects. It also includes wrappers to handle
' attaching files to a mail message.
'
' Public Methods:
' MailAddRecipient(strName As String, Optional fType As Boolean)
' strName: Name of recipient to add to message.
' fType: Outlook MailItem Type property setting.
' SendMail(Optional blnShowMailFirst As Boolean)
' blnShowMailFirst: Whether to show the Outlook mail message
' before sending it. Set to True 'programmatically
' if unable to resolve recipient addresses.
'
' Private Methods:
' InitializeOutlook()
' CreateMail()
'
' Public Properties:
' MailSubject: (Write only, String)
' MailMessage: (Write only, String)
' MailAttachments: (Write only, String)
'
' Usage: From any standard module, declare an object variable of type
' clsMailMessage. Use that object variable to access the methods
' and properties of this class.
Where appropriate, add comments to describe the functionality of a particular line or block of code. These comments should be used sparingly and should be used to document any unusual aspects of the code. A blank line should precede all comments, and they should be aligned with the code to which they apply. Insert comments before the line or block of code to which they apply, not on the same line as the code itself.
In certain circumstances, you will use comments to document the arguments passed to a procedure, to state whether those arguments should be within a certain range of values, to convey whether global variables are changed within the procedure, and to relate the procedure's return values. It is not unusual to include comments that document a procedure's revision history, the names of other procedures that call the current procedure, the author of a procedure (or a revision), or a sample syntax line showing how the procedure is called.
It is a good practice to write comments at the same time (or earlier than) you write your code. Some developers write the comments for all of their procedures before they write a single line of code. It can be very effective to design procedures using only comments to describe what the code will do. This is a way to sketch out a framework for a procedure, or several related procedures, without getting bogged down in the details of writing the code itself. Later, when you write the code to implement the framework, your original high-level descriptions can be effective comments. Whatever technique you use, always enter or revise your comments as soon as you write the code. Avoid "saving it for later," because there is rarely time to do it later, or, if there is, you will not understand the code as well when you come back to it.
You add comments to an HTML page by wrapping them in comment tags. The HTML element for a comment is the <!- and -> tag pair. At a minimum, add comments to document the HTML where appropriate. Use an introductory (header) comment to document each subroutine and function in the HTML page. How you add comments to script in an HTML page depends on the scripting language you are using. In VBScript, comments are indicated by an apostrophe (') character. In Microsoft® JScript®, you use either //, which indicates that the rest of the line is a comment, or /* comment text */, which indicates that all of the comment text is a comment, no matter how many lines it spans.
Comments serve an additional purpose when they are used in script in an HTML file. Browsers will ignore any unrecognized HTML tag. However, if the script tags are ignored, the browser will attempt to render the script itself as plain text. The correct way to format script so older browsers will ignore both the script tags and the script itself is to wrap your script (but not the script tags) in the <!- and -> comment tags. If you are using VBScript, you must use the apostrophe character to add comments to script that is nested within the <!- and -> comment tags. The following example uses both forms of comment tags:
<SCRIPT LANGUAGE="VBSCRIPT">
<!--
Option Explicit
Sub UpdateMessage()
' This procedure calls code in a scriptlet to get
' values for the current day, month, and year, and then
' uses the innerHTML property of a <DIV> tag to dynamically
' display those values on the page.
.
.
.
-->
</SCRIPT>
3.2.Automating Code Commenting
It is a good practice to add thorough comments to any code that you write. Although it might be perfectly clear to you how your code works, it might not be clear to someone else (or even to yourself) when that code must be modified at a later time. Clear comments can save you a lot of time and effort in the future.
The VBA Code Commenter and Error Handler add-in helps you to add consistent comments to your code, including author name, date and time of creation, and other pertinent information. It inserts a header template at the beginning of a procedure; you fill in the blanks. You can choose to add comments to a single procedure, to all of the procedures within a module, or to all procedures in your project at once.
To add comments to your code
- From the Add-Ins menu, select VBA Code Commenter.
Note The VBA Code Commenter menu item is available only when the VBA Code Commenter and Error Handler add-in is loaded.
- Make a selection from the Add Comments To group to determine whether comments will be added to the current procedure, all procedures in the current module, or all procedures in the current project.
- Verify that the desired template is selected in the Code Header Template box.
- Type the name and initials for the Author if desired. With the default template, this information appears as part of the code comments. This information is stored in the registry for future use. If you do not enter information, the name and initials default to the registered user of the product.
- Click OK.
The VBA Code Commenter and Error Handler add-in uses template files (.eht) to control the format of comments. You can edit the standard template (CodeCommenter.eht) or create your own templates using Notepad or another text editor.
4.Designing Code to Be Used Again
The following topics cover techniques you can use to transform your code into reusable components.
These topics are designed to get you started writing code that can be used in different situations.
In This Section
Reusable code is code that can be used, without modification, to perform a specific service regardless of what application uses the code.
There are many ways to write code that performs some valuable service.
Whether you are working on a team or by yourself, you can integrate source code control, code sharing, and code reuse for your projects, simplifying just about any development project.
4.1.What Is Reusable Code?
Reusable code is code that can be used, without modification, to perform a specific service regardless of what application uses the code.
There are everyday objects that perform a specific service in different circumstances all around you. Think of a calendar. It gives you the ability to look up days and dates. You can use it to determine that this year your birthday falls on a Tuesday, or that Thanksgiving is on the 25th, or that there are two extra days to file tax returns because April 15 is on a Saturday. When you are building software, objects are created in code, and reusable objects that perform specific services in different circumstances are called components.
When you use Microsoft® Office to build custom applications, you write code that leverages the power of Office components. Using an Office component means you not only do not have to write the code yourself, but you are using a component that has been tested and found reliable in different conditions. In the past, developers would consider writing a custom program to check spelling. Today, you would call the spelling checker provided with Microsoft® Word. Similarly, nobody would develop custom code to calculate depreciation or determine principal and interest payments on a long-term loan. Instead, you would call the VBA built-in financial functions or use the Microsoft® Excel Application object to handle complex calculations for you.
Just as you can build custom applications based on components supplied as part of Office, you also can build them by using reusable components you have created yourself. You can think of reusable code from the perspective of the code that will call it to perform its service. This reusable code is a black box that accepts a known input value and returns a known output value. What happens inside the box (how the procedure actually works) is irrelevant to the code that calls it.
When you get into the habit of writing reusable procedures, you will find that you often have applications where groups of related procedures work together to perform a single service or a group of related services. For example, you might have a group of procedures that provides data access services or another group that consists of string-handling routines. This is an opportunity to group related procedures in their own module (or in a class module that exposes methods and properties to gain access to the procedures). Then, you can add the module to any application that requires the services it provides.
4.2.Writing Reusable Code
There are many ways to write code that performs some valuable service. Options range from recording a macro that can replay a sequence of keystrokes and menu selections to creating a class module that provides a wrapper around complicated Microsoft® Windows® application programming interface (API) functions.
It is not difficult to write reusable code. It is really a matter of how you approach the problem. If you understand how to create and use class modules, then you already know a great deal about how to approach writing reusable code.
The first consideration when you are writing reusable code is writing code that uses a consistent naming convention, that is formatted properly, and that contains useful comments.
Examine your existing code to make sure that your procedures have a single, specific purpose. Can you describe your procedures in a short, plain sentence? For example, "This procedure accepts an SQL string as an argument and returns a Recordset object containing the records described by the string." If you are unable to describe a procedure simply and clearly, it probably does too many things. Break down complicated procedures into smaller ones that do one thing each. Procedures should contain only code that clearly belongs together.
In the first of the following two examples, you have application-specific code that provides an application, but is not reusable. In the second example, you have created a reusable component that can perform its service from within any application.
Avoid making specific reference to named application objects. For example, the following code makes a specific reference to a combo box control and a text box control on a Microsoft® Access form:
strEmployeeName = Forms!frmEmployees!cboEmployeeName
strSQL = "SELECT * FROM Employees WHERE LastName = '" & _
Mid(strEmployeeName, InStr(strEmployeeName, " ") + 1) & "'"
Set rstAddresses = dbs.OpenRecordset(strSQL)
Forms!frmEmployees!txtHireDate = rstAddresses!HireDate
It would not be possible to reuse the previous code without revising it substantially. However, the procedure could be rewritten as a function that accepts a table name, a field name, and the record-selection criteria and returns the matching data. The following procedure could be used in any application that must retrieve a value from a field in a table:
Function GetDataFromField(strTableName As String, _
strFieldName As String, _
strCriteria As String) As Variant
' Returns a value from the field specified by strFieldName
' in the table specified by strTableName according to the
' criteria specified by strCriteria.
Dim rstFieldData As New ADODB.Recordset
Dim strSQL As String
On Error Resume Next
strSQL = "SELECT " & strFieldName & " FROM " & _
strTableName & " WHERE " & strCriteria
rstFieldData.Open strSQL, DATA_CONNECTSTRING & DATA_PATH
If Err = 0 Then
GetDataFromField = rstFieldData(strFieldName)
Else
GetDataFromField = ""
End If
End Function
In the previous code sample, notice that two constants were used in place of the database connection string and database path in the Microsoft® ActiveX® Data Object (ADO) Recordset object's Open method. This sample highlights another important consideration when you are writing reusable code: Avoid hard-coding values used in your code. If a string or number is used repeatedly, define a module-level constant and use the constant in your code. If you must use a string or number in more than one module, declare the constant by using the Public keyword. If you have a string or number that is local to a procedure, consider rewriting the procedure to pass the value as an argument or by using a local constant.
Try to minimize the number of arguments in a procedure and pass in only what is actually required by the procedure. In addition, make sure your procedures use all the arguments passed to them.
Group related procedures and the constants they use together in the same module, and where appropriate, consider grouping related procedures together in a class module with a clearly defined interface.
Keep procedures in standard modules and not in modules behind forms or documents. The code in form modules should be only that code that is tied directly to the form itself and the code required for calling general procedures stored in standard modules.
Communicate between procedures by passing data as arguments to the procedures. Persist data by writing it to disk or to the Windows registry. Avoid using a procedure to write to a global variable so another procedure can read data from that global variable. Avoid communicating with another procedure by passing data out of the application, for example, using one procedure to write data to a disk file, .ini file, or the registry so another procedure can read that data.
The same considerations that go into writing reusable code also apply to writing reusable script. The easiest way to reuse script is to group related procedures together in a scriptlet and then link the scriptlet to the HTML page in which you want to use the script.
4.3.Source Code Sharing
Whether you are working on a team or by yourself, you can integrate source code control, code sharing, and code reuse for your projects, simplifying just about any development project. Microsoft® Visual
SourceSafeTM and the Code Librarian make it easier to share, reuse, and move your code safely among individual programmers, development teams, and project stages.
Visual SourceSafe is a version control system that makes it possible for you and other team members to share files, modify them independently, and later merge the changes. Visual SourceSafe also saves past versions of the files in a database, tracks the date and time of changes, and provides an option to keep a comment log.
The Code Librarian is a database for code snippets, functions, or modules that might be useful to other developers. You can select functions, change the name, change the description, and specify search criteria. In addition, you can create new code snippets and delete existing ones.
扫一扫
302
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
