[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

RE: Speeding Up Excel Performance-VBA Loops

Hans Dummer

12/12/2006 9:48:00 AM

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.ph...
> >
> > 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?p... |
> > +-------------------------------------------------------------------+
> >
> > --
> > okelly
> > ------------------------------------------------------------------------
> > okelly's Profile: http://www.excelforum.com/member.php?action=getinfo&us...
> > View this thread: http://www.excelforum.com/showthread.php?threa...
> >
> >