Speeding Up Excel Performance-VBA Loops

O

okelly

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 Options>Tools>Calculations 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 |
+-------------------------------------------------------------------+
 
T

Tom Ogilvy

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.
 
H

Hans Dummer

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Lookup Multiple text in String 3
macro - small change 5
Unhide sheet, copy and rename new sheets from list, rehide sheet 8
Working in Excel 2003 but not Excel 2007. Can not protect sheet. 2
VBA code disables Undo command 11
Why does this Worksheet Calculate Event run so slow? 6
this code crashes excel. How come? 3
Check Format 3

Top