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
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
Lottery k-Tuples 208
Mini Calculator 211
Mortality Annuities 218
Complex Array Formula (Worst) 218
Simple user defined function in VBA (Better) 219
Pre-calculated Table plus an NPV Formula (Maybe Best) 219
Financial Mathematics – Options 220
The binomial Method 220
Trinomial Trees and Finite Difference Methods 226
Monte Carlo Simulation 232
Generate all Permutations of an Array – Quickperm 242
A maintenance-free Database 244
Limitations 244
Responsibilities 244
System Documentation 245
User Documentation 245
Super User with Read/Write Access 245
Normal User with Read-Only Access 245
Appendix – SQL Code 246
Appendix – VBA Code 249
Rounding Values Preserving Their Sum with RoundToSum 252
Abstract 252
Rounding Values Preserving Their Sum 252
Percentage Example 252
Example with Absolute Values 253
The User-Defined VBA Function RoundToSum 253
RoundToSum Program Code 254
Round2Sum Lambda Expression 255
Rounding Values Alters Their Sum 256
How many numbers do you need to round into the wrong direction? 258
Usage Examples of RoundToSum 260
Allocation of Overheads 260
Example of an Exact Relation of Random Numbers 262
The User-Defined VBA Function sbExactRandHistogrm 263
Fair Staff Selection Based on Team Size – sbFairStaffSelection 265
Distribute a Sample Normally 267
Distribution of Budgets Among Remaining Staff 272
A Simple Approach 272
A Correct Calculation 272
Take Vacation When Less is Going on 273
Simple Example 273
More Complex Example 274
Assign Work Units Adjusted by Delivered Output 275
RoundToSum Versus Other Methods 276
RoundToSum Versus Other "Simple" Methods 276
RoundToSum Compared to the D’Hondt Approach 279
Literature 279
Random Number Generation (Excel / VBA) 280
Abstract 280
Random Integers 280
Natural Random Numbers – UniqRandInt 280
Random Integers – sbRandInt 282
Random Numbers with a Specified Sum 284
Minimum of Random Numbers given – sbLongRandSumN 284
Minimum and Maximum of Random Numbers given – sbRandIntFixSum 285
Usage Examples for Random Integers 287
Krabat, the Satanic Mill – How old can the apprentices become? 287
Generate a Math Test with Random Integer Inputs – Generate_Math_Test 288
Monte Carlo Simulation to Generate Teams Fairly – sbGenerateTeams 290
Monte Carlo Simulation for a Regatta Flight Plan – sbRegattaFlightPlan 294
Chances at Board Game Risk 297
A Simple Monte Carlo Simulation 301
Random Floating Point Numbers 303
Generate an Ideal Normal Distribution – sbGenNormDist 303
Generate Random Numbers with a Sum of 1 – sbRandSum1 305
Distributions of Random Floating Point Numbers 307
sbRandGeneral 307
sbRandHistogrm 310
sbRandTriang 313
sbRandTrigen 314
sbRandCauchy 318
sbRandCDFInv 319
sbRandPDF 320
sbRandCumulative 321
Brownian Bridges 323
sbGrowthSeries 323
Fix Sum from Random Corridors 325
Correlated Random Numbers 327
Cholesky Decomposition 327
Iman-Conover Method 329
Practical Applications of General Random Numbers 336
Generating Test Data – sbGenerateTestData 336
Random Numbers which do not reappear too soon – sbRandomNoRepeatBeforeN 346
Excursus 348
Calculating Probabilities – Drawing Cards With and Without Replacement 348
Fun without Practical Relevance for Advanced Learners 349
A Simple VBA Pivot Table – sbMiniPivot 349
Round-Robin Tournament Pairings with Excel Worksheet Functions 352
Developing a Formula-Based Solution for a Round-Robin Tournament 352
TEXTJOIN 356
Index 357
Please read my Disclaimer.
Plumhoff_Excel_VBA_A_Collection.pdf [13.3 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.4 MB PDF file, open and use at your own risk]