Use for formulas in VBA   

Hello,

    I want to display remaining budget in a project for a task. I start with 
a fixed amount of budget with first task with zero expense. Then I enter 
expenses for each task. The question is to know the remaining amount for 
each task.

    I created three columns: Budget, Expenses and Remaining Budget. The 
example below shows values for these fields for the first three tasks.

    For task 1: Budget=250,000.00    Expense=0.0               Remaining 
Budget = 250,000.00
    For task 2: Budget=250,000.00    Expense=3,055.00      Remaining Budget 
= 246,945.00
    For task 3: Budget=246.945.00    Expense=40,000.00    Remaining Budget = 
206,945.00
....

    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 Term Sheet         "$250,000.00"        "0.00" 
"250,000.00"

2    Drafting CAD                "$250,000.00"        "3,055.00" 
"$246,945.00"

3    Pay President's salary    "$246,945.00"        "40,000.00" 
"$206,945.00"

    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 am using Project 2000.

    Thank you for any help.

Athena

date: Wed, 20 Aug 2008 19:49:53 -0400   author:   Athena

RE: Use for formulas in VBA   

Hi Athena,

Would a formula in a calculated field work in this application? If you
rename Text1 "Remaining Budget" and put the following in the formula:
IIf(([Summary]),"$" & ([Cost2]-[Actual Cost]),"")
Select "Use Formula" for the "Calculation for task and group summary
rows" options.
This assumes you've used "Cost2" as your budget cost.
This works on my Project 2003 Standard

cheers
"Athena" wrote:

> Hello,
> 
>     I want to display remaining budget in a project for a task. I start with 
> a fixed amount of budget with first task with zero expense. Then I enter 
> expenses for each task. The question is to know the remaining amount for 
> each task.
> 
>     I created three columns: Budget, Expenses and Remaining Budget. The 
> example below shows values for these fields for the first three tasks.
> 
>     For task 1: Budget=250,000.00    Expense=0.0               Remaining 
> Budget = 250,000.00
>     For task 2: Budget=250,000.00    Expense=3,055.00      Remaining Budget 
> = 246,945.00
>     For task 3: Budget=246.945.00    Expense=40,000.00    Remaining Budget = 
> 206,945.00
> .....
> 
>     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 Term Sheet         "$250,000.00"        "0.00" 
> "250,000.00"
> 
> 2    Drafting CAD                "$250,000.00"        "3,055.00" 
> "$246,945.00"
> 
> 3    Pay President's salary    "$246,945.00"        "40,000.00" 
> "$206,945.00"
> 
>     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 am using Project 2000.
> 
>     Thank you for any help.
> 
> Athena 
> 
>

date: Mon, 1 Sep 2008 00:50:00 -0700   author:   Marcus Wolfe