Excel VBA A Collection
This is a collection of Excel VBA programs and of some Excel spreadsheet formulas which I found reasonable.
Note: I successfully tested the applications and formulas presented here with Excel 2024.
Table of Contents
Excel / VBA – A Collection 1
Abstract 2
Excel Learning Path 2
The Excel / VBA Programming Environment 11
Abstract 11
Basics 11
Variables 11
Types of Variable 11
Scope 12
During Editing 12
During Program Execution 13
Breakpoints 13
Error Handling 13
Environment Variables 15
Good Programming Practices 15
Be a Good Programmer 15
Good Excel and VBA Knowledge 15
Programming Conventions 16
Clean Up Macro Recordings 16
Document Your Program Adequately 16
Test Your Program Thoroughly 16
Log Your Program Execution 16
Speed up Your Program (Profiling) 17
Modules 17
Normal 17
Class Modules 19
System Status Save and Restore – SystemState Class 20
System Status Variables 20
SystemState Program Code 21
Documenting Program Flow – Logging Class 23
Pros and Cons 23
Parameters 24
Sample Output 25
Modules 26
Class Modules 31
Excursus: Logger for PowerShell – Write-Log 32
Start / Stop OneDrive Synchronization 33
Export a Range as Picture – sbExportRange2Picture 35
List Named Ranges – sbNamedRanges 38
Number of Dimensions of an Array – ArrayDim 38
Show Excel Version – ApplicationVersion 39
Retrieve Cell Information – sbGetCell 40
Next Floating Point Number – sbNextFloat 46
Calling Other Windows Programs Using the Example sbZip 47
Excel Don’t’s – What You Should not be Doing with Excel 49
A table of what you should avoid 49
Number Systems, Formats, and Transformations 50
Abstract 50
Transformations and Calculations of Numbers 50
Spell Numbers in English Words – sbSpellNumber 50
Convert Decimal into Binary Numbers – sbDec2Bin / sbBin2Dec 54
Identify German Bank Holidays – IstFeiertag 59
Present the Full-Length Number – sbNum2Str 63
Significant Digits of a Number – sbNSig 64
Return the Number for a Month’s Name – sbMonthNumber 65
Calculation of the Circle Constant π 68
First 1,000 Digits of π 71
Calculation of Euler's Number e 72
First 1,000 Digits of e 73
Literature 74
Shorten a Number Sequence Representation – sbParseNumSeq 74
Rational Numbers = Fractions 76
Nearest Rational Number to a Given Floating Point Number – sbNRN 76
Linear Equations with Rational Coeffizients 79
Present Quota Changes as Fractions 82
Monthly Fractions 83
Linear Combination of Integers 84
Extended Euklidean Algorithm – sbEuklid 84
Time Representations 86
Calculate Working Hours Between Two Time Points – sbTimeDiff 86
Add Working Hours to a Time Point – sbTimeAdd 89
Convert a Time to a Different Time Zone – ConvertTime 92
Check Digits 92
Calculate or Check a European Article Number – sbEAN 92
Ordinal Numbers 93
Simple Math with Formulas 94
How to Analyze Spreadsheet Formulas 94
Testing Date Formulas 95
First or last Weekday of a Month 97
Same Weekday and Calendarweek last year 98
Budget Control 99
Increment Lowest Significant Digit 100
Linear Breakdown 101
Merge two columns justified into one 104
Minimum Truck Load Problem 105
Count Trailing Zeros 106
Minimax Interpolation 106
Interpolation with PERCENTILE / PERCENTRANK 108
Interpolation with TREND 110
REFA Time Slicing 111
Roles and Rights 112
Rounding is Fun 113
Tip Distribution 114
Smoothing Inner Period Values 116
Cell Based Charts 117
Simple VBA Programs 119
Abstract 119
Sum up Numbers with same Number Format – sbSumMyFormat 119
Count Cells with Certain Color – sbCountMyColor 120
Allocate Assets to a Portfolio – sbAllocate 121
Eliminate Outliers – sbORB 122
Fair Distribution of a Limited Budget – sbDistBudget 124
Compute Collatz Length – sbCollatz 126
Rank Item Uniquely – sbUniqRank 127
Eliminate Points of a Graph with Small Slope Changes – sbReducePoints 129
Birthday List – sbBirthdayList 131
Accumulated Trade Blotter – sbAccumulatedTadeBlotter 133
Most Frequent Pairs – sbMostFrequentPairs 135
Interpolate – sbInterp 137
Combinations with Subsets k of n 139
Lookup Variants 141
Minimal Number of Banknotes and Coins – sbMinCash 144
Rebalance AssetClass Weights of a Portfolio – sbRebalancedReturn 147
Optimal Pitstops 149
Optimal Usage of Vacation Days 151
Create a Round Robin Tournament – sbRondRobin 153
Test Access Rights 156
Advanced VBA Programs 159
Abstract 159
Employee Revenue Shares 159
Keeping Track of extreme Cell Values – sbCellWatermarks 165
A Task List – sbTaskList 167
Accounts Receivable Problem 170
Data Analysis – sbDatastats 173
System Handbook 173
Overview 173
Parameters in Sheet Param 174
User Handbook 174
Summary 174
Config File FileSpecs.csv 175
Numstats Output 175
Numstats Move Output 176
Textstats Output 176
Textstats Move Output 176
Output Limits File 177
Output Limits Move File 178
Weight Calculation 208
Useful Extensions and Generalizations 210
Lottery k-Tuples 217
Mini Calculator 220
Mortality Annuities 227
Complex Array Formula (Worst) 227
Simple user defined function in VBA (Better) 228
Pre-calculated Table plus an NPV Formula (Maybe Best) 228
Financial Mathematics – Options 229
The binomial Method 229
Trinomial Trees and Finite Difference Methods 235
Monte Carlo Simulation 241
Generate all Permutations of an Array – Quickperm 251
A maintenance-free Database 253
Limitations 253
Responsibilities 253
System Documentation 254
User Documentation 254
Super User with Read/Write Access 254
Normal User with Read-Only Access 254
Appendix – SQL Code 255
Appendix – VBA Code 258
Excursus: Compare Correlation Matrices 261
Abstract 261
Implemention Approach 261
Parameters 261
Rounding Values Preserving Their Sum with RoundToSum 275
Abstract 275
Rounding Values Preserving Their Sum 275
Percentage Example 275
Example with Absolute Values 276
The User-Defined VBA Function RoundToSum 276
RoundToSum Program Code 277
Round2Sum Lambda Expression 278
Rounding Values Alters Their Sum 279
Usage Examples of RoundToSum 281
Allocation of Overheads 281
Example of an Exact Relation of Random Numbers 283
The User-Defined VBA Function sbExactRandHistogrm 284
Fair Staff Selection Based on Team Size – sbFairStaffSelection 286
Distribute a Sample Normally 288
Distribution of Budgets Among Remaining Staff 293
A Simple Approach 293
A Correct Calculation 293
Take Vacation When Less is Going on 294
Simple Example 294
More Complex Example 295
Assign Work Units Adjusted by Delivered Output 296
RoundToSum Versus Other Methods 297
RoundToSum Versus Other "Simple" Methods 297
RoundToSum Compared to the D’Hondt Approach 300
Literature 300
Random Number Generation (Excel / VBA) 301
Abstract 301
Random Integers 301
Natural Random Numbers – UniqRandInt 301
Random Integers – sbRandInt 303
Random Numbers with a Specified Sum 305
Minimum of Random Numbers given – sbLongRandSumN 305
Minimum and Maximum of Random Numbers given – sbRandIntFixSum 306
Usage Examples for Random Integers 308
Krabat, the Satanic Mill – How old can the apprentices become? 308
Generate a Math Test with Random Integer Inputs – Generate_Math_Test 309
Monte Carlo Simulation to Generate Teams Fairly – sbGenerateTeams 311
Monte Carlo Simulation for a Regatta Flight Plan – sbRegattaFlightPlan 315
Chances at Board Game Risk 318
A Simple Monte Carlo Simulation 322
Random Floating Point Numbers 324
Generate an Ideal Normal Distribution – sbGenNormDist 324
Generate Random Numbers with a Sum of 1 – sbRandSum1 326
Distributions of Random Floating Point Numbers 328
sbRandGeneral 328
sbRandHistogrm 331
sbRandTriang 334
sbRandTrigen 335
sbRandCauchy 339
sbRandCDFInv 340
sbRandPDF 341
sbRandCumulative 342
Brownian Bridges 344
sbGrowthSeries 344
Fix Sum from Random Corridors 346
Correlated Random Numbers 348
Cholesky Decomposition 348
Iman-Conover Method 350
Practical Applications of General Random Numbers 357
Generating Test Data – sbGenerateTestData 357
Random Numbers which do not reappear too soon – sbRandomNoRepeatBeforeN 367
Excursus 369
Calculating Probabilities – Drawing Cards With and Without Replacement 369
Fun without Practical Relevance for Advanced Learners 370
A Simple VBA Pivot Table – sbMiniPivot 370
Round-Robin Tournament Pairings with Excel Worksheet Functions 373
Developing a Formula-Based Solution for a Round-Robin Tournament 373
TEXTJOIN 377
Index 378
Please read my Disclaimer.
Plumhoff_Excel_VBA_A_Collection.pdf [13.6 MB PDF file, open and use at your own risk]
Extract on rounding numbers preserving their rounded sum:
Plumhoff_Rounding_Values_Preserving_Their_Sum.pdf [3.8 MB PDF file, open and use at your own risk]
Extract on generating random numbers:
Plumhoff_Generating_Random_Numbers_with_Excel_VBA.pdf [13.4 MB PDF file, open and use at your own risk]