VBA richtig einsetzen

[Binary91]

Guten Abend zusammen,

ich arbeite an einer größeren Exceldatei mit zahlreichen Tabellen, die teilweise über mehrere tausend Zeilen dieselben Formeln repetitiv nutzen, um Zeile für Zeile von Usereingaben zu prüfen.

Weiterhin habe ich quasi ein "Setup"-Datenblatt mit diversen Kumulativberechnungen und Bits, die vom User gesetzt werden können.

Da ich mittlerweile für einige Berechnungen kaum mehr um die Nutzung von VBA herumkomme frage ich mich aktuell, für welche Art Aufgaben ich VBA am geeignesten einsetzen kann.

Ist es sinnvoll, große und repetitive Formelkalkulationen weiterhin mit excelinternen Formeln zu lösen und VBA lediglich für das "I-Tüpfelchen" zu verwenden, um gewisse Aktionen sicher und unumständlich durchzuführen oder kann bzw. sollte ich VBA auch nutzen, um mit großen Funktionen und Klassen ebenfalls die großen Datentabellen nach jeder Usereingabe neu zu kalkulieren?

Gibt es hierbei Performanceunterschiede je nachdem, was man tut oder kommt das letztlich auf dieselbe Rechenzeit heraus, einmal angenommen, man programmiert auf beide Varianten gleich gut oder schlecht?

Über ein paar Vorschläge und Kritik würde ich mich freuen!

LG Binary

[Käpt'n Blaubär]

Hallo,

in Deiner Beschreibung finde ich nicht den geringsten Ansatz, Deine Fragen beantworten zu können.
Ihre Berechtigung haben Formeln und VBA. Vielleicht stellst Du uns mal eine Beispieldatei vor.
Damit könnte zumindest ein wenig klarer werden, worüber Du überhaupt sprichst.
Mit VBA ist es zwingend nötig, zu wissen, was das Programm leisten soll.

[LuckyJoe]

Hallo Binary,

da schließe ich mich Peters Meinung an: nach deinen ausschmückenden Beschreibungen ist im Kern nicht wirklich klar, was genau du vorhast.

VBA und Formeln haben beide ihre Daseinsberechtigung und schließen sich nicht gegenseitig aus. Umfangreichere Arbeiten lassen sich dabei dann eher mit VBA lösen, wenn Formeln nicht mehr ausreichen oder Formeln zu unübersichtlich werden. Dabei spielen die persönlichen Vorlieben sicherlich auch eine Rolle. Ich selber schreibe lieber eine kleine VBA-Routine, wenn Formeln "meterlang" werden und ich nach gewisser Zeit nicht mehr genau nachvollziehen kann, wass denn genau die Formel da eigentlich macht. Umgekehrt gibt es geradezu begnadete "Künstler", die Aufgabenstellungen mit pfiffigen Formeln lösen (so z.B. www.herber.de/excelformeln und bitte suchen .../).

Ohne konkrete Vorstellung über dein Projekt und den einzelnen Aufgabenstellungen (Beispieldatei) läßt sich deine Frage nicht beantworten.

[Binary91]

Guten Tag zusammen,

erst einmal ein herzliches Dankeschön für eure Rückmeldung.

Da meine Datei sehr groß ist und ich sie nur ungern veröffentliche, kann ich das Projekt selbst nicht hochladen.
Ich versuche es anhand einer Beschreibung zu erklären, was genau das Projekt tut und hoffe, dass ihr damit etwas anfangen könnt. Es geht ja nicht um konkrete Lösungsansätze, sondern lediglich um Prinzipien des Projektmanagements, wann VBA und wann Excel zu verwenden wäre. Ich meine das nicht als "entweder" / "oder", sondern als Kombilösung mit der Frage: Was für welche Aufgaben.

Also, das Projekt besteht aus 3 Tabellen. Tabelle 1 ist eine Setup- bzw. Profiltabelle, in welcher sowohl Personalien als auch Kumulativstatistiken aus der Haupttabelle (Log) angezeigt und bearbeitet werden können. Jede Zeile dort ist individuell und teilweise in der Berechnung sehr komplex.

Tabelle 2 ist die Hauptinput-Tabelle, quasi ein Logbuch mit tausenden Zeilen und etwa 15 Spalten, in welcher der Nutzer je Zeile einen Log-Datensatz eintragen kann. Spalte 'A' ist die (automatisch errechnete) ID, Spalte 'B' das Datum, Spalte 'C' - 'O' dann weitere Kategorien, teils DDL-basierte Texteingaben, teils Zahlenwerte etc.

Da für diese Tabelle sowie für die Kumulativberechnungen enorm wichtig ist, dass Daten korrekt eingegeben werden, muss jede Zelle eines jeden Datensatzes intern über eine Formel kontrolliert werden. Dies geschieht mit weiteren, ausgeblendeten Spalten 'P' bis 'Z'. Ein Beispiel: Die korrespondierende Prüfformel für Spalte 'B' testet a) Wurde ein gültiges Datum eingegeben? b) liegt das Datum auch nach dem Geburtsdatum und c) liegt das Datum vor einem in Tabelle 3 definiertem Enddatum.

Wie man sieht, sind die Formeln in dieser Tabelle repetitiv, da sie Zeile für Zeile dasselbe tun.

Tabelle 3 ist eine interne, ebenfalls ausgeblendete Tabelle mit Vorlagen für DDLs, Grundeinstellungen etc.

Da ich in Tabelle 1 mit excelinternen Formeln teilweise sehr an meine Grenzen stoße, habe ich mich entschieden hierfür auf VBA zurückzugreifen. Da ich daher nun ohnehin ein VBA-basiertes Dokument habe, würde es mich interessieren ob es Sinn macht, auch alle Berechnungen aus der Log-Tabelle per VBA zu lösen.
Hierbei frage ich mich nun, ob es bei diesen zahlreichen Berechnungen einen Performanceunterschied gibt, wenn ich jede Änderung per VBA abfange und dann über große Schleifen die ganze Tabelle durchrechne oder ob es schneller geht, das mit Excel zu tun.

Um gleich vorweg zu greifen: Ich weiß, dass selbst bei gut programmierten Formeln Excel eigentlich nicht für derartige Aufgaben ausgelegt ist und dass ich mit einer Datenbank wie Access oder SQL besser bedient wäre. Leider lässt sich das aktuell nicht umsetzen, das hat Nutzergründe.

Mein aktueller Ansatz wäre der, sämtliche Usereingaben über Formulare per VBA zu handeln und direkt zu prüfen und lediglich die weitere repetitive Auswertung (für bedingte Formatierungen etc. nötig) in der Log-Tabelle über excelinterne Formeln durchzuführen. Somit würde ich mir bereits sämtliche Überprüfungen sparen und deutlich an Rechenzeit einsparen.
Ob es aber nochmal Einsparung bzw. Performancegewinn wäre, auch diese Formeln über VBA zu ersetzen? Ich hatte einmal versucht, eine große Tabelle komplett in eine Schleife zu laden und im Anschluss, ähnlich einer Sortierfunktion, alle Daten neu in die Tabelle einzutragen. Dies hat das Programm mal schnell für 2min lahmgelegt, was entweder an meinen Programmierkünsten liegt oder aber an der Tatsache, dass man hierfür möglicherweise eher Excel selbst arbeiten lässt.

Ich würde mich dennoch über ein paar Denkanregungen sehr freuen.

Freundliche Grüße
Binary

[Klaus-Dieter]

Hallo,

man kann das sicher nicht pauschal beantworten, zumal wenn man deine Datei nicht kennt.

Wenn sich auf einem Tabellenblatt viele Formeln befinden, die möglicherweis volatil sind, kann sich das sehr auf die Performance auswirken. Hier wäre VBA sicher von Vorteil. Persönlich mache ich damit sehr viel, weil es dann keine Formeln auf dem Tabellenblatt gibt, die kann dann auch keiner zerstören. Das spricht aus meiner Sicht sehr für VBA. Dazu gibt es aber auch gegenteilige Auffassungen.

[LuckyJoe]

Hi,

... Dies geschieht mit weiteren, ausgeblendeten Spalten 'P' bis 'Z'. Ein Beispiel: Die korrespondierende Prüfformel für Spalte 'B' testet a) Wurde ein gültiges Datum eingegeben? b) liegt das Datum auch nach dem Geburtsdatum und c) liegt das Datum vor einem in Tabelle 3 definiertem Enddatum.

Ich glaube, für solche Prüfungen (insbesondere, wenn dafür extra 11 Spalten ausgeblendet werden müssen - und das bei tausenden Zeilen ... und dann vielleicht auch noch bedingte Formatierungen...) eignet sich VBA. Bei entsprechender Kenntnis kannst du dort viel übersichtlicher (und meiner Meinung nach auch viel einfacher) solche Prüfungen durchführen lassen. Außerdem sind mit VBA Ausnahmen einfacher umzusetzen.

Ob dabei auch umfangreichere Berechnungen an/mit der Tabelle sinnvoll sind, kommt immer auf den Einzelfall und deine Programmierkenntnisse an. 

Ich hatte einmal versucht, eine große Tabelle komplett in eine Schleife zu laden und im Anschluss, ähnlich einer Sortierfunktion, alle Daten neu in die Tabelle einzutragen. Dies hat das Programm mal schnell für 2min lahmgelegt ...

Berechnungen in einer Schleife sind oftmals sehr zeitaufwendig, wenn sie "unsauber" programmiert wurden. Für Schleifen gibt es aber auch Alternativen, die wesentlich schneller sind ("With ... End With"-Anweisungen, Array-Berechnungen usw). Auch gibt es Möglichkeiten, bestimmte Ereignisse vor solchen Berechnungen aus- und später wieder einzuschalten.

Am einfachsten ist es, du fängst einfach mal an, das ein oder andere in VBA umzusetzen und schaust dir dann die Performance und vor allem die Übersichtlichkeit deiner Konstrukte an.

Viel Erfolg.

[schauan]

Hallöchen,

neben den bereits gegebenen Hinweisen kann man auch überlegen, ob man die Eingaben Zeile für Zeile in der umfangreichen Tabelle vornimmt oder ob man selbige per Userform oder auch in einer weiteren Tabelle vornimmt und dann in die Gesamttabelle einträgt.

Wenn Du die Eingaben z.B. in einer Zeile eines zusätzlichen Tabellenblattes vornimmst benötigst Du die Prüfungen dann nur in dieser einen Zeile. Programmtechnisch hast Du dann vielleicht nur noch einen Button der Dir die Daten überträgt. Änderungen könntest Du dort auch durchführen, indem Du z.B. eine eindeutige Eigenschaft wie z.B. eine Personenkennzahl o.a. eingibst und dann wiederum per VBA den Datensatz zur Änderung holst und danach wieder überträgst.

Manches geht z.B. auch mit den Funktionalitäten auf einem Tabellenblatt einfacher oder auch schneller zu erledigen, z.B. filtern oder sortieren. Da kann man auch mal irgendwelche Daten auf ein temporäres Blatt einfügen, sortieren, und dann die Daten wieder in ein Array holen oder an anderer Stelle einer Datei ausgeben.

Genau so gut kann man auch Formeln temporär eintragen, um was zu berechnen. Sind die Daten anschließend wieder statisch, kann man die Formeln durch die Ergebnisse / Werte ersetzen und hat danach wieder etwas mehr Performance.

Die Makros kann man sich zuweilen flexibel halten, indem man Einstellungen und Parameter auf einem entsprechenden Tabellenblatt ablegt und nicht codiert. Man kann dann bei Bedarf Änderungen auf dem Blatt vornehmen und braucht deswegen die codes nicht zu ändern ...

[Binary91]

Guten Morgen,

vielen Dank für die Rückmeldungen!

Berechnungen in einer Schleife sind oftmals sehr zeitaufwendig, wenn sie "unsauber" programmiert wurden. Für Schleifen gibt es aber auch Alternativen, die wesentlich schneller sind ("With ... End With"-Anweisungen, Array-Berechnungen usw). Auch gibt es Möglichkeiten, bestimmte Ereignisse vor solchen Berechnungen aus- und später wieder einzuschalten.

Stimmt, ich gehe auch davon aus, dass ich unsauber programmiere. Ich habe mir überlegt, vor und nach solchen Berechnungen einmal per Zeitstempel die Rechenzeit ausgeben zu lassen und so verschiedene Möglichkeiten auszutesten.

neben den bereits gegebenen Hinweisen kann man auch überlegen, ob man die Eingaben Zeile für Zeile in der umfangreichen Tabelle vornimmt oder ob man selbige per Userform oder auch in einer weiteren Tabelle vornimmt und dann in die Gesamttabelle einträgt.

Wenn Du die Eingaben z.B. in einer Zeile eines zusätzlichen Tabellenblattes vornimmst benötigst Du die Prüfungen dann nur in dieser einen Zeile. Programmtechnisch hast Du dann vielleicht nur noch einen Button der Dir die Daten überträgt. Änderungen könntest Du dort auch durchführen, indem Du z.B. eine eindeutige Eigenschaft wie z.B. eine Personenkennzahl o.a. eingibst und dann wiederum per VBA den Datensatz zur Änderung holst und danach wieder überträgst.

Jawohl, genau das hatte ich mir auch überlegt! Dafür habe ich die "ID"-Spalte am Anfang der Tabelle erstellt, eben um künftig die Einträge nur noch Zeile für Zeile zu "erlauben" per UserForm. Die Tabelle an sich bliebe dann gesperrt und somit hätte ich auch maximale Sicherheit, dass mir keiner per Copy & Paste meine Formatierungen zerschießt. Super Tipp, vielen Dank!

Manches geht z.B. auch mit den Funktionalitäten auf einem Tabellenblatt einfacher oder auch schneller zu erledigen, z.B. filtern oder sortieren. Da kann man auch mal irgendwelche Daten auf ein temporäres Blatt einfügen, sortieren, und dann die Daten wieder in ein Array holen oder an anderer Stelle einer Datei ausgeben.

Ich hatte mir auch schon gedacht, dem Nutzer das Sortieren am besten selbst zu überlassen. Das kann man ja bei gesperrten Zellen dennoch freischalten. Was ich nicht weiß bzw. noch nicht selbst ausgiebig erörtert habe ist, ob sich beim Sortieren auch die Zellbezüge ändern.
Mal angenommen, ich brauche trotz allem noch 1-2 Prüfspalten im ausgeblendeten Bereich, sei es nur für bed. Formatierungen. Diese sollten ja weiterhin die korrespondierende Zeile prüfen, in der die jew. Formel auch steht. Angenommen, ich lasse nun die Tabelle sortieren (die Prüfspalten werden nicht mitsortiert). Kann es dann sein, dass eine Formel aus Zeile #3 dann auf eine Zelle in Zeile #8 verweist oder bleiben die Bezüge statisch?
Ansonsten müsste ich über INDIREKT() die Zellen per ZEILE() adressieren, das wäre dann aber whs. auch wieder deutliche Performance-Einbusse.
Die Lösung mit dem temp. Blatt muss ich mir mal näher ansehen, auch keine schlechte Idee.

Die Makros kann man sich zuweilen flexibel halten, indem man Einstellungen und Parameter auf einem entsprechenden Tabellenblatt ablegt und nicht codiert. Man kann dann bei Bedarf Änderungen auf dem Blatt vornehmen und braucht deswegen die codes nicht zu ändern ..

Das mache ich genau so in der internen Tabelle 3 :19: :19:  Ich habe quasi eine "VBA-Intitalisierungen"-Tabelle erstellt, die in einer "Initialisierungsfunktion" beim Ereignis "Workbook_Open()" aufgerufen und ausgelesen wird. Darin ist auch ein Bit enthalten, welches mir anzeigt ob das Projekt erstmalig geöffnet wurde oder nicht. Entsprechend öffnet sich ein UserForm zur erstmaligen Eingabe der Personalien.
Freut mich aber, dass ich nicht der Einzige bin, der so seinen VBA-Code initialisiert :21: 

Vielen Dank für eure Unterstützung, ich werde mal ein wenig ausprobieren.

Grüßle
Binary