Herbers Excel-Forum - das Archiv

Selbstentwickelte Funktionen bremsen Excel

Bild

Betrifft: Selbstentwickelte Funktionen bremsen Excel
von: Holger

Geschrieben am: 16.12.2006 14:51:09
Hallo zusammen,
ich habe mir einige Funktionen selbst in VBA erstellt, da es keine passende Excel-Funktion gab. Ich verwende diese Funktionen alle zusammen ca. 10.000 mal
auf einem Arbeitsblatt. Dies bremst mir Excel ziemlich aus wenn eine Neuberechnung durchgeführt wird. Ich verwende auf dem selben Arbeitsblatt wahrscheinlich weit über 100.000 Funktionen die Excel zu Verfügung stellt (z.B. wenn, sverweis usw.)
das hatte keine merkliche Einflüsse auf eine Neuberechnung, erst seit ich die eigenen Funktionen verwende kann sich das bis zu 10 Sekunden hinziehen!
Was könnte man dagegen tun? Vielen Dank im Voraus,
Holger
Bild

Betrifft: AW: Selbstentwickelte Funktionen bremsen Excel
von: Matthias G
Geschrieben am: 16.12.2006 14:57:01
Hallo Holger,
VBA ist deutlich langsamer als integrierte Excel-Funktionen.
Um zu sehen, ob die Funktionen zu beschleunigen sind, müsste man den Code sehen.
Gruß Matthias
Bild

Betrifft: AW: Selbstentwickelte Funktionen bremsen Excel
von: Holger

Geschrieben am: 16.12.2006 15:04:10
Hallo Matthias,
der unten abgebildete Code ist eine von vier Funktionen,
die anderen sind aber grundlegend identisch aufgebaut!
Option Explicit und Variablendeklaration innerhalb der Funktion habe ich schon mal weggelassen!
Function Serie_ggNull_max(Bereich As Range, Optional GHA As String, Optional HeimGast As Range) As Byte
'ggNull heißt größer gleich Null (>=0), GHA heißt Gesamt oder Heim oder Auswärts
Dim c As Range
Dim Zähler As Byte, Maximal As Byte
Dim Zeile As Integer, Spalte As Integer, Inhalt As String
If Funktion_Stop = True Then Exit Function
Zähler = 0
Maximal = 0
Zeile = 0
If GHA = "Heim" Or GHA = "Gast" Then Zeile = HeimGast.Row
For Each c In Bereich
Spalte = c.Column
If Zeile > 0 Then Inhalt = Cells(Zeile, Spalte) Else Inhalt = "Gesamt"
If Inhalt Like GHA Or Inhalt Like "Gesamt" Then
If c.Value <> "" And c.Value <> "-" And c.Value > 0.1 Then Zähler = Zähler + 1
If c.Value <> "" And (c.Value Like "-" Or c.Value < 0.1) Then Zähler = 0
If Zähler > Maximal Then Maximal = Zähler
End If
Next
Serie_ggNull_max = Maximal
End Function

Bis dann,
Holger
Bild

Betrifft: AW: Selbstentwickelte Funktionen bremsen Excel
von: Daniel Eisert

Geschrieben am: 16.12.2006 15:36:38
Hallo
Excel ist grundsätzlich langsam, wenn mit Objekten wie Range gearbeitet wird.
Wenn nur die Werte der Zellen für die Rechung der Zellen benötigt werden und nicht andere Eigenschaften wie Farben oder Formate, ist es besser, die Werte des Range-Objektes in eine Array-Variable zu kopieren und diese dann zu bearbeiten.
Das ist wesentlich schneller.
Das geht einfach mit
Dim ArrBereich
arrBereich = Bereich.value
somit hast du eine 2-dimensionale Feldvariable erzeugt, die genauso groß ist wie die Zeilen und Spalten vom Range-Objekt
um mit Schleifen zu arbeiten benötigst du noch den Befehel UBOUND(bereich,1), damit kannst du die Grösse des Arrays in der jeweiligen Dimension abfragen.
Wenn sämtliche Zellen eines Range-Objekets im Array bearbeitet werden sollen, sieht das dann so aus:
arrBereich = rngBereich.value
For z = 1 to ubound(arrBereich,1)
For s = 1 to Ubound(arrBereich,2)
Summe = Summe + arrBereich(z,s)
next s
next z

als Objekt-Schleife würde es so aussehen:
for each Zelle in rngBereich
Summe = Summe + Zelle.value
next

die For-Each-Schleife ist zwar etwas einfacher zu programmieren, aber wesentlich langsamer.
Das zurückschreiben von Werten geht genau so, allerdings müssen Array und Range-Objekt gleich gross sein.
Gruß, Daniel
ps. Option Exlpicit lässt man nie weg.
Bild

Betrifft: AW: Selbstentwickelte Funktionen bremsen Excel
von: Holger
Geschrieben am: 16.12.2006 16:09:00
Hallo Daniel,
das überfordert mich auf die Schnelle noch etwas, werd mich mal mit den von dir beschriebenen Befehlen vertraut machen.
Auf jeden Fall mal Danke,
Holger
Bild

Betrifft: AW: Selbstentwickelte Funktionen bremsen Excel
von: Ali Mente
Geschrieben am: 16.12.2006 16:21:27
Hi Excelfreund,
"die For-Each-Schleife ist zwar etwas einfacher zu programmieren, aber wesentlich langsamer."
Wie kommst du auf dieses dünne Brett? Genau andersherum ist es richtig.
mfg Ali
Bild

Betrifft: AW: Selbstentwickelte Funktionen bremsen Excel
von: Daniel Eisert

Geschrieben am: 16.12.2006 16:52:02
Hallo Ali
wenn die Einzel-Zellen des Range-Objekts bearbeitet werden sollen, ist For-Each sicherlich schneller als eine normale Schleife mit anschließender indizierung der Objekte (Range(x,y).value)
hier werden aber erst die Werte der Einzelzellen in eine Array-Variable kopiert und dann wird diese Array-Variable mit Schleifen abgearbeitet anstelle des Range-Objekts.
DAS ist wesentlich schneller als die Bearbeitung eines Range-Objekts, egal ob das Range-Objekt mit For-Each oder normal mit Indizierung abgearbeitet wird.
Das sich auch die Array-Variable mit For-Each bearbeiten lässt, wäre mir neu, falls es schneller ist und funktioniert werde ich es gerne übernehmen.
Allerdings wüsste ich dann gerne, wie die Syntax dazu aussieht.
gruß, Daniel
Bild

Betrifft: AW: Selbstentwickelte Funktionen bremsen Excel
von: Ali Mente

Geschrieben am: 16.12.2006 17:27:05
Hi Excelfreund,
eine For Each Next Schleife über ein Rangeobject mit einer For Next Schleife über ein Array zu vergleichen, ist wie das Spielchen mit Äpfeln und Birnen.
For each über ein Array geht auch, aber nur wenn es vom Datentyp Variant ist,
schneller gehts mit VBA nicht.
50.000 Werte
For each Next : 0,42 Sek.
For Next : 0,65 Sek.
For each(Array): 0,02 Sek.
mfg Ali
Bild

Betrifft: AW: Selbstentwickelte Funktionen bremsen Excel
von: Daniel Eisert

Geschrieben am: 16.12.2006 19:11:01
Hallo
das For Each auch mit Arrays geht, ist natürlich schön.
Aber hier ging es ja erstmal um das Problem Objekt-Variable, was ja ganz offensichtlich die
Hauptbremse ist.
Der Zeitunterschied zwischen For und ForEach macht Faktor 1,4 und von Objekt auf Variable immerhin 32.
Wie groß ist denn der Zeitunterschied bei Zwischen For-Next und For-Each bei Anwendung auf Array-Variablen?
Gruß, Danuiel
Bild

Betrifft: AW: Selbstentwickelte Funktionen bremsen Excel
von: Ali Mente
Geschrieben am: 16.12.2006 19:26:43
Hi Excelfreund,
das kommt nun wieder darauf an, wie die Schleife durchlaufen wird.
Lbound to Ubound langsamer als 1 to 50000.
mfg Ali
Bild

Betrifft: AW: Selbstentwickelte Funktionen bremsen Excel
von: Daniel Eisert
Geschrieben am: 16.12.2006 19:42:35
Hi,
lohnt es sich dann, bei variablen Feldgrössen Ubound und Lbound vor der Verwendung in der Schleife in eine Variable zu schreiben oder ist das dann egal?
und wie messe ich die Zeitunterschiede, wenn sie im 1/100-sec. Bereich liegen?
Gruß, Daniel
Bild

Betrifft: AW: Selbstentwickelte Funktionen bremsen Excel
von: Matthias G
Geschrieben am: 16.12.2006 19:47:24
Hallo Daniel,
für deine eigenen Experimente:
Sub Zeitnahme()
Dim i
Dim t1 As Single, t2 As Single
'Start:
t1 = Timer
'Dein Testcode
For i = 1 To 10000000: Next i
'Stop:
t2 = Timer
'Ergebnis:
Debug.Print t2 - t1
End Sub

Gruß Matthias
Bild

Betrifft: Danke owt
von: Daniel Eisert
Geschrieben am: 16.12.2006 20:04:41
Bild

Betrifft: AW: Selbstentwickelte Funktionen bremsen Excel
von: Ali Mente

Geschrieben am: 16.12.2006 20:23:05
Hi,
"lohnt es sich dann, bei variablen Feldgrössen Ubound und Lbound vor der Verwendung in der Schleife in eine Variable zu schreiben oder ist das dann egal?"
Es lohnt sich immer, Variable sind dem System schon beim Kompilieren bekannt, andernfalls werden die Werte erst zur Laufzeit ermittelt, das kostet Zeit.
Ich verwende die API GetTickcount, geht aber auch mit Timer, wenn es supergenau sein
muss eine eigene Timerklasse.
mfg Ali
Bild

Betrifft: AW: Selbstentwickelte Funktionen bremsen Excel
von: Yal

Geschrieben am: 16.12.2006 21:16:21
Hi,
Dass eine Variable bei der Kompilierung bekannt ist, ist ok. Das Inhalt diese Variable ist aber im voraus nicht bekannt. Sie muss auch zur Laufzeit gelesen werden.
Ausserdem bei einem
For i = LBound(arr1) to UBound(arr1)
werden die Werte LBound und UBound nur einmal bei der Initialisierung der For-Schleife ermittelt und nicht bei jedem Schleifendurchlauf. Es kostet daher unwesentlich mehr Zeit.
Beweis:
Sub Test()
Dim i%, j
j = 100
For i = 1 To j
j = j - 1
Next i
Debug.Print j
End Sub

Wenn das "To j" jedesmal neu ermittelt wäre, würde j am Ende 50 sein. Ist aber 0!: die Schleife lauft 100 mal. Unabhängig davon, wie j sich verändert.
Wenn ich dabei an diese interessante Diskution beitragen darf...
Viele Grüße
Yal
Bild

Betrifft: AW: Selbstentwickelte Funktionen bremsen Excel
von: Ali Mente

Geschrieben am: 16.12.2006 21:39:40
Hi,
genau das ist der Grund, warum an Zählvariablen auch nicht "rumgemacht" werden sollte.
mfg Ali
Bild

Betrifft: Woher hast du das mit "For Each",...
von: Luc:-?
Geschrieben am: 16.12.2006 19:27:19
...Daniel?
Ich hab (vielleicht auch bei PHP oder JS) genau das Gegenteil gehört!
Gruß Luc :-?
Bild

Betrifft: Inzwischen Ali gelesen! ;-) - erledigt + Gruß owT
von: Luc:-?
Geschrieben am: 16.12.2006 19:32:22
:-?
Bild

Betrifft: AW: Woher hast du das mit "For Each",...
von: Daniel Eisert

Geschrieben am: 16.12.2006 19:38:17
ganz einfach:
For Each mit Objekt ist langsamer als For mit Array
oder ist das etwa falsch?
und um nochmal bezug auf die Ausgangsproblemstellung zu kommen, wenn ich im Schleifendurchlauf an irgendeiner Stelle die genauere Position benötige (Zeile oder Spalte), kann ich die dann auch beim For-Each-Schleifendurchlauf im Array abfragen oder muß ich dann doch mit Schleifenzählern arbeiten?
Gruß, Daniel
Bild

Betrifft: Das hat MichaV schon mal hier oder bei...
von: Luc:-?

Geschrieben am: 16.12.2006 22:57:02
...Online-Excel gefragt, Daniel,
und man antwortete, dass alles andere aufwendiger als ein Zähler sei.
Gruß Luc :-?
PS: Das mit dem Variablentyp Variant steht übrigens auch in der VBA-Hilfe!
 Bild
Excel-Beispiele zum Thema "Selbstentwickelte Funktionen bremsen Excel"
Alle Prozeduren und Funktionen auflisten Handhabung von Arrays in benutzerdefininierten Funktionen
Variablen auf mehrere Arten in Funktionen integrieren Eine Zusammenstellung von Matrix-Funktionen