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 getestet.
Inhaltsverzeichnis
Excel / VBA – Eine Sammlung 1
Abstract 2
Excel Lernpfad 2
Die Excel / VBA Programmierumgebung 11
Abstract 11
Grundlegendes 11
Variablen 11
Typen 11
Scope 12
Während des Editierens 12
Während der Programmausführung 13
Breakpoints 13
Fehlerbehandlung 14
Gute Programmierpraxis 15
Seien Sie ein guter Programmierer 15
Gutes Excel und VBA Wissen 15
Programmierkonventionen 15
Säubern Sie Makroaufzeichnungen 15
Dokumentieren Sie Ihr Programm ausreichend 16
Testen Sie Ihr Programm gut 16
Protokollieren Sie Ihre Programmausführung 16
Optimieren Sie Ihr Programm 16
Systemstatus sichern und zurückschreiben – SystemState Klasse 17
Systemstatus Variablen 17
Programmablauf dokumentieren – Logging Klasse 20
Für und Wider 20
Parameter 21
Beispielausgabe 22
Module 22
Klassenmodule 26
Exkurs: Logger für PowerShell – Write-Log 27
Start / Stop OneDrive Synchronization 29
Exportiere einen Bereich als Grafik – sbExportRange2Picture 30
Bereichsnamen anzeigen – sbNamedRanges 33
Anzahl der Dimensionen eines Arrays – ArrayDim 33
Excel Version anzeigen – ApplicationVersion 34
Zellinformationen ausgeben – sbGetCell 35
Nächste Gleitkommazahl – sbNextFloat 41
Aufruf anderer Windows Programme am Beispiel sbZip 42
Excel Don’ts – Was man mit Excel besser sein lässt 44
Eine Tabelle mit Beispielen die man besser vermeidet 44
Zahlensysteme, Formate und Umwandlungen 45
Abstract 45
Umwandlungen und Berechnungen von Zahlen 45
Zahlen in Worten ausgeben – sbInWorten 45
Umwandlungen zwischen dem Dezimalsystem und dem Binärsystem 49
Feiertage ermitteln – IstFeiertag 54
Zahl vollständig nicht-wissenschaftlich darstellen – sbNum2Str 58
Signifikante Ziffern einer Zahl – sbNSig 59
Nummer eines Monatsnamens – sbMonatsZahl 60
Die Berechnung der Kreiszahl π 63
Pi Ausgabe auf 1000 Stellen 66
Die Berechnung der Eulerschen Zahl e 67
e Ausgabe auf 1000 Stellen 68
Literatur 69
Zahlenfolge kürzer darstellen – sbParseNumSeq 69
Rationale Zahlen = Brüche 71
Ermittle die nächstliegende rationale Zahl zu einer Gleitkommazahl – sbNRN 71
Lineare Gleichungssysteme mit rationalen Koeffizienten 74
Anteilsveränderung als Bruch 77
Monatsanteil 78
Linearkombination Ganzer Zahlen 79
Erweiterter Euklidischer Algorithmus – sbEuklid 79
Uhrzeiten 81
Arbeitszeit zwischen 2 Zeitpunkten – sbTimeDiff 81
Arbeitszeit zu einem Zeitpunkt addieren – sbTimeAdd 84
Uhrzeit für eine andere Zeitzone umwandeln – ConvertTime 87
Prüfziffern 87
Berechne oder prüfe eine Europäische Artikelnummer – sbEAN 87
Ordinalzahlen (englisch) 88
Einfache Mathematik in Formeln 89
Wie man Tabellenblattformeln analysiert 89
Datumsformeln testen 90
Erster oder letzter Wochentag im Monat 92
Derselbe Wochentag und Kalenderwoche im vorigen Jahr 93
Budgetkontrolle 94
Geringste Signifikante Ziffer erhöhen 95
Linearer Breakdown 96
Füge zwei Spalten mit Blocksatz zusammen 99
Minimum Truck Load Problem 100
Nachstehende Nullen zählen 101
Minimax Interpolation 101
Interpolation mit PERZENTIL / PROZENTRANG 103
Interpolation mit TREND 105
REFA Zeitklassen 106
Rollen und Rechte 107
Rundungstricks 108
Trinkgeld Verteilung 109
Unterjährige Werte glätten 111
Zellenbasiertes Diagramm 112
Einfache VBA Programme 114
Abstract 114
Addiere Zahlen desselben Zahlenformats – sbSumMyFormat 114
Zähle Zellen mit einer bestimmten Farbe – sbCountMyColor 115
Asset Allokation für ein Portfolio – sbAllocate 116
Ausreißer Werte eliminieren – sbORB 117
Faire Verteilung eines begrenzten Budgets – sbDistBudget 119
Collatz Länge Berechnen - sbCollatz 121
Eindeutigen Rang auch bei Duplikaten vergeben – sbUniqRank 122
Eliminiere Punkte eines Graphen mit kleiner Steigungsänderung – sbReducePoints 124
Geburtstagsliste – sbBirthdayList 126
Akumuliertes Handelsblatt – sbAccumulatedTradeBlotter 128
Häufigste Paare – sbMostFrequentPairs 130
Interpolieren – sbInterp 132
Erzeuge alle Kombinationen der Subsets k von n 134
Lookup Varianten 136
Minimale Anzahl von Scheinen und Münzen für einen Geldbetrag – sbMinCash 139
Neugewichtung der Assets eines Portfolios – sbRebalancedReturn 142
Optimale Boxenstopps 144
Optimale Nutzung von Urlaubstageen 146
Rundensystem für Turnier Jeder Gegen Jeden - sbRoundRobin 148
Zugriffsrechte prüfen 151
VBA Programme für Fortgeschrittene 153
Abstract 153
Mitarbeiter Umsatzanteile 153
Extremwerte von Zellen speichern – sbCellWatermarks 158
Aufgabenliste – sbTaskList 160
Zahlungseingangsproblem 163
Datenanalyse – sbDatastats 166
System Handbuch 166
Übersicht 166
Parameter im Tabellenblatt Param 167
Anwenderhandbuch 168
Zusammenfassung 168
Konfigurationsdatei FileSpecs.csv 168
NumStats Ausgabe 169
NumStatsMove Ausgabe 169
TextStats Ausgabe 169
TextStatsMove Ausgabe 169
Limits_Output Datei 170
Limits_Move_Output Datei 171
Gewichtberechnung 201
Sinnvolle Erweiterungen und Verallgemeinerungen 203
Lotto k-Tupel 210
Minirechner 213
Sterblichkeitsrente 220
Komplexe Matrixformel (Schlechteste Wahl) 220
Einfache Benutzerdefinierte Funktion mit VBA (Bessere Wahl) 221
Vorkalkulierte Tabelle und eine NBW Formel (Wahrscheinlich am Besten) 221
Finanzmathematik – Optionen 222
Das Binomialbaummodell 222
Das Trinomial-Optionspreismodell und die Methode der Finiten Differenzen 228
Monte Carlo Simulation 234
Erzeuge alle Permutationen eines Arrays – Quickperm 244
Eine wartungsfreie Datenbenk 246
Einschränkungen 246
Verantwortlichkeiten 246
Systemdokumentation 247
Anwenderdokumentation 247
Super User mit Schreib-/Lesezugriff 247
Normaler Anwender mit nur Lesezugriff 247
Appendix – SQL Code 248
Appendix – VBA Code 251
Exkurs: Vergleiche Korrelationsmatrizen 254
Abstract 254
Implementierungsansatz 254
Parameter 254
Summenerhaltendes Runden mit RoundToSum 268
Abstract 268
Summenerhaltendes Runden 268
Beispiel für Prozentzahlen 268
Beispiel für absolute Zahlen 269
Die benutzerdefinierte VBA Funktion RoundToSum 269
RoundToSum Programmcode 270
Round2Sum Lambda-Ausdruck 271
Werte runden ändert ihre Summe 272
Anwendungsbeispiele für RoundToSum 274
Gemeinkostenumlage 274
Beispiel für ein exaktes Verhältnis von Zufallszahlen 276
Die benutzerdefinierte VBA Funktion sbExactRandHistogrm 277
Faire Mitarbeiterauswahl nach Teamgröße – sbFairStaffSelection 279
Stichprobe normalverteilen 281
Verteilung nach Restmenge 286
Ein simpler Ansatz 286
Eine korrekte Rechnung 286
Urlaub nehmen wenn weniger los ist 287
Simples Beispiel 287
Komplexeres Beispiel 288
Zuweisen von Arbeitseinheiten vermindert um geleistete 289
RoundToSum im Vergleich 290
RoundToSum im Vergleich mit anderen “einfachen” Methoden 290
RoundToSum im Vergleich zum D‘Hondt Verfahren 293
Literatur 293
Zufallszahlen erzeugen 294
Abstract 294
Ganze Zufallszahlen 294
Natürliche Zufallszahlen – UniqRandInt 294
Ganze Zufallszahlen – sbRandInt 296
Zufallszahlen mit einer festgelegten Summe 298
Minimum für die Zufallszahlen vorgegeben - sbLongRandSumN 298
Minimum und Maximum für die Zufallszahlen vorgegeben - sbRandIntFixSum 299
Praktische Anwendungen ganzer Zufallszahlen 301
Krabat – Wie alt können die Lehrlinge werden? 301
Ein Mathematiktest mit ganzen Zufallszahlen – Generate_Math_Test 302
Monte Carlo Simulation für eine faire Teamverteilung – sbGenerateTeams 304
Monte Carlo Simulation für einen Regatta Flight Plan – sbRegattaFlightPlan 308
Chancen beim Brettspiel Risiko 311
Eine simple Monte Carlo Simulation 315
Gleitkomma-Zufallszahlen 317
Eine ideale Normalverteilung – sbGenNormDist 317
Zufallszahlen mit der Summe 1 – sbRandSum1 319
Verteilungen von Gleitkomma-Zufallszahlen 321
sbRandGeneral 321
sbRandHistogrm 324
sbRandTriang 327
sbRandTrigen 328
sbRandCauchy 332
sbRandCDFInv 333
sbRandPDF 334
sbRandCumulative 335
Brownsche Brücken 337
sbGrowthSeries 337
Fixe Summe aus verschiedenen Zufallsbereichen 339
Korrelierte Zufallszahlen 341
Cholesky Zerlegung 341
Iman-Conover Methode 343
Praktische Anwendungen allgemeiner Zufallszahlen 350
Testdaten erzeugen – sbGenerateTestData 350
Zufallszahlen die sich nicht sofort wiederholen – sbRandomNoRepeatBeforeN 360
Exkurs 362
Wahrscheinlichkeiten berechnen – Ziehen von Karten mit und ohne Zurücklegen 362
Spaß ohne Praxisrelevanz für Fortgeschrittene 363
Eine einfache VBA Pivot Tabelle – sbMiniPivot 363
Rundenturnier-Paarungen mit Excel Tabellenblattfunktionen 366
Entwickeln einer Formellösung für ein Rundenturnier Jeder gegen Jeden 366
TEXTVERKETTEN 370
Index 371
Bitte den Haftungsausschluss im Impressum beachten.
Plumhoff_Excel_VBA_Eine_Sammlung.pdf [12,9 MB PDF Datei, ohne jegliche Gewährleistung]
Auszug über das summenerhaltende Runden:
Plumhoff_Summenerhaltendes_Runden.pdf [3,1 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]