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 33
Start / Stop OneDrive Synchronization 34
Export a Range as Picture – sbExportRange2Picture 36
List Named Ranges – sbNamedRanges 39
Number of Dimensions of an Array – ArrayDim 39
Show Excel Version – ApplicationVersion 40
Retrieve Cell Information – sbGetCell 41
Next Floating Point Number – sbNextFloat 47
Calling Other Windows Programs Using the Example sbZip 48
Excel Don’t’s – What You Should not be Doing with Excel 50
A table of what you should avoid 50
Number Systems, Formats, and Transformations 51
Abstract 51
Transformations and Calculations of Numbers 51
Spell Numbers in English Words – sbSpellNumber 51
Convert Decimal into Binary Numbers – sbDec2Bin / sbBin2Dec 55
Identify German Bank Holidays – IstFeiertag 60
Present the Full-Length Number – sbNum2Str 64
Significant Digits of a Number – sbNSig 65
Return the Number for a Month’s Name – sbMonthNumber 66
Calculation of the Circle Constant π 69
First 1,000 Digits of π 72
Calculation of Euler's Number e 73
First 1,000 Digits of e 74
Literature 75
Shorten a Number Sequence Representation – sbParseNumSeq 75
Rational Numbers = Fractions 77
Nearest Rational Number to a Given Floating Point Number – sbNRN 77
Linear Equations with Rational Coeffizients 80
Present Quota Changes as Fractions 83
Monthly Fractions 84
Linear Combination of Integers 85
Extended Euklidean Algorithm – sbEuklid 85
Time Representations 87
Calculate Working Hours Between Two Time Points – sbTimeDiff 87
Add Working Hours to a Time Point – sbTimeAdd 90
Convert a Time to a Different Time Zone – ConvertTime 93
Check Digits 93
Calculate or Check a European Article Number – sbEAN 93
Ordinal Numbers 94
Simple Math with Formulas 95
How to Analyze Spreadsheet Formulas 95
Testing Date Formulas 96
First or last Weekday of a Month 98
Same Weekday and Calendarweek last year 99
Budget Control 100
Increment Lowest Significant Digit 101
Linear Breakdown 102
Merge two columns justified into one 105
Minimum Truck Load Problem 106
Count Trailing Zeros 107
Minimax Interpolation 107
Interpolation with PERCENTILE / PERCENTRANK 109
Interpolation with TREND 111
REFA Time Slicing 112
Roles and Rights 113
Rounding is Fun 114
Tip Distribution 115
Smoothing Inner Period Values 117
Cell Based Charts 118
Simple VBA Programs 120
Abstract 120
Sum up Numbers with same Number Format – sbSumMyFormat 120
Count Cells with Certain Color – sbCountMyColor 121
Allocate Assets to a Portfolio – sbAllocate 122
Eliminate Outliers – sbORB 123
Fair Distribution of a Limited Budget – sbDistBudget 125
Compute Collatz Length – sbCollatz 127
Rank Item Uniquely – sbUniqRank 128
Eliminate Points of a Graph with Small Slope Changes – sbReducePoints 130
Birthday List – sbBirthdayList 132
Accumulated Trade Blotter – sbAccumulatedTadeBlotter 134
Most Frequent Pairs – sbMostFrequentPairs 136
Interpolate – sbInterp 138
Combinations with Subsets k of n 140
Lookup Variants 142
Minimal Number of Banknotes and Coins – sbMinCash 145
Rebalance AssetClass Weights of a Portfolio – sbRebalancedReturn 148
Optimal Pitstops 150
Optimal Usage of Vacation Days 152
Create a Round Robin Tournament – sbRondRobin 154
Test Access Rights 157
Advanced VBA Programs 160
Abstract 160
Employee Revenue Shares 160
Keeping Track of extreme Cell Values – sbCellWatermarks 166
A Task List – sbTaskList 168
Accounts Receivable Problem 171
Data Analysis – sbDatastats 174
System Handbook 174
Overview 174
Parameters in Sheet Param 175
User Handbook 175
Summary 175
Config File FileSpecs.csv 176
Numstats Output 176
Numstats Move Output 177
Textstats Output 177
Textstats Move Output 177
Output Limits File 178
Output Limits Move File 179
Weight Calculation 209
Useful Extensions and Generalizations 211
Lottery k-Tuples 218
Mini Calculator 221
Mortality Annuities 228
Complex Array Formula (Worst) 228
Simple user defined function in VBA (Better) 229
Pre-calculated Table plus an NPV Formula (Maybe Best) 229
Financial Mathematics – Options 230
The binomial Method 230
Trinomial Trees and Finite Difference Methods 236
Monte Carlo Simulation 242
Generate all Permutations of an Array – Quickperm 252
A maintenance-free Database 254
Limitations 254
Responsibilities 254
System Documentation 255
User Documentation 255
Super User with Read/Write Access 255
Normal User with Read-Only Access 255
Appendix – SQL Code 256
Appendix – VBA Code 259
Excursus: Compare Correlation Matrices 262
Abstract 262
Implemention Approach 262
Parameters 262
Rounding Values Preserving Their Sum with RoundToSum 276
Abstract 276
Rounding Values Preserving Their Sum 276
Percentage Example 276
Example with Absolute Values 277
The User-Defined VBA Function RoundToSum 277
RoundToSum Program Code 278
Round2Sum Lambda Expression 279
Rounding Values Alters Their Sum 280
Usage Examples of RoundToSum 282
Allocation of Overheads 282
Example of an Exact Relation of Random Numbers 284
The User-Defined VBA Function sbExactRandHistogrm 285
Fair Staff Selection Based on Team Size – sbFairStaffSelection 287
Distribute a Sample Normally 289
Distribution of Budgets Among Remaining Staff 294
A Simple Approach 294
A Correct Calculation 294
Take Vacation When Less is Going on 295
Simple Example 295
More Complex Example 296
Assign Work Units Adjusted by Delivered Output 297
RoundToSum Versus Other Methods 298
RoundToSum Versus Other "Simple" Methods 298
RoundToSum Compared to the D’Hondt Approach 301
Literature 301
Random Number Generation (Excel / VBA) 302
Abstract 302
Random Integers 302
Natural Random Numbers – UniqRandInt 302
Random Integers – sbRandInt 304
Random Numbers with a Specified Sum 306
Minimum of Random Numbers given – sbLongRandSumN 306
Minimum and Maximum of Random Numbers given – sbRandIntFixSum 307
Usage Examples for Random Integers 309
Krabat, the Satanic Mill – How old can the apprentices become? 309
Generate a Math Test with Random Integer Inputs – Generate_Math_Test 310
Monte Carlo Simulation to Generate Teams Fairly – sbGenerateTeams 312
Monte Carlo Simulation for a Regatta Flight Plan – sbRegattaFlightPlan 316
Chances at Board Game Risk 319
A Simple Monte Carlo Simulation 323
Random Floating Point Numbers 325
Generate an Ideal Normal Distribution – sbGenNormDist 325
Generate Random Numbers with a Sum of 1 – sbRandSum1 327
Distributions of Random Floating Point Numbers 329
sbRandGeneral 329
sbRandHistogrm 332
sbRandTriang 335
sbRandTrigen 336
sbRandCauchy 340
sbRandCDFInv 341
sbRandPDF 342
sbRandCumulative 343
Brownian Bridges 345
sbGrowthSeries 345
Fix Sum from Random Corridors 347
Correlated Random Numbers 349
Cholesky Decomposition 349
Iman-Conover Method 351
Practical Applications of General Random Numbers 358
Generating Test Data – sbGenerateTestData 358
Random Numbers which do not reappear too soon – sbRandomNoRepeatBeforeN 368
Excursus 370
Calculating Probabilities – Drawing Cards With and Without Replacement 370
Fun without Practical Relevance for Advanced Learners 371
A Simple VBA Pivot Table – sbMiniPivot 371
Round-Robin Tournament Pairings with Excel Worksheet Functions 374
Developing a Formula-Based Solution for a Round-Robin Tournament 374
TEXTJOIN 378
Index 379
Please read my Disclaimer.
Plumhoff_Excel_VBA_A_Collection.pdf [13.9 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]