Microsoft Excel

Herbers Excel/VBA-Archiv

VBA Code beschleunigen | Herbers Excel-Forum


Betrifft: VBA Code beschleunigen von: Horst
Geschrieben am: 06.01.2012 23:48:29

Hallo Excel-Freaks,

ich verwende folgenden VBA-Code in einem ziemlich großen, vielfach verknüpften .xls, der leider nur sehr langsam abläuft. Gibt es eine Möglichkeit die Funktion umzuschreiben und zu beschleunigen?

Option Explicit
Sub Berechnung()
End Sub
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With

Public Function Semivola(Bereich As Range) As Double
Dim Anzahl%, varianzsumme As Double, zelle As Range, Mittelwert As Double
With Application.WorksheetFunction
  Anzahl = .Count(Bereich)
  Mittelwert = .Average(Bereich)
  For Each zelle In Bereich
    If .IsNumber(zelle) = True Then
      If zelle < Mittelwert Then
        varianzsumme = varianzsumme + (zelle - Mittelwert) ^ 2
      End If
    End If
  Next
End With
Semivola = (varianzsumme / Anzahl) ^ 0.5

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With

End Function


Besten Dank vorab!

Gruß, Horst

  

Betrifft: AW: VBA Code beschleunigen von: Josef Ehrensberger
Geschrieben am: 07.01.2012 00:05:16


Hallo Horst,

UDF's sind nun mal um Welten langsamer als Excelfunktionen.

Events und Berechnung ausschalten bring dabei überhaupt nichts.

Probier mal diese Version.

Public Function Semivola(Bereich As Range) As Double
  Dim strRef As String
  
  Application.Volatile
  
  strRef = "'" & Bereich.Parent.Name & "'!" & Bereich.Address(0, 0)
  
  Semivola = Evaluate("(SUM(IF((" & strRef & "<AVERAGE(" & strRef & _
    "))*(ISNUMBER(" & strRef & ")),((" & strRef & "-AVERAGE(" & strRef & _
    "))^2)))/COUNT(" & strRef & "))^0.5")
  
End Function






« Gruß Sepp »



  

Betrifft: AW: VBA Code beschleunigen von: Horst
Geschrieben am: 07.01.2012 00:13:16

Servus Sepp,

hab's bereits getestet, funktioniert super schnell und perfekt wie immer!!

Hab' besten Dank!

Gruß, Horst


  

Betrifft: Der nicht blaue Teil deines Codes dürfte in der... von: Luc:-?
Geschrieben am: 07.01.2012 04:18:57

…hier dargestellten Form auch gar nicht fktionieren, Horst,
sondern höchstens Syntaxfehler liefern (Position von End Sub)! ;-)
Gruß Luc :-?
PS @Sepp: Langsamer ja, aber gleich um Welten? Das hängt doch wohl auch sehr von der Programmierg ab (nicht nur bei Evaluate-Verwendung)! Der Rest ist wohl der VBA-Kompilierungsmethode geschuldet; ist halt nicht direkt in den XlKorpus integriert, was wohl auch immer noch bedingt für einige später hinzugefügte xlFktt gilt. „Events und Berechnung ausschalten bringt dabei überhaupt nichts” bezweifle ich ebenfalls — manches lässt sich kaum anders beherrschen, egal, ob mit oder ohne udFktt (hängt natürl auch von der Hardware ab und davon, ob die xlAutomatik die richtige Berechnungsreihenfolge hinbekommt, denn nicht alles kann immer deren Logik untergeordnet wdn). Meine Erfahrung sagt mir eher, dass Application.Volatile kaum was bringt … ;-)
Dir & Horst natürlich noch ein GutsNeus! Luc :-?


  

Betrifft: AW: Der nicht blaue Teil deines Codes dürfte in der... von: Horst
Geschrieben am: 07.01.2012 13:17:55

Hallo zusammen,

die Rechenzeitersparnis bei Application.Volatile ist schon spürbar, wenngleich es etwas schneller gehen dürfte. Gibt es noch andere Möglichkeiten? Hardware ist ein Quadcore mit 2,4 Ghz und 4 GB RAM, HD 500 GB.

Interessanterweise habe ich bei obiger Formel das Problem, dass bei großen Sheets mit vielen Verknüpfungen sich manche Bereiche von Zellen nicht berechnen lassen. Beispielsweise lässt sich SEMIVOLA(B8:B4276) fehlerfrei berechnen, bei Eingabe von SEMIVOLA(B4277:4721) tritt der Fehler #Wert auf. Kopiere ich das gleiche Sheet in eine neue Arbeitsmappe ist der Fehler weg. (Arbeitsspeicherproblem?)

Gruß, Horst


  

Betrifft: AW: Der nicht blaue Teil deines Codes dürfte in der... von: Josef Ehrensberger
Geschrieben am: 07.01.2012 13:32:38


Hallo Horst,

Application.Volatile hat mit der Geschwindigkeit gar nichts zu tun, es bewirkt nur, dass die UDF neu berechnet wird.

Also bei funktioniert SEMIVOLA() auch bei 100.000 Zellen ohne Probleme, allerdings langsamer als die von mir in deinem vorherigen Thread zu diesem Thema gepostete Formel-Alternative.




« Gruß Sepp »