“Excel-Tabellen gehören wahrscheinlich zu den gefährlichsten Erfindungen der letzten Jahrzehnte.” [Rolf Dobelli]
Excel VBA Eine Sammlung
Dies ist eine Sammlung von Excel VBA Programmen und auch einigen Excelformeln, die mir sinnvoll erschienen.
Hinweis: Ich habe die hier vorgestellten Programme und Formeln erfolgreich mit Excel 2024 / 365 getestet.
Inhaltsverzeichnis
Excel / VBA – Eine Sammlung 1
Abstract 2
Excel Lernpfad 2
Die Excel / VBA Programmierumgebung 11
Abstract 11
Grundlegendes 11
Variablen 11
Variablentypen 11
Scope 12
Während des Editierens 12
Während der Programmausführung 13
Breakpoints 13
Fehlerbehandlung 14
Umgebungsvariablen 15
Gute Programmierpraxis 15
Seien Sie ein guter Programmierer 15
Gutes Excel und VBA Wissen 15
Programmierkonventionen 16
Säubern Sie Makroaufzeichnungen 16
Dokumentieren Sie Ihr Programm ausreichend 16
Testen Sie Ihr Programm gut 16
Protokollieren Sie Ihre Programmausführung 16
Laufzeit Optimieren 17
Module 17
Normal 17
Klassenmodule 19
Systemstatus sichern und zurückschreiben – SystemState Klasse 20
Systemstatus Variablen 20
Programmablauf dokumentieren – clsLog Klasse 23
Für und Wider 23
Parameter 24
Beispielausgabe 25
Module 26
Normal 26
Klassenmodule 31
Start / Stop OneDrive Synchronisierung 33
Exportiere einen Bereich als Grafik – sbExportRange2Picture 35
Bereichsnamen anzeigen – sbNamedRanges 38
Anzahl der Dimensionen eines Arrays – ArrayDim 38
Excel Version anzeigen – ApplicationVersion 39
Zellinformationen ausgeben – sbGetCell 40
Nächste Gleitkommazahl – sbNextFloat 46
Aufruf anderer Windows Programme am Beispiel sbZip 47
Excel Don’ts – Was man mit Excel besser sein lässt 49
Eine Tabelle mit Beispielen die man besser vermeidet 49
Zahlensysteme, Formate und Umwandlungen 50
Abstract 50
Umwandlungen und Berechnungen von Zahlen 50
Zahlen in Worten ausgeben – sbInWorten 50
Umwandlungen zwischen dem Dezimalsystem und dem Binärsystem 54
Feiertage ermitteln – IstFeiertag 59
Zahl vollständig nicht-wissenschaftlich darstellen – sbNum2Str 63
Signifikante Ziffern einer Zahl – sbNSig 64
Nummer eines Monatsnamens – sbMonatsZahl 65
Die Berechnung der Kreiszahl π 68
Pi Ausgabe auf 1000 Stellen 71
Die Berechnung der Eulerschen Zahl e 72
e Ausgabe auf 1000 Stellen 73
Literatur 74
Zahlenfolge kürzer darstellen – sbParseNumSeq 74
Rationale Zahlen = Brüche 76
Ermittle die nächstliegende rationale Zahl zu einer Gleitkommazahl – sbNRN 76
Lineare Gleichungssysteme mit rationalen Koeffizienten 79
Anteilsveränderung als Bruch 82
Monatsanteil 83
Linearkombination Ganzer Zahlen 84
Erweiterter Euklidischer Algorithmus – sbEuklid 84
Uhrzeiten 86
Arbeitszeit zwischen 2 Zeitpunkten – sbTimeDiff 86
Arbeitszeit zu einem Zeitpunkt addieren – sbTimeAdd 89
Uhrzeit für eine andere Zeitzone umwandeln – ConvertTime 92
Prüfziffern 92
Berechne oder prüfe eine Europäische Artikelnummer – sbEAN 92
Ordinalzahlen (englisch) 93
Einfache Mathematik in Formeln 94
Wie man Tabellenblattformeln analysiert 94
Datumsformeln testen 95
Erster oder letzter Wochentag im Monat 97
Derselbe Wochentag und Kalenderwoche im vorigen Jahr 98
Budgetkontrolle 99
Geringste Signifikante Ziffer erhöhen 100
Linearer Breakdown 101
Füge zwei Spalten mit Blocksatz zusammen 104
Minimum Truck Load Problem 105
Nachstehende Nullen zählen 106
Minimax Interpolation 106
Interpolation mit PERZENTIL / PROZENTRANG 108
Interpolation mit TREND 110
REFA Zeitklassen 111
Rollen und Rechte 112
Rundungstricks 113
Trinkgeld Verteilung 114
Unterjährige Werte glätten 116
Zellenbasiertes Diagramm 117
Einfache VBA Programme 119
Abstract 119
Addiere Zahlen desselben Zahlenformats – sbSumMyFormat 119
Zähle Zellen mit einer bestimmten Farbe – sbCountMyColor 120
Asset Allokation für ein Portfolio – sbAllocate 121
Ausreißer Werte eliminieren – sbORB 122
Faire Verteilung eines begrenzten Budgets – sbDistBudget 124
Collatz Länge Berechnen - sbCollatz 126
Eindeutigen Rang auch bei Duplikaten vergeben – sbUniqRank 127
Eliminiere Punkte eines Graphen mit kleiner Steigungsänderung – sbReducePoints 129
Geburtstagsliste – sbBirthdayList 131
Akumuliertes Handelsblatt – sbAccumulatedTradeBlotter 133
Häufigste Paare – sbMostFrequentPairs 135
Interpolieren – sbInterp 137
Erzeuge alle Kombinationen der Subsets k von n 139
Lookup Varianten 141
Minimale Anzahl von Scheinen und Münzen für einen Geldbetrag – sbMinCash 144
Neugewichtung der Assets eines Portfolios – sbRebalancedReturn 147
Optimale Boxenstopps 149
Optimale Nutzung von Urlaubstageen 151
Rundensystem für Turnier Jeder Gegen Jeden - sbRoundRobin 153
Zugriffsrechte prüfen 156
VBA Programme für Fortgeschrittene 159
Abstract 159
Mitarbeiter Umsatzanteile 159
Extremwerte von Zellen speichern – sbCellWatermarks 165
Aufgabenliste – sbTaskList 167
Zahlungseingangsproblem 170
Datenanalyse – sbDatastats 173
System Handbuch 173
Übersicht 173
Parameter im Tabellenblatt Param 174
Anwenderhandbuch 175
Zusammenfassung 175
Konfigurationsdatei FileSpecs.csv 175
NumStats Ausgabe 176
NumStatsMove Ausgabe 176
TextStats Ausgabe 176
TextStatsMove Ausgabe 176
Limits_Output Datei 177
Limits_Move_Output Datei 178
Lotto k-Tupel 208
Minirechner 211
Sterblichkeitsrente 218
Komplexe Matrixformel (Schlechteste Wahl) 218
Einfache Benutzerdefinierte Funktion mit VBA (Bessere Wahl) 219
Vorkalkulierte Tabelle und eine NBW Formel (Wahrscheinlich am Besten) 219
Finanzmathematik – Optionen 220
Das Binomialbaummodell 220
Das Trinomial-Optionspreismodell und die Methode der Finiten Differenzen 226
Monte Carlo Simulation 232
Erzeuge alle Permutationen eines Arrays – Quickperm 242
Eine wartungsfreie Datenbenk 244
Einschränkungen 244
Verantwortlichkeiten 244
Systemdokumentation 245
Anwenderdokumentation 245
Super User mit Schreib-/Lesezugriff 245
Normaler Anwender nur mit Lesezugriff 245
Appendix – SQL Code 246
Appendix – VBA Code 249
Summenerhaltendes Runden mit RoundToSum 252
Abstract 252
Summenerhaltendes Runden 252
Beispiel für Prozentzahlen 252
Beispiel für absolute Zahlen 253
Die benutzerdefinierte VBA Funktion RoundToSum 253
RoundToSum Programmcode 254
Round2Sum Lambda-Ausdruck 255
Werte runden ändert ihre Summe 256
Wieviele Summanden müssen im Mittel 'falsch' gerundet werden? 258
Anwendungsbeispiele für RoundToSum 260
Gemeinkostenumlage 260
Beispiel für ein exaktes Verhältnis von Zufallszahlen 262
Die benutzerdefinierte VBA Funktion sbExactRandHistogrm 263
Faire Mitarbeiterauswahl nach Teamgröße – sbFairStaffSelection 265
Stichprobe normalverteilen 267
Verteilung nach Restmenge 272
Ein simpler Ansatz 272
Eine korrekte Rechnung 272
Urlaub nehmen wenn weniger los ist 273
Simples Beispiel 273
Komplexeres Beispiel 274
Zuweisen von Arbeitseinheiten vermindert um geleistete 275
RoundToSum im Vergleich 276
RoundToSum im Vergleich mit anderen “einfachen” Methoden 276
RoundToSum im Vergleich zum D‘Hondt Verfahren 279
Literatur 279
Zufallszahlen erzeugen 280
Abstract 280
Ganze Zufallszahlen 280
Natürliche Zufallszahlen – UniqRandInt 280
Ganze Zufallszahlen – sbRandInt 282
Zufallszahlen mit einer festgelegten Summe 284
Minimum für die Zufallszahlen vorgegeben - sbLongRandSumN 284
Minimum und Maximum für die Zufallszahlen vorgegeben - sbRandIntFixSum 285
Praktische Anwendungen ganzer Zufallszahlen 287
Krabat – Wie alt können die Lehrlinge werden? 287
Ein Mathematiktest mit ganzen Zufallszahlen – Generate_Math_Test 288
Monte Carlo Simulation für eine faire Teamverteilung – sbGenerateTeams 290
Monte Carlo Simulation für einen Regatta Flight Plan – sbRegattaFlightPlan 294
Chancen beim Brettspiel Risiko 297
Eine simple Monte Carlo Simulation 301
Gleitkomma-Zufallszahlen 303
Eine ideale Normalverteilung – sbGenNormDist 303
Zufallszahlen mit der Summe 1 – sbRandSum1 305
Verteilungen von Gleitkomma-Zufallszahlen 307
sbRandGeneral 307
sbRandHistogrm 310
sbRandTriang 313
sbRandTrigen 314
sbRandCauchy 318
sbRandCDFInv 319
sbRandPDF 320
sbRandCumulative 321
Brownsche Brücken 323
sbGrowthSeries 323
Fixe Summe aus verschiedenen Zufallsbereichen 325
Korrelierte Zufallszahlen 327
Cholesky Zerlegung 327
Iman-Conover Methode 329
Praktische Anwendungen allgemeiner Zufallszahlen 336
Testdaten erzeugen – sbGenerateTestData 336
Zufallszahlen die sich nicht sofort wiederholen – sbRandomNoRepeatBeforeN 346
Exkurs 348
Wahrscheinlichkeiten berechnen – Ziehen von Karten mit und ohne Zurücklegen 348
Spaß ohne Praxisrelevanz für Fortgeschrittene 349
Eine einfache VBA Pivot Tabelle – sbMiniPivot 349
Rundenturnier-Paarungen mit Excel Tabellenblattfunktionen 352
Entwickeln einer Formellösung für ein Rundenturnier Jeder gegen Jeden 352
TEXTVERKETTEN 356
Index 357
Bitte den Haftungsausschluss im Impressum beachten.
Plumhoff_Excel_VBA_Eine_Sammlung.pdf [12,4 MB PDF Datei, ohne jegliche Gewährleistung]
Auszug über das summenerhaltende Runden:
Plumhoff_Summenerhaltendes_Runden.pdf [1,5 MB PDF Datei, ohne jegliche Gewährleistung]
Auszug über die Erzeugung von Zufallszahlen:
Plumhoff_Zufallszahlen_erzeugen_mit_Excel_VBA.pdf [12,3 MB PDF Datei, ohne jegliche Gewährleistung]