Speeding Up Excel Performance-VBA Loops
Hi everyone, I've been follwoing the thread on speeding up Excel & slow performanc from VBA loops. http://www.excelforum.com/showthread.php?t=572195 My workbooks are still "Calculating Cells" every time I update a cel or drop/drag a new formula. I've set OptionsToolsCalculations to th default "Automatic" setting. (screenshot attached) All my code is in a single "Module1" . Any feedback is appreciated. I' pretty new to this. Thanks Conor Code ------------------- Function OperatingSystem(pVal As String) As String ' Check server for operating system and return Operating system parent type With Application .ScreenUpdating = False myCalc = .Calculation .Calculation = xlCalculationManual .EnableEvents = False .DisplayAlerts = False End With If InStr(pVal, "Windows") Then OperatingSystem = "Windows" ElseIf InStr(pVal, "Window") Then OperatingSystem = "Windows" ElseIf InStr(pVal, "Win") Then OperatingSystem = "Windows" ElseIf InStr(pVal, "Solaris") Then OperatingSystem = "SUN/Solaris" ElseIf InStr(pVal, "Sun") Then OperatingSystem = "SUN/Solaris" ElseIf InStr(pVal, "Linux") Then OperatingSystem = "Linux" ElseIf InStr(pVal, "Red Hat") Then OperatingSystem = "Linux" ElseIf InStr(pVal, "RHEL") Then OperatingSystem = "Linux" ElseIf InStr(pVal, "EL") Then OperatingSystem = "Linux" ElseIf InStr(pVal, "HP-UX") Then OperatingSystem = "HP-UX" ElseIf InStr(pVal, "11") Then OperatingSystem = "HP-UX" ' if not found return a value of other Else OperatingSystem = "Other" End If With Application .ScreenUpdating = True .EnableEvents = True .DisplayAlerts = True .Calculation = myCalc End With End Function Function Add10ServerType(pVal As String) As String ' Search server model and determine its billing ' category under Addendum 10 ' Mark all HP Proliant DL3xx family as small servers ' Mark all HP Proliant DL5xx family as medium servers ' If category doesn't match then return as value ' of Non-defined HW ' Code by HP Ericsson Finance & Operations Team With Application .ScreenUpdating = False myCalc = .Calculation .Calculation = xlCalculationManual .EnableEvents = False .DisplayAlerts = False End With If InStr(pVal, "DL38") Then Add10ServerType = "Small" ElseIf InStr(pVal, "rx2620") Then Add10ServerType = "Small" ElseIf InStr(pVal, "rp3440") Then Add10ServerType = "Small" ElseIf InStr(pVal, "DL58") Then Add10ServerType = "Medium" ElseIf InStr(pVal, "rx4640") Then Add10ServerType = "Medium" ElseIf InStr(pVal, "rp4440") Then Add10ServerType = "Medium" ElseIf InStr(pVal, "rp8420") Then Add10ServerType = "Large" ElseIf InStr(pVal, "V240") Then Add10ServerType = "Small" ElseIf InStr(pVal, "V440") Then Add10ServerType = "Medium 2" ElseIf InStr(pVal, "V490") Then Add10ServerType = "Medium 3" ElseIf InStr(pVal, "V890") Then Add10ServerType = "Large" ElseIf InStr(pVal, "T2000") Then Add10ServerType = "Medium" ' if model not found then return a value of non-defined hw Else Add10ServerType = "Non-predefined hw" End If With Application .ScreenUpdating = True .EnableEvents = True .DisplayAlerts = True .Calculation = myCalc End With End Function Function Server(pVal As String, pVal2 As String) As String With Application .ScreenUpdating = False myCalc = .Calculation .Calculation = xlCalculationManual .EnableEvents = False .DisplayAlerts = False End With If InStr(pVal, "DL380") And InStr(pVal2, "HP") Then Server = "HP Proliant DL380" ElseIf InStr(pVal, "DL340") And InStr(pVal2, "HP") Then Server = "HP Proliant DL340" ElseIf InStr(pVal, "DL580") And InStr(pVal2, "HP") Then Server = "HP Proliant DL580" Else Server = "Hardware Not Defined" End If With Application .ScreenUpdating = True .EnableEvents = True .DisplayAlerts = True .Calculation = myCalc End With End Function Function ServerType(pVal As String) As String ' Search server model and determine its billing With Application .ScreenUpdating = False myCalc = .Calculation .Calculation = xlCalculationManual .EnableEvents = False .DisplayAlerts = False End With If InStr(pVal, "Proliant") Then ServerType = "HP Proliant" ElseIf InStr(pVal, "DL32") Then ServerType = "HP Proliant" ElseIf InStr(pVal, "DL36") Then ServerType = "HP Proliant" ElseIf InStr(pVal, "DL38") Then ServerType = "HP Proliant" ElseIf InStr(pVal, "DL56") Then ServerType = "HP Proliant" ElseIf InStr(pVal, "DL58") Then ServerType = "HP Proliant" ElseIf InStr(pVal, "RP44") Then ServerType = "rp4440rx4640" ElseIf InStr(pVal, "rp44") Then ServerType = "rp4440rx4640" ElseIf InStr(pVal, "RX46") Then ServerType = "rp4440rx4640" ElseIf InStr(pVal, "rx46") Then ServerType = "rp4440rx4640" ElseIf InStr(pVal, "RP34") Then ServerType = "rp3440rx2620" ElseIf InStr(pVal, "rp34") Then ServerType = "rp3440rx2620" ElseIf InStr(pVal, "RX26") Then ServerType = "rp3440rx2620" ElseIf InStr(pVal, "rx26") Then ServerType = "rp3440rx2620" ElseIf InStr(pVal, "SUN") Then ServerType = "Sun" ElseIf InStr(pVal, "Sun") Then ServerType = "Sun" ElseIf InStr(pVal, "Sun Fire") Then ServerType = "Sun" ElseIf InStr(pVal, "V24") Then ServerType = "Sun" ElseIf InStr(pVal, "V44") Then ServerType = "Sun" ElseIf InStr(pVal, "V49") Then ServerType = "Sun" ElseIf InStr(pVal, "T2000") Then ServerType = "Sun" ' if model not found then return a value of other Else ServerType = "Other" End If With Application .ScreenUpdating = True .EnableEvents = True .DisplayAlerts = True .Calculation = myCalc End With End Function -------------------- +-------------------------------------------------------------------+ |Filename: screen.jpg | |Download: http://www.excelforum.com/attachment.php?postid=5218 | +-------------------------------------------------------------------+ -- okelly ------------------------------------------------------------------------ okelly's Profile: http://www.excelforum.com/member.php...o&userid=36708 View this thread: http://www.excelforum.com/showthread...hreadid=572620 |
Speeding Up Excel Performance-VBA Loops
The code you have added is meaningless if these functions are being used in
the worksheet. Why meaningless - because the functions don't affect any of those settings. I would take those settings out of your functions. You need to go to Tools=Options=Calculate tab and set calculation to manual. -- Regards, Tom Ogilvy "okelly" wrote: Hi everyone, I've been follwoing the thread on speeding up Excel & slow performance from VBA loops. http://www.excelforum.com/showthread.php?t=572195 My workbooks are still "Calculating Cells" every time I update a cell or drop/drag a new formula. I've set OptionsToolsCalculations to the default "Automatic" setting. (screenshot attached) All my code is in a single "Module1" . Any feedback is appreciated. I'm pretty new to this. Thanks Conor Code: -------------------- Function OperatingSystem(pVal As String) As String ' Check server for operating system and return Operating system parent type With Application .ScreenUpdating = False myCalc = .Calculation .Calculation = xlCalculationManual .EnableEvents = False .DisplayAlerts = False End With If InStr(pVal, "Windows") Then OperatingSystem = "Windows" ElseIf InStr(pVal, "Window") Then OperatingSystem = "Windows" ElseIf InStr(pVal, "Win") Then OperatingSystem = "Windows" ElseIf InStr(pVal, "Solaris") Then OperatingSystem = "SUN/Solaris" ElseIf InStr(pVal, "Sun") Then OperatingSystem = "SUN/Solaris" ElseIf InStr(pVal, "Linux") Then OperatingSystem = "Linux" ElseIf InStr(pVal, "Red Hat") Then OperatingSystem = "Linux" ElseIf InStr(pVal, "RHEL") Then OperatingSystem = "Linux" ElseIf InStr(pVal, "EL") Then OperatingSystem = "Linux" ElseIf InStr(pVal, "HP-UX") Then OperatingSystem = "HP-UX" ElseIf InStr(pVal, "11") Then OperatingSystem = "HP-UX" ' if not found return a value of other Else OperatingSystem = "Other" End If With Application .ScreenUpdating = True .EnableEvents = True .DisplayAlerts = True .Calculation = myCalc End With End Function Function Add10ServerType(pVal As String) As String ' Search server model and determine its billing ' category under Addendum 10 ' Mark all HP Proliant DL3xx family as small servers ' Mark all HP Proliant DL5xx family as medium servers ' If category doesn't match then return as value ' of Non-defined HW ' Code by HP Ericsson Finance & Operations Team With Application .ScreenUpdating = False myCalc = .Calculation .Calculation = xlCalculationManual .EnableEvents = False .DisplayAlerts = False End With If InStr(pVal, "DL38") Then Add10ServerType = "Small" ElseIf InStr(pVal, "rx2620") Then Add10ServerType = "Small" ElseIf InStr(pVal, "rp3440") Then Add10ServerType = "Small" ElseIf InStr(pVal, "DL58") Then Add10ServerType = "Medium" ElseIf InStr(pVal, "rx4640") Then Add10ServerType = "Medium" ElseIf InStr(pVal, "rp4440") Then Add10ServerType = "Medium" ElseIf InStr(pVal, "rp8420") Then Add10ServerType = "Large" ElseIf InStr(pVal, "V240") Then Add10ServerType = "Small" ElseIf InStr(pVal, "V440") Then Add10ServerType = "Medium 2" ElseIf InStr(pVal, "V490") Then Add10ServerType = "Medium 3" ElseIf InStr(pVal, "V890") Then Add10ServerType = "Large" ElseIf InStr(pVal, "T2000") Then Add10ServerType = "Medium" ' if model not found then return a value of non-defined hw Else Add10ServerType = "Non-predefined hw" End If With Application .ScreenUpdating = True .EnableEvents = True .DisplayAlerts = True .Calculation = myCalc End With End Function Function Server(pVal As String, pVal2 As String) As String With Application .ScreenUpdating = False myCalc = .Calculation .Calculation = xlCalculationManual .EnableEvents = False .DisplayAlerts = False End With If InStr(pVal, "DL380") And InStr(pVal2, "HP") Then Server = "HP Proliant DL380" ElseIf InStr(pVal, "DL340") And InStr(pVal2, "HP") Then Server = "HP Proliant DL340" ElseIf InStr(pVal, "DL580") And InStr(pVal2, "HP") Then Server = "HP Proliant DL580" Else Server = "Hardware Not Defined" End If With Application .ScreenUpdating = True .EnableEvents = True .DisplayAlerts = True .Calculation = myCalc End With End Function Function ServerType(pVal As String) As String ' Search server model and determine its billing With Application .ScreenUpdating = False myCalc = .Calculation .Calculation = xlCalculationManual .EnableEvents = False .DisplayAlerts = False End With If InStr(pVal, "Proliant") Then ServerType = "HP Proliant" ElseIf InStr(pVal, "DL32") Then ServerType = "HP Proliant" ElseIf InStr(pVal, "DL36") Then ServerType = "HP Proliant" ElseIf InStr(pVal, "DL38") Then ServerType = "HP Proliant" ElseIf InStr(pVal, "DL56") Then ServerType = "HP Proliant" ElseIf InStr(pVal, "DL58") Then ServerType = "HP Proliant" ElseIf InStr(pVal, "RP44") Then ServerType = "rp4440rx4640" ElseIf InStr(pVal, "rp44") Then ServerType = "rp4440rx4640" ElseIf InStr(pVal, "RX46") Then ServerType = "rp4440rx4640" ElseIf InStr(pVal, "rx46") Then ServerType = "rp4440rx4640" ElseIf InStr(pVal, "RP34") Then ServerType = "rp3440rx2620" ElseIf InStr(pVal, "rp34") Then ServerType = "rp3440rx2620" ElseIf InStr(pVal, "RX26") Then ServerType = "rp3440rx2620" ElseIf InStr(pVal, "rx26") Then ServerType = "rp3440rx2620" ElseIf InStr(pVal, "SUN") Then ServerType = "Sun" ElseIf InStr(pVal, "Sun") Then ServerType = "Sun" ElseIf InStr(pVal, "Sun Fire") Then ServerType = "Sun" ElseIf InStr(pVal, "V24") Then ServerType = "Sun" ElseIf InStr(pVal, "V44") Then ServerType = "Sun" ElseIf InStr(pVal, "V49") Then ServerType = "Sun" ElseIf InStr(pVal, "T2000") Then ServerType = "Sun" ' if model not found then return a value of other Else ServerType = "Other" End If With Application .ScreenUpdating = True .EnableEvents = True .DisplayAlerts = True .Calculation = myCalc End With End Function -------------------- +-------------------------------------------------------------------+ |Filename: screen.jpg | |Download: http://www.excelforum.com/attachment.php?postid=5218 | +-------------------------------------------------------------------+ -- okelly ------------------------------------------------------------------------ okelly's Profile: http://www.excelforum.com/member.php...o&userid=36708 View this thread: http://www.excelforum.com/showthread...hreadid=572620 |
Speeding Up Excel Performance-VBA Loops
Thank for this, It seems a little crazy though to hit F9 after every macro
run. Is there not another way that I can get rid of this calculation problem? What do you attribute this to? Is it perhaps the processing speed of the PC / NB? -- Thank you in advance for your Help "Tom Ogilvy" wrote: The code you have added is meaningless if these functions are being used in the worksheet. Why meaningless - because the functions don't affect any of those settings. I would take those settings out of your functions. You need to go to Tools=Options=Calculate tab and set calculation to manual. -- Regards, Tom Ogilvy "okelly" wrote: Hi everyone, I've been follwoing the thread on speeding up Excel & slow performance from VBA loops. http://www.excelforum.com/showthread.php?t=572195 My workbooks are still "Calculating Cells" every time I update a cell or drop/drag a new formula. I've set OptionsToolsCalculations to the default "Automatic" setting. (screenshot attached) All my code is in a single "Module1" . Any feedback is appreciated. I'm pretty new to this. Thanks Conor Code: -------------------- Function OperatingSystem(pVal As String) As String ' Check server for operating system and return Operating system parent type With Application .ScreenUpdating = False myCalc = .Calculation .Calculation = xlCalculationManual .EnableEvents = False .DisplayAlerts = False End With If InStr(pVal, "Windows") Then OperatingSystem = "Windows" ElseIf InStr(pVal, "Window") Then OperatingSystem = "Windows" ElseIf InStr(pVal, "Win") Then OperatingSystem = "Windows" ElseIf InStr(pVal, "Solaris") Then OperatingSystem = "SUN/Solaris" ElseIf InStr(pVal, "Sun") Then OperatingSystem = "SUN/Solaris" ElseIf InStr(pVal, "Linux") Then OperatingSystem = "Linux" ElseIf InStr(pVal, "Red Hat") Then OperatingSystem = "Linux" ElseIf InStr(pVal, "RHEL") Then OperatingSystem = "Linux" ElseIf InStr(pVal, "EL") Then OperatingSystem = "Linux" ElseIf InStr(pVal, "HP-UX") Then OperatingSystem = "HP-UX" ElseIf InStr(pVal, "11") Then OperatingSystem = "HP-UX" ' if not found return a value of other Else OperatingSystem = "Other" End If With Application .ScreenUpdating = True .EnableEvents = True .DisplayAlerts = True .Calculation = myCalc End With End Function Function Add10ServerType(pVal As String) As String ' Search server model and determine its billing ' category under Addendum 10 ' Mark all HP Proliant DL3xx family as small servers ' Mark all HP Proliant DL5xx family as medium servers ' If category doesn't match then return as value ' of Non-defined HW ' Code by HP Ericsson Finance & Operations Team With Application .ScreenUpdating = False myCalc = .Calculation .Calculation = xlCalculationManual .EnableEvents = False .DisplayAlerts = False End With If InStr(pVal, "DL38") Then Add10ServerType = "Small" ElseIf InStr(pVal, "rx2620") Then Add10ServerType = "Small" ElseIf InStr(pVal, "rp3440") Then Add10ServerType = "Small" ElseIf InStr(pVal, "DL58") Then Add10ServerType = "Medium" ElseIf InStr(pVal, "rx4640") Then Add10ServerType = "Medium" ElseIf InStr(pVal, "rp4440") Then Add10ServerType = "Medium" ElseIf InStr(pVal, "rp8420") Then Add10ServerType = "Large" ElseIf InStr(pVal, "V240") Then Add10ServerType = "Small" ElseIf InStr(pVal, "V440") Then Add10ServerType = "Medium 2" ElseIf InStr(pVal, "V490") Then Add10ServerType = "Medium 3" ElseIf InStr(pVal, "V890") Then Add10ServerType = "Large" ElseIf InStr(pVal, "T2000") Then Add10ServerType = "Medium" ' if model not found then return a value of non-defined hw Else Add10ServerType = "Non-predefined hw" End If With Application .ScreenUpdating = True .EnableEvents = True .DisplayAlerts = True .Calculation = myCalc End With End Function Function Server(pVal As String, pVal2 As String) As String With Application .ScreenUpdating = False myCalc = .Calculation .Calculation = xlCalculationManual .EnableEvents = False .DisplayAlerts = False End With If InStr(pVal, "DL380") And InStr(pVal2, "HP") Then Server = "HP Proliant DL380" ElseIf InStr(pVal, "DL340") And InStr(pVal2, "HP") Then Server = "HP Proliant DL340" ElseIf InStr(pVal, "DL580") And InStr(pVal2, "HP") Then Server = "HP Proliant DL580" Else Server = "Hardware Not Defined" End If With Application .ScreenUpdating = True .EnableEvents = True .DisplayAlerts = True .Calculation = myCalc End With End Function Function ServerType(pVal As String) As String ' Search server model and determine its billing With Application .ScreenUpdating = False myCalc = .Calculation .Calculation = xlCalculationManual .EnableEvents = False .DisplayAlerts = False End With If InStr(pVal, "Proliant") Then ServerType = "HP Proliant" ElseIf InStr(pVal, "DL32") Then ServerType = "HP Proliant" ElseIf InStr(pVal, "DL36") Then ServerType = "HP Proliant" ElseIf InStr(pVal, "DL38") Then ServerType = "HP Proliant" ElseIf InStr(pVal, "DL56") Then ServerType = "HP Proliant" ElseIf InStr(pVal, "DL58") Then ServerType = "HP Proliant" ElseIf InStr(pVal, "RP44") Then ServerType = "rp4440rx4640" ElseIf InStr(pVal, "rp44") Then ServerType = "rp4440rx4640" ElseIf InStr(pVal, "RX46") Then ServerType = "rp4440rx4640" ElseIf InStr(pVal, "rx46") Then ServerType = "rp4440rx4640" ElseIf InStr(pVal, "RP34") Then ServerType = "rp3440rx2620" ElseIf InStr(pVal, "rp34") Then ServerType = "rp3440rx2620" ElseIf InStr(pVal, "RX26") Then ServerType = "rp3440rx2620" ElseIf InStr(pVal, "rx26") Then ServerType = "rp3440rx2620" ElseIf InStr(pVal, "SUN") Then ServerType = "Sun" ElseIf InStr(pVal, "Sun") Then ServerType = "Sun" ElseIf InStr(pVal, "Sun Fire") Then ServerType = "Sun" ElseIf InStr(pVal, "V24") Then ServerType = "Sun" ElseIf InStr(pVal, "V44") Then ServerType = "Sun" ElseIf InStr(pVal, "V49") Then ServerType = "Sun" ElseIf InStr(pVal, "T2000") Then ServerType = "Sun" ' if model not found then return a value of other Else ServerType = "Other" End If With Application .ScreenUpdating = True .EnableEvents = True .DisplayAlerts = True .Calculation = myCalc End With End Function -------------------- +-------------------------------------------------------------------+ |Filename: screen.jpg | |Download: http://www.excelforum.com/attachment.php?postid=5218 | +-------------------------------------------------------------------+ -- okelly ------------------------------------------------------------------------ okelly's Profile: http://www.excelforum.com/member.php...o&userid=36708 View this thread: http://www.excelforum.com/showthread...hreadid=572620 |
All times are GMT +1. The time now is 12:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com