Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delay when writing cell information to Excel from VBA Macro
When I loop through in VBA setting a value for a variable the loop goes
fast. When I use cell(i,j).value to put the value in a cell there is about a 1 second delay between each cell. For 250 cells it is a long wait. Is there any way to control this wait? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delay when writing cell information to Excel from VBA Macro
add
Application.ScreenUpdating = False Application.Calculation = xlCalculationManual to the beginning of your code and Application.ScreenUpdating = true Application.Calculation = xlCalculationAutomatic at the end -- Gary "Dick HSV" <Dick wrote in message ... When I loop through in VBA setting a value for a variable the loop goes fast. When I use cell(i,j).value to put the value in a cell there is about a 1 second delay between each cell. For 250 cells it is a long wait. Is there any way to control this wait? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delay when writing cell information to Excel from VBA Macro
Gary,
Thanks alot. New to Excel macros - was stumped. Dick HSV "Gary Keramidas" wrote: add Application.ScreenUpdating = False Application.Calculation = xlCalculationManual to the beginning of your code and Application.ScreenUpdating = true Application.Calculation = xlCalculationAutomatic at the end -- Gary "Dick HSV" <Dick wrote in message ... When I loop through in VBA setting a value for a variable the loop goes fast. When I use cell(i,j).value to put the value in a cell there is about a 1 second delay between each cell. For 250 cells it is a long wait. Is there any way to control this wait? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delay when writing cell information to Excel from VBA Macro
Also, read the range directly into a VB array, and if necessary, loop
through the array in memory. Then write the entire array to a range in one step. Sample Usage: Reading: Dim vArray As Variant ' must be "variant" to read range into VBA array vArray = ActiveSheet.Range("A1:D10").Value Writing: ' can be array of any variable type ActiveSheet.Range("A1:D10").Value = myArray This will produce a great improvement in execution speed, particularly writing back to the sheet. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Gary Keramidas" <GKeramidasAtMsn.com wrote in message ... add Application.ScreenUpdating = False Application.Calculation = xlCalculationManual to the beginning of your code and Application.ScreenUpdating = true Application.Calculation = xlCalculationAutomatic at the end -- Gary "Dick HSV" <Dick wrote in message ... When I loop through in VBA setting a value for a variable the loop goes fast. When I use cell(i,j).value to put the value in a cell there is about a 1 second delay between each cell. For 250 cells it is a long wait. Is there any way to control this wait? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
writing information but not having it look like a table? | New Users to Excel | |||
Need help writing Excel macro to solve a cell in each row of a spreadsheet | Excel Programming | |||
When writing a macro in excel workbook, how do I refer to ea cell | Excel Programming | |||
writing information from a userform to an excel sheet | Excel Programming | |||
Delay/Slow a Macro In Excel 2003 | Excel Worksheet Functions |