“Spreadsheets are probably among the most dangerous inventions of recent decades.” [Rolf Dobelli]
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 / 365.
Table of Contents
Excel / VBA – A Collection 1
Abstract 2
Excel Learning Path 2
The Excel / VBA Programming Environment 10
Abstract 10
Basics 10
Variables 10
Types of Variable 10
Scope 11
During Editing 11
During Program Execution 12
Breakpoints 12
Error Handling 13
Environment Variables 14
Good Programming Practices 14
Be a Good Programmer 14
Good Excel and VBA Knowledge 14
Programming Conventions 15
Clean Up Macro Recordings 15
Document Your Program Adequately 15
Test Your Program Thoroughly 15
Log Your Program Execution 15
Speed up Your Program (Profiling) 16
System Status Save and Restore – SystemState Class 17
System Status Variables 17
Documenting Program Flow – Logging Class 20
Pros and Cons 20
Parameters 21
Sample Output 22
Modules 22
Class Modules 26
Checking Program Interfaces 28
Start / Stop OneDrive Synchronization 30
Export a Range as Picture – sbExportRange2Picture 32
List Named Ranges – sbNamedRanges 35
Number of Dimensions of an Array – ArrayDim 35
Show Excel Version – ApplicationVersion 36
Retrieve Cell Information – sbGetCell 37
Next Floating Point Number – sbNextFloat 43
Calling Other Windows Programs Using the Example sbZip 44
Excel Don’t’s – What You Should not be Doing with Excel 46
A table of what you should avoid 46
Number Systems, Formats, and Transformations 47
Abstract 47
Transformations and Calculations of Numbers 47
Spell Numbers in English Words – sbSpellNumber 47
Convert Decimal into Binary Numbers – sbDec2Bin / sbBin2Dec 51
Identify German Bank Holidays – IstFeiertag 56
Significant Digits of a Number – sbNSig 60
Return the Number for a Month’s Name – sbMonthNumber 61
Calculation of the Circle Constant π 64
First 1,000 Digits of π 67
Calculation of Euler's Number e 68
First 1,000 Digits of e 69
Literature 70
Shorten a Number Sequence Representation – sbParseNumSeq 70
Rational Numbers = Fractions 72
Nearest Rational Number to a Given Floating Point Number – sbNRN 72
Linear Equations with Rational Coeffizients 75
Present Quota Changes as Fractions 78
Monthly Fractions 79
Linear Combination of Integers 80
Extended Euklidean Algorithm – sbEuklid 80
Time Representations 82
Calculate Working Hours Between Two Time Points – sbTimeDiff 82
Add Working Hours to a Time Point – sbTimeAdd 85
Convert a Time to a Different Time Zone – ConvertTime 88
Check Digits 88
Calculate or Check a European Article Number – sbEAN 88
Ordinal Numbers 89
Simple Math with Formulas 90
How to Analyze Spreadsheet Formulas 90
Testing Date Formulas 91
First or last Weekday of a Month 93
Same Weekday and Calendarweek last year 94
Budget Control 95
Increment Lowest Significant Digit 96
Linear Breakdown 97
Merge two columns justified into one 100
Minimum Truck Load Problem 101
Minimax Interpolation 102
Roles and Rights 104
Rounding is Fun 105
Tip Distribution 106
Smoothing Inner Period Values 108
Cell Based Charts 109
Simple VBA Programs 111
Abstract 111
Sum up Numbers with same Number Format – sbSumMyFormat 111
Count Cells with Certain Color – sbCountMyColor 112
Allocate Assets to a Portfolio – sbAllocate 113
Eliminate Outliers – sbORB 114
Fair Distribution of a Limited Budget – sbDistBudget 116
Compute Collatz Length – sbCollatz 118
Rank Item Uniquely – sbUniqRank 119
Eliminate Points of a Graph with Small Slope Changes – sbReducePoints 121
Birthday List – sbBirthdayList 123
Most Frequent Pairs – sbMostFrequentPairs 125
Interpolate – sbInterp 127
Combinations with Subsets k of n 129
Lookup Variants 131
Minimal Number of Banknotes and Coins – sbMinCash 133
Rebalance AssetClass Weights of a Portfolio – sbRebalancedReturn 136
Optimal Pitstops 138
Optimal Usage of Vacation Days 140
Create a Round Robin Tournament – sbRondRobin 142
Test Access Rights 145
Advanced VBA Programs 148
Abstract 148
Employee Revenue Shares 148
Keeping Track of extreme Cell Values – sbCellWatermarks 153
A Task List – sbTaskList 155
Accounts Receivable Problem 158
Lottery k-Tuples 161
Mortality Annuities 164
Complex Array Formula (Worst) 164
Simple user defined function in VBA (Better) 165
Pre-calculated Table plus an NPV Formula (Maybe Best) 165
Financial Mathematics – Options 166
The Binomial Method 166
Trinomial Trees and Finite Difference Methods 172
Monte Carlo Simulation 178
Rounding Values Preserving Their Sum with RoundToSum 188
Abstract 188
Rounding Values Preserving Their Sum 188
Percentage Example 188
Example with Absolute Values 189
The User-Defined VBA Function RoundToSum 189
RoundToSum Program Code 190
Round2Sum Lambda Expression 191
Runtimes 192
Rounding Values Alters Their Sum 193
How many numbers do you need to round into the wrong direction? 195
Usage Examples of RoundToSum 197
Allocation of Overheads 197
Example of an Exact Relation of Random Numbers 199
The User-Defined VBA Function sbExactRandHistogrm 200
Fair Staff Selection Based on Team Size – sbFairStaffSelection 202
Distribute a Sample Normally 204
Distribution of Budgets Among Remaining Staff 209
A Simple Approach 209
A Correct Calculation 209
Take Vacation When Less is Going on 210
Simple Example 210
More Complex Example 211
Assign Work Units Adjusted by Delivered Output 212
RoundToSum Versus Other Methods 213
RoundToSum Versus Other "Simple" Methods 213
RoundToSum Compared to the D’Hondt Approach 216
Literature 216
Random Number Generation (Excel / VBA) 217
Abstract 217
Random Integers 217
Natural Random Numbers – UniqRandInt 217
Random Integers – sbRandInt 219
Random Numbers with a Specified Sum 221
Minimum of Random Numbers given – sbLongRandSumN 221
Minimum and Maximum of Random Numbers given – sbRandIntFixSum 222
Usage Examples for Random Integers 224
Krabat, the Satanic Mill – How old can the apprentices become? 224
Generate a Math Test with Random Integer Inputs – Generate_Math_Test 225
Monte Carlo Simulation to Generate Teams Fairly – sbGenerateTeams 227
Monte Carlo Simulation for a Regatta Flight Plan – sbRegattaFlightPlan 231
Chances at Board Game Risk 234
A Simple Monte Carlo Simulation 238
Random Floating Point Numbers 240
Generate an Ideal Normal Distribution – sbGenNormDist 240
Generate Random Numbers with a Sum of 1 – sbRandSum1 242
Distributions of Random Floating Point Numbers 244
sbRandGeneral 244
sbRandHistogrm 247
sbRandTriang 250
sbRandTrigen 251
Brownian Bridges 255
sbGrowthSeries 255
Fix Sum from Random Corridors 257
Correlated Random Numbers 259
Cholesky Decomposition 259
Iman-Conover Method 261
Practical Applications of General Random Numbers 268
Generating Test Data – sbGenerateTestData 268
Random Numbers which do not reappear too soon – sbRandomNoRepeatBeforeN 278
Excursus 280
Calculating Probabilities – Drawing Cards With and Without Replacement 280
Fun without Practical Relevance for Advanced Learners 281
A Simple VBA Pivot Table – sbMiniPivot 281
Round-Robin Tournament Pairings with Excel Worksheet Functions 284
Developing a Formula-Based Solution for a Round-Robin Tournament 284
TEXTJOIN 288
Index 289
Please read my Disclaimer.
Plumhoff_Excel_VBA_A_Collection.pdf [11.4 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 [1.3 MB PDF file, open and use at your own risk]
Extract on generating random numbers:
Plumhoff_Generating_Random_Numbers_with_Excel_VBA.pdf [13.1 MB PDF file, open and use at your own risk]