After getting a lot of help from my company’s MS Project product manager, I customized the following VBA macro. He also recommended that I pick up a copy of Rod Gill’s "VBA Programming for Microsoft Office Project " which is now on my Christmas list.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub Calc_Number2()
Dim intCounter As Integer
Dim intArraySize As Integer
intArraySize = 2 ‘intArraySize is the number of elements in the arrays
‘ Arrays strTest and intMult must be = intArraySize
Dim strTest(2) As Variant
Dim intValue(2) As Integer
Dim strTaskName As Variant
‘ This is where you define the test (strTest) condition and values(intValue)
‘ Note: if you have two test conditions that are true the last test condition
‘ will be the one that modifies Number2
‘ Tsk is an object that contains all task fields for a given task. The For – Next
‘ statement loops through every Project task and performs the test and calculations
For Each Tsk In ActiveProject.Tasks
intCounter = 1
If Not Tsk Is Nothing Then ‘Do not process blank tasks. Keeps macro from blowing up.
Do Until intCounter > intArraySize
If Tsk.Name Like strTest(intCounter) Then
Tsk.Number2 = (Tsk.Number1 * intValue(intCounter)) / 60
End If
intCounter = intCounter + 1
Loop
End If
Next Tsk
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
A big thank you to Joe Peltier for the above code!
