macro running extremely slow
I have a model that produces multi-year financial output for a product.
The model has an input to change the product under review. I need to create summary reports based on this financial output for a number of products, which gets a little tricky as far as I can tell. Originally I was using a two-way data tables with one axis as the product input values and the other axis as the values I need for the output, i.e. Y1 Revenue, Y2 Revenue, etc. I was accomplishing this using choose function for the output axis. However, this involved some manual copying and pasting and performed somewhat slowly, so I thought VBA would do the trick. With the help of some folks in this forum, I've created the following code, which appears to work, but is *extremely* slow -- even slower than the data table solution. Any suggestions on how to improve the speed? Here's the code: Sub ScenOut() Dim rngNames As Range, rngScenInput As Range Dim rng1 As Range Dim cell As Range, cell1 As Range Dim nm As Name Dim c As Integer, r As Integer Dim iMaxScen As Integer Dim wks As Worksheet Set wks = Worksheets("Output") wks.Range("A3").Activate Set rngNames = Range(Application.Names!OutputNames) Set rngScenInput = Range(Application.Names("Product.Input")) c = 1 iMaxScen = 10 Do While c <= iMaxScen rngScenInput.Value = c For Each cell In rngNames r = 0 Set rng1 = Range(cell.Value) For Each cell1 In rng1 r = r + 1 wks.Cells(r, c).Value = cell1.Value Next cell1 Next cell Loop End Sub |
macro running extremely slow
For starters, you might try adding:
Application.ScreenUpdating = False 'to the top of your code Application.ScreenUpdating = True 'to the bottom of your code Regards, Paul "sloth" wrote in message oups.com... I have a model that produces multi-year financial output for a product. The model has an input to change the product under review. I need to create summary reports based on this financial output for a number of products, which gets a little tricky as far as I can tell. Originally I was using a two-way data tables with one axis as the product input values and the other axis as the values I need for the output, i.e. Y1 Revenue, Y2 Revenue, etc. I was accomplishing this using choose function for the output axis. However, this involved some manual copying and pasting and performed somewhat slowly, so I thought VBA would do the trick. With the help of some folks in this forum, I've created the following code, which appears to work, but is *extremely* slow -- even slower than the data table solution. Any suggestions on how to improve the speed? Here's the code: Sub ScenOut() Dim rngNames As Range, rngScenInput As Range Dim rng1 As Range Dim cell As Range, cell1 As Range Dim nm As Name Dim c As Integer, r As Integer Dim iMaxScen As Integer Dim wks As Worksheet Set wks = Worksheets("Output") wks.Range("A3").Activate Set rngNames = Range(Application.Names!OutputNames) Set rngScenInput = Range(Application.Names("Product.Input")) c = 1 iMaxScen = 10 Do While c <= iMaxScen rngScenInput.Value = c For Each cell In rngNames r = 0 Set rng1 = Range(cell.Value) For Each cell1 In rng1 r = r + 1 wks.Cells(r, c).Value = cell1.Value Next cell1 Next cell Loop End Sub |
macro running extremely slow
I would expect it to run forever, since you say
Do While c <= iMaxScen and you never change the value of "c" inside the loop. Why are you using addressing like this: Set rngNames = Range(Application.Names!OutputNames) Set rngScenInput = Range(Application.Names("Product.Input")) Set rngNames = Range("OutputNames") Set rngScenInput = Range("Product.Input") would be more direct. -- Regards, Tom Ogilvy "sloth" wrote: I have a model that produces multi-year financial output for a product. The model has an input to change the product under review. I need to create summary reports based on this financial output for a number of products, which gets a little tricky as far as I can tell. Originally I was using a two-way data tables with one axis as the product input values and the other axis as the values I need for the output, i.e. Y1 Revenue, Y2 Revenue, etc. I was accomplishing this using choose function for the output axis. However, this involved some manual copying and pasting and performed somewhat slowly, so I thought VBA would do the trick. With the help of some folks in this forum, I've created the following code, which appears to work, but is *extremely* slow -- even slower than the data table solution. Any suggestions on how to improve the speed? Here's the code: Sub ScenOut() Dim rngNames As Range, rngScenInput As Range Dim rng1 As Range Dim cell As Range, cell1 As Range Dim nm As Name Dim c As Integer, r As Integer Dim iMaxScen As Integer Dim wks As Worksheet Set wks = Worksheets("Output") wks.Range("A3").Activate Set rngNames = Range(Application.Names!OutputNames) Set rngScenInput = Range(Application.Names("Product.Input")) c = 1 iMaxScen = 10 Do While c <= iMaxScen rngScenInput.Value = c For Each cell In rngNames r = 0 Set rng1 = Range(cell.Value) For Each cell1 In rng1 r = r + 1 wks.Cells(r, c).Value = cell1.Value Next cell1 Next cell Loop End Sub |
macro running extremely slow
I'm addressing like set rng = Range(Application.Names!DefinedName) per
examples in Steven Roman's book, Writing Excel Macros. Would you suggest something different? I turned off screen updating and added incrementing of c inside loop, but it still runs painfully slow. Any other thoughts? |
macro running extremely slow
I did suggest something different.
Only time I have seen Steve post here was when he was asking a question. Twice as I remember and that was several years ago. The only way I would see that as being painfully slow would be if your named ranges had extensive numbers of cells in them and/or if your sheet takes a long time to recalculate and you macro causes recalculation each time it writes to the sheet. In that latter case, I would put Application.Calculation = xlManual ' executable code Application.Calculation = xlAutomatic However, since it appears you are doing some kind of whatif, it might be necessary to have automatic calculation turned on to get the proper results. If it is just because of numerous cells, then perhaps recording the cell values in an array and then writing them all at once to the sheet would be faster. -- Regards, Tom Ogilvy "sloth" wrote: I'm addressing like set rng = Range(Application.Names!DefinedName) per examples in Steven Roman's book, Writing Excel Macros. Would you suggest something different? I turned off screen updating and added incrementing of c inside loop, but it still runs painfully slow. Any other thoughts? |
All times are GMT +1. The time now is 03:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com