| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed up calcs within macro....
This started off over in microsoft.public.excel:
http://groups.google.com/group/micro...a5ee1032d2925c Basically I was using a really inefficient macro to run some calcs and paste in values to restrict file size. Luckily, Otto kindly managed to sort out some more efficient code, but as there are still 172,000 calcs, so obviously they take some time (on the machine I need tom to work well on, they take about 9mins). Here's the latest code I'm using: Sub driver_calc() Application.ScreenUpdating = False Dim myLC As Long Dim myLR As Long Dim myLRr As Long Dim myLCr As Long Dim ws As Worksheet Dim TheFormula As String Const Formula1 = "=if(sumproduct(--('Course List by division'!$c $6:$C$607=E$4),'Course List by division'!$i$6:$i$607)=0,0,sumproduct(-- ('Course List by division'!$E$6:$e$607=$b6),--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$i$6:$i$607)/ sumproduct(--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$i$6:$i$607))" Const Formula2 = "=if(sumproduct(--('Course List by division'!$c $6:$C$607=E$4),'Course List by division'!$j$6:$j$607)=0,0,sumproduct(-- ('Course List by division'!$E$6:$e$607=$b6),--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$j$6:$j$607)/ sumproduct(--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$j$6:$j$607))" Const Formula3 = "=if(sumproduct(--('Course List by division'!$c $6:$C$607=E$4),'Course List by division'!$k$6:$k$607)=0,0,sumproduct(-- ('Course List by division'!$E$6:$e$607=$b6),--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$k$6:$k$607)/ sumproduct(--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$k$6:$k$607))" For Each ws In Sheets(Array("Driver 1 - STUDENTS", "Driver 2 - TIME", "Driver 3 - STUDENTSxTIME")) With ws Application.Calculation = xlCalculationManual myLC = .Range("IV4").End(xlToLeft).Column myLR = .Cells(Rows.Count, "C").End(xlUp).Row myLCr = .Range("IV6").End(xlToLeft).Column myLRr = .Cells(Rows.Count, "E").End(xlUp).Row .Range("E6", .Cells(myLR, myLC)).ClearContents Select Case ws.Name Case "Driver 1 - STUDENTS": TheFormula = Formula1 Case "Driver 2 - TIME": TheFormula = Formula2 Case "Driver 3 - STUDENTSxTIME": TheFormula = Formula3 End Select .Range("E6").Formula = TheFormula .Range("E6").Copy .Range("E6", .Cells(myLR, myLC)) Application.Calculation = xlCalculationAutomatic .Range("E6", .Cells(myLR, myLC)).Copy .Range("E6").PasteSpecial Paste:=xlPasteValues .Application.CutCopyMode = False End With Next ws MsgBox "Drivers succesfully updated", vbInformation, "Lxxxxxxxxxxxxx" End Sub I was wandering if there was a better way of using the formulae rather than getting Excel to do them "on the fly". FYI, I'm using Excel 2003 on XP, but the client I'm doing this for uses Excel 2007 on XP, so getting superfast on 03 isn't essential. If anyone can offer any advice.guidance I'd massively appreciate it! Cheers Matt |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Speed up calcs within macro....
just a thought, not sure what it would do because i don't know all of the
implications, but maybe just calculating the relevant cells instead of global recalc. then turn calculation back on at the end, instead of the end of every loop. -- Gary Keramidas Excel 2003 "Matt Knight" wrote in message ... This started off over in microsoft.public.excel: http://groups.google.com/group/micro...a5ee1032d2925c Basically I was using a really inefficient macro to run some calcs and paste in values to restrict file size. Luckily, Otto kindly managed to sort out some more efficient code, but as there are still 172,000 calcs, so obviously they take some time (on the machine I need tom to work well on, they take about 9mins). Here's the latest code I'm using: Sub driver_calc() Application.ScreenUpdating = False Dim myLC As Long Dim myLR As Long Dim myLRr As Long Dim myLCr As Long Dim ws As Worksheet Dim TheFormula As String Const Formula1 = "=if(sumproduct(--('Course List by division'!$c $6:$C$607=E$4),'Course List by division'!$i$6:$i$607)=0,0,sumproduct(-- ('Course List by division'!$E$6:$e$607=$b6),--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$i$6:$i$607)/ sumproduct(--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$i$6:$i$607))" Const Formula2 = "=if(sumproduct(--('Course List by division'!$c $6:$C$607=E$4),'Course List by division'!$j$6:$j$607)=0,0,sumproduct(-- ('Course List by division'!$E$6:$e$607=$b6),--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$j$6:$j$607)/ sumproduct(--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$j$6:$j$607))" Const Formula3 = "=if(sumproduct(--('Course List by division'!$c $6:$C$607=E$4),'Course List by division'!$k$6:$k$607)=0,0,sumproduct(-- ('Course List by division'!$E$6:$e$607=$b6),--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$k$6:$k$607)/ sumproduct(--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$k$6:$k$607))" For Each ws In Sheets(Array("Driver 1 - STUDENTS", "Driver 2 - TIME", "Driver 3 - STUDENTSxTIME")) With ws Application.Calculation = xlCalculationManual myLC = .Range("IV4").End(xlToLeft).Column myLR = .Cells(Rows.Count, "C").End(xlUp).Row myLCr = .Range("IV6").End(xlToLeft).Column myLRr = .Cells(Rows.Count, "E").End(xlUp).Row .Range("E6", .Cells(myLR, myLC)).ClearContents Select Case ws.Name Case "Driver 1 - STUDENTS": TheFormula = Formula1 Case "Driver 2 - TIME": TheFormula = Formula2 Case "Driver 3 - STUDENTSxTIME": TheFormula = Formula3 End Select .Range("E6").Formula = TheFormula .Range("E6").Copy .Range("E6", .Cells(myLR, myLC)) Application.Calculation = xlCalculationAutomatic .Range("E6", .Cells(myLR, myLC)).Copy .Range("E6").PasteSpecial Paste:=xlPasteValues .Application.CutCopyMode = False End With Next ws MsgBox "Drivers succesfully updated", vbInformation, "Lxxxxxxxxxxxxx" End Sub I was wandering if there was a better way of using the formulae rather than getting Excel to do them "on the fly". FYI, I'm using Excel 2003 on XP, but the client I'm doing this for uses Excel 2007 on XP, so getting superfast on 03 isn't essential. If anyone can offer any advice.guidance I'd massively appreciate it! Cheers Matt |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Speed up calcs within macro....
Hi Gary,
Thanks for the response - I'm not sure that doing what you've suggested would have a massive impact, unless I've misunderstood what you mean. Seeing as I have to calculate 172000 fairly complex formulas, would it make much of a difference as to when the calcs are done? Matt On Jan 7, 9:29*pm, "Gary Keramidas" <GKeramidasAtMSN.com wrote: just a thought, not sure what it would do because i don't know all of the implications, but maybe just calculating the relevant cells instead of *global recalc. then turn calculation back on at the end, instead of the end of every loop. -- Gary Keramidas Excel 2003 "Matt Knight" wrote in message ... This started off over in microsoft.public.excel: http://groups.google.com/group/micro...rowse_thread/t... Basically I was using a really inefficient macro to run some calcs and paste in values to restrict file size. *Luckily, Otto kindly managed to sort out some more efficient code, but as there are still 172,000 calcs, so obviously they take some time (on the machine I need tom to work well on, they take about 9mins). *Here's the latest code I'm using: Sub driver_calc() * *Application.ScreenUpdating = False * *Dim myLC As Long * *Dim myLR As Long * *Dim myLRr As Long * *Dim myLCr As Long * *Dim ws As Worksheet * *Dim TheFormula As String * *Const Formula1 = "=if(sumproduct(--('Course List by division'!$c $6:$C$607=E$4),'Course List by division'!$i$6:$i$607)=0,0,sumproduct(-- ('Course List by division'!$E$6:$e$607=$b6),--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$i$6:$i$607)/ sumproduct(--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$i$6:$i$607))" * *Const Formula2 = "=if(sumproduct(--('Course List by division'!$c $6:$C$607=E$4),'Course List by division'!$j$6:$j$607)=0,0,sumproduct(-- ('Course List by division'!$E$6:$e$607=$b6),--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$j$6:$j$607)/ sumproduct(--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$j$6:$j$607))" * *Const Formula3 = "=if(sumproduct(--('Course List by division'!$c $6:$C$607=E$4),'Course List by division'!$k$6:$k$607)=0,0,sumproduct(-- ('Course List by division'!$E$6:$e$607=$b6),--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$k$6:$k$607)/ sumproduct(--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$k$6:$k$607))" * *For Each ws In Sheets(Array("Driver 1 - STUDENTS", "Driver 2 - TIME", "Driver 3 - STUDENTSxTIME")) * * * *With ws * * * * * *Application.Calculation = xlCalculationManual * * * * * *myLC = .Range("IV4").End(xlToLeft).Column * * * * * *myLR = .Cells(Rows.Count, "C").End(xlUp).Row * * * * * *myLCr = .Range("IV6").End(xlToLeft).Column * * * * * *myLRr = .Cells(Rows.Count, "E").End(xlUp).Row * * * * * *.Range("E6", .Cells(myLR, myLC)).ClearContents * * * * * *Select Case ws.Name * * * * * * * *Case "Driver 1 - STUDENTS": TheFormula = Formula1 * * * * * * * *Case "Driver 2 - TIME": TheFormula = Formula2 * * * * * * * *Case "Driver 3 - STUDENTSxTIME": TheFormula = Formula3 * * * * * *End Select * * * * * *.Range("E6").Formula = TheFormula * * * * * *.Range("E6").Copy .Range("E6", .Cells(myLR, myLC)) * * * * * *Application.Calculation = xlCalculationAutomatic * * * * * *.Range("E6", .Cells(myLR, myLC)).Copy * * * * * *.Range("E6").PasteSpecial Paste:=xlPasteValues * * * * * *.Application.CutCopyMode = False * * * *End With * *Next ws * *MsgBox "Drivers succesfully updated", vbInformation, "Lxxxxxxxxxxxxx" End Sub I was wandering if there was a better way of using the formulae rather than getting Excel to do them "on the fly". *FYI, I'm using Excel 2003 on XP, but the client I'm doing this for uses Excel 2007 on XP, so getting superfast on 03 isn't essential. If anyone can offer any advice.guidance I'd massively appreciate it! Cheers Matt |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Speed up calcs within macro....
Matt Knight wrote:
This started off over in microsoft.public.excel: http://groups.google.com/group/micro...a5ee1032d2925c Basically I was using a really inefficient macro to run some calcs and paste in values to restrict file size. Luckily, Otto kindly managed to sort out some more efficient code, but as there are still 172,000 calcs, so obviously they take some time (on the machine I need tom to work well on, they take about 9mins). Here's the latest code I'm using: You might get some mileage by factoring out common terms and working them out using VBA Application.WorksheetFunction.SumProduct() It looks like you are enabling recalculation inside the inner loop which will definitely hurt performance. Different ways of indexing ranges have noticeably different overheads so it is worth experimenting. The form you have chosen may be one of the slower variants. Again it might be worth trying working on an explicit memory array copy and copying the final numbers back to the spreadsheet. BTW your long constant formulae are a maintainence trap with three nearly identical versions differing in tiny ways. You should simplify them to one common expression with the differences made explicit. Regards, Martin Brown Sub driver_calc() Application.ScreenUpdating = False Dim myLC As Long Dim myLR As Long Dim myLRr As Long Dim myLCr As Long Dim ws As Worksheet Dim TheFormula As String Const Formula1 = "=if(sumproduct(--('Course List by division'!$c $6:$C$607=E$4),'Course List by division'!$i$6:$i$607)=0,0,sumproduct(-- ('Course List by division'!$E$6:$e$607=$b6),--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$i$6:$i$607)/ sumproduct(--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$i$6:$i$607))" Const Formula2 = "=if(sumproduct(--('Course List by division'!$c $6:$C$607=E$4),'Course List by division'!$j$6:$j$607)=0,0,sumproduct(-- ('Course List by division'!$E$6:$e$607=$b6),--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$j$6:$j$607)/ sumproduct(--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$j$6:$j$607))" Const Formula3 = "=if(sumproduct(--('Course List by division'!$c $6:$C$607=E$4),'Course List by division'!$k$6:$k$607)=0,0,sumproduct(-- ('Course List by division'!$E$6:$e$607=$b6),--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$k$6:$k$607)/ sumproduct(--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$k$6:$k$607))" For Each ws In Sheets(Array("Driver 1 - STUDENTS", "Driver 2 - TIME", "Driver 3 - STUDENTSxTIME")) With ws Application.Calculation = xlCalculationManual myLC = .Range("IV4").End(xlToLeft).Column myLR = .Cells(Rows.Count, "C").End(xlUp).Row myLCr = .Range("IV6").End(xlToLeft).Column myLRr = .Cells(Rows.Count, "E").End(xlUp).Row .Range("E6", .Cells(myLR, myLC)).ClearContents Select Case ws.Name Case "Driver 1 - STUDENTS": TheFormula = Formula1 Case "Driver 2 - TIME": TheFormula = Formula2 Case "Driver 3 - STUDENTSxTIME": TheFormula = Formula3 End Select .Range("E6").Formula = TheFormula .Range("E6").Copy .Range("E6", .Cells(myLR, myLC)) Application.Calculation = xlCalculationAutomatic .Range("E6", .Cells(myLR, myLC)).Copy .Range("E6").PasteSpecial Paste:=xlPasteValues .Application.CutCopyMode = False End With Next ws MsgBox "Drivers succesfully updated", vbInformation, "Lxxxxxxxxxxxxx" End Sub I was wandering if there was a better way of using the formulae rather than getting Excel to do them "on the fly". FYI, I'm using Excel 2003 on XP, but the client I'm doing this for uses Excel 2007 on XP, so getting superfast on 03 isn't essential. If anyone can offer any advice.guidance I'd massively appreciate it! Cheers Matt |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Speed up calcs within macro....
Hi Martin, thanks a lot for your reply.
As I'm fairly new to the whole VBA game, I don't think I could say that I was proficient to add things to people's code (rather I can copy it and make it work on whatever I'm working on!). Any quick hints and I reckon I should be okay to get experimenting, but the last thing I want to do is to start messing up the code! Matt On Jan 8, 12:18*pm, Martin Brown wrote: Matt Knight wrote: This started off over in microsoft.public.excel: http://groups.google.com/group/micro...rowse_thread/t... Basically I was using a really inefficient macro to run some calcs and paste in values to restrict file size. *Luckily, Otto kindly managed to sort out some more efficient code, but as there are still 172,000 calcs, so obviously they take some time (on the machine I need tom to work well on, they take about 9mins). *Here's the latest code I'm using: You might get some mileage by factoring out common terms and working them out using VBA Application.WorksheetFunction.SumProduct() It looks like you are enabling recalculation inside the inner loop which will definitely hurt performance. Different ways of indexing ranges have noticeably different overheads so it is worth experimenting. The form you have chosen may be one of the slower variants. Again it might be worth trying working on an explicit memory array copy and copying the final numbers back to the spreadsheet. BTW your long constant formulae are a maintainence trap with three nearly identical versions differing in tiny ways. You should simplify them to one common expression with the differences made explicit. Regards, Martin Brown Sub driver_calc() * * Application.ScreenUpdating = False * * Dim myLC As Long * * Dim myLR As Long * * Dim myLRr As Long * * Dim myLCr As Long * * Dim ws As Worksheet * * Dim TheFormula As String * * Const Formula1 = "=if(sumproduct(--('Course List by division'!$c $6:$C$607=E$4),'Course List by division'!$i$6:$i$607)=0,0,sumproduct(-- ('Course List by division'!$E$6:$e$607=$b6),--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$i$6:$i$607)/ sumproduct(--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$i$6:$i$607))" * * Const Formula2 = "=if(sumproduct(--('Course List by division'!$c $6:$C$607=E$4),'Course List by division'!$j$6:$j$607)=0,0,sumproduct(-- ('Course List by division'!$E$6:$e$607=$b6),--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$j$6:$j$607)/ sumproduct(--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$j$6:$j$607))" * * Const Formula3 = "=if(sumproduct(--('Course List by division'!$c $6:$C$607=E$4),'Course List by division'!$k$6:$k$607)=0,0,sumproduct(-- ('Course List by division'!$E$6:$e$607=$b6),--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$k$6:$k$607)/ sumproduct(--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$k$6:$k$607))" * * For Each ws In Sheets(Array("Driver 1 - STUDENTS", "Driver 2 - TIME", "Driver 3 - STUDENTSxTIME")) * * * * With ws * * * * * * Application.Calculation = xlCalculationManual * * * * * * myLC = .Range("IV4").End(xlToLeft).Column * * * * * * myLR = .Cells(Rows.Count, "C").End(xlUp).Row * * * * * * myLCr = .Range("IV6").End(xlToLeft).Column * * * * * * myLRr = .Cells(Rows.Count, "E").End(xlUp).Row * * * * * * .Range("E6", .Cells(myLR, myLC)).ClearContents * * * * * * Select Case ws.Name * * * * * * * * Case "Driver 1 - STUDENTS": TheFormula = Formula1 * * * * * * * * Case "Driver 2 - TIME": TheFormula = Formula2 * * * * * * * * Case "Driver 3 - STUDENTSxTIME": TheFormula = Formula3 * * * * * * End Select * * * * * * .Range("E6").Formula = TheFormula * * * * * * .Range("E6").Copy .Range("E6", .Cells(myLR, myLC)) * * * * * * Application.Calculation = xlCalculationAutomatic * * * * * * .Range("E6", .Cells(myLR, myLC)).Copy * * * * * * .Range("E6").PasteSpecial Paste:=xlPasteValues * * * * * * .Application.CutCopyMode = False * * * * End With * * Next ws * * MsgBox "Drivers succesfully updated", vbInformation, "Lxxxxxxxxxxxxx" End Sub I was wandering if there was a better way of using the formulae rather than getting Excel to do them "on the fly". *FYI, I'm using Excel 2003 on XP, but the client I'm doing this for uses Excel 2007 on XP, so getting superfast on 03 isn't essential. If anyone can offer any advice.guidance I'd massively appreciate it! Cheers Matt |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Speed up calcs within macro....
Sumproduct formulas are known to slow down a workbook considerable. Putting ther formula into VBA the way you did doesn't change the speed. what you can do is replace the formula with a value. You can use the evaluate method in ?VBA like this Formula1 = "if(sumproduct(--('Course List by division'!$c $6:$C$607=E$4),'Course List by division'!$i$6:$i$607)=0,0,sumproduct(-- ('Course List by division'!$E$6:$e$607=$b6),--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$i$6:$i$607)/ sumproduct(--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$i$6:$i$607))" Results = Evaluate(formula1) The evaluate still takes the same amount of time to execute as putting the formula in the worksheet but the calculation is only performed once, not every time the workbook is updated. All I did was to remove the equal sign from you formula and added parenthesis around you code. you had Formula1 = "=........." I replaced it with Formula1 = "........." Results = evaluate(Formula1) to make you formula more versitile what I do is add ranges like this with sheets("Course List by division'") Set MyRange1 = .Range("$C6:$C$607") MyRangeAddr1 = MyRange1.address(external:=true) Set MyRange2 = .Range("$I6:$I$607") MyRangeAddr2 = MyRange2.address(external:=true) Set MyRange3 = .Range("$E6:$E$607") MyRangeAddr3 = MyRange3.address(external:=true) Set MyRange4 = .Range("$J6:$J$607") MyRangeAddr4 = MyRange3.address(external:=true) Formula1 = "if(sumproduct(--(" & MyRangeAddr4 & "=E$4)," & _ "--(" & MyRangeAddr2 & "=0,0," & _ "sumproduct(--(" & MyRangeAddr3 & "=$b6)," & _ "--(" & MyrangeAddr1 & "=E$4)," & _ MyRangeAddr2 & ")/" & _ "sumproduct(--(" & MyRangeAddr1 & "=E$4)," & _ MyRangeAddr2 & "))" I didn't test my changes but they should be close. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=167924 Microsoft Office Help |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Speed up calcs within macro....
Thanks Joel - really appreciate it. I'll have a play around now and
see what happens! Matt On Jan 8, 2:09*pm, joel wrote: Sumproduct formulas are known to slow down a workbook considerable. Putting ther formula into VBA the way you did doesn't change the speed. what you can do is replace the formula with a value. *You can use the evaluate method in ?VBA like this Formula1 = "if(sumproduct(--('Course List by division'!$c $6:$C$607=E$4),'Course List by division'!$i$6:$i$607)=0,0,sumproduct(-- ('Course List by division'!$E$6:$e$607=$b6),--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$i$6:$i$607)/ sumproduct(--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$i$6:$i$607))" Results = Evaluate(formula1) The evaluate still takes the same amount of time to execute as putting the formula in the worksheet but the calculation is only performed once, not every time the workbook is updated. All I did was to remove the equal sign from you formula and added parenthesis around you code. you had Formula1 = "=........." I replaced it with Formula1 = "........." Results = evaluate(Formula1) to make you formula more versitile what I do is add ranges like this with sheets("Course List by division'") Set MyRange1 = .Range("$C6:$C$607") MyRangeAddr1 = MyRange1.address(external:=true) Set MyRange2 = .Range("$I6:$I$607") MyRangeAddr2 = MyRange2.address(external:=true) Set MyRange3 = .Range("$E6:$E$607") MyRangeAddr3 = MyRange3.address(external:=true) Set MyRange4 = .Range("$J6:$J$607") MyRangeAddr4 = MyRange3.address(external:=true) Formula1 = "if(sumproduct(--(" & MyRangeAddr4 & "=E$4)," & _ "--(" & MyRangeAddr2 & "=0,0," & _ "sumproduct(--(" & MyRangeAddr3 & "=$b6)," & _ "--(" & MyrangeAddr1 & "=E$4)," & _ MyRangeAddr2 & ")/" & _ "sumproduct(--(" & MyRangeAddr1 & "=E$4)," & _ MyRangeAddr2 & "))" I didn't test my changes but they should be close. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=167924 Microsoft Office Help |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Speed up calcs within macro....
My apologies for the radio silence on this - been busy on other jobs
with limited internet connectivity. But rest assured myself and a colleague have been scratching our heads over this for the last week on and off. We've essentially merged a few of the methods that have been suggested and are just trying to iron out some small problems. Hopefully we'll have cracked it by the end of today. EIther way I'll keep you posted and post the final solution for anyone else to steal. Matt On Jan 8, 2:39*pm, Matt Knight wrote: Thanks Joel - really appreciate it. *I'll have a play around now and see what happens! Matt On Jan 8, 2:09*pm, joel wrote: Sumproduct formulas are known to slow down a workbook considerable. Putting ther formula into VBA the way you did doesn't change the speed. what you can do is replace the formula with a value. *You can use the evaluate method in ?VBA like this Formula1 = "if(sumproduct(--('Course List by division'!$c $6:$C$607=E$4),'Course List by division'!$i$6:$i$607)=0,0,sumproduct(-- ('Course List by division'!$E$6:$e$607=$b6),--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$i$6:$i$607)/ sumproduct(--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$i$6:$i$607))" Results = Evaluate(formula1) The evaluate still takes the same amount of time to execute as putting the formula in the worksheet but the calculation is only performed once, not every time the workbook is updated. All I did was to remove the equal sign from you formula and added parenthesis around you code. you had Formula1 = "=........." I replaced it with Formula1 = "........." Results = evaluate(Formula1) to make you formula more versitile what I do is add ranges like this with sheets("Course List by division'") Set MyRange1 = .Range("$C6:$C$607") MyRangeAddr1 = MyRange1.address(external:=true) Set MyRange2 = .Range("$I6:$I$607") MyRangeAddr2 = MyRange2.address(external:=true) Set MyRange3 = .Range("$E6:$E$607") MyRangeAddr3 = MyRange3.address(external:=true) Set MyRange4 = .Range("$J6:$J$607") MyRangeAddr4 = MyRange3.address(external:=true) Formula1 = "if(sumproduct(--(" & MyRangeAddr4 & "=E$4)," & _ "--(" & MyRangeAddr2 & "=0,0," & _ "sumproduct(--(" & MyRangeAddr3 & "=$b6)," & _ "--(" & MyrangeAddr1 & "=E$4)," & _ MyRangeAddr2 & ")/" & _ "sumproduct(--(" & MyRangeAddr1 & "=E$4)," & _ MyRangeAddr2 & "))" I didn't test my changes but they should be close. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=167924 Microsoft Office Help- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Speed up calcs within macro....
Right, this is the first part of the code we've been trying to get to
work: Sub driver_calc() Application.ScreenUpdating = False Dim myLC As Long Dim myLR As Long Dim myLRr As Long Dim myLCr As Long Sheets("Driver 1 - STUDENTS").Select With Sheets("Driver 1 - STUDENTS") Application.Calculation = xlCalculationManual myLC = .Range("IV4").End(xlToLeft).Column myLR = .Cells(Rows.Count, "B").End(xlUp).Row myLCr = .Range("IV5").End(xlToLeft).Column myLRr = .Cells(Rows.Count, "C").End(xlUp).Row .Range("e6", .Cells(myLRr, myLCr)).ClearContents Set MyRange1 = .Range("B5:B" & myLR) MyRangeAddr1 = MyRange1.Address(external:=True) Set MyRange2 = .Range("C4", .Cells(4, myLC)) MyRangeAddr2 = MyRange2.Address(external:=True) End With Formula1 = "if(sumproduct(--('Course List by division'!$c$6:$C $607=" & MyRangeAddr2 & "),'Course List by division'!$i$6:$i$607) =0,0,sumproduct(--('Course List by division'!$E$6:$e$607=" & MyRangeAddr1 & "),--('Course List by division'!$c$6:$c$607=" & MyRangeAddr2 & "),'Course List by division'!$i$6:$i$607)/sumproduct(-- ('Course List by division'!$c$6:$c$607=" & MyRangeAddr1 & "),'Course List by division'!$i$6:$i$607))" '"" & MyRangeAddr1 & " * " & MyRangeAddr2 & "" Result1 = Evaluate(Formula1) Sheets("Driver 1 - STUDENTS").Range("c5", Cells(myLR, myLC)).Formula = Result1 The issue we're having is that we get an error 2015 producing "#VALUE!" in all the cells in Excel. Have seen a couple fo examples of 2015s elsewhere on this group but not figured out a solution to this problem (if there is one?!) Cheers Matt On Jan 15, 9:13*am, Matt Knight wrote: My apologies for the radio silence on this - been busy on other jobs with limited internet connectivity. But rest assured myself and a colleague have been scratching our heads over this for the last week on and off. *We've essentially merged a few of the methods that have been suggested and are just trying to iron out some small problems. *Hopefully we'll have cracked it by the end of today. *EIther way I'll keep you posted and post the final solution for anyone else to steal. Matt On Jan 8, 2:39*pm, Matt Knight wrote: Thanks Joel - really appreciate it. *I'll have a play around now and see what happens! Matt On Jan 8, 2:09*pm, joel wrote: Sumproduct formulas are known to slow down a workbook considerable. Putting ther formula into VBA the way you did doesn't change the speed. what you can do is replace the formula with a value. *You can use the evaluate method in ?VBA like this Formula1 = "if(sumproduct(--('Course List by division'!$c $6:$C$607=E$4),'Course List by division'!$i$6:$i$607)=0,0,sumproduct(-- ('Course List by division'!$E$6:$e$607=$b6),--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$i$6:$i$607)/ sumproduct(--('Course List by division'!$c$6:$c$607=E$4),'Course List by division'!$i$6:$i$607))" Results = Evaluate(formula1) The evaluate still takes the same amount of time to execute as putting the formula in the worksheet but the calculation is only performed once, not every time the workbook is updated. All I did was to remove the equal sign from you formula and added parenthesis around you code. you had Formula1 = "=........." I replaced it with Formula1 = "........." Results = evaluate(Formula1) to make you formula more versitile what I do is add ranges like this with sheets("Course List by division'") Set MyRange1 = .Range("$C6:$C$607") MyRangeAddr1 = MyRange1.address(external:=true) Set MyRange2 = .Range("$I6:$I$607") MyRangeAddr2 = MyRange2.address(external:=true) Set MyRange3 = .Range("$E6:$E$607") MyRangeAddr3 = MyRange3.address(external:=true) Set MyRange4 = .Range("$J6:$J$607") MyRangeAddr4 = MyRange3.address(external:=true) Formula1 = "if(sumproduct(--(" & MyRangeAddr4 & "=E$4)," & _ "--(" & MyRangeAddr2 & "=0,0," & _ "sumproduct(--(" & MyRangeAddr3 & "=$b6)," & _ "--(" & MyrangeAddr1 & "=E$4)," & _ MyRangeAddr2 & ")/" & _ "sumproduct(--(" & MyRangeAddr1 & "=E$4)," & _ MyRangeAddr2 & "))" I didn't test my changes but they should be close. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=167924 Microsoft Office Help- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| excessive calcs for simple macro . . . | Excel Discussion (Misc queries) | |||
| Speed Up this macro? | Excel Programming | |||
| Speed Up Macro | Excel Programming | |||
| Speed-up a macro! | Excel Programming | |||
| MACRO Speed? | Excel Programming | |||
Linear Mode
