QUERY & HELP: so slow executing VBA code... :S

[KevinGPO]

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?

 

[Andibevan]

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?

Kevin,

Here is a link to something that claims to be able to generate an excel
workbook without needing excel:-

http://www.freevbcode.com/ShowCode.Asp?ID=6856 -"This class will create a
simple Excel Workbook(BIFF8 format) without having copy of Microsoft Excel
installed. It has support for multiple sheets, Labels, Numbers, Dates, blank
cells and has limited support for formula cells. includes support for print
settings, lines, colors, patterns, text alignments, row heights, hidden
rows, column widths, merged cells, number and date formatting, rotated and
stacked text, password protection, etc See the supplied test project on how
to use the class."

I haven't tried it but should be a good starting point.

Regards

Andy

 

[John Keith]

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

 

[KevinGPO]

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

 

[KevinGPO]

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]

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