Hallo Julia,
Eins vorweg.
Ich zeige hier jetzt keine alternative für die letztens genannte Formel, so wie ich es angesprochen hatte, sondern sie wird jetzt komplett ersetzt und direkt im Makro ausgeführt.
Auch wenn es sich "nur" um eine einzige Formel handelt, die zudem auch nicht immer im Tabellenblatt eingetragen wird, so ist sie doch dafür Ideal geeignet um eine bestimmte Technik zu zeigen. Und damit meine ich
*eine IF...THEN in eine Schleife zu packen*. Du wirst nacher noch sehen und verstehen was ich damit meine. Und das ist etwas was man durchaus immer wieder mal irgendwo gebrauchen kann.
Also denn, los gehts!
Thema:
Ersatz für eine Formel
Um die Formel zu ersetzen können, muss einiges im Makro ergänzt werden. Es kommen so einige Codezeilen hinzu. Aber da die Arbeit im Arbeitsspeicher erledigt wird, und das auch nur wenn die dementsprechende Sub abgearbeitet wird, ist kein Geschwindigkeitseinbruch zu erwarten. Und außerdem wird dadurch die Ladezeit der Datei verringert, da beim öffnen keine Neuberechnung der Zellinhalte vorgenommen werden muss. Und das entlastet ja wohl den kleinen Arduino bei seiner Arbeit
Um das Ziel zu erreichen arbeite ich jetzt wieder mit Array.
Diese Formel
Code: Alles auswählen
=WENN(K11=0;"";WENN(K11=50;"Mitarbeiter";WENN(UND(K11>50;K11<100);"Sachentnahme";WENN(K11=100;"Kostenfreie Abgabe";"Sonstige"))))
soll jetzt nicht mehr im Tabellenblatt erscheinen, aber dennoch soll im Makro deren Funktion nachgebildet werden, so das in der dementsprechenden Zelle der richtige Text ausgegeben wird wie z.B.
Sachentnahme.
Auf den ersten Blick, so dachte ich zumindest für mich, stört der folgende Vergleich.
Denn wenn sich alle Wahrheitsüberprüfungen der WENN() auf z.B. (=) beziehen würden, wäre ein abzufragendes Array recht schnell aufgebaut. Aber dem ist ja nicht so, und folglich muss ein kleiner Trick herhalten.
Doch bevor es zum Trick geht, schauen wir uns noch mal kurz die Funktionsweise einer WENN(PRÜFUNG;DANN;SONST) Funktion im Tabellenblatt und einer IF ... THEN ... ELSE... END IF Anweisung an.
Sie machen beide das gleiche. Es findet eine Wahrheitsüberprüfung statt, und je nachdem wie die Überprüfung ausgefallen ist, geht es dann dementsprechend weiter.
Die Überprüfung ergibt WAHR:
Bei der Funktion gehts mit DANN weiter.
Bei der Makro Anweisung geht es mit THEN weiter.
Beide kann man sehr stark ineinander verschachteln, bei der Funktion ist ja die obige gezeigte Formel (die ja jetzt auch ersetzt werden soll) der Beweis dafür. Und auch die Makro Anweisung liese sich sehr stark verschachteln. Jedoch kann sich das unter Umständen zu einem wahrlichem *Makro-Code Monster* entwickeln. Und manchmal bleibt einem so ein Monster nicht erspart. Aber hier kann man sich mit der richtigen Überlegung einiges an Arbeit ersparen, vorausgesetzt man hat eine gewisse Erfahrung.
Die WENN-Formel hat in diesem Falle einen doch großen Vorteil, der uns sehr entgegen kommt. Da sie nur auf fest vorgegebene Werte hin ein Vergleich anstellt, lässt sich das jetzt recht leicht direkt im Makro ausführen. Anders würds aussehen, wenn verschiedene zwischenberechnungen mit variablem Ergebnis durchgeführt würden. Doch dem ist ja nicht so.
Aber zerpflücken wir erstmal die Formel in die wichtigsten Bestandteile.
Code: Alles auswählen
WENN(K11=0;"";
WENN(K11=50;"Mitarbeiter";
WENN(UND(K11>50;K11<100);"Sachentnahme";
WENN(K11=100;"Kostenfreie Abgabe";
Es fällt auf, das ich etwas ausgelassen habe, und zwar das hier.
Das hat auch seinen Grund. Denn das sind alles Überprüfungen und deren Reaktionen darauf, wenn WAHR zurückgegeben wird. Das ausgelassene jedoch kommt erst dann zum tragen, wenn FALSCH zurückgegeben wird.
Jetzt kommen wir zu dem eben angesprochenen störenden Vergleich, und zu meinem Trick.
Wie wir ja wissen, kann eine IF ... THEN z.B wie folgt aufgebaut sein.
Das heißt, es können verschiedene und/oder mehrere Vergleichsoperanten angewendet werden.
Diese Vergleichsform wollen wir auch tatsächlich so anwenden. Denn schließlich spiegelt sie das wieder, was in der WENN-Formel passiert. Dort wird ja auch auf (=) hin überprüft, aber auch einmal auf UND. Da Du ja diese Technik ja selber schon angewendet hast, brauch ich jetzt nicht mehr viel was dazu sagen.
Jetzt kommen wir zum Punkt *Array*.
Ich hatte letztens schon mal diesen Punkt kurz erklärt, und vorgeführt. Und diesmal brauchen wir 3 solcher einfachen Arrays.
Bevor es weiter geht, solltest Du ein neues Modul anlegen. Denn in dieses Modul kommt zu testzwecken alles rein was gleich noch folgt. Das hat den Vorteil das Du dadrin selber ein wenig experimentieren kannst, ohne jedesmal den Dialog auszuführen. Und wenn Du dich so weit davon überzeugt hast, das alles korrekt funktioniert, kopierst Du dann alles in das richtige Modul.
Als erstes müssen diese Arrays Deklariert werden. Doch diesmal machen wir das nicht öffentlich, also so wie bisher, sondern Lokal. Und das bedeutet, das in dem neu angelegten Modul
in der *Sub Main* Deklariert wird.
Code: Alles auswählen
Sub Main
Dim aUnterGrenze(3) as Integer, aOberGrenze(3) as Integer
Dim aRabattText(3) as String
Jetzt werden die Arrays definiert und mit Leben befüllt.
Code: Alles auswählen
aUnterGrenze(0) = 0 : aOberGrenze(0) = 0 : aRabattText(0) = " - "
aUnterGrenze(1) = 50 : aOberGrenze(1) = 50 : aRabattText(1) = "Mitarbeiter"
aUnterGrenze(2) = 50 : aOberGrenze(2) = 100 : aRabattText(2) = "Sachentnahme"
aUnterGrenze(3) = 100 : aOberGrenze(3) = 100 : aRabattText(3) = "Kostenfreie Abgabe"
Das kopierst Du genau so in die Sub rein, und zwar direkt unter der Deklaration.
Achte aber unbedingt darauf das mindestens 1 Leerzeichen VOR und HINTER dem Doppelpunkt (:) steht. Denn wenn das nicht der Fall ist, gibts garantiert Probleme.
Du siehst jetzt so betrachtet eigentlich 4 Zeilen. Aber in wirklichkeit sind das jedoch 12 Zeilen. Der Grund für diese Art der Zeilenkomprimierung ist der Doppelpunkt (:). Denn durch dem wird dem Makro
in diesem speziellen Falle gesagt, das das folgende eine weitere Zeile ist. So kann man leichter verstehen was wo zugeordnet ist. Das jetzt aber der RabattText "Sonstige" fehlt, hat wiederum seinen Grund, das wirst Du aber nachher noch verstehen warum das so ist.
Durch diese spezielle Anordnung der einzelnen Arrays kann man sich nun auch leichter merken, und vor allem auch nachvollziehen, das ein Array starke ähnlichkeit mit einem Tabellenblatt hat. Im Tabellenblatt kann man einzelne durchgehende Spalten mit einem Titel versehen, wie z.B. Untergrenze. Das ist jetzt im Array auch so, blos hier habe ich ein Array verständlicherweise als aUntergrenze benannt. Also 3 einzel Arrays mit 3 unterschiedlichen Titeln. Aber alle haben, und das ist
extrem wichtig zu beachten, die gleiche Anzahl an Zeilen.
Jetzt sind so gesehen alle grundlegenden Bedingungen erfüllt, um eine Abfrage und Auswertung zu starten, die die Formel ersetzen soll. Also ran an den Feind
Die folgende Auswertung ist nicht vollständig ausgefüllt, da Du jetzt nicht abgelenkt werden sollst. Und ferner dient das jetzt nur der Erklärung, also nicht in die Sub eingeben.
Code: Alles auswählen
iTmp = 0
For iFE = LBound(aUnterGrenze()) to UBound(aUnterGrenze())
If sgRabatt_Fl= aOberGrenze(iFE) OR (sgRabatt_Fl> aUnterGrenze(iFE) AND sgRabatt_Fl< aOberGrenze(iFE))
End If
Next iFE
If iTmp = 0 then
sZellRabatt = "Sonstige"
end If
MsgBox sZellRabatt
"Was geschieht dort?"
Als erstes habe ich, ich sags mal so rum, ein "Wechselschalter"
iTmp eingebaut. Vergleichbar mit einem Lichtschalter. Dieser hat, wie zu sehen, vor der Auswertung den Wert 0 (Null), das Licht bleibt aus.
Nun wird die Schleife (For Next) ausgeführt. Und innerhalb der Schleife wird überprüft ob die zu sehende Vergleichsbedingung zutrifft. Und wir sagen jetzt mal das alle 4 Durchläufe negativ ausgefallen sind. Und auf Grund dessen kommt bei der
IF gar nicht der
THEN Teil zum tragen, es bleibt also vorläufig im Dunkeln was dort steht.
Kurz dazwischen.
iFE heißt integerFormelErsatz (meine Kreation, die Du aber nicht übernehmen musst).
Jetzt wird wieder ein Vergleich angestellt, ob
iTmp gleich 0 (Null) ist. Und da das offensichtlich der Fall ist, wird jetzt der Variable
sZellRabatt der Text
Sonstige zugewiesen.
Anschließend wird der Inhalt der Variable in der MsgBox ausgegeben.
So, und nun fast das gleiche noch mal, aber diesmal ist die Auswertung vollständig komplett, also nix mehr ausgeblendet oder so. Und diesmal kopierst Du jetzt alles so wie zu sehen in die *Sub Main* rein.
Code: Alles auswählen
sgRabatt_Fl = '<- dort zu testzwecken verschiedene Werte eingeben'
iTmp = 0
For iFE = LBound(aUnterGrenze()) to UBound(aUnterGrenze())
If sgRabatt_Fl= aOberGrenze(iFE) OR (sgRabatt_Fl> aUnterGrenze(iFE) AND sgRabatt_Fl< aOberGrenze(iFE)) Then
iTmp = 1
sZellRabatt = aRabattText(iFE)
End If
Next iFE
If iTmp = 0 then
sZellRabatt = "Sonstige"
end If
MsgBox sZellRabatt
In die erste Zeile kannst Du, wie zu lesen, Werte eingeben um die Funktion zu überprüfen.
Prinzipiell das gleiche wie vorhin. Jedoch sagen wir jetzt diesmal, das im 2. Schleifendurchlauf der Vergleich in der
IF positiv ist und dementsprechend der
THEN Teil zum tragen kommt. Dadurch wird
iTmp auf
1 geschaltet, das Licht geht also jetzt an, und es wird jetzt der Variable
sZellRabatt der Text aus dem passenden Array
aRabattText(1) zugewiesen.
Wenn jetzt die Schleife (FOR...NEXT) fertig durchgelaufen ist, so hat ja
iTmp den Wert
1. Und es geht weiter mit dem jetzt folgenden Vergleich.
Diesmal ist
iTmp aber nicht gleich 0 (Null), wurde ja vorhin auf
1 geschaltet/gesetzt, und folglich kommt hier jetzt NICHT der
THEN Teil zum tragen. Dadurch wird jetzt die schon beschriebene Variable sZellRabatt nicht noch mal beschrieben, beziehungsweise überschrieben. Also wird jetzt der Inhalt der Variable in der MsgBox ausgegeben.
Code: Alles auswählen
For iFE = LBound(aUnterGrenze()) to UBound(aUnterGrenze())
Warum habe ich das so gemacht, und nicht einfach *0 TO 3*?
Nun, da die Arrays ordnungsgemäß Deklariert wurden, haben sie eine Untere und eine Obere Begrenzung. Und diese Begrenzungen kann man mit LBound (Untere) und UBound (Obere) auslesen lassen. Man kann sich z.B. so gesehen nicht veschreiben, also anstatt 0 to 3 hätte man 0 to 4 geschrieben. Die Folge bei dem vertuer ist nämlich der, das es zwangsläufig zu einer Fehlermeldung kommt.
Ferner besteht der Vorteil auch dadrin, das bei einer Erweiterung, oder Verkürzung des Array die Schleife nicht geändert werden muss. Man muss lediglich die Deklaration anpassen und die Array Einträge erweitern oder reduzieren. Kurzes Beispiel dazu, was lediglich zeigt wo was geändert werden muss.
Code: Alles auswählen
Dim aUnterGrenze(5) as Integer, aOberGrenze(5) as Integer
Dim aRabattText(5) as String
aUnterGrenze(0) = 0 : aOberGrenze(0) = 0 : aRabattText(0) = " - "
aUnterGrenze(1) = 50 : aOberGrenze(1) = 50 : aRabattText(1) = "Mitarbeiter"
aUnterGrenze(2) = 50 : aOberGrenze(2) = 100 : aRabattText(2) = "Sachentnahme"
aUnterGrenze(3) = 100 : aOberGrenze(3) = 100 : aRabattText(3) = "Kostenfreie Abgabe"
aUnterGrenze(4) = 150 : aOberGrenze(4) = 200 : aRabattText(4) = "Freies Gelage"
aUnterGrenze(5) = 200 : aOberGrenze(5) = 300 : aRabattText(5) = "Kater Stimmung"
Die Auswertung bleibt unberührt, die bleibt so wie sie ist.
Jetzt muss natürlich nach erfolgreichen Tests alles noch an seinen richtigen Platz hinkommen.
Also, es kommt alles in das
Modul *Flachenverkauf*. Und zwar wie folgt.
In die
Sub Neuer_Flaschenverkauf kommt gleich zu Anfang das hier rein.
Code: Alles auswählen
Dim aUnterGrenze(3) as Integer, aOberGrenze(3) as Integer
Dim aRabattText(3) as String
aUnterGrenze(0) = 0 : aOberGrenze(0) = 0 : aRabattText(0) = " - "
aUnterGrenze(1) = 50 : aOberGrenze(1) = 50 : aRabattText(1) = "Mitarbeiter"
aUnterGrenze(2) = 50 : aOberGrenze(2) = 100 : aRabattText(2) = "Sachentnahme"
aUnterGrenze(3) = 100 : aOberGrenze(3) = 100 : aRabattText(3) = "Kostenfreie Abgabe"
Dann schmeißt Du folgende Zeile raus, einfach löschen.
Code: Alles auswählen
sFormulaRabattArt_Fl = "=IF(K" & iEnd_Row & "=0;"""";IF(K" & iEnd_Row & "=50;""Mitarbeiter"";IF(AND(K" & iEnd_Row & ">50;K" & iEnd_Row & "<100);""Sachentnahme"";IF(K" & iEnd_Row & "=100;""Kostenfreie Abgabe"";""Sonstige""))))"
Dann kommt ja der Block:
' Prüfen, ob Rabatt kleiner 0
Und nach dem dortigem
Endif kommt dann das folgende rein, also noch vor
' Ausschluss vorhandener Flaschen-Artikelnummern.
Code: Alles auswählen
iTmp = 0
For iFE = LBound(aUnterGrenze()) to UBound(aUnterGrenze())
If sgRabatt_Fl= aOberGrenze(iFE) OR (sgRabatt_Fl> aUnterGrenze(iFE) AND sgRabatt_Fl< aOberGrenze(iFE)) Then
iTmp = 1
sZellRabatt = aRabattText(iFE)
End If
Next iFE
If iTmp = 0 then
sZellRabatt = "Sonstige"
end If
Ein paar Zeilen tiefer kommt ja das hier.
Code: Alles auswählen
oAktuellesBlatt.getCellRangeByName("L" & iEnd_Row).Formula = sFormulaRabattArt_Fl
Das änderst Du nun wie folgt um.
Code: Alles auswählen
oAktuellesBlatt.getCellRangeByName("L" & iEnd_Row).String = sZellRabatt
Jetzt löscht Du das temporär eingefügte Modul, speicherst die Datei, und öffnest sie erneut.
Anschließend den Dialog aufrufen und testen ob auch mit dem Dialog die vorgenommenen Änderungen so weit alles funktioniert.
Eigentlich wollte ich dir ja noch zeigen wo Du das neue System mit dem Array noch anwenden kannst, aber ich habs mir noch mal überlegt und komme zu folgendem Entschluß: Für heute solls das gewesen sein.
Wenn Du alles so weit zufrieden stellend nachbauen konntest, dann sag bescheid, und dann hole ich das jetzt ausgelassene nach.
Viel Spaß, und Erfolg
Gruß
balu