Need for performance boost
 

I have a macro that could really use a performance boost. It executes
the following actions on roughly between 400 and 2000 cells in
different columns:
- extract parts of the cell contents using instr, left, right, mid
functions and copy those to other cells
- apply formatting (background, font colour ...) to those cells based
upon their contents

I followed normal performance suggestions such as
- using object variables where possible (e.g. for ranges)
- never using copy but range.offset()=

However, it still takes (too) long to execute the macro. Are there any
other tips to improve speed? Is it possible to replace the For each
.... in Range I use to loop through all cells with a more efficient
approach?

Tia,
Chris

Need for performance boost
 

You could pick up all your cells into an array in one command and then
process the array - building another array which you could then place back
on the spreadsheet. This would depend on where you source and destination
cells are.

Without seeing what your code looks like, it would be hard to make specific
suggestions.

--
Regards,
Tom Ogilvy

"chris" wrote in message
om...
I have a macro that could really use a performance boost. It executes
the following actions on roughly between 400 and 2000 cells in
different columns:
- extract parts of the cell contents using instr, left, right, mid
functions and copy those to other cells
- apply formatting (background, font colour ...) to those cells based
upon their contents

I followed normal performance suggestions such as
- using object variables where possible (e.g. for ranges)
- never using copy but range.offset()=

However, it still takes (too) long to execute the macro. Are there any
other tips to improve speed? Is it possible to replace the For each
... in Range I use to loop through all cells with a more efficient
approach?

Tia,
Chris

Need for performance boost
 

Thanks for your suggestion, Tom. I can surely use it for the string
processing actions of the macro. But I was wondering if this would
also work for the formatting part, where I need to check each
individual cell.
I include part of the code which I think is relevant (left out the
variable declarations). It would be great if you or someone else could
have a short look at it from the performance point of view.
Chris


Private Sub cmdInitialiseren_Click()

Set rngStart = sh.Range("A1")

For x = 1 To intKlassen ‘number of columns to process
With rngStart
.offset(0, x * intKols - 1).HorizontalAlignment = xlRight
.offset(0, x * intKols - 2).ColumnWidth = 4.5
.offset(0, x * intKols - 1).ColumnWidth = 4.2
.offset(0, x * intKols - 0).ColumnWidth = 0


'details
For Y = 0 To intVakken ‘number of rows to process
blnComment = False
Set rngUren = .offset(Y + 5, x * intKols - 1)

If Len(rngUren) 0 Then 'rngUren contains string to
process
Set rngNr = rngUren.offset(0, 1)
Set rngLkr = rngUren.offset(0, -1)
intPos1 = InStr(rngUren, "%")
intPos2 = InStr(rngUren, "\")
intPos3 = InStr(rngUren, "{")
intPos4 = InStr(rngUren, "§")
intPos5 = InStr(rngUren, "#")

With rngLkr
.Value = Left(rngUren, intPos1 - 1)
If Len(rngLkr) = 0 Then
.Interior.ColorIndex = 36
End If
strTmp = Mid(rngUren, intPos3 + 1, intPos4 -
intPos3 - 1)
If Len(strTmp) 6 Then
blnComment = True
.AddComment strTmp
End If
End With

rngNr = Mid(rngUren, intPos2 + 1, intPos3 - intPos2 -
1)

strKlasToegekend = Mid(rngUren, intPos5 + 1, 100)

sglUrenToegekend = Mid(rngUren, intPos4 + 1, intPos5 -
intPos4 - 1)
With rngUren
.Value = Replace(Mid(rngUren, intPos1 + 1, intPos2
- intPos1 - 1), ",", ".")
If .Value = 0 Then
.ClearContents
.Interior.ColorIndex = 36
Else
.Interior.ColorIndex = 40
End If
If .Value = sglUrenToegekend And sglUrenToegekend
0 Then
.Font.Bold = True
If Len(rngLkr) 0 Then
rngLkr.Interior.ColorIndex = 40
rngLkr.Font.Bold = True
End If
If blnComment And strKlasToegekend =
rngStart.offset(0, x * intKols - 1) Then
.Font.Underline = True
End If
End If
End With
End If
Next Y

End With

Next x


End Sub






"Tom Ogilvy" wrote in message ...
You could pick up all your cells into an array in one command and then
process the array - building another array which you could then place back
on the spreadsheet. This would depend on where you source and destination
cells are.

Without seeing what your code looks like, it would be hard to make specific
suggestions.

--
Regards,
Tom Ogilvy

"chris" wrote in message
om...
I have a macro that could really use a performance boost. It executes
the following actions on roughly between 400 and 2000 cells in
different columns:
- extract parts of the cell contents using instr, left, right, mid
functions and copy those to other cells
- apply formatting (background, font colour ...) to those cells based
upon their contents

I followed normal performance suggestions such as
- using object variables where possible (e.g. for ranges)
- never using copy but range.offset()=

However, it still takes (too) long to execute the macro. Are there any
other tips to improve speed? Is it possible to replace the For each
... in Range I use to loop through all cells with a more efficient
approach?

Tia,
Chris

Need for performance boost
 

Thanks for replying, Pancho. I should have added that I already
switched off automatic calculation and screen updating.
Chris

"pancho" wrote in message ...
Maybe you can try to disable Automatic Calculation, with:

Application.Calculation = xlCalculationManual

make all your changes and finnaly restore the original
calculation mode with:

Application.Calculation = xlCalculationAutomatic

It allways improve the calculations, because there are no
calculations, between any change you make on your sheets,
but you need to check if your code needs the Automatic
calculation (You can use Application.Calculate to make a
whole calculation in a particular moment)

-----Original Message-----
I have a macro that could really use a performance boost.
It executes
the following actions on roughly between 400 and 2000
cells in
different columns:
- extract parts of the cell contents using instr, left,
right, mid
functions and copy those to other cells
- apply formatting (background, font colour ...) to those
cells based
upon their contents

I followed normal performance suggestions such as
- using object variables where possible (e.g. for ranges)
- never using copy but range.offset()=

However, it still takes (too) long to execute the macro.
Are there any
other tips to improve speed? Is it possible to replace
the For each
.... in Range I use to loop through all cells with a more
efficient
approach?

Tia,
Chris
.

Need for performance boost
 

Thanks for the suggestions, Bill. I'll surely give them a try.

(Wild Bill) wrote in message ...
A couple of quick observations:

Always use Option Explicit, regardless of the speed issue. I can't tell
if y (and especially x) are integers or longs and they should be one of
the two. Maybe you used public variables, but I can't see that. The
point is to use longs and ints where suitable, not variants (or
especially doubles) unless they're useful. You do math on x, and loop
on x and y, and the types really matter.

You first indicated 400 to 2000 cells. The comments suggest 64 million
loop trips - 8216*8216. If so, consider if that's the real issue (not
that optimizing the code is still not a big payoff). When there really
are that many calculations and/or cells, and the final fully optimized
code still takes quite a while just because it must, when all is said
and done I would at least give visual comfort through the Status bar as
to progress (of which I have been told, that in moderation, is not
costly in execution time). Perhaps bang on the status bar every 1,000
trips through the outer loop, if this is the case. (It's also helpful
during development to quickly see if your optimizations are effective.)
Of course, you could think about a progress meter too.

That said, your code looks good. I can see clear cases of where you've
taken trouble to tweak speed. One possible additional tweak is to not
do each intpos if only one is possible. It would mean messy if
statements but if performance matters and it's appropriate, try it.
Another might be to work on strings instead of ranges. I don't even
know what happens when using Left or Mid on a range but I wouldn't
imagine them to run better than using a string. (Again, I'm guessing,
that your variables beginning with rng are Range as opposed to String)

By the way, if your application is German, I'm impressed with your
ambition to work multilingually. Your English usage is flawless, at any
rate :)

Need for performance boost
 

Thanks for the suggestion Tom. I'm still thinking about the array
approach. Could you give me an indication whether I would gain
performance if I still have to loop through all cells for the
formatting.
Thanks a lot,
Chris

"Tom Ogilvy" wrote in message ...
if there are a lot of cells that do not have text strings, you might use


For each rngUren in rngStart.offset(5,x * intKols -
1).Resize(intVakken). _
specialCells(xlConstants,xlTextValues)
blnComment = False
' If Len(rngUren) 0 Then 'comment out and also matching
' end if
Set rngNr = rngUren.offset(0, 1)

Since not much happens for not string cells, not sure if this will speed
things up much or not.

--
Regards,
Tom Ogilvy



chris wrote in message
om...
Thanks for your suggestion, Tom. I can surely use it for the string
processing actions of the macro. But I was wondering if this would
also work for the formatting part, where I need to check each
individual cell.
I include part of the code which I think is relevant (left out the
variable declarations). It would be great if you or someone else could
have a short look at it from the performance point of view.
Chris


Private Sub cmdInitialiseren_Click()

Set rngStart = sh.Range("A1")

For x = 1 To intKlassen ‘number of columns to process
With rngStart
.offset(0, x * intKols - 1).HorizontalAlignment = xlRight
.offset(0, x * intKols - 2).ColumnWidth = 4.5
.offset(0, x * intKols - 1).ColumnWidth = 4.2
.offset(0, x * intKols - 0).ColumnWidth = 0


'details
For Y = 0 To intVakken ‘number of rows to process
blnComment = False
Set rngUren = .offset(Y + 5, x * intKols - 1)

If Len(rngUren) 0 Then 'rngUren contains string to
process
Set rngNr = rngUren.offset(0, 1)
Set rngLkr = rngUren.offset(0, -1)
intPos1 = InStr(rngUren, "%")
intPos2 = InStr(rngUren, "\")
intPos3 = InStr(rngUren, "{")
intPos4 = InStr(rngUren, "§")
intPos5 = InStr(rngUren, "#")

With rngLkr
.Value = Left(rngUren, intPos1 - 1)
If Len(rngLkr) = 0 Then
.Interior.ColorIndex = 36
End If
strTmp = Mid(rngUren, intPos3 + 1, intPos4 -
intPos3 - 1)
If Len(strTmp) 6 Then
blnComment = True
.AddComment strTmp
End If
End With

rngNr = Mid(rngUren, intPos2 + 1, intPos3 - intPos2 -
1)

strKlasToegekend = Mid(rngUren, intPos5 + 1, 100)

sglUrenToegekend = Mid(rngUren, intPos4 + 1, intPos5 -
intPos4 - 1)
With rngUren
.Value = Replace(Mid(rngUren, intPos1 + 1, intPos2
- intPos1 - 1), ",", ".")
If .Value = 0 Then
.ClearContents
.Interior.ColorIndex = 36
Else
.Interior.ColorIndex = 40
End If
If .Value = sglUrenToegekend And sglUrenToegekend
0 Then
.Font.Bold = True
If Len(rngLkr) 0 Then
rngLkr.Interior.ColorIndex = 40
rngLkr.Font.Bold = True
End If
If blnComment And strKlasToegekend =
rngStart.offset(0, x * intKols - 1) Then
.Font.Underline = True
End If
End If
End With
End If
Next Y

End With

Next x


End Sub






"Tom Ogilvy" wrote in message
...
You could pick up all your cells into an array in one command and then
process the array - building another array which you could then place
back
on the spreadsheet. This would depend on where you source and
destination
cells are.

Without seeing what your code looks like, it would be hard to make
specific
suggestions.

--
Regards,
Tom Ogilvy

"chris" wrote in message
om...
I have a macro that could really use a performance boost. It executes
the following actions on roughly between 400 and 2000 cells in
different columns:
- extract parts of the cell contents using instr, left, right, mid
functions and copy those to other cells
- apply formatting (background, font colour ...) to those cells based
upon their contents

I followed normal performance suggestions such as
- using object variables where possible (e.g. for ranges)
- never using copy but range.offset()=

However, it still takes (too) long to execute the macro. Are there any
other tips to improve speed? Is it possible to replace the For each
... in Range I use to loop through all cells with a more efficient
approach?

Tia,
Chris