Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow execution changing cell value
I've got the code below that loops through a range of cells fills in
some information for a tournament, including assigning a unique code for a section judge (i.e. J-AB1). Everything works great, except that it's extremely slow. I tested the speed of each line of code using a stopwatch sub that others have posted. Each line runs between 0-4 milliseconds except the line: JdgCell = "J-" & c & i which runs upward of 320 milliseconds. All it's doing is changing the value of a cell so I don't understand what the holdup is. I'm using almost the exact same code in another part of the program and it runs fast. Any ideas? Thanks! Nate For Each c In SCodes With c.Offset(, 16) If .Value 0 Then 'check if number of judges 0 For i = 1 To .Value Set JdgCell = JdgCell.Offset(1) 'fill in school code JdgCell.Offset(, -2) = c.Text 'fill in school name JdgCell.Offset(, -1) = c.Offset(, 1).Text 'fill in judge code JdgCell = "J-" & c & i <---- this line is taking 300+ milliseconds to execute Next i End If End With Next c |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow execution changing cell value
The only thing I notice is that you are using just "c" instead of "c.text"
on that last line... "Nate" wrote in message ... I've got the code below that loops through a range of cells fills in some information for a tournament, including assigning a unique code for a section judge (i.e. J-AB1). Everything works great, except that it's extremely slow. I tested the speed of each line of code using a stopwatch sub that others have posted. Each line runs between 0-4 milliseconds except the line: JdgCell = "J-" & c & i which runs upward of 320 milliseconds. All it's doing is changing the value of a cell so I don't understand what the holdup is. I'm using almost the exact same code in another part of the program and it runs fast. Any ideas? Thanks! Nate For Each c In SCodes With c.Offset(, 16) If .Value 0 Then 'check if number of judges 0 For i = 1 To .Value Set JdgCell = JdgCell.Offset(1) 'fill in school code JdgCell.Offset(, -2) = c.Text 'fill in school name JdgCell.Offset(, -1) = c.Offset(, 1).Text 'fill in judge code JdgCell = "J-" & c & i <---- this line is taking 300+ milliseconds to execute Next i End If End With Next c |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow execution changing cell value
I even tried commenting out the variables like:
JdgCell = "J-" and it still took a long time to execute. <shrug On Feb 26, 12:34*pm, "egun" wrote: The only thing I notice is that you are using just "c" instead of "c.text" on that last line... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow execution changing cell value
Is calculation on Automatic? Maybe a recalc is occurring.
Disable calculation to see if that helps: Dim OrigCalc As Long OrigCalc = Application.Calculation Application.Calculation = xlCalculationManual ' ...Code Application.Calculation = OrigCalc Also, one suggestion. Use c.Value rather than c.Text. The Text property returns what is displayed in the cell, not necessarily the underlying value. It is also slow. To see how this can be a problem: Type a number in a cell. Format it as Accounting style Run this macro Sub test() MsgBox "'" & ActiveCell.Text & "'" MsgBox "'" & ActiveCell.Value & "'" End Sub Single quotes are used to show how padding is included too. The Value property returns the real underlying value. The Text property returns what is displayed in the cell. Use Text if you only want to return what is literally displayed in the cell. This includes #### which is displayed in Excel when the column is too narrow. Value returns the underlying value, not ####. Here is how I would imagine it should look with those changes (untested) Dim OrigCalc As Long OrigCalc = Application.Calculation Application.Calculation = xlCalculationManual ' Declare all other variables For Each c In sCodes With c.Offset(, 16) If .Value 0 Then For i = 1 To .Value Set JdgCell = JdgCell.Offset(1) JdgCell.Offset(, -2).Value = c.Value JdgCell.Offset(, -1).Value = c.Offset(, 1).Value JdgCell.Value = "J-" & CStr(c.Value) & CStr(i) Next i End If End With Next c Application.Calculation = OrigCalc -- Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison Free & Pro versions "Nate" wrote in message ... I've got the code below that loops through a range of cells fills in some information for a tournament, including assigning a unique code for a section judge (i.e. J-AB1). Everything works great, except that it's extremely slow. I tested the speed of each line of code using a stopwatch sub that others have posted. Each line runs between 0-4 milliseconds except the line: JdgCell = "J-" & c & i which runs upward of 320 milliseconds. All it's doing is changing the value of a cell so I don't understand what the holdup is. I'm using almost the exact same code in another part of the program and it runs fast. Any ideas? Thanks! Nate For Each c In SCodes With c.Offset(, 16) If .Value 0 Then 'check if number of judges 0 For i = 1 To .Value Set JdgCell = JdgCell.Offset(1) 'fill in school code JdgCell.Offset(, -2) = c.Text 'fill in school name JdgCell.Offset(, -1) = c.Offset(, 1).Text 'fill in judge code JdgCell = "J-" & c & i <---- this line is taking 300+ milliseconds to execute Next i End If End With Next c |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow execution changing cell value
Tim,
Turning off the calculation worked! I guess I didn't realize (or forgot) that there were formulas 15 sheets away that were pulling from that column, so as a new value got filled in it had to recalc. Thanks for the explanation between .value and .text as well. Admittedly, I have been a little confused by the difference between the two - some reference manuals don't help. But it's crystal clear now. Thanks again!! Nate On Feb 26, 1:31*pm, "Tim Zych" <- wrote: Is calculation on Automatic? Maybe a recalc is occurring. Disable calculation to see if that helps: Dim OrigCalc As Long OrigCalc = Application.Calculation Application.Calculation = xlCalculationManual * * ' ...Code Application.Calculation = OrigCalc Also, one suggestion. Use c.Value rather than c.Text. The Text property returns what is displayed in the cell, not necessarily the underlying value. It is also slow. To see how this can be a problem: Type a number in a cell. Format it as Accounting style Run this macro Sub test() * * MsgBox "'" & ActiveCell.Text & "'" * * MsgBox "'" & ActiveCell.Value & "'" End Sub Single quotes are used to show how padding is included too. The Value property returns the real underlying value. The Text property returns what is displayed in the cell. Use Text if you only want to return what is literally displayed in the cell. This includes #### which is displayed in Excel when the column is too narrow. Value returns the underlying value, not ####. Here is how I would imagine it should look with those changes (untested) * * Dim OrigCalc As Long * * OrigCalc = Application.Calculation * * Application.Calculation = xlCalculationManual * * ' Declare all other variables * * For Each c In sCodes * * * * With c.Offset(, 16) * * * * * * If .Value 0 Then * * * * * * * * For i = 1 To .Value * * * * * * * * * * Set JdgCell = JdgCell.Offset(1) * * * * * * * * * * JdgCell.Offset(, -2).Value = c.Value * * * * * * * * * * JdgCell.Offset(, -1).Value = c.Offset(, 1).Value * * * * * * * * * * JdgCell.Value = "J-" & CStr(c.Value) & CStr(i) * * * * * * * * Next i * * * * * * End If * * * * End With * * Next c * * Application.Calculation = OrigCalc -- Tim Zychhttp://www.higherdata.com Workbook Compare - Excel data comparison Free & Pro versions "Nate" wrote in message ... I've got the code below that loops through a range of cells fills in some information for a tournament, including assigning a unique code for a section judge (i.e. J-AB1). *Everything works great, except that it's extremely slow. *I tested the speed of each line of code using a stopwatch sub that others have posted. *Each line runs between 0-4 milliseconds except the line: *JdgCell = "J-" & c & i * which runs upward of 320 milliseconds. *All it's doing is changing the value of a cell so I don't understand what the holdup is. *I'm using almost the exact same code in another part of the program and it runs fast. *Any ideas? Thanks! *Nate * *For Each c In SCodes * * * *With c.Offset(, 16) * * * *If .Value 0 Then *'check if number of judges 0 * * * * * *For i = 1 To .Value * * * * * * * *Set JdgCell = JdgCell.Offset(1) * * * * * * * *'fill in school code * * * * * * * *JdgCell.Offset(, -2) = c.Text * * * * * * * *'fill in school name * * * * * * * *JdgCell.Offset(, -1) = c.Offset(, 1).Text * * * * * * * *'fill in judge code * * * * * * * *JdgCell = "J-" & c & i * * *<---- this line is taking 300+ milliseconds to execute * * * * * *Next i * * * *End If * * * *End With * *Next c- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Execution very slow | Excel Programming | |||
Changing cell value in a range terminates code execution | Excel Programming | |||
Slow code execution | Excel Programming | |||
Automatic Macro Execution Upon Cell Values Changing | Excel Programming | |||
slow macro execution | Excel Programming |