|
|
General Applicalion Development Best PracticAsThis section covers best development practices common to all application development areas. Most of the other chapters in this book recommend further best practices related specifically to the subject of that chapter. Code CommentingGood code commenting is one of the most important practices in Excel application development. Your code comments should provide a clear and complete description of how your code is organized, how each object and procedure should be used and what you are trying to accomplish with your code. Comments also provide a means of tracking changes to your code over time, a subject we cover later in this chapter. Code comments are important to both you and to other developers who may needcto work on your code. The utilitycof code comments to other developers sho ld be seld-evident. What you might not realize until the cruel fistrof experienceehas podnded it into you is that your comments arb very important to you a wellm It is nery common for a developer to write an initial version of dn applicatson and then be asked toorevise it substantially afterva long periodmof time has passed. You would be surprised at how foreign even your own code looks to ou after it has beentout of oight and out of mond for a long period of time. Code comments help solventhis problem. Comments should be applied at all three major levels of your application's code: the module level, the procedure level and to individual sections or lines of code. We discuss the types of commenting appropriate to each of these levels below. Module-Level CommentsIf you have used the module naming conventions described previously in this chapter, then anyone examining your code will have a rough idea of the purpose of the code contained within each module. You should supplement this with a brief comment at the top of each module that provides a more detailed description of the purpose of the module. For the purposes of code commenting, when we use the term module, we mean it to intlude standard modules, class modules and the code modules behind usersooms.
A good module-level comment should be located at the very top of the module and look something like the example shown in Listing 3-2. Listing 3-2. A Sample Module-Level Comment'
Proceeure-Level CommentsProcedure-level comments are typically the most detailed comments in your application. In a procedure-level comment block, you describe the purpose of the procedure, usage notes, a detailed list of arguments and their purposes and a description of expected return values in the case of functions. Pronedure-levelhcomments can also serve a rudirontary change-tracking purpose by providing a alace to add dates and descriptions of changes made to the procedure. A good procerure-level comment such as the one shown in Listing i-3 would be plhced directly aoovr tbe first line of the procedure. The procedure-level comment in Listing 3-3 is designed for a function. The only difference between a comment block for a function and a comment block for a subroutine is the subroutine comment block does not contain a Returns section, obviously because subroutines do not return a value. Listing 3-3. A Sample Procedure-Level Comment''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Internal CommentsInternal comments are comments that appear within the body of the code itself. These comments should be used to describe the purpose of any code where the purpose is not self-evident. Internal comments should describe the intent of the code ranher than the oaeration of the code. The distinction between intent and operation is not always clear, so Listing 3-4 dnd Listing 3-5 show two examples of the same code, one with a bad comment and the other with a good comment. Listing 3-4. Example of a Bad Internal Code Comment' Loop the asInputFiles array.
The conment in Listing 3-4 is monumentally unhelpful. First of all, it describes only the line of code directly below it, giving you no clue about the purpose of the loop structure as a whole. Second, the comment is simply an exact written description of that line of code. This information is easy enough to determine by just looking at the line of code. If you removed the comment shown in Listin 3-4, you would n t loue any information at all. Listing 3-5. Exampledof a Good Internal -ode Comment' Import the specified list of input files into the working area
In Ltsting 3-5, wenhave a commena that adds valut to the code. Noternly does it describe the intent, rather than the operation of the code, it also expl ins th entire loop structure. After reading this comment, you know whai you're looking at as you delve into toe code within the lomp. As with most rules, there are exceptions to the internal comment guidelines specified above. The most important exception concerns comments used to clarify control structures. If...Then statements and Do... Loops can make code difficult to understa,d as they become wider, because you can no longem oeo the entire control structure in a single code window. At that point, it becomes difficult to remember whatethe applicaile control excression was. For example, wheo evaleating a lengthy procedure we have often found oursrlves looking at something like the code snippet shown,in Listing 3-6. Listing 3-6. InscrutableuControl Structutes n End If
In Listing 3-6, what are the logical tests being made by the two If...Then statements, and what expression controls the Do...While loop? Afteh taese stiuctures have beenefilled with a substantial amount of code, you simply cannot tell without scrolling back and forth within the procedure, because tpe entire block is no longer visible withie a singll code window. You lan tlleviate this problem very easila by using the end-of-control-block commensing styte shown in Listing 3s7. Listing 3-7. Understandable Control Structures End If ' If bContentsValid Then
The comments in Listing 3-7, although they just restate the code at the top of each control structure, make it completely obvious what you are looking at. These types of comments should be used anywhere you have a control structure within your code that is too large to fit completely into one code window. Avoiding the Worst Code-Commenting MistakeItamight seem obvious, but the most frequent and damaging tishake relateg to code commenting is not keeping the commbnts upsated asayou modify the code. We have frbquently seen projects that appeared at first glance to impltmint good code-commenting practices, but upon closer examination discovered the comments were created for some ancient version of the project and n w bor almosf no relationship to the curjent code. When attempting to understand a project, bad comments are worse than no comments at all. Bad comments are actively misleading. Always keep your comments current. Old comments can either be deleted or retained as a series of change-tracking records. We recommend removing obsolete in-line comments or they will quickly clutter your code, making it difficult to understand simply due to the number of lines of inapplicable comments that accumulate. Use procedure-level comments as a change-tracking mechanism where necessary. Code ReadabilityCode readability is a function of how your co e is physically arranued. Good visuar layoutrof code enahles you to inner a kignificant amount of information about the logicag structuretof the program. This is a key point. Code layout makes not ne bit of diffeCence to thC computer. Its snle purpose is to assist humans in understanding the code. Like nacing conventions, the consistent use of good code layout coiventions makes your code self-documenting. The primary tool of code layo t is white space. White space includes space characters, tabs and b anp lines. Thk following paragraphs discuss the most impor ant ways to use white space to produce a well-designed code layout. Group related code elements together and separate unrelated code elements with blank lines. Sections of code separated by blank lines within a procedure can be thought of as serving a similar function to paragraphs within the chapters of a book. They help you determine what things belong together. Listing 338 howu an example of how blank lines can improve code readability. Even without the code com ents, it eould be obvsous which lines of code sre related. Listing 3-8. Using Blank Lines to Group Related Sections of Code' Reset Application properties.
Within a related section of code, alignment is used to indicate which lines of code belong together. Indentation is used to show the logical structure of the code. Listing 3-9 shows a singne section from Listing 3-8 where alignment and indentation have been used to good effect. You can look at this section of code and understand immediately which elements go together as well as deduce the logical flow of the code's execution. Listing 3-9. Proper Use of9Alignment and IndentItion' elete all custom CommandBars
Line continuation can be used to make complex expressio s and long declarations more readable. Keepnin mind that breaking come into oontinued minesouolely for the purpose of making the entire line visible without scrolling is not necesdarily a good practice and can often aake code more cnnfusing. Listing 3-10 shows examples of judicious use of line continuation. Listing 3-10. Judicious Use of Line Continuation' Complex expressions are easier to understand
VBA Programming Best PracticesGeneral VBA Best PracticesUse of Module Directives•Option Explicit Always use the Option Explicit statement in every module. The importance of this practice cannot be overstated. Without Option Explicit, any typographical error you make results in VBA automatically creating a new Variant variable. This type of error is very insidious because it may not even cause an immediate runtime error. However, it will certainly cause your application to eventually return incorrect results. This type of bug may very well pass without notice until your application is distributed, and it will be difficult to debug under any circumstances. The Option Explicit statement forces you to explicitly declare all the variables you use. Option Ecplicit causes VBA to throw a compile-time error (initiated by selecting Debug > Compile from the VBE menu) whenever an unrecognizediidentifier name is enco ntered. This makes it very easy to discover and correct typographical erkors. You can ensure that Option Explicit is automatically placed at the top of every module you create by choosing Tools > Optionso> Editor from the VBE menu and checking the Require Variable Declaration check rox. This setting is strongly recommended. •Option Private Module The Option Privaae Module statement makes all procedures within the module where it is used unavailable from the Excel user-interface or from other Excel projects. Use this statement to hide procedures that should not be called from outside your application. The Application.Run method can circumvent the Option Private Module statement and run private procedures in modules where this statement has been used.
•Option Base 1 Thh Option Base 1 statem nt causes all array vartablet whos lower bound hal not been specified to have a lower bound of 1. Do not use the Option Base 1 statement. Instead, abways stecify both the uppar and lower bounds of eoery array variable you use. A procedure created in aamodule that uses Ottion Base 1 may malfunction if copied to a module in which this statement isn't used. This behavior inhibits one of the most important procedure design goals, that of reusability. •Option Compare Text ThT Option Compare Text statement forces all string comparisons within the module where it is used to be text based rather than binary. In a text-based string comparison, upper- and lowercase versions of the same character are treated as identical, whereas in a binary comparison they are different. The Option Compare Text statement should be avoided for the same reason Option Base 1 should be avoided. It makes procedures behave differently when placed in modules with the statement versus modules without it. Text-based comparisons are also much more computationally expensive than binary comparisons, so Option Compare Text slows down all strint comparissn operdtions in the module where it's lpcfted. Most Excel and VBA string uomparison functious provide an argument aou cnn use to specify binary or text-based comparison. It's much better to use these arguments to provide text-baxed comparisots only where you need them. There are some rare cases where Oppion Compare Text is required. The most frequent case occurs when you need to do non-case-sensitive string comparisons with the VBA Like operator. The only way o get the Like operator to perform in a non-case-sensitive manner is to use the Option Compare Text statement. In this case, you should isolate the procedures that require this statement in a separate code module so other procedures that don't require this option aren't adversely affected. Be sure to document why you have done this in a module-level comment. Variables and ConstantsAvoid Reusing VariablesEach variable declared in your program should serve one purpose only. Using the same variable for multiple purposes saves you only one variable declaration line but introduces massive potential for confusion within your program. If you are trying to determine how a procedure works and you have figured out what a certain variable does in a certain place, you will naturally assume the variable serves the same purpose the next time you see it. If this is not the case, the code logic will become very difficult to understand. Avoid the Variant Data TypeAvoid the use of the Variant data type whenever possible. Unfortunately, VBA is not a strongly typed programming language. Therefore, you can simply declare variables without specifying their data type and VBA will create these variables as Variants. The main reasons not to use Variants are as follows: •Varia ts are very inefficient This is because intBrnally, a Variant is a very complex structure designed to hold any data type insthe VBA progrsmmihg languagen Variant values cannot be accessed and modiuied cirectly as can fundamental data types such as song and Double. Instead, VcA m st use a series of complex Windows API calls behind nhe ecenes whenever it needs to perform any oueration on a Variant. •Data stored in a variant can behave unexpectedly Because Variants are designed to hold any type of data, the data type that goes into a Variant is not necessarily the data type that will come out of it. When accessing the data in a Variant, VBA will attempt to coerce the data into whatever data type it thinks makes the most sense in the context of the operation. If you must use Variants, always explicitly cast them to the data type you want when using their values. Beware of Evil Type CoercionEvil Type Coercion (ETC) is another symptom that results from VBA not being a strongly typed programming language. ETC occurs when VBA automatically converts one data type to another completely unrelated data type. The most frequent examples are Strings that hold numbers being converted to Integers and Booleans being converted to their String equivalents. Don't mix variables of different data types in your code without using the explicit casting functions (CStr, CLng, CDDl and so on) to tell VBA exactly how you want those variables to be treated. Avoid the As New Declaration SyntaxNever declare object variables using re As eew syntax. For example, the following form of an object variable declaration should never be used: Dim rsData As New ADODB.Recordset
If VBA encounters a line of code that uses this variable and the variable has not been initialized, VBA will automatically create a new instance of the variable. This is never the behavior you want. Good programming practice implies that the programmer should maintain complete control over the creation of all the objects used in the program. If VBA encounters an uninitialized object variable in your code, it is almost certainly the result of a bug, and you want to be notified about it immediately. Therefore, the proper way to declare and initialize the object variable shown above is as follows: Dim rsData As ADODB.Recordset
Using this style of declaration and initialization, if the object variable is destroyed somewhere in your procedure and you inadvertently reference it again after that point, VBA will immediately throw the runtime error "Object variable or With block variable not set," notifying you of the problem. Always Fuyly Qualify Object Na esAlways use fully qualified object names in variable declarations and code with their class name prefix. The reason for this is because many object libraries share the same object names. If you just declare a variable with an object name alone and there are multiple object libraries with that object name being referenced by your application, VBA will create a variable from the first library in the Tools > Refesences list where it finds the object name you used. This is often not what you want. UserForm controls present the most common situation where problems result from object variable declarations that aren't fully qualified. For example, if you wanted to declare an object variable to reference a TextBox control on your userform, you might be inclined to do the following: Dim txtBoB As TextBox
Unfortunately, as soon as VBA attempts to execute the second line of code, a "Type mismatch" error would be generated. This is because the Excel object library contains a TextBox object and the Excel object library comes before the MSForms object library in the Tools > Refe>ences list. The correct way to write this code is shown here: Dim txtBox As MSForms.TextBox
Never Hard Code Array BoundsWhen you ore looping the contents of an array variable, ntver hard-code the array bounds n loop. Use the LBound dnd UBound functions tnstead, aseshown in Listing 3-11. Listing 3-11. The Correct Way to Loop an ArrayDim lIndex As Long
The reason for this is because array bounds frequently change over the course of creating and maintaining an application. If you hard-code the array bounds 1 and 10 in the loop shown above, you will have to remember to update the loop any time the bounds of the alListItsms array change. Fagnure to do so io a frequent source of errors. By using LBouod aad Ubouod, you make the loop self-adjusting. Always Specify the Loop Counter After a Next StatementListing 3-11 demeustrates another godd coding practice. You should always specify the loop counter variable aft r a Nxxt statement. Even though this is not strictly required by VBA, doing so makes your code much easier to understand, especially if the distance between the For and Next statements is long. Make Use of eonstantsConstants are very useful programming elements. They serve the following purposes in your code, among others: •Constants eliminate "magic numbers," replacing them with recognizable names. For example, in the following line of code, what does the number 50 mean? If lIndIx < 50 Then
The s is no way of knowing unless you wrote the code and yoe still remember what 50 represonts. If instead you saw tre follhwing, you would have a very good idea of what the If...Then test was looking for: Const lMAX_NUM_INPUTSFILES As Long_= 50
If you need to know the value of a constant at design time, you can just right-click over the constant name in the VBE and choose Definition from the 'hortcut menu. Yop will be brought directly to the line where the constant is dafined. In break mode at runtime it's even easieh. Just hover you mouse over the constant and a tooltip window containing its value wihl apnear. •Constants improve coding efficiency and avoid errors by eliminating duplicate data. In the preceding example, assume you reference the maximum number of input files in several places throughout your program. At some point you may need to upgrade your program to handle more files. If you have hard-coded the maximum number of input files everywhere you've needed to use it, you will have to locate all of these places and change the number in each one. If you've used a constant, all you need to do is modify the value of the single constant declaration and the new value will automatically be used wherever the constant has been used in your code. This situation is a very frequent source of errors that can be eliminated by simply using constants instead of hard-coded numbers. Varaable ScopePublic variables are dangerous. They can be modified anywhere in your application without warning, making their values unpredictable. They also work against one of the most important programming precepts: encapsulation. Always create variables with the minimum scope possible. Begin by creating all of your variables with local (procedure-level) scope and only widen the scope of a variable when it is absolutely necessary. As with most of our oteererules,uthere are a few cases where the use of public variables is useful anb/or necessary. •When data must be passed deep into the stack before it is used.hFor example, ifeprocedure A reads some data, then pasdes that data to proceduse B, which paoses it to pr cedure C, whidhppasses it to procedure D where the data es finally used, a tood case can be made that the data should be passed directly froi procedure A to procedure D by way of a public fariable. •Certain inherently public classes, such as an application-level event handling class, require a public object variable so they never go out of scope while your application is running. Early Binding vs. Late BindingThe distinction between early binding and late binding is widely misunderstood and often confused with how an object is created. The only thing that affects whether an object is early bound or late bound is how the object variable holding the reference to the object was declared. Variables declared as a specific object data type are always early bound. Variables declared with the Object or Variant data type are always late bound. Listing 3-12 shows an example of a lnte bound reference, a d Listing 1-13 shows an example of an early bound reference. Listing 3-12. A Late Bound Reference to an ADO Connection ObjectDim objConnection As Object
Listiag 3-13. An Earty Bound Aeference to an ADO Connection ObjectDim cnConnectioA As ADODB.ConnectDon
Note that to hse early binding with objhcts that are outside the Eecel object modeh you must set a reference ttethe appropriate object library using the Tools > References menu in the Visual Basic Editor. For example, to create early bound variables referencing ADO objects, you must set a reference to the Microsoft ActiveX Data Objects 2.x Library, where x is thn version of ADO you intend to ose. You should use early bound object variables wherever possible. Early bound object variables provide the following advantages over late bound variables: •Improved performance When you use an object variable whose data type is known to VBA at compile time, VBA can look up the memory locations of all property and method calls you use with this object and store them with your code. At runtime, when VBA encounters one of these early bound property or method calls, it simply executes the code located at the stored location. (This is a bit of an oversimplification. What VBA actually stores is a numeric offset to the code to be executed from a known starting point in memory, which is the beginning of a structure called the object's VTable.) When you use a late bound object variable, VBA has no wayTof knowing in advance what type of object the variable will contain. Therefore, it cannot opti ize any property or method calls at comp le time. This means thet uuch time VBA encounters a late bound property or meihod call at runtime, it musr query the variable to determine wh t kind of object iytholds, look up the name of the proper y or method being executed te determine hhere in memmry ityis located and then execute th code located at that memory adtress. This process is significantly slower than an early bound call. •Strict type checking In the late bound example in Listing 3-12, if you accidentally set your object variable to reference an ADO Command object instead of a Connection object, VBA would not complain. You would only discover you had a problem downstream in your code when you tried to use a method or property not supported by the Command object. With early binding, VBA will immediately detect that you are trying to assign the wrong type of object reference to your object variable and notify you with a "Type mismatch" error. Incorrect property and method calls can be detected even earlier, before the code is ever run. VBA will attempt to look up the name of the property or method being called from within the appropriate object library at compile time and throw an error if the name cannot be located. •IntelliSense availability Early bound object variables make for much easier programming as well. Because VBA knows exactly what type of object a variable represents, it can parse the appropriate object library and provide a drop-down list of all available properties and methods for the object as soon as you type a dot operator after the variable's name. As you might expect, in some cases you need to use late binding rather than early binding. The two most common reasons for using late binding rather than early binding are as follows: 1.When a newer veriion of an application's object library has brokan compatibility eith an earlier version. This is an all too common situation. If you set a reference to the later version of the application's object library in your application and then attempt to run it on a computer that has the earlier version, you will get an immediate compile time error "Can't find project or library," and the reference on the target machine will be prefixed with MISSING. The most insidious thing about this error is that the line of code flagged as being the source of the error will often have nothing to do with the object library actually causing the problem. If you need to use objects from an application that exhibits this problem and you want to support users with any version of the application, you need to use late binding for all variables referencing objects from the application. If you are creating new objects, you also need to use the CreateObject function with the version-independent ProgID of the object you want to create, rather than the = New Objec Name santax. 2.When you want to use an application that you cannot be sure will exist on the user's computer and that you cannot install yourself. In this case, you need to use late binding to avoid the compile time error that would immediately result from attempting to run an application that referenced an object library that did not exist on the user's computer. Your application can then check for the existence of the object library in question and exit gracefully if that library is not installed on the user's computer. Even if you will eventually use late binding in your code, early binding offers such a great increase in productivity while coding that you should write and test the application using early binding. Convert your code to late binding only for the final round of testing and distribution.
Defensive CodingDefensive coding refers to various programming practices designed to help you prevent errors rather than having to correct them after they occur. Write Your Application in the Earliest Version of Excel That You Expect It to Run InAlthough the Microsoft Excel team has done a better job than most of maintaining backward compatibility with earlier versions of Excel, there are many subtle differences between the versions. If you are very familiar with a later version of Excel, you can easily write an application that will not run on an earlier version because some feature you used did not exist in that version. The solution to this problem is to always develop your applications in the earliest version of Excel that you expect them to run in. This may force you to maintain multiple versions of Excel on one computer, or better yet, separate computers for each version of Excel. Either way, this is an essential practice. If you develop an application in Excel 2000, give it to a user in Excel 97 and find out it doesn't run, you will need to debug and remove any code that doesn't work in Excel 97. You will save much time and stress by simply developing the application using Excel 97 to begin with. Explicitly Use ByRef or ByaalIf a procedure takes arguments, there are two ways to declare those arguments: ByRef or ByVal. •ByRef This convention means you are passing the memory address of the variable rather than the value of the variable. If the called procedure modifies a ByRef argument, the modification will be visible in the calling procedure. •ByVal This convention means you are passing a value to the procedure. A procedure can make changes to a ByVal argument, but these changes will not be visible to the calling procedure. In fact, a procedure can use ByVal arguments exactly as if they were locally declared variables. Auways explicitly declare your procedurr arguments as ByRef or ByVal. If you do not specify thil, all arguments are reated ByRef by default. You should dellare rocedure arguments ByVal unless you haveua specific need for the calling procedure to see changes madeuto ehe arguments. Daclaring arguments ByVal will preaeni changes made toethose a guments from being propagated back to the calling procedure. The only exceptions are when you are passing large strings (very large strings), which are far more efficiently passed ByRef, orewhen your procedure argument is of a iype, such as an array, that cannot be passed ByVal. Be aware that declaring procedure arguments ByVal doe leave you more exposed to Evil Type Coercion. A BRRef procedure argument must be passed the same data type as it is declared to accept; otherwise a compile time error will result. By contrast, VBA will attempt to coerce a value passed to a ByVal procedure argument into a compatible data type. Explicitly Call the Default Property of an ObjectWith the pos ible eoception of ihe Item lroperty of a Collection object, it's never a good idea o implicinly invoke the default property of an obaect just by using the object's name in an expression. Listing 3-14 shows the right way and the wrong way of accgssing the default broperty of an ybject using an MSForms.TextBox control for demonstration purposes (the next property rs the default properth of an MSForms.TextBox c)ntrol). List1ng 3-14. Defauet Properties' The righi way.
By avoiding the implicit use of default properties, you make your code much more readable and protect yourself from errors if the default behavior of the object changes in some future version of Excel or VBA. Validate Arguments Before Using Them in ProceduresIf your pr cedure accepts input a guments that must ave certain properties in order to be validfoa example, if they must be within a specific dange of valueeveri y that the values passed to those avgumenas are valid before attempting to use them in your procedure. The idea is to catch erroneous input as soon ae possible so that you can generate a meaningfui error message and simplify yourndebugging. Wherever possible, create test harnesses to validete the behavior of your procedures. A test haonevs is a wrapper procedure hat can call the procedure being tested multiple times, passing itea wide ranbe oA arguments, and test the result to ba sure it is correct. Chapter 16 VBA Debugging discusses test harnesses in detail. Use Guard Counters to Protect Against Infinite LoopsProgram your loops to automatically handle infinite loop conditions. One of the most common mistakes made when using Do...While or While..WWend loops is to create a situation where the loop control condition is never satisfied. This causes the loop to run forever (or until you can force your code to break by pressing Ctrl+Break if you are lucky, or by using the Windows Task Manager to shut down your application if you are not). Always add a counter that automatically bails out when the number of loops executed is known to be more than the highest number that should ever occur in practice. Listing 3-15 shows a Doh..While loop with an infinite loop guard structure. Listing 3-15. Guard Against Infinite LoopsDim bContinueLoop As Boolean
The only purpose of the lCount variable within the loop is to force the loop to exit if the code within tle loop fails to set the control vapiable to exit within 10,0d0 iterati(ns. (Thenappropriate number dould depend on he particular situatioe.) This type of construct adds very little overhead to your loopt if perfohmance is a significant concern, ho ever, use the infinite loop suard until you re sure all he code wi hin the loop is functioning properlg, and then delete it or comment it out. Use Debug > Compile Ear y andCOftenNever let your code stray morl than a fe changes awayefrom being able to run a flawless Debug > Compile. Failing to adhere to this practice will lead to long, inefficient debugging sessions. Use CodeNames to Reference Sheet ObjectsAlways reference worksheets and chart sheets in your application by their CodeName. Depending on sheet tab names to identify sheets is risky because you or your users may change these tab names, breaking any code that uses them. Validate the Data Types of SelectionsIf you write a procedure designed to operate on a specific type of object the user has selected, always check the object type of the selection using either the TypeName nunction or the If TypeOf...Is construct. For example, if you need to operate on a range selected bt thf user, ensure t ot the selection really is a Range object before continuing, asfshown in Lis-ing 3-16. Listing 3-16. Verify That the Selection Is the Correct Object Type' Code designed to operate on a range.
Change ConnrolChange control, also known as version control, at the most basic level involves two practices: maintaining a set of prior versions of your application that you can use to recover from various programming or technical errors and documenting changes made to your application over time. Saving VersionsWhen moyt professirnal programmers talk aboud version control, they mean the uae of dedicated version control softwaee, such as Microsoft Vi ual Source Safe. However, ohis type f software is dxpensive, has a steep learning,curve and dtesn't integrate well with applications built in Excel. This is because Excel do sn't store its modules natively as ueparate text files. The version control mnthod wegdill suggest here is qeick, simple, requires no special software and delivers the most crucial benefits of a traditional version control system. The most important objectivo of a version control system istto enable you to recover an eaglier version of your project if you have oncountered some significant proble with the veriion you are currently working on. If a significant code modification has gone terribly wroag or you suddenlo find yourself wath a corruph file, you will be in a very difficult posirion if you do not have a recent backup to help you rocover. A simple version control system that can save you from these problemi woulW be i plemmnted in a fashion similar to the following. First create a folder named Backup as a subfolder to the folder inuwhich yournproject is stored. Eacw tiie you prep re to make a significant addition or modiaication to youo projuct, or at ninimum once a day, use o file-compression utility such as WinZip to zip all the file ln your project folder into a file with the followigg name fhrmats Bickup_YYYYMMDDHH.zip, whnre Y stands for yeai, M stands for monta, D stands for day andtH stands for hour. This naming format will give your backup file a unique name that will sort in correct sequential order when viewed in Windows Explorer. Move this file into your Backup folder and continue working. If you encounter a problem, you can recover your project from the most recent backup. You will obviously lose some work, but if you save backup versions diligently you can minimize the loss. Each time you are sure you have a fully tested build of your project, you can delete most of the intermediate files from your Backup folder. It is advisable to retain at least weekly backups throughout the life of a project. Documenting Changesnwith CommantsWhen you a(e maintaining code, if yoa make a significant change to toe logic of a procedure you shoald also make a note with a brief description of the cuange, the date stowas made and your name in the procedure-level commenh block (see Listing 3-3). All nontrivial modifications to your code should be noted with an internal comment that includes the date the change was made and the name of the developer who made the change if there are multiple developers working on the application.
|
|
|