Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.sdk,microsoft.public.excel.worksheet.functions,microsoft.public.office.developer.automation
|
|||
|
|||
QUERY & HELP: so slow executing VBA code... :S
First thing that could help is to turn off updating here are a couple of
routines to do that: Call Updates_Off at the top of your routine then call Updates_On at the end Public Sub Updates_Off() With Application .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual End With End Sub Public Sub Updates_On() With Application .StatusBar = False .ScreenUpdating = True .DisplayAlerts = True .EnableEvents = True .EnableCancelKey = xlInterrupt .Cursor = xlDefault .Calculation = xlCalculationAutomatic End With End Sub I know that formatting takes a very long time to apply cell by cell if you do not turn off screen updating. Another way you can speed things up (if you are interrogating/populating your sheet's cells row by row, col by col I.E. using .cells or .offset to reference each cell)... Use Variant arrays to load your sheets cells then process the array, changing the values you need to change then load that array back to the sheet. Dim vaData as Variant Dim lRow as long, lCol as long Dim addr As string ' Load your worksheets data vaData = Range(ActiveSheet.UsedRange.Address).Value addr = ActiveSheet.UsedRange.Address ' Process the data 1 row at a time, 1 col at a time For lRow = 1 to UBound(vaData, 1) For lCol = 1 to UBound(vaData, 2) if vaData(lRow, lCol) = "X" then vaData(lRow, lcol) = "" EndIf '*** any other value changes to each row, col by col till '*** the end of your data (you cant apply formatting here '*** since your not directly referencing the cell) Next lCol Next lRow ' Put the array back to the same cells in the worksheet ActiveWorksheet.Range(addr).value = vaData Note that Variant data arrays must always be 2 dimensional for this to work. The array defined is 1..X for each dimension based on the size of the block of cells. You can build a 2D array in code while parsing your XML file. then with one statement load all that data to the block of cells. Just make sure it is 1..Rows and 1..Cols hope that helps -- Regards, John "KevinGPO" wrote: I have programmed my own set of modules for Excel to read, parse a xml file then scan the Excel worksheets for "import/bookmark" tags and do a replace, pasting in the corresponding xml data. At the same time it does formatting/style/colors, etc. which is taken from the xml attributes & nodes. I found a 759kb xml file takes ages to parse. The excel template file is 376kb. Importing usually takes 9-10 minutes long. Is there any way I can optimise and speed things up (besides buying a faster PC - I have a P4 3.0GHz 512MB). Is there anyway to create/generate a valid excel file without having to load up Excel? What is this Office SDK and what can it do? |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.sdk,microsoft.public.excel.worksheet.functions,microsoft.public.office.developer.automation
|
|||
|
|||
QUERY & HELP: so slow executing VBA code... :S
Thanks for the advice & tips. I tried using the sub-routines to turn updates
off. I don't see any difference, though at least I don't get to see all the cells being filled in slowly. This is a massive VBA importing XML data script. It usually takes around 5 minutes to 10 minutes to complete. Just wondering if you know if it's possible to create a progress bar or something so the user knows it's still working. I've still yet to analyse my code to see if I can make more improvements using your .cells / .offset technique. More news yet to come. Kev "John Keith" wrote in message ... First thing that could help is to turn off updating here are a couple of routines to do that: Call Updates_Off at the top of your routine then call Updates_On at the end Public Sub Updates_Off() With Application .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual End With End Sub Public Sub Updates_On() With Application .StatusBar = False .ScreenUpdating = True .DisplayAlerts = True .EnableEvents = True .EnableCancelKey = xlInterrupt .Cursor = xlDefault .Calculation = xlCalculationAutomatic End With End Sub I know that formatting takes a very long time to apply cell by cell if you do not turn off screen updating. Another way you can speed things up (if you are interrogating/populating your sheet's cells row by row, col by col I.E. using .cells or .offset to reference each cell)... Use Variant arrays to load your sheets cells then process the array, changing the values you need to change then load that array back to the sheet. Dim vaData as Variant Dim lRow as long, lCol as long Dim addr As string ' Load your worksheets data vaData = Range(ActiveSheet.UsedRange.Address).Value addr = ActiveSheet.UsedRange.Address ' Process the data 1 row at a time, 1 col at a time For lRow = 1 to UBound(vaData, 1) For lCol = 1 to UBound(vaData, 2) if vaData(lRow, lCol) = "X" then vaData(lRow, lcol) = "" EndIf '*** any other value changes to each row, col by col till '*** the end of your data (you cant apply formatting here '*** since your not directly referencing the cell) Next lCol Next lRow ' Put the array back to the same cells in the worksheet ActiveWorksheet.Range(addr).value = vaData Note that Variant data arrays must always be 2 dimensional for this to work. The array defined is 1..X for each dimension based on the size of the block of cells. You can build a 2D array in code while parsing your XML file. then with one statement load all that data to the block of cells. Just make sure it is 1..Rows and 1..Cols hope that helps -- Regards, John "KevinGPO" wrote: I have programmed my own set of modules for Excel to read, parse a xml file then scan the Excel worksheets for "import/bookmark" tags and do a replace, pasting in the corresponding xml data. At the same time it does formatting/style/colors, etc. which is taken from the xml attributes & nodes. I found a 759kb xml file takes ages to parse. The excel template file is 376kb. Importing usually takes 9-10 minutes long. Is there any way I can optimise and speed things up (besides buying a faster PC - I have a P4 3.0GHz 512MB). Is there anyway to create/generate a valid excel file without having to load up Excel? What is this Office SDK and what can it do? |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.sdk,microsoft.public.excel.worksheet.functions,microsoft.public.office.developer.automation
|
|||
|
|||
QUERY & HELP: so slow executing VBA code... :S
I see your point about loading sheet cells into variant arrays, filling the
arrays then loading that array back to the sheet. I could load an entire sheet into a 2D array and run through it looking for bookmark tags. Then replace the the bookmark tags with values. This will include row expansion from inserting tables. Then load the array back onto the whole sheet. However, I use a lot of colour/border formating. Currently I can see the huge slow down is due to the fact that am formating each individual row separately. I think I should stop this and format at the end. I can do a total format, by calculating the spanned cells to format. Is this what you were thinking too John? "John Keith" wrote in message ... I know that formatting takes a very long time to apply cell by cell if you do not turn off screen updating. Another way you can speed things up (if you are interrogating/populating your sheet's cells row by row, col by col I.E. using .cells or .offset to reference each cell)... Use Variant arrays to load your sheets cells then process the array, changing the values you need to change then load that array back to the sheet. Dim vaData as Variant Dim lRow as long, lCol as long Dim addr As string ' Load your worksheets data vaData = Range(ActiveSheet.UsedRange.Address).Value addr = ActiveSheet.UsedRange.Address ' Process the data 1 row at a time, 1 col at a time For lRow = 1 to UBound(vaData, 1) For lCol = 1 to UBound(vaData, 2) if vaData(lRow, lCol) = "X" then vaData(lRow, lcol) = "" EndIf '*** any other value changes to each row, col by col till '*** the end of your data (you cant apply formatting here '*** since your not directly referencing the cell) Next lCol Next lRow ' Put the array back to the same cells in the worksheet ActiveWorksheet.Range(addr).value = vaData Note that Variant data arrays must always be 2 dimensional for this to work. The array defined is 1..X for each dimension based on the size of the block of cells. You can build a 2D array in code while parsing your XML file. then with one statement load all that data to the block of cells. Just make sure it is 1..Rows and 1..Cols hope that helps -- Regards, John "KevinGPO" wrote: I have programmed my own set of modules for Excel to read, parse a xml file then scan the Excel worksheets for "import/bookmark" tags and do a replace, pasting in the corresponding xml data. At the same time it does formatting/style/colors, etc. which is taken from the xml attributes & nodes. I found a 759kb xml file takes ages to parse. The excel template file is 376kb. Importing usually takes 9-10 minutes long. Is there any way I can optimise and speed things up (besides buying a faster PC - I have a P4 3.0GHz 512MB). Is there anyway to create/generate a valid excel file without having to load up Excel? What is this Office SDK and what can it do? |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel.sdk,microsoft.public.excel.worksheet.functions,microsoft.public.office.developer.automation
|
|||
|
|||
QUERY & HELP: so slow executing VBA code... :S
yep, split the process so formatting is done at the end. I hadn't thought of
figuring the spanned cells for blocking up the format operations but that should help too. For the progress bar... there is a lot of discussion about that and I have seen a real slick implementation of a message box type of progress indicator in "Professional Excel Development" a book by Stephen Bullen, Rob Bovey and John Green. A very useful book to take VBA to the next level! But you can use the statusbar to get the same information so you can see where in the processing loop your code is. try inserting: Application.Statusbar = "macro running " & loopcounter & " of " & loopmax Then to turn it back over to excel at the end: Application.Statusbar = false -- Regards, John "KevinGPO" wrote: I see your point about loading sheet cells into variant arrays, filling the arrays then loading that array back to the sheet. I could load an entire sheet into a 2D array and run through it looking for bookmark tags. Then replace the the bookmark tags with values. This will include row expansion from inserting tables. Then load the array back onto the whole sheet. However, I use a lot of colour/border formating. Currently I can see the huge slow down is due to the fact that am formating each individual row separately. I think I should stop this and format at the end. I can do a total format, by calculating the spanned cells to format. Is this what you were thinking too John? "John Keith" wrote in message ... I know that formatting takes a very long time to apply cell by cell if you do not turn off screen updating. Another way you can speed things up (if you are interrogating/populating your sheet's cells row by row, col by col I.E. using .cells or .offset to reference each cell)... Use Variant arrays to load your sheets cells then process the array, changing the values you need to change then load that array back to the sheet. Dim vaData as Variant Dim lRow as long, lCol as long Dim addr As string ' Load your worksheets data vaData = Range(ActiveSheet.UsedRange.Address).Value addr = ActiveSheet.UsedRange.Address ' Process the data 1 row at a time, 1 col at a time For lRow = 1 to UBound(vaData, 1) For lCol = 1 to UBound(vaData, 2) if vaData(lRow, lCol) = "X" then vaData(lRow, lcol) = "" EndIf '*** any other value changes to each row, col by col till '*** the end of your data (you cant apply formatting here '*** since your not directly referencing the cell) Next lCol Next lRow ' Put the array back to the same cells in the worksheet ActiveWorksheet.Range(addr).value = vaData Note that Variant data arrays must always be 2 dimensional for this to work. The array defined is 1..X for each dimension based on the size of the block of cells. You can build a 2D array in code while parsing your XML file. then with one statement load all that data to the block of cells. Just make sure it is 1..Rows and 1..Cols hope that helps -- Regards, John "KevinGPO" wrote: I have programmed my own set of modules for Excel to read, parse a xml file then scan the Excel worksheets for "import/bookmark" tags and do a replace, pasting in the corresponding xml data. At the same time it does formatting/style/colors, etc. which is taken from the xml attributes & nodes. I found a 759kb xml file takes ages to parse. The excel template file is 376kb. Importing usually takes 9-10 minutes long. Is there any way I can optimise and speed things up (besides buying a faster PC - I have a P4 3.0GHz 512MB). Is there anyway to create/generate a valid excel file without having to load up Excel? What is this Office SDK and what can it do? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
QUERY & HELP: so slow executing VBA code... :S | Excel Worksheet Functions | |||
do anybody have a sample code for executing excel macro from vb code?<eom | Excel Programming | |||
Code not executing | Excel Programming | |||
(trying again...) code stops executing | Excel Programming | |||
Code Changes Not Executing | Excel Programming |