VBA Speed Issues: 2003 to 2007

Hi,

I've been looking at a Projet Finance Model that we use in house that utilises a VBA macro as part of deciding how much debt we can use in a project that we finance. The model itself is excel heavy and VBA light(ish). The VBA is kept pretty much down to Goal seeks and a few copy/paste operations in order to break a circular relating to interest charged during construction. The VBA does iterate a number of times tho.

My problem is that if I open the file in Excel 2003, running a single case takes the model about 9seconds to run through . If import the same version into 2007, save it as macro enabled 2007, then reopen it (i.e. same version just in 2007) then run the model through it took 26 seconds to finish. (& Yes i feel somewhat ashamed in timing an Excel spreadsheet but forgive me.....)

My key questions is why the difference? Is this an Excel problem or a VBA problem? Can you offer me some commonly known issues with 2007 that make the process slower? Additionally, with the code below is there any way that I could speed it up? Is it necessary to clear out the memory after operations?

The code is as follows

Sub Run_Cases()

For x = Range("upper_case") To Range("lower_case") Step -1 ' steps through the defined cases ending with 1, ie the base case


Range("case") = x

If Range("Fee_Calculation").Value = "On" Then

Range("Development_Fee") = 0
Call Model_Optimisation
Range("Development_Fee") = Range("Fee").Value
End If

Call Model_Optimisation
Range("case" & x).Formula = Range("current").Value

Next x

End Sub

Sub Model_Optimisation()

Application.ScreenUpdating = Range("Screen_update").Value ' refers to excel sheet to see if screen updating is switched off (off is quicker)
Range("proj1_afroz2") = 0 ' resets any variables
Range("proj1_Bfroz2") = 0
Range("Mezzcap1") = 0
Range("Mezz_froz2") = 0

Dim n As Integer
For n = 1 To 5

Range("wind") = Range("bank_wind").Value ' sets assumptions to bank case
Range("tariff") = "Bank"
Range("BANK_DIFF").GoalSeek goal:=0, changingcell:=Range("BANK_EQ")

If Range("Leverage_Switch").Value = "Off" Then
Range("proj1_afroz2") = 0
Range("proj1_Bfroz2") = 0
Range("Mezzcap1") = 0
Range("Mezz_froz2") = 0
End If

If Range("Tranche_A_Switch").Value = "On" Then Proj1_OptA Else Range("proj1_afroz2") = 0
If Range("Tranche_B_Switch").Value = "On" Then Proj1_OptB Else Range("proj1_Bfroz2") = 0
If Range("Mezzanine_Switch").Value = "On" Then
Range("tariff") = Range("Mezz_Tariff").Value
Call Mezzanine_Opt
Else
Range("Mezzcap1") = 0
Range("Mezz_froz2") = 0
End If

Next n

Range("BANK_DIFF").GoalSeek goal:=0, changingcell:=Range("BANK_EQ")
Range("wind") = Range("Equity_wind").Value 'sets assumptions back to equity case
Range("tariff") = "Equity"
Application.ScreenUpdating = True 'turns screen updating back on


End Sub


Sub Proj1_OptA() 'for Tranche A debt sizing

For n = 1 To 3
Range("proj1_Afroz2").Formula = Range("proj1_Afroz1").Value
Range("proj1_A2").GoalSeek goal:=0, changingcell:=Range("proj1_A1")
Range("DSRA_paste").Value = Range("DSRA_copy").Value
Range("proj1_dsrA2").Formula = Range("proj1_dsrA1").Value
Range("levcap2").GoalSeek goal:=0, changingcell:=Range("levcap1")
Next n

End Sub

Sub Proj1_OptB() ' for Tranche B debt sizing

For n = 1 To 10

Range("proj1_Bfroz2").Formula = Range("proj1_Bfroz1").Value
Range("proj1_B2").GoalSeek goal:=0, changingcell:=Range("proj1_B1")
Range("proj1_dsrA2").Formula = Range("proj1_dsrA1").Value

Next n

End Sub

Sub Mezzanine_Opt()

For n = 1 To 10

Range("mezz_froz2").Formula = Range("Mezz_froz1").Value
Range("mezz1_a2").GoalSeek goal:=0, changingcell:=Range("mezz1_a1") ' does not feed into DSRA
Range("Mezzcap2").GoalSeek goal:=0, changingcell:=Range("Mezzcap1")
Next n

End Sub