Excel VBA Sortierug und Verweis

[Pyrukar]

Hallo,

Ich hoffe dass ich hier richtig bin und das Anliegen nicht bei Programmieren besser aufgehoben ist. Sollte das doch so sein bitte ich einen Mod das ganze zu verschieben.

ich habe ein VBA Programm und habe noch keine effektive Methode gefunden Zahlen zu sortieren und wieder zuzuordnen . Bisher schreibe ich die Daten dafür auf das Excelsheet, schmeiße einmal die Max Funktion drauf und lasse dieses Ergebnis dann via SVerweis dem richtigen Element zuordnen. Anscheinend kostet jedoch diese Frontend Berechnung Excel relativ viel Zeit.

Um zu verdeutlichen was ich möchte hier ein anschauliches Beispiel. Ich habe x Äpfel, y Birnen und z Bananen auf Lager und brauche jetzt eine Funktion die entscheidet, von welchem ich am meisten habe und dieses dann ausgibt. Also wenn z.B. y am größten ist soll in der Zielvariablen Birnen stehen nicht y.

Natürlich ist mir etwas wie Bublesort ein Begriff nur fürchte ich dass das nicht schneller ist und schlussendlich mit Kannonen auf Spatzen geschossen ist. Zum anderen weis ich durchaus, dass man die Excelfunktionen auch direkt im Programmcode aufrufen kann nur bin ich mir nicht sicher ob man damit überhaupt einen Effizienzforteil bekommt.

Hat von euch vielleicht jemand eine Idee wie man dieses Problem elegant oder noch wichtiger effizient lösen kann denn gefühlt verliere ich an dieser Stelle ca 1-2sec für einen eigentlich minimalen Rechenaufwandt. (im eigentlichen Programmcode sind es 5 Elemente die Sortiert werden müssen)

gruß

Pyrukar

 

[diablobase]

Ich würde ein Dictionary verwenden und dazu die Methode "SortDictionary" von dieser Seite (ganz unten):
http://www.cpearson.com/excel/CollectionsAndDictionaries.htm

Auch sonst sind da einige nette kleine Funktionen zu finden

 

[Pyrukar]

hmmm,

das ist genau das, was ich meine wenn ich sage ich schieße mit Kannonen auf Spatzen. Klar bei den Beispielfunktionen auf dieser Seite ist der Großteil kommentar aber dennoch ist das ein sehr ausgewachsener Algorithmus von dem ich fast schätze dass er ähnlich langsam läuft wie die Frontend berechnung ... hat da jemand vll Erfahrungswerte wie viel die Frontend Funktionen langsamer sind? gruß

Pyrukar

 

[Andreas_]

Um zu verdeutlichen was ich möchte hier ein anschauliches Beispiel. Ich habe x Äpfel, y Birnen und z Bananen auf Lager und brauche jetzt eine Funktion die entscheidet, von welchem ich am meisten habe und dieses dann ausgibt. Also wenn z.B. y am größten ist soll in der Zielvariablen Birnen stehen nicht y.

Es gibt die Methode WorksheetFunction.Max, die von bis zu 30 Werten den Maximalwert ermittelt. Es ist dafür nicht erforderlich, die Werte in ein Sheet zu schreiben.

 

[diablobase]

Nur aufgrund der Länge bzw. der angeblichen Komplexität einer Methode lässt sich bei Weitem nicht auf deren Effizienz schließen.
Grundsätzlich sind reine VB-Funktionen deutlich schneller als alles das mit den Excelobjekten arbeiten muss.
Ich habe eine kleine Testfunktion geschrieben und 1000x sortiert:

Public Sub sort()
    Dim dic As Dictionary
    Dim t As Single
    Dim i As Integer
    
    t = Timer()
    For i = 1 To 1000
        Set dic = New Dictionary
        Call dic.Add("Apfel", 5)
        Call dic.Add("Birne", 4)
        Call dic.Add("Banane", 7)
        
        Call SortDictionary(dic, False)
    Next i
    
    Debug.Print CStr(Timer() - t) & " Sekunden"
End Sub

Ergebnis:

0,078125 Sekunden

 

[Pyrukar]

so,

Problem gelöst (oder zumindest habe ich, sollte ich einen Logikfehler gemacht haben ihn noch nicht bemerkt)

ich habe die 5 Elemente (Äpfel, Birnen etc) in einen Array gepackt. Beim Ausrechnen der Werte (x,y,z) direkt einen 2er Array eingefügt der im ersten Array(1) den letzten Wert gespeichert hat und im Array(2) den dazugehörigen ZählerSchleifenwert (i). Danach habe ich die Zielvariable mit dem Element(Array(2)) vollgepackt was dann eben entsprechend Äpfel bzw Birnen sind.

dadurch braucht der Code für eine Iteration jetzt nur noch ms anstatt ca 6 sec pro Iteration ... das ist schon ziemlich heftig wie viel effizienter diese Methode ist.

Jetzt stehe ich vor einem etwas anderen Problem: Es geht so schnell dass man gar nix mehr sehen kann! ich hab jetz zwischen den Iterationen eine Wartezeit eingebaut nur gibts die anscheinend nur im Sekundentakt. Ich verwende die Application.Wait (Now + TimeValue("0:00:01")) hätte aber gerne nur ca 1/2 sec Zeitversatz zwischen den Iterationen ... ( TimeValue("0:00:00:xx")) aktzeptiert er nicht da es anscheinend auf dem Zeitformat von Excel beruht.
Kennt jemand eine Möglichkeit (Außer nimm ne Schleife, lass bis 10.000 zählen, bevor du weiter machst) ne halbe sekunde Totzuschlagen. bestenfalls ohne Prozessorauslastung *vielleicht sollte ich dafür ein neues Thema aufmachen aber es sollte ja auch nicht gespammt werden oder

gruß

Pyrukar


Edit: Sorry, diablobase, da war ich mit meiner vermeindlich trivialen Lösung wohl schneller , aber natürlich sehe ich das jetzt durchaus auch, dass nur weil viel drinn steht der code nicht zwangsläufig länger braucht ... danke für den Link zu der Samlung nur wie du vielleicht an meiner jetztigen Lösung siehst ist die deutlich kürzer und scheint auch zu funktionieren da ich ja keine Reihenfolge sondern nur das Max und die dazugehörige Zählervariable gebraucht habe um mir dann mit dieser aus dem Array die bezeichnung rauszulesen

 

[diablobase]

Nunja es stellt sich halt immer die Frage: Warum selbst neu erfinden wenn es bereits eine funktionierende allgemeine Funktion dazu gibt?
Ich habe keine 5 Minuten gebraucht um diese Seite per Google zu finden und meine Testfunktion zu schreiben.
Das ist natürlich nicht böse gemeint, sicherlich ist es immer gut zum üben und zu verstehen wie man soetwas selbst machen könnte.

Und soviel ich aus deiner Erklärung verstehe (Code wäre definitiv einfacher ) machst du schlussendlich nur dasselbe, nur eben mit einem zweidimensionalen Array statt eines Dictionaries.

Dein jetziges Problem verstehe ich allerdings nicht, warum ist es jetzt plötzlich zu schnell?
Was willst du mit der Wartezeit erreichen?

 

[Pyrukar]

also die ergebnisdaten aus dem VBA werden nach Abschluss jedes Iterationsschritts im Sheet angezeigt. Die Anzeige wird aber direkt am Anfang des neuen Iterationsschritts wieder überschrieben bzw gelöscht und nur die groben Rahmendaten werden in einer Statistik erfasst und somit konserviert. Ich möchte aber die Gesamtheit der Daten wenigstens kurz (1/2 sec) sehen können bevor sie wieder gelöscht werden ...
früher war das kein problem, da ein Iterationsschritt ca 6 sec gedauert hat und es sowieso nicht schneller ging

gruß
Pyrukar

 

[diablobase]

Verwende die Sleep-Funktion von Windows, dieser kannst du Millisekunden angeben.
Deklariere sie in einem Modul:

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

und verwende sie im Code so um 500 Millisekunden zu warten:

Call Sleep(500)

 

[Pyrukar]

cool danke