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
Lottery k-Tuples 209
Mini Calculator 212
Mortality Annuities 219
Complex Array Formula (Worst) 219
Simple user defined function in VBA (Better) 220
Pre-calculated Table plus an NPV Formula (Maybe Best) 220
Financial Mathematics – Options 221
The binomial Method 221
Trinomial Trees and Finite Difference Methods 227
Monte Carlo Simulation 233
Generate all Permutations of an Array – Quickperm 243
A maintenance-free Database 245
Limitations 245
Responsibilities 245
System Documentation 246
User Documentation 246
Super User with Read/Write Access 246
Normal User with Read-Only Access 246
Appendix – SQL Code 247
Appendix – VBA Code 250
Excursus: Compare Correlation Matrices 253
Abstract 253
Implemention Approach 253
Parameters 253
Rounding Values Preserving Their Sum with RoundToSum 267
Abstract 267
Rounding Values Preserving Their Sum 267
Percentage Example 267
Example with Absolute Values 268
The User-Defined VBA Function RoundToSum 268
RoundToSum Program Code 269
Round2Sum Lambda Expression 270
Rounding Values Alters Their Sum 271
Usage Examples of RoundToSum 273
Allocation of Overheads 273
Example of an Exact Relation of Random Numbers 275
The User-Defined VBA Function sbExactRandHistogrm 276
Fair Staff Selection Based on Team Size – sbFairStaffSelection 278
Distribute a Sample Normally 280
Distribution of Budgets Among Remaining Staff 285
A Simple Approach 285
A Correct Calculation 285
Take Vacation When Less is Going on 286
Simple Example 286
More Complex Example 287
Assign Work Units Adjusted by Delivered Output 288
RoundToSum Versus Other Methods 289
RoundToSum Versus Other "Simple" Methods 289
RoundToSum Compared to the D’Hondt Approach 292
Literature 292
Random Number Generation (Excel / VBA) 293
Abstract 293
Random Integers 293
Natural Random Numbers – UniqRandInt 293
Random Integers – sbRandInt 295
Random Numbers with a Specified Sum 297
Minimum of Random Numbers given – sbLongRandSumN 297
Minimum and Maximum of Random Numbers given – sbRandIntFixSum 298
Usage Examples for Random Integers 300
Krabat, the Satanic Mill – How old can the apprentices become? 300
Generate a Math Test with Random Integer Inputs – Generate_Math_Test 301
Monte Carlo Simulation to Generate Teams Fairly – sbGenerateTeams 303
Monte Carlo Simulation for a Regatta Flight Plan – sbRegattaFlightPlan 307
Chances at Board Game Risk 310
A Simple Monte Carlo Simulation 314
Random Floating Point Numbers 316
Generate an Ideal Normal Distribution – sbGenNormDist 316
Generate Random Numbers with a Sum of 1 – sbRandSum1 318
Distributions of Random Floating Point Numbers 320
sbRandGeneral 320
sbRandHistogrm 323
sbRandTriang 326
sbRandTrigen 327
sbRandCauchy 331
sbRandCDFInv 332
sbRandPDF 333
sbRandCumulative 334
Brownian Bridges 336
sbGrowthSeries 336
Fix Sum from Random Corridors 338
Correlated Random Numbers 340
Cholesky Decomposition 340
Iman-Conover Method 342
Practical Applications of General Random Numbers 349
Generating Test Data – sbGenerateTestData 349
Random Numbers which do not reappear too soon – sbRandomNoRepeatBeforeN 359
Excursus 361
Calculating Probabilities – Drawing Cards With and Without Replacement 361
Fun without Practical Relevance for Advanced Learners 362
A Simple VBA Pivot Table – sbMiniPivot 362
Round-Robin Tournament Pairings with Excel Worksheet Functions 365
Developing a Formula-Based Solution for a Round-Robin Tournament 365
TEXTJOIN 369
Index 370
Please read my Disclaimer.
Plumhoff_Excel_VBA_A_Collection.pdf [13.7 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]