.By Dave Smith
When developing in VBA for Project 98, it helps to have a tool set available that provides the minimal functionality commonly required by task, resource, and assignment management applications. The Application object, which sits at the top of the Project 98 object model hierarchy, provides powerful properties, methods, objects, and collections that enable the VBA developer to quickly build reliable applications.
However, as extensive as the Project 98 object model is (see FIGURE 1), some requisite functionality is missing and must therefore be implemented by the developer. In this article, solutions to three common problems will be presented:
How to freeze the screen display.
How to positively identify which project a running VBA procedure belongs to.
How to suppress the display of unwanted dialog boxes during procedure execution.
.
FIGURE 1: A top-level diagram of the Project 98 object model from online Help.
Freezing the Screen Display
Those experienced with Excel VBA will immediately recognize this feature as the ScreenUpdating property of the Excel Application object. Unfortunately, the Project 98 Application object has no analogous property. What is this feature? And why is it so important? Simply put, it's often necessary to lock the screen display to prevent window flashing, which occurs while a VBA procedure is running.
A good example of this is the procedure shown in FIGURE 2, which saves each open project in a running instance of Project 98. In Project 98, the FileSave method of the Application object is used to save a project file. However, this method only works on the project currently being displayed, also known as the active project. To save each open project, we must iterate each member of the Projects collection property of the Application object, first making each item the active project with the Activate method of the Project object, then invoking the aforementioned FileSave method. (Note that projects opened as read-only are skipped because these would cause the unwanted display of a FileSaveAs-generated dialog box prompt, as are projects whose windows are not visible, because an attempt to activate a hidden project would cause a run-time error.)
Public Sub SaveOpenProjects_Version1()
Dim objAnyOpenProject As Project
' Iterate through each open project.
For Each objAnyOpenProject In Application.Projects
With objAnyOpenProject
' Don't bother saving read-only projects or
' hidden projects.
If Not .ReadOnly And .Windows(1).Visible Then
.Activate
Application.FileSave
End If
End With
Next
End SubFIGURE 2: This procedure saves each open project in a running instance of Project 98.
Upon executing this code against an instance of Project 98 with multiple open projects, you may notice the screen display rapidly switches between active projects. This phenomenon can be a major annoyance when running complex procedures that are performing a significant amount of switching between active projects — especially when the switching occurs so quickly that the screen literally "flashes" like a strobe light. Your users will complain of eyestrain related headaches, your boss will think you are playing video games on company time, and children in Japan will suffer relapses of cartoon-induced seizures!
The best way to deal with this problem is by making use of API calls provided by the User32 library of the Win32 operating system. Use of the procedures shown in FIGURE 3 shields the developer from the complexity of the underlying API code, thereby allowing ease of reuse throughout an application. Calling the API function LockWindowUpdate in the procedure FreezeScreenUpdating freezes the display of the specified window until it's called a second time in the UnFreezeScreenUpdating procedure, resulting in the refreshing or updating of the window display.
Private Declare Function FindWindow Lib "User32"
Alias "FindWindowA"(ByVal lpClassName As String,
ByVal lpWindowName As String) As Long
Private Declare Function LockWindowUpdate Lib "User32"( _
ByVal hwndLock As Long) As Long
Public Sub FreezeScreenUpdating(sWindowCaption As String)
Dim lHwnd As Long
Dim lReturnVal As Long
' Determine the system handle of the specified window
' from its caption.
lHwnd = FindWindow(vbNullString, sWindowCaption)
' Freeze the updating for the passed handle.
lReturnVal = LockWindowUpdate(lHwnd)
End Sub
Public Sub UnFreezeScreenUpdating()
Dim lReturnVal As Long
' Re-enable updating on whichever window is
' currently frozen.
lReturnVal = LockWindowUpdate(0)
End SubFIGURE 3: Make use of API calls provided by the User32 library of the Win32 operating system.
The API function, FindWindow, is used to retrieve the identity, also known as the window handle of the Project 98 instance. This handle is passed as an argument to the LockWindowUpdate function so that it knows which window on your desktop to freeze. Note that to unfreeze a window, a zero is entered in place of a valid window handle. Now, the procedure (shown in FIGURE 4) runs without the irritating flash evident in the procedure shown in FIGURE 2.
Public Sub SaveOpenProjects_Version2()
Dim objAnyOpenProject As Project
' Freeze the updating on the Project 98 window,
' i.e. call procedure in FIGURE 3.
FreezeScreenUpdating Application.Caption
' Iterate through each open project.
For Each objAnyOpenProject In Application.Projects
With objAnyOpenProject
' Don't bother saving read-only projects or
' hidden projects.
If Not .ReadOnly And .Windows(1).Visible Then
.Activate
Application.FileSave
End If
End With
Next
' Unfreeze the screen updating on the frozen window,
' i.e. call procedure in FIGURE 3.
UnFreezeScreenUpdating
End SubFIGURE 4: This procedure runs without the irritating flash displayed by the procedure in FIGURE 2.
Identifying the Project Controlling VBA Execution
In demonstrating the previous tip, the Projects collection was iterated, one project at a time, to perform the same task on each project within that collection. What if, instead of saving every open project, we wanted to skip the one that actually contains (and is currently executing) the project-closing procedure? How can we distinguish the project that the running procedure belongs to from the other members of the Projects collection? In Excel VBA, the ThisWorkbook property of its Application object is used to uniquely identify the workbook currently executing code, so comparisons can be made when iterating its Workbooks collection. Sadly, there is no analogous ThisProject property in the Project 98 Application object.
It should be noted that there is a module object named ThisProject included with every project by default. It can be viewed by opening the Visual Basic Editor (VBE), displaying the project window, and opening the folder named Microsoft Project Objects for any given project. In code, this object can be used to return references to the running project simply by typing:
ThisProject.followed by any project properties or methods. However, this object's Name property can be edited through the VBE Properties window and could be changed from ThisProject to MyProject. In this case, all hard-coded references such as:
Msgbox ThisProject.Namewould need to be changed to:
Msgbox MyProject.Namebefore the code will recompile. What would really be nice is an easy-to-use, project-name-independent procedure that will always return a reference to the project currently running code. The procedure shown in FIGURE 5, a variation of that shown in FIGURE 4, demonstrates how one might use such a tool. Here, a function named RunningProject is used to return an object reference to the project that owns, and is currently running, the procedure.
Public Sub SaveOpenProjects_Version3()
Dim objAnyOpenProject As Project
' Freeze the updating on the Project 98 window,
' i.e. call procedure in FIGURE 3.
FreezeScreenUpdating Application.Caption
' Iterate through each open project.
For Each objAnyOpenProject In Application.Projects
With objAnyOpenProject
' Skip over the running project, i.e. call
' procedure in FIGURE 6.
If objAnyOpenProject <> RunningProject Then
' Don't bother saving read-only projects or
' hidden projects.
If Not .ReadOnly And .Windows(1).Visible Then
.Activate
Application.FileSave
End If
End If
End With
Next
' Un-freeze the screen updating on the frozen window,
' i.e. call procedure in FIGURE 3.
UnFreezeScreenUpdating
End SubFIGURE 5: The RunningProject function returns an object reference to the project that owns (and is currently running) the procedure.
Getting Recursive
So, how does the code behind the function RunningProject accomplish this? The key to solving this problem is to make use of a programming technique known as recursion. For those not familiar with this concept, recursion is when a procedure calls itself. Care must be taken when using this approach because one could very easily create an endless chain of recursive calls, resulting in an "out of stack space" error. To avoid this problem, a static variable can be used to help keep track of the amount of recursive nesting. Unlike normally dimensioned variables of procedure level scope, static variables will preserve their values between calls.
To further aid in tracking recursive calls, a single, optional argument can be declared in the argument list of the recursive procedure. This optional argument will allow the first external, non-recursive call to be distinguished from any subsequent internal, recursive calls. If external calls to the procedure always pass the optional argument (while the procedure's calls to itself do not), the presence or absence of the optional argument can be used to determine if the current call is the first call or an ensuing recursive call.
Let's look at how the RunningProject function works. FIGURE 6 lists the pair of procedures — a sub procedure and a function — that will return the identity of the running project. Upon examining the function RunningProject, it becomes apparent that the sub procedure named FindRunningProjectName is actually doing most of the work. The variable vntRunningProjectName is passed as an optional argument to this sub procedure, which sets its value to equal the name of the running project so that a reference to that object can be returned to the calling procedure.
Public Function RunningProject() As Project
Dim sRunningProjectName As String
' Find the running project name.
FindRunningProjectName sRunningProjectName
' Pass the project reference back to the
' calling procedure.
Set RunningProject = _
Application.Projects(sRunningProjectName)
End Function
Public Sub FindRunningProjectName( _
Optional vntRunningProjectName As Variant)
Static nRecursionCount As Integer
Dim objAnyOpenProject As Project
Dim bMacroFound As Boolean
' Increment the variable tracking the level of
' recursive nesting.
nRecursionCount = nRecursionCount + 1
' Exit the procedure if this is a recursive call:
' Only the first (non-recursive) call will pass this
' optional argument.
If IsMissing(vntRunningProjectName) Then
Exit Sub
End If
' Iterate through each open project.
For Each objAnyOpenProject In Application.Projects
With objAnyOpenProject
' Attempt to run this procedure in the project.
On Error Resume Next
Application.Macro .Name & "!FindRunningProjectName"
bMacroFound = (Err.Number = 0)
Err.Clear
On Error GoTo 0
' If this procedure is found and run, then recursion
' count will have incremented itself.
If bMacroFound Then
If nRecursionCount > 1 Then
' Now we know the name of this project.
vntRunningProjectName = .Name
Exit For
End If
End If
End With
Next
' Must reset the recursive count once the original call
' has reached this point (remember the recursive calls
' were kicked out of the procedure just after
' incrementing this variable).
nRecursionCount = 0
End SubFIGURE 6: A close look at how RunningProject works.
Let's now examine how the recursive FindRunningProjectName sub procedure works. Aside from the aforementioned devices that handle recursive nesting management — the static variable nRecursionCount and the optional argument vntRunningProjectName — the only noteworthy code is the section that makes use of the Application object's Macro method. The idea is to recursively re-run this procedure in each open project, one by one, until the recursion counter is found to have incremented itself. Once the recursion counter has changed, we know the name of the project the recursive procedure belongs to, and that value can be returned to RunningProject.
Suppressing Dialog Box Display
The Application object has a property named DisplayAlerts that, when set to False, will suppress the display of error messages while a procedure runs. The result is a less cluttered interface and the elimination of burdensome message box and dialog box prompts. However, there are some Project 98 prompts that cannot be suppressed during the course of procedure execution. A good example is when a project whose resources are being shared with a resource pool is saved using the now familiar FileSave method of the Application object. Depending on how many other projects sharing the same resource pool are open, the user will be prompted with the dialog box displayed in FIGURE 7 (and possibly with that shown in FIGURE 8 as well). For the purposes of this example, the consecutive display of both dialog boxes will be discussed.
.
FIGURE 7: This dialog box may appear when a project whose resources are being shared with a resource pool is saved using the FileSave method of the Application object.
.
FIGURE 8: The display of this — and the dialog box in FIGURE 7 — may not be suppressed by setting the DisplayAlerts property to False.
Instead of forcing the user to answer the questions asked by the dialog boxes, it would be nice if something in the code would somehow answer these questions on the user's behalf. To accommodate this requirement, the VBA statement SendKeys will be used to send the appropriate responses to the dialog boxes just before the FileSave method is invoked.
The procedure shown in FIGURE 9, a variation of that shown in FIGURE 5, demonstrates how this is done. The first SendKeys statement sends the code "%Y", which simulates pressing AY, thus selecting the Yes button on the dialog box shown in FIGURE 7. The second SendKeys statement sends the code "{ENTER}", which simulates pressing R, thus selecting the default button, OK, on the dialog box shown in FIGURE 8. (Note that the SendKeys statements are only executed if the project in question has a shared resource pool, as determined by the value of its ResourcePoolName property.)
Public Sub SaveOpenProjects_Version4()
Dim objAnyOpenProject As Project
' Freeze the updating on the Project 98 window, i.e. call
' procedure in FIGURE 3.
FreezeScreenUpdating Application.Caption
' Iterate through each open project.
For Each objAnyOpenProject In Application.Projects
' Skip over the running project, i.e. call procedure
' in FIGURE 6.
If objAnyOpenProject <> RunningProject Then
With objAnyOpenProject
' Don't bother saving read-only projects or
' hidden projects.
If Not .ReadOnly And .Windows(1).Visible Then
.Activate
' Before issuing the save as command, anticipate
' the prompt to update its resource pool.
If .ResourcePoolName <> "" Then
' Answer first prompt (shown in FIGURE 7).
SendKeys "%Y"
' Answer second prompt (shown in FIGURE 8).
SendKeys "{ENTER}"
End If
Application.FileSave
End If
End With
End If
Next
' Un-freeze the screen updating on the frozen window,
' i.e. call procedure in FIGURE 3.
UnFreezeScreenUpdating
End SubFIGURE 9: The SendKeys statement can be used to answer the dialog boxes shown in FIGURES 7 and 8 just before the FileSave method is invoked.
ConclusionAs you can see, the SaveOpenProjects procedure has evolved greatly over the course of this article. What began as a simple, delicate procedure is now a robust, general-purpose solution that embodies many different techniques to achieve a great deal of flexibility. These strategies can be applied to almost any type of Project 98 application, and will go a long way toward creating solid, productive tools for your users.
The approaches these procedures employ can be used with other Microsoft Office family members, as well as in any of an ever-growing number of third-party, VBA-enabled products, such as AutoCAD or Visio. One key to authoring professional applications with any VBA development tool is to leverage the feature set of the Win32 operating system. The knowledge of how techniques such as API calls, recursion, and SendKeys can be applied to Project 98 applications will quickly become an asset when developing on any VBA platform.
Download source code for this article here.
Dave Smith is an Associate at Micro Modeling Associates, Inc. in New York City, a leading technology consulting firm and Microsoft Solution Provider Partner. Dave is a Microsoft Certified Solution Developer who specializes in building integrated desktop and intranet applications using VBA, Visual Basic, HTML, and ASP. He can be reached via e-mail at smithd@micromodeling.com. For information concerning services provided by Micro Modeling Associates send e-mail to info@micromodeling.com or visit http://www.micromodeling.com.