A
Athena
Hello,
I want to display remaining budget amount for a task. I start with
a fixed budget amount for the first task with zero expense. Then I enter
expenses for following tasks. The aim is to calculate the remaining amount
for
each task automatically as one enters the expenses.
I created three columns: Budget, Expenses and Remaining Budget. The
example below shows values for these fields for the first three tasks.
Task 1: Budget=250,000 Expense=0.0 Remaining Budget = 250,000
Task 2: Budget=250,000 Expense=3,055 Remaining Budget = 246,945
Task 3: Budget=246.945 Expense=40,000 Remaining Budget = 206,945
.....
i.e. (new) Remaining Budget = Budget - sum of expenses so far. So the
formula should be if it were in Excel (Using the letter column headers) : G1
= E1, G2 = E2 - F1, G3 = E3 - F2, ....
Here is the table (Please view the email in html format to be able to
see the table)
A E F G
Task Name Budget Expense Remaining Budget
1 Close TS "$250,000" "$0.00" "$250,000"
2 Drafting "$250,000" "$3,055" "$246,945"
3 Pay Salary "$246,945" "$40,000" "$206,945"
I believe the only way to do this is to use VBA. Although I know VB6
very well I am not familiar with VBA for Project.
I wrote the code below and run it in form click event. It is working.
Now I have two questions:
1. I want to calculate the remaining budget for tasks only not for sub
tasks. How can I do this programmatically in VBA?
2. I want to have this feature (macro/program) to start when I load this
Project automatically rather than running it manually. How can I do that?
Thank you for any help.
Athena
Code:
--------------------------------------------------
Dim ts As Tasks
SelectAll
Set ts = ActiveSelection.Tasks
Dim t As Task
Dim budget As Single
Dim expense As Single
Dim remainingbudget As Single
i = 0
For Each t In ts
i = i + 1
If i = 3 Then budget = t.Cost1
If i > 2 Then
expense = t.Cost2
t.Cost1 = FormatCurrency(budget, 2)
budget = budget - expense
remainingbudget = budget
t.Text2 = FormatCurrency(remainingbudget, 2)
End If
Next t
I want to display remaining budget amount for a task. I start with
a fixed budget amount for the first task with zero expense. Then I enter
expenses for following tasks. The aim is to calculate the remaining amount
for
each task automatically as one enters the expenses.
I created three columns: Budget, Expenses and Remaining Budget. The
example below shows values for these fields for the first three tasks.
Task 1: Budget=250,000 Expense=0.0 Remaining Budget = 250,000
Task 2: Budget=250,000 Expense=3,055 Remaining Budget = 246,945
Task 3: Budget=246.945 Expense=40,000 Remaining Budget = 206,945
.....
i.e. (new) Remaining Budget = Budget - sum of expenses so far. So the
formula should be if it were in Excel (Using the letter column headers) : G1
= E1, G2 = E2 - F1, G3 = E3 - F2, ....
Here is the table (Please view the email in html format to be able to
see the table)
A E F G
Task Name Budget Expense Remaining Budget
1 Close TS "$250,000" "$0.00" "$250,000"
2 Drafting "$250,000" "$3,055" "$246,945"
3 Pay Salary "$246,945" "$40,000" "$206,945"
I believe the only way to do this is to use VBA. Although I know VB6
very well I am not familiar with VBA for Project.
I wrote the code below and run it in form click event. It is working.
Now I have two questions:
1. I want to calculate the remaining budget for tasks only not for sub
tasks. How can I do this programmatically in VBA?
2. I want to have this feature (macro/program) to start when I load this
Project automatically rather than running it manually. How can I do that?
Thank you for any help.
Athena
Code:
--------------------------------------------------
Dim ts As Tasks
SelectAll
Set ts = ActiveSelection.Tasks
Dim t As Task
Dim budget As Single
Dim expense As Single
Dim remainingbudget As Single
i = 0
For Each t In ts
i = i + 1
If i = 3 Then budget = t.Cost1
If i > 2 Then
expense = t.Cost2
t.Cost1 = FormatCurrency(budget, 2)
budget = budget - expense
remainingbudget = budget
t.Text2 = FormatCurrency(remainingbudget, 2)
End If
Next t