Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Speeding Up Excel Performance-VBA Loops
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default 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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Beginner-need help creating speeding fines system with butto Snake Excel Worksheet Functions 3 April 7th 10 03:25 PM
Speeding up Excel calculations JP Ronse Excel Worksheet Functions 11 March 1st 10 03:23 PM
Speeding up Excel 2003 yogart Excel Discussion (Misc queries) 3 August 30th 08 01:21 AM
help with speeding this up... Simon Excel Programming 16 April 26th 05 03:25 AM
excel 97: if then and for next loops help JMCN Excel Programming 2 November 10th 03 02:24 PM


All times are GMT +1. The time now is 06:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement