Trying to improve the performance of my code
Hi Gary
thank you for your input!
instead of 40 seconds it ran for 36 seconds. Which is still long!
But it is an improvement!
I hope someone can give me a hint with which i am able to
go down to a duration of maximum 20 seconds or so!
Would really appreciate it.
Thanks again
Carlo
"Gary Keramidas" wrote:
try adding these 2 lines at the beginning of your code:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
and add these before the end sub:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
--
Gary
"Carlo" wrote in message
...
Hi All
with following code i'm splitting up text into rows.
The textpassages aren't longer than ColWidth.
'-------------------------------------------------------
Sub Tool_Hyphenation(TextSplit As String, EndRange As Range, ColWidth As
Double, Collength as Byte)
Dim tmpstr, ApprovedStr, StrScore As String
Dim Arrstr, ArrTmpStr, ArrScore As Variant
Dim Fontsize_ As Single
Dim Linenr, LenArrStr, ScoreCount As Integer
Fontsize_ = 7.5
Arrstr = Split(TextSplit)
Columns("IV").Clear
Columns("IV").Font.Size = Fontsize_
Columns("IV").Font.Name = "ITCFranklinGothic LT Book"
Linenr = 1
tmpstr = ""
For i = 0 To UBound(Arrstr)
tmpstr = Trim(tmpstr & " " & Arrstr(i))
If Len(tmpstr) ColLength Then
ActiveSheet.Range("IV" & Linenr) = tmpstr
Columns("IV:IV").AutoFit
If Columns("IV:IV").ColumnWidth <= ColWidth Then
ApprovedStr = tmpstr
Else
ActiveSheet.Range("IV" & Linenr) = ApprovedStr
Linenr = Linenr + 1
tmpstr = Arrstr(i)
End If
End If
Next i
If Range("IV" & Linenr).FormulaR1C1 < tmpstr Then
Range("IV" & Linenr).FormulaR1C1 = tmpstr
End If
Range("IV1:IV" & Linenr).copy Range(EndRange)
Columns("IV:IV").Clear
End Sub
'-------------------------------------------------------
The problem now is, that this code, like it is now, is really slow.
Does anyone has an idea how the performance can be improved?
I improved it a little with the use of Collength, which controls the
length of tmpstring.
I appreciate every comment.
Thanks in advance
Carlo
|