| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Function gets slower every time I use it...
Hi all
I have a function which splits text for me. I autofit the width of the column and compare it to the width that i have declared. if the column width is wider than mine, i go to the next line. this functions works slow, but I've noted, that every time the function runs again, it is getting slower. first time it runs about 30seconds, 2nd time it runs 45seconds, 3rd time more then a minute!! I don't know why this happens, could that have something to do with memory which isn't released exactly? Thanks for any help Carlo |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Function gets slower every time I use it...
Just curious, are you using ScreenUpdating?
Sub yourSubHere() Application.ScreenUpdating = False ...your code here... Application.ScreenUpdating = True End Sub -- Toby Erkson -- Portland, Oregon -- http://www.bidata.net/ "Carlo" wrote in message ... Hi all I have a function which splits text for me. I autofit the width of the column and compare it to the width that i have declared. if the column width is wider than mine, i go to the next line. this functions works slow, but I've noted, that every time the function runs again, it is getting slower. first time it runs about 30seconds, 2nd time it runs 45seconds, 3rd time more then a minute!! I don't know why this happens, could that have something to do with memory which isn't released exactly? Thanks for any help Carlo |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Function gets slower every time I use it...
Hi Toby
yes i do. And i even use: Application.Calculation = xlCalculationManual altough both help me to make the code faster they don't prevent that ig gets slower every time. Only when i close excel and open it again it runs again faster. Thanks for your reply Carlo "T. Erkson" wrote: Just curious, are you using ScreenUpdating? Sub yourSubHere() Application.ScreenUpdating = False ...your code here... Application.ScreenUpdating = True End Sub -- Toby Erkson -- Portland, Oregon -- http://www.bidata.net/ "Carlo" wrote in message ... Hi all I have a function which splits text for me. I autofit the width of the column and compare it to the width that i have declared. if the column width is wider than mine, i go to the next line. this functions works slow, but I've noted, that every time the function runs again, it is getting slower. first time it runs about 30seconds, 2nd time it runs 45seconds, 3rd time more then a minute!! I don't know why this happens, could that have something to do with memory which isn't released exactly? Thanks for any help Carlo |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Function gets slower every time I use it...
Carlo,
Depends somewhat on what you code actually does. Sure it's not working on a larger set of data each time ? NickHK "Carlo" wrote in message ... Hi Toby yes i do. And i even use: Application.Calculation = xlCalculationManual altough both help me to make the code faster they don't prevent that ig gets slower every time. Only when i close excel and open it again it runs again faster. Thanks for your reply Carlo "T. Erkson" wrote: Just curious, are you using ScreenUpdating? Sub yourSubHere() Application.ScreenUpdating = False ...your code here... Application.ScreenUpdating = True End Sub -- Toby Erkson -- Portland, Oregon -- http://www.bidata.net/ "Carlo" wrote in message ... Hi all I have a function which splits text for me. I autofit the width of the column and compare it to the width that i have declared. if the column width is wider than mine, i go to the next line. this functions works slow, but I've noted, that every time the function runs again, it is getting slower. first time it runs about 30seconds, 2nd time it runs 45seconds, 3rd time more then a minute!! I don't know why this happens, could that have something to do with memory which isn't released exactly? Thanks for any help Carlo |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Function gets slower every time I use it...
Hi NickHK
with the exact same data, 4 times in a row i get following times: 18sec 25sec 37sec 45sec it takes more or less 50% longer each time. this is the code i'm using. If i do any bigger mistakes, please tell me, I'm eager to learn from mistakes I do: ------------------------------------------------- Sub TextSplit(TextSplit As String, EndRange As Range, ColWidth As Double, ColLength As Byte, RowsDel As Byte) Debug.Print "Start:", Time Dim tmpstr As String Dim ApprovedStr As String Dim Arrstr As Variant Dim tmparr As Variant Dim Fontsize_ As Single Dim Linenr As Integer ActiveWindow.Zoom = 100 Fontsize_ = 7.5 Arrstr = Split(TextSplit) ActiveSheet.Range(Chr(EndRange.Column + 64) & EndRange.Row & ":" & Chr(EndRange.Column + 64) & EndRange.Row + RowsDel - 1).Clear Columns("IV").Clear Columns("IV").Font.Size = Fontsize_ Columns("IV").Font.Name = "ITCFranklinGothic LT Book" Linenr = 1 tmpstr = "" For i = 0 To UBound(Arrstr) If InStr(1, Arrstr(i), Chr(10)) < 0 Then tmparr = Split(Arrstr(i), Chr(10)) For j = 0 To UBound(tmparr) - 1 tmpstr = Trim(tmpstr & " " & tmparr(j)) ActiveSheet.Range("IV" & Linenr) = tmpstr Columns("IV:IV").AutoFit If Columns("IV:IV").ColumnWidth <= ColWidth Then Linenr = Linenr + 1 Else ActiveSheet.Range("IV" & Linenr) = ApprovedStr ActiveSheet.Range("IV" & Linenr + 1) = left(Arrstr(i), InStr(1, Arrstr(i), Chr(10)) - 1) Linenr = Linenr + 2 End If tmpstr = "" Next j tmpstr = tmparr(UBound(tmparr)) Else 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 tmpstr = Arrstr(i) ActiveSheet.Range("IV" & Linenr) = ApprovedStr Linenr = Linenr + 1 End If End If End If Next i Range("IV1:IV" & Linenr).copy EndRange Columns("IV:IV").Clear Debug.Print "End:", Time End Sub ----------------------------------------------------------- I sure hope it's not total crap what i'm trying to do here :) Thank you all for your support Carlo "NickHK" wrote: Carlo, Depends somewhat on what you code actually does. Sure it's not working on a larger set of data each time ? NickHK "Carlo" wrote in message ... Hi Toby yes i do. And i even use: Application.Calculation = xlCalculationManual altough both help me to make the code faster they don't prevent that ig gets slower every time. Only when i close excel and open it again it runs again faster. Thanks for your reply Carlo "T. Erkson" wrote: Just curious, are you using ScreenUpdating? Sub yourSubHere() Application.ScreenUpdating = False ...your code here... Application.ScreenUpdating = True End Sub -- Toby Erkson -- Portland, Oregon -- http://www.bidata.net/ "Carlo" wrote in message ... Hi all I have a function which splits text for me. I autofit the width of the column and compare it to the width that i have declared. if the column width is wider than mine, i go to the next line. this functions works slow, but I've noted, that every time the function runs again, it is getting slower. first time it runs about 30seconds, 2nd time it runs 45seconds, 3rd time more then a minute!! I don't know why this happens, could that have something to do with memory which isn't released exactly? Thanks for any help Carlo |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Function gets slower every time I use it...
Carlo,
And how are you calling this routine ? With which values ? NickHK "Carlo" wrote in message ... Hi NickHK with the exact same data, 4 times in a row i get following times: 18sec 25sec 37sec 45sec it takes more or less 50% longer each time. this is the code i'm using. If i do any bigger mistakes, please tell me, I'm eager to learn from mistakes I do: ------------------------------------------------- Sub TextSplit(TextSplit As String, EndRange As Range, ColWidth As Double, ColLength As Byte, RowsDel As Byte) Debug.Print "Start:", Time Dim tmpstr As String Dim ApprovedStr As String Dim Arrstr As Variant Dim tmparr As Variant Dim Fontsize_ As Single Dim Linenr As Integer ActiveWindow.Zoom = 100 Fontsize_ = 7.5 Arrstr = Split(TextSplit) ActiveSheet.Range(Chr(EndRange.Column + 64) & EndRange.Row & ":" & Chr(EndRange.Column + 64) & EndRange.Row + RowsDel - 1).Clear Columns("IV").Clear Columns("IV").Font.Size = Fontsize_ Columns("IV").Font.Name = "ITCFranklinGothic LT Book" Linenr = 1 tmpstr = "" For i = 0 To UBound(Arrstr) If InStr(1, Arrstr(i), Chr(10)) < 0 Then tmparr = Split(Arrstr(i), Chr(10)) For j = 0 To UBound(tmparr) - 1 tmpstr = Trim(tmpstr & " " & tmparr(j)) ActiveSheet.Range("IV" & Linenr) = tmpstr Columns("IV:IV").AutoFit If Columns("IV:IV").ColumnWidth <= ColWidth Then Linenr = Linenr + 1 Else ActiveSheet.Range("IV" & Linenr) = ApprovedStr ActiveSheet.Range("IV" & Linenr + 1) = left(Arrstr(i), InStr(1, Arrstr(i), Chr(10)) - 1) Linenr = Linenr + 2 End If tmpstr = "" Next j tmpstr = tmparr(UBound(tmparr)) Else 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 tmpstr = Arrstr(i) ActiveSheet.Range("IV" & Linenr) = ApprovedStr Linenr = Linenr + 1 End If End If End If Next i Range("IV1:IV" & Linenr).copy EndRange Columns("IV:IV").Clear Debug.Print "End:", Time End Sub ----------------------------------------------------------- I sure hope it's not total crap what i'm trying to do here :) Thank you all for your support Carlo "NickHK" wrote: Carlo, Depends somewhat on what you code actually does. Sure it's not working on a larger set of data each time ? NickHK "Carlo" wrote in message ... Hi Toby yes i do. And i even use: Application.Calculation = xlCalculationManual altough both help me to make the code faster they don't prevent that ig gets slower every time. Only when i close excel and open it again it runs again faster. Thanks for your reply Carlo "T. Erkson" wrote: Just curious, are you using ScreenUpdating? Sub yourSubHere() Application.ScreenUpdating = False ...your code here... Application.ScreenUpdating = True End Sub -- Toby Erkson -- Portland, Oregon -- http://www.bidata.net/ "Carlo" wrote in message ... Hi all I have a function which splits text for me. I autofit the width of the column and compare it to the width that i have declared. if the column width is wider than mine, i go to the next line. this functions works slow, but I've noted, that every time the function runs again, it is getting slower. first time it runs about 30seconds, 2nd time it runs 45seconds, 3rd time more then a minute!! I don't know why this happens, could that have something to do with memory which isn't released exactly? Thanks for any help Carlo |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Function gets slower every time I use it...
Do you see the dotted lines that you get after you do a print or print preview?
If you do Tools|Options|view tab|uncheck display page breaks does the run time go back to normal? Since you're changing columnwidths, excel could be figuring out where those dotted lines go. You may want to do something like: Option Explicit Sub testme() Dim CalcMode As Long Dim ViewMode As Long Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'do the work 'put things back to what they were Application.Calculation = CalcMode ActiveWindow.View = ViewMode End Sub Being in View|PageBreak Preview mode can slow macros down, too. Carlo wrote: Hi NickHK with the exact same data, 4 times in a row i get following times: 18sec 25sec 37sec 45sec it takes more or less 50% longer each time. this is the code i'm using. If i do any bigger mistakes, please tell me, I'm eager to learn from mistakes I do: ------------------------------------------------- Sub TextSplit(TextSplit As String, EndRange As Range, ColWidth As Double, ColLength As Byte, RowsDel As Byte) Debug.Print "Start:", Time Dim tmpstr As String Dim ApprovedStr As String Dim Arrstr As Variant Dim tmparr As Variant Dim Fontsize_ As Single Dim Linenr As Integer ActiveWindow.Zoom = 100 Fontsize_ = 7.5 Arrstr = Split(TextSplit) ActiveSheet.Range(Chr(EndRange.Column + 64) & EndRange.Row & ":" & Chr(EndRange.Column + 64) & EndRange.Row + RowsDel - 1).Clear Columns("IV").Clear Columns("IV").Font.Size = Fontsize_ Columns("IV").Font.Name = "ITCFranklinGothic LT Book" Linenr = 1 tmpstr = "" For i = 0 To UBound(Arrstr) If InStr(1, Arrstr(i), Chr(10)) < 0 Then tmparr = Split(Arrstr(i), Chr(10)) For j = 0 To UBound(tmparr) - 1 tmpstr = Trim(tmpstr & " " & tmparr(j)) ActiveSheet.Range("IV" & Linenr) = tmpstr Columns("IV:IV").AutoFit If Columns("IV:IV").ColumnWidth <= ColWidth Then Linenr = Linenr + 1 Else ActiveSheet.Range("IV" & Linenr) = ApprovedStr ActiveSheet.Range("IV" & Linenr + 1) = left(Arrstr(i), InStr(1, Arrstr(i), Chr(10)) - 1) Linenr = Linenr + 2 End If tmpstr = "" Next j tmpstr = tmparr(UBound(tmparr)) Else 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 tmpstr = Arrstr(i) ActiveSheet.Range("IV" & Linenr) = ApprovedStr Linenr = Linenr + 1 End If End If End If Next i Range("IV1:IV" & Linenr).copy EndRange Columns("IV:IV").Clear Debug.Print "End:", Time End Sub ----------------------------------------------------------- I sure hope it's not total crap what i'm trying to do here :) Thank you all for your support Carlo "NickHK" wrote: Carlo, Depends somewhat on what you code actually does. Sure it's not working on a larger set of data each time ? NickHK "Carlo" wrote in message ... Hi Toby yes i do. And i even use: Application.Calculation = xlCalculationManual altough both help me to make the code faster they don't prevent that ig gets slower every time. Only when i close excel and open it again it runs again faster. Thanks for your reply Carlo "T. Erkson" wrote: Just curious, are you using ScreenUpdating? Sub yourSubHere() Application.ScreenUpdating = False ...your code here... Application.ScreenUpdating = True End Sub -- Toby Erkson -- Portland, Oregon -- http://www.bidata.net/ "Carlo" wrote in message ... Hi all I have a function which splits text for me. I autofit the width of the column and compare it to the width that i have declared. if the column width is wider than mine, i go to the next line. this functions works slow, but I've noted, that every time the function runs again, it is getting slower. first time it runs about 30seconds, 2nd time it runs 45seconds, 3rd time more then a minute!! I don't know why this happens, could that have something to do with memory which isn't released exactly? Thanks for any help Carlo -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| macros making files slower and slower | Excel Discussion (Misc queries) | |||
| String manipulation in macro runs slower each time ran. | Excel Programming | |||
| Excel gets slower and slower.... | Excel Programming | |||
| Slower run with many sheets | Excel Programming | |||
Hybrid Mode
