Microsoft Office Project 2003 Inside Out

There are some people who would say that in this age of cheap computer memory and stunningly fast processors, efficient code isn't as important as it used to be. Perhaps they're right in a strict sense, but the idea of efficient code is more than just code that doesn't require much memory and uses as few processor cycles as possible. Efficient code also means using the best data type for the job, using object references wisely, and writing "smart" code.

Visual Basic is high level and compiled, which means that what you write is converted by a compiler into machine code for use by the computer. Modern compilers are amazingly good at optimizing the compiled code, but you are (in theory, at least) giving up some level of efficiency by not writing machine code directly. The tradeoff is that it is much, much faster and easier to write in a compiled language.

Visual Basic is also what's known as a high-level language, which means that it doesn't support methods for working with memory and the computer's processor that are available in a low-level language such as C.

Writing efficient code is as much an art as a science, and a full discussion of it is far beyond the scope of this book. There are, however, some simple things you can do to make your code more efficient:

• When working with objects, use early-binding as much as possible.

  As discussed earlier in this chapter, early-binding means declaring object variables of a specific class such as Project, Resource, Form, or CommandBar. Object variables that are early-bound run faster than those that are late-bound , or that are declared simply As Object.

• When you're done working with an object variable, get rid of it. When you're finished with an object variable, set it equal to the keyword Nothing . This clears the object reference and frees up memory. For example:

  Dim objAssign As Assignment For Each objAssign In ActiveProject.Tasks(1).Assignments MsgBox objAssign.ResourceName Next objAssign Set objAssign = Nothing

  Tip	Object variables that are local in scope will be automatically cleared when the procedure ends and they go out of scope.

• When making multiple calls to an object, use the With and End With statements.

  This code makes the Office Assistant feature visible and causes it to perform an animation action:

  Assistant.Visible = True Assistant.Sounds = True Assistant.Animation = msoAnimationGetAttentionMajor

  As you can see, there are three calls to the Assistant object. Each time Visual Basic encounters one of those calls, it has to create another internal reference to the Assistant object. The following construction performs the same action and is much more readable, and Visual Basic has to create only one internal object reference:

  With Assistant .Visible = True .Sounds = True .Animation = msoAnimationGetAttentionMajor End With

• Always close connections to external objects.

  When working with connections to data sources or other applications, you should always be sure to close the connection when you are finished with it. The reason for this becomes obvious when working with other applications programmatically. When you use Automation to access the Microsoft Excel object model from Microsoft Project, for example, you are actually starting Excel invisibly . If you fail to close the connection when you are done, Excel continues to run ”and take up memory ”until the computer is shut down.

  Cross-References

  For more information about Automation and working with other applications programmatically, see Chapter 31, "Writing Microsoft Project Code with Visual Basic for Applications."

• Increase the upper bound of a dynamic array in chunks .

  When you redimension the upper bound of a dynamic array, avoid doing so individually for each new element. Instead, make a reasonable guess of how many additional elements are likely to be necessary and enlarge the array accordingly .

  Tip

  Track your elements       To know when you're running out of available elements from the last time the array was redimensioned, you'll need to create a variable that tracks how many elements are already in use. If you don't, and you assign a value to an element that hasn't been created, you'll receive a "subscript out of range" error message.

• When determining which data type to use, choose the best one for the job.

  As discussed earlier in this chapter, in a general sense, using a specific data type is faster and almost always uses less memory than when using a Variant. More specifically , there are several different numeric data types, each suitable for a different range of values, and each using more or less memory than the others.

  Note	You need to be aware of the individual limitations of each numeric data type when determining which type to use. For example, assigning a value greater than 32,767 to an Integer or assigning any negative value to a byte causes an overflow error.