Project 2000 is primarily designed for internal project management. The budget features are not equipped to handle many of the needs of contractors. Project managers for contractors or consulting firms will actually want to track two budgets: an actual’ cost budget and the charged cost budget. The need to track two sets of costs Is not unique to contractors. Departments often charge a fixed rate for work done for other departments, but need ro track and manage internal costs as a project progresses.
One solution Is to create two projects with different rates, but this involves double entry as you receive status reports. We’re assuming that you have other, more pressing project management tasks to occupy your time. Our approach takes advantage of a new feature in Project 2000: multiple rate tables.
Project 2000 provides five rate tables for resources. The rate tables appear on the Costs tab of the Resource Information form:
If we place Internal costs in table A and charged costs in Table B, switching the rate table for the all the assignments in a project will switch budgets.
This simple application automates the process of switching between rate tables. For this application, you really need only a command bar with two macros. One sets all the assignments to Rate Table A and the other sets all assignments to Rate Table B.Although you could build this macro application by recording a find and replace action, you would have to record changing to a view with assignments in it. Jf you build this application using Assignment objects, you will not need to alter the view
1. In the Project Explorer, choose the Global .MPTVB project
2. Choose Insert >- Module from the menu.
3. Select the new module in the Project Explorer.
4. In the Properties window, name the module bas Change Cost Table
The first step is to build the subroutine that will actually do the work. For a procedure to run \s a macro, it must be declared as a public subroutine without parameters. Any procedure that meets these requirements will be listed as an available macro, even if it was not created by using the Macro Recorder. You can add the macro to a command bar if you wish. A public subroutine without parameters begins with the familiar snippet yu saw beginning in Here’s an example from earlier
The method for changing the tables is identical, so it makes sense to build the method as a subroutine and pass it the table identifier as a table. This procedure uses a nested loop structure, discussed in to change the Cost Rate Table property of au the assignments in the project.
The Assignments collection is not directly accessible from the Project object; each Task has its own collection. The outer loop iterates (moves) through each Task object in the Project to get that task’s Assignments collection. The inner loop then iterates .through each Assignment object in the collection and changes the Cost Rate Table
At this point, you have a simple yet functional application. Tomake the application user friendly, you’ll need to create a command bar for your application (refer to
Further Suggestions for the Charged Cost Budget Application
As the application stands, there are limitations. Although only one of the two budgets can be base lined, this should be a minor limitation in most cases. The work, duration, and date baseline values will be identical. The actual and total costs will be accurate, with the exception of fixed costs, mentioned as follows, You will be able to calculate variances on only one budget, however,
This simple application does not adjust fixed costs. Fixed costs are not dependent on a rate table. This is a problem if the charged cost for fixed costs is different from the internal value for fixed cost\ A possible solution would be to create two custom Cost fields for tasks. One is for fixed cost to be used in Table A and the other for fixed cost in Table B. You could then add code to the procedure so that when it executes, it copies the current value out of the Fixed Cost field and places the appropriate value into the table.
Creating the Linked Expense Application
Your company has a beautiful Excel workbook template for calculating the fixed. costs of a project. Currently, you enter or copy and paste values from the workbook into the appropriate cell of the project’s Task table. Values in the workbook change occasionally, so importing the workbook’s contents into a project isn’t a one-time solution. It would be nice if you could link the value of a Fixed Cost cell in a Project 2000 file to a ‘specific cell in an Excel workbook. Unlike Word and Excel, however! Project 2000 does not support a field links feature.
Though it is doubtful that the degree of field linking that is offered in Word or Excel can be achieved in a VBA project, the potential benefits of even limited field linking are too good to pass up. Double entry is not only inefficient, it also increase your chances of data-entry errors. This application implements a basic linked field. This application falls far short of plumbing the potential VBA offers for creating linked fields, but it provides a good starting point for understanding linking, using automation outside Project 2000, and creating a neat application, all at the same time.
This application has several requirements. To accomplish the linking task, it’s not enough to simply create a link. The application needs to be able to do the following:
• Select the Excel workbook that contains budget information that is linked to the project.
• Edit and remove the file link.
• Link the specified cell in Excel to the Project Fixed Cost field.
• Remove these links.
• Update the project file when the information in the Excel workbook changes.
• Release Excel
It takes a fair amount of time to open up Excel. To prevent unnecessary opening and closing of Excel, the macros will be designed to see whether the workbook is already open. If not, they will open the workbook. When the macro is done, it will leave the workbook open so other macros can use it .