Advice on optimizing spreadsheet

[schizoid_man]

Hi,

I have a spreadsheet that has multiple rows (>10000) with several
compound If statements throughout one sheet of my workbook. An example
of such a statement is:
IF(C2<>"",IF(C2<=$M$14,0,IF(C2>=$M$15,$M$17,C2-$M$14)),"")

My question is: would it be faster for me to remove all the If
statements from this sheet and simply write a VBA subroutine that
would do the same thing?

I'm certain that it would make the spreadsheet more compact, but would
it improve performance?

I'm writing a Monte-Carlo simulation so performance is paramount for
me.

Thanks.

 

[RB Smissaert]

I think in this particular case there won't be much difference in speed, but
doing this is VBA
is a lot neater, will make the workbook a lot smaller and will make it
easier to optimize for speed.
Only some testing will tell you what the speeds will be:

Option Explicit
Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Private lStartTime As Long

Sub StartSW()
lStartTime = timeGetTime()
End Sub

Sub StopSW(Optional ByRef strMessage As Variant = "")
MsgBox "Done in " & timeGetTime() - lStartTime & " msecs", , strMessage
End Sub

Sub test()

'Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic

StartSW

Cells(14, 13).Value = 0

Application.Calculate

StopSW

End Sub

Sub test2()

Dim dVal1 As Double
Dim dVal2 As Double
Dim dVal3 As Double
Dim arr
Dim i As Long

Cells(14, 13).Value = 2

dVal1 = Cells(14, 13).Value
dVal1 = Cells(15, 13).Value
dVal1 = Cells(17, 13).Value

arr = Range(Cells(1), Cells(10000, 3))

StartSW

For i = 1 To 10000
If arr(i, 1) <> "" Then
If arr(i, 1) <= dVal1 Then
arr(i, 1) = 0
Else
If arr(i, 1) >= dVal2 Then
arr(i, 1) = dVal3
Else
arr(i, 1) = arr(i, 1) - dVal1
End If
End If
End If
Next i

StopSW

End Sub


RBS

 

[Guest]

A couple of things...

I should start by saying that I don't know what will be faster -- I suspect
it will depend on the project.

If I were doing a large Monte Carlo simulation and I thought that I might
have to use it again and again I would probably put it VBA just so that it
would easier to maintain and adjust. By "adjust" I mean that it might be
easier making a few coding changes than changing all of your formulas.

There are tools out there that do simulations in Excel. One that I used
about 10 years ago is @Risk http://www.palisade.com/risk/default.asp.
If you're working on a personal project, then @Risk may too expensive --
$700 or so. I have seen what appear to be free add ins that do Monte Carlo
simulation. I haven't used them so I can't recommend them from personal
experience. You could check http://home.uchicago.edu/~rmyerson/addins.htm.

 

[Carl Hartness]

If you run speed measurements, please post the results. In my
experience, user defined functions (UDFs) are always slower than
builtin functions. 10,000 instances will be a good test.

Carl.

 

[Schizoid Man]

Thanks for the code, RB.

I did implement it in two versions of my model - one in which I had
explicitly copied the If statements down 10000 rows, and the other in
which I was writing the columns in the VBA subroutine itself.

The first spreadsheet was about 3.5 MB in size, and the second was about
2.5 MB. The contents include two graphs, one work sheet with about 20
rows and the main worksheet with 3 columns of 10000 rows where each row
represents the results for 1 simulation.

The results of the timing were as follows:
Explicit IF statements in the worksheet - 30094 milliseconds
Range statements in the VBA subroutine - 31047 milliseconds

When I repeated the process for 2000 simulations:
Explicit IF statements in the worksheet - 6344 milliseconds
Range statements in the VBA subroutine - 6797 milliseconds

The difference in the speed is almost negligible, since I am writing
only one column in the first case (the pre-defined formulas calculate
the other two), whereas in the second I am writing all three columns.