Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advice on optimizing spreadsheet
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advice on optimizing spreadsheet
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 "schizoid_man" wrote in message ps.com... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advice on optimizing spreadsheet
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. "schizoid_man" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advice on optimizing spreadsheet
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. On Apr 14, 6:00 am, "RB Smissaert" wrote: 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 "schizoid_man" wrote in message ps.com... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advice on optimizing spreadsheet
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lessons from optimizing a slow spreadsheet | Excel Discussion (Misc queries) | |||
Advice on Creating A Spreadsheet - HELP! | Excel Discussion (Misc queries) | |||
Multiple copying of a Spreadsheet advice please | Excel Discussion (Misc queries) | |||
Moving data from form to spreadsheet + VBA advice | Excel Programming | |||
Legal advice on selling Excel spreadsheet program file | Excel Programming |