Speeding Up Excel Performance-VBA Loops

[okelly]

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

Please Login or Register  to view this content.

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

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