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
Error Handling 14
Good Programming Practices 15
Be a Good Programmer 15
Good Excel and VBA Knowledge 15
Programming Conventions 15
Clean Up Macro Recordings 15
Document Your Program Adequately 15
Test Your Program Thoroughly 16
Log Your Program Execution 16
Optimize Your Program 16
System Status Save and Restore – SystemState Class 17
System Status Variables 17
SystemState Code 18
Documenting Program Flow – Logging Class 20
Pros and Cons 20
Parameters 21
Sample Output 22
Modules 22
Class Modules 26
Excursus: Logger for PowerShell – Write-Log 27
Start / Stop OneDrive Synchronization 29
Export a Range as Picture – sbExportRange2Picture 30
List Named Ranges – sbNamedRanges 32
Number of Dimensions of an Array – ArrayDim 33
Show Excel Version – ApplicationVersion 34
Retrieve Cell Information – sbGetCell 35
Next Floating Point Number – sbNextFloat 41
Calling Other Windows Programs Using the Example sbZip 42
Excel Don’t’s – What You Should not be Doing with Excel 44
A table of what you should avoid 44
Number Systems, Formats, and Transformations 45
Abstract 45
Transformations and Calculations of Numbers 45
Spell Numbers in English Words – sbSpellNumber 45
Convert Decimal into Binary Numbers – sbDec2Bin / sbBin2Dec 49
Identify German Bank Holidays – IstFeiertag 54
Present the Full-Length Number – sbNum2Str 58
Significant Digits of a Number – sbNSig 59
Return the Number for a Month’s Name – sbMonthNumber 60
Calculation of the Circle Constant π 63
First 1,000 Digits of π 66
Calculation of Euler's Number e 67
First 1,000 Digits of e 68
Literature 69
Shorten a Number Sequence Representation – sbParseNumSeq 69
Rational Numbers = Fractions 71
Nearest Rational Number to a Given Floating Point Number – sbNRN 71
Linear Equations with Rational Coeffizients 74
Present Quota Changes as Fractions 77
Monthly Fractions 78
Linear Combination of Integers 79
Extended Euklidean Algorithm – sbEuklid 79
Time Representations 81
Calculate Working Hours Between Two Time Points – sbTimeDiff 81
Add Working Hours to a Time Point – sbTimeAdd 84
Convert a Time to a Different Time Zone – ConvertTime 87
Check Digits 87
Calculate or Check a European Article Number – sbEAN 87
Ordinal Numbers 88
Simple Math with Formulas 89
How to Analyze Spreadsheet Formulas 89
Testing Date Formulas 90
First or last Weekday of a Month 92
Same Weekday and Calendarweek last year 93
Budget Control 94
Increment Lowest Significant Digit 95
Linear Breakdown 96
Merge two columns justified into one 99
Minimum Truck Load Problem 100
Count Trailing Zeros 101
Minimax Interpolation 101
Interpolation with PERCENTILE / PERCENTRANK 103
Interpolation with TREND 105
REFA Time Slicing 106
Roles and Rights 107
Rounding is Fun 108
Tip Distribution 109
Smoothing Inner Period Values 111
Cell Based Charts 112
Simple VBA Programs 114
Abstract 114
Sum up Numbers with same Number Format – sbSumMyFormat 114
Count Cells with Certain Color – sbCountMyColor 115
Allocate Assets to a Portfolio – sbAllocate 116
Eliminate Outliers – sbORB 117
Fair Distribution of a Limited Budget – sbDistBudget 119
Compute Collatz Length – sbCollatz 121
Rank Item Uniquely – sbUniqRank 122
Eliminate Points of a Graph with Small Slope Changes – sbReducePoints 124
Birthday List – sbBirthdayList 126
Accumulated Trade Blotter – sbAccumulatedTadeBlotter 128
Most Frequent Pairs – sbMostFrequentPairs 130
Interpolate – sbInterp 132
Combinations with Subsets k of n 134
Lookup Variants 136
Minimal Number of Banknotes and Coins – sbMinCash 139
Rebalance AssetClass Weights of a Portfolio – sbRebalancedReturn 142
Optimal Pitstops 144
Optimal Usage of Vacation Days 146
Create a Round Robin Tournament – sbRondRobin 148
Test Access Rights 151
Advanced VBA Programs 153
Abstract 153
Employee Revenue Shares 153
Keeping Track of extreme Cell Values – sbCellWatermarks 158
A Task List – sbTaskList 160
Accounts Receivable Problem 163
Data Analysis – sbDatastats 166
System Handbook 166
Overview 166
Parameters in Sheet Param 167
User Handbook 167
Summary 167
Config File FileSpecs.csv 168
Numstats Output 168
Numstats Move Output 169
Textstats Output 169
Textstats Move Output 169
Output Limits File 170
Output Limits Move File 171
Weight Calculation 201
Useful Extensions and Generalizations 203
Lottery k-Tuples 210
Mini Calculator 213
Mortality Annuities 220
Complex Array Formula (Worst) 220
Simple user defined function in VBA (Better) 221
Pre-calculated Table plus an NPV Formula (Maybe Best) 221
Financial Mathematics – Options 222
The binomial Method 222
Trinomial Trees and Finite Difference Methods 228
Monte Carlo Simulation 234
Generate all Permutations of an Array – Quickperm 244
A maintenance-free Database 246
Limitations 246
Responsibilities 246
System Documentation 247
User Documentation 247
Super User with Read/Write Access 247
Normal User with Read-Only Access 247
Appendix – SQL Code 248
Appendix – VBA Code 251
Excursus: Compare Correlation Matrices 254
Abstract 254
Implemention Approach 254
Parameters 254
Rounding Values Preserving Their Sum with RoundToSum 268
Abstract 268
Rounding Values Preserving Their Sum 268
Percentage Example 268
Example with Absolute Values 269
The User-Defined VBA Function RoundToSum 269
RoundToSum Program Code 270
Round2Sum Lambda Expression 271
Rounding Values Alters Their Sum 272
Usage Examples of RoundToSum 274
Allocation of Overheads 274
Example of an Exact Relation of Random Numbers 276
The User-Defined VBA Function sbExactRandHistogrm 277
Fair Staff Selection Based on Team Size – sbFairStaffSelection 279
Distribute a Sample Normally 281
Distribution of Budgets Among Remaining Staff 286
A Simple Approach 286
A Correct Calculation 286
Take Vacation When Less is Going on 287
Simple Example 287
More Complex Example 288
Assign Work Units Adjusted by Delivered Output 289
RoundToSum Versus Other Methods 290
RoundToSum Versus Other "Simple" Methods 290
RoundToSum Compared to the D’Hondt Approach 293
Literature 293
Random Number Generation (Excel / VBA) 294
Abstract 294
Random Integers 294
Natural Random Numbers – UniqRandInt 294
Random Integers – sbRandInt 296
Random Numbers with a Specified Sum 298
Minimum of Random Numbers given – sbLongRandSumN 298
Minimum and Maximum of Random Numbers given – sbRandIntFixSum 299
Usage Examples for Random Integers 301
Krabat, the Satanic Mill – How old can the apprentices become? 301
Generate a Math Test with Random Integer Inputs – Generate_Math_Test 302
Monte Carlo Simulation to Generate Teams Fairly – sbGenerateTeams 304
Monte Carlo Simulation for a Regatta Flight Plan – sbRegattaFlightPlan 308
Chances at Board Game Risk 311
A Simple Monte Carlo Simulation 315
Random Floating Point Numbers 317
Generate an Ideal Normal Distribution – sbGenNormDist 317
Generate Random Numbers with a Sum of 1 – sbRandSum1 319
Distributions of Random Floating Point Numbers 321
sbRandGeneral 321
sbRandHistogrm 324
sbRandTriang 327
sbRandTrigen 328
sbRandCauchy 332
sbRandCDFInv 333
sbRandPDF 334
sbRandCumulative 335
Brownian Bridges 337
sbGrowthSeries 337
Fix Sum from Random Corridors 339
Correlated Random Numbers 341
Cholesky Decomposition 341
Iman-Conover Method 343
Practical Applications of General Random Numbers 350
Generating Test Data – sbGenerateTestData 350
Random Numbers which do not reappear too soon – sbRandomNoRepeatBeforeN 360
Excursus 362
Calculating Probabilities – Drawing Cards With and Without Replacement 362
Fun without Practical Relevance for Advanced Learners 363
A Simple VBA Pivot Table – sbMiniPivot 363
Round-Robin Tournament Pairings with Excel Worksheet Functions 366
Developing a Formula-Based Solution for a Round-Robin Tournament 366
TEXTJOIN 370
Index 371
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]