You are not logged in.

  • Login

MS Excel VBA, kennt sich da wer aus?

Dear visitor, welcome to MastersForum. If this is your first visit here, please read the Help. It explains in detail how this page works. To use all features of this page, you should consider registering. Please use the registration form, to register here or read more information about the registration process. If you are already registered, please login here.

AtroX_Worf

Sage

  • "AtroX_Worf" started this thread

Posts: 11,465

Location: Hamburg

Occupation: GER

  • Send private message

1

Thursday, June 26th 2008, 4:46pm

MS Excel VBA, kennt sich da wer aus?

Habe mal ne Frage zur Performance und grundsätzlichen Machbarkeit.

Was ist besser, ein (mehrdimensionales) Array oder ein Collection-Object (oder Dictionary?)?

Bekanntlich ist ReDim Preserve Array ja sehr langsam, da dazu das gesamte Array kopiert werden muss. Collection.add ist da viel freundlicher.

Problematisch ist auch, dass ich in einem mehrdimensionalen Array nur die letzte Dimension überhaupt mit ReDim ändern kann, alle vorherigen sind fix.
Jagged Arrays (also Arrays of Arrays) zu verwenden ist etwas unpraktisch, wäre aber prinzipiell eine Option.

Ich brauche die Arrays oder was auch immer als Strukturen für Matrizen, will also viele Matrizenoperationen (zumindest Matrixmultiplikation) damit ausführen.
Wie geht dies überhaupt bei einem Collection-Object? Hat dieses eine fixe Reihenfolge über welche ich iterieren kann oder muss ich die Reihenfolge mit dem Schlüssel ansprechen? Wäre dann eventuell ein Dictionary-Object besser, damit ich die Schlüsselwerte nachträglich verändern kann?

Ich will aus Matrizen einzelne Zeilen herausstreichen oder diese hinzufügen. Bei ersterem ist natürlich die Position wichtig, bei letzterem nicht. Jetzt überlege ich, welche Datenstruktur ich dafür am besten nehmen sollte.
Wie gesagt, eindeutiges ansprechen ist wichtig, immer über den Key würde nerven und unpraktikabel sein (wenn aus der Matrix schon einige Zeilen aus der Mitte herausgestrichen wurden und am Ende neue hinzugekommen sind).

Irgendwelche Ideen oder Vorschläge?

AtroX_Worf

Sage

  • "AtroX_Worf" started this thread

Posts: 11,465

Location: Hamburg

Occupation: GER

  • Send private message

2

Friday, July 4th 2008, 12:46pm

So, habe jetzt ein bißchen rumgetestet.

Grundsätzlich iteriert man bei Collections über

Source code

 
1

for each obj in Collection
bei Arrays über den Index mit

Source code

 
1

for Index = LBound(array) to Ubound(array)
Dies ist auch aarays auch richtig schnell. Collection ist da lahmer, Vorteil ist die Bandbreite an Datentypen. Dictionary-Objecte sind hingegen wieder richtig schell.

Zur Geschwindigkeitsmessung habe ich einfach modulweit

Source code

 
1
2

Public Declare Function GetTickCount Lib "kernel32" () As Long
Public start As Long
gesetzt und kann damit in jeder Prozedur mittels

Source code

 
1
2
3

start = GetTickCount
'Code, welcher untersucht werden soll
Debug.Print "time: " & (GetTickCount - start) / 1000 & " s"
die benötige Zeit auslesen.
Meist macht es nur in Schleifen Sinn, dafür muss man etwas rumtesten, dass der Code ca. zwischen 1 und 10 Sekunden läuft. Dann kann man die Performance erhöhen. Natürlich ist diese Methode nicht sehr genau, ist die reine Rechenzeit nie für Performancemessung, aber man bekokommt ganz gute und auch stabile Ergebnisse. Wenn man Lust hat, kann man sich auch ne eigene Klasse für den Timmer mit den 2 Methoden start und stop bauen, aber bei insgesamt 4 zeilen Code halte ich das für unnötig, hinzu könnte der Overhead die Performancemessung verfälschen und ich müsste ihn erst wieder herausrechnen.

Man sollte beispielsweise immer die Maximum-Function selbst definieren mittels

Source code

 
1
2
3

Public Function max(x, y)
  If (x >= y) Then max = x Else max = y
End Function
anstatt

Source code

 
1
2
3

Public Function max(x, y)
  max = WorksheetFunction.Max(x, y)
End Function
zu benutzen. Das ist ca. 50x schneller, also von Größenordnung ln(50)/ln(10) = 1.7. Krass!
Wenn man zudem noch die Funktionen nicht als Variant sondern als explizter Datentyp (Double, Integer, Long) definiert, ist die selbstgeschriebene Function noch einmal 58% schneller - bei der Worksheet.Function gibt es dagegen keinen merklichen Geschwindigkeitsvorteil.

Ansonsten benutze ich für das Proket recht intensiv selbstdefinierte Klassen. Leider braucht man für jede neue Klasse ein neues Klassenmodul. ?( Ist jetzt nicht unbedingt so der Performancebringer, aber für die Lesbarkeit des Codes extrem hilfreich, außerdem denkt es sich so leichter in Problemlösungskategorien.
So ein größeres Projekt ist mal ne nette Erfahrung für einen, der sonst immer nur rein Lösungsorientiert programmiert hatte und deswegen nur prozedural.

Mittels der Klassenlösung kann ich auch viele 1-dimensionale Arrays verwenden. Erstens bringt dies auch wieder Geschwindigkeit, da VBA Excel 1-dim arrays sehr effizient benutzen kann, zum anderen fällt dann das resize problem nicht mehr so ins Gewicht.
Theoretisch könnte ich noch mti CopyMemory oder ähnlichen Dingen direkt an der Speicheradresse arbeiten, aber denke das ist hier nicht notwendig - wäre aber sehr schnell.

So, dass waren mal ein paar Erfahrungen, welche ich in den letzten Tagen zum Thema gemacht habe. Hilft vielleicht dem ein oder anderem.

This post has been edited 1 times, last edit by "AtroX_Worf" (Jul 4th 2008, 1:04pm)