Excel VBA A Collection
This is a collection of Excel VBA programs and of some Excel spreadsheet formulas which I found reasonable.
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 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
Export a Range as Picture – sbExportRange2Picture 29
List Named Ranges – sbNamedRanges 32
Show Excel Version – ApplicationVersion 33
Number of Dimensions of an Array – ArrayDim 34
Retrieve Cell Information – sbGetCell 34
Next Floating Point Number – sbNextFloat 40
Calling Other Windows Programs Using the Example sbZip 40
Excel Don’t’s – What You Should not be Doing with Excel 42
A table of what you should avoid 42
Number Systems, Formats, and Transformations 43
Abstract 43
Transformations and Calculations of Numbers 43
Spell Numbers in English Words – sbSpellNumber 43
Convert Decimal into Binary Numbers – sbDec2Bin / sbBin2Dec 47
Identify German Bank Holidays – IstFeiertag 52
Present the Full-Length Number – sbNum2Str 56
Significant Digits of a Number – sbNSig 57
Return the Number for a Month’s Name – sbMonthNumber 58
Calculation of the Circle Constant π 61
First 1,000 Digits of π 63
Calculation of Euler's Number e 64
First 1,000 Digits of e 65
Literature 66
Shorten a Number Sequence Representation – sbParseNumSeq 66
Rational Numbers = Fractions 68
Nearest Rational Number to a Given Floating Point Number – sbNRN 68
Linear Equations with Rational Coeffizients 71
Present Quota Changes as Fractions 74
Monthly Fractions 75
Linear Combination of Integers 76
Extended Euklidean Algorithm – sbEuklid 76
Time Representations 78
Calculate Working Hours Between Two Time Points – sbTimeDiff 78
Add Working Hours to a Time Point – sbTimeAdd 81
Convert a Time to a Different Time Zone – ConvertTime 84
Check Digits 84
Calculate or Check a European Article Number – sbEAN 84
Ordinal Numbers 85
Simple Math with Formulas 86
How to Analyze Spreadsheet Formulas 86
Testing Date Formulas 87
First or last Weekday of a Month 89
Same Weekday and Calendarweek last year 90
Budget Control 91
Increment Lowest Significant Digit 92
Linear Breakdown 93
Merge two columns justified into one 96
Minimum Truck Load Problem 97
Count Trailing Zeros 98
Minimax Interpolation 98
Interpolation with PERCENTILE / PERCENTRANK 100
Interpolation with TREND 102
REFA Time Slicing 103
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
Accumulated Trade Blotter – sbAccumulatedTadeBlotter 125
Most Frequent Pairs – sbMostFrequentPairs 127
Interpolate – sbInterp 129
Combinations with Subsets k of n 131
Lookup Variants 133
Minimal Number of Banknotes and Coins – sbMinCash 136
Rebalance AssetClass Weights of a Portfolio – sbRebalancedReturn 139
Optimal Pitstops 141
Optimal Usage of Vacation Days 143
Create a Round Robin Tournament – sbRondRobin 145
Further Reading 145
Test Access Rights 148
Advanced VBA Programs 150
Abstract 150
Keeping Track of extreme Cell Values – sbCellWatermarks 150
A Task List – sbTaskList 152
Accounts Receivable Problem 155
Data Analysis – sbDatastats 158
System Handbook 158
Overview 158
Parameters in Sheet Param 159
User Handbook 159
Summary 159
Config File FileSpecs.csv 160
Numstats Output 160
Numstats Move Output 161
Textstats Output 161
Textstats Move Output 161
Output Limits File 162
Output Limits Move File 163
Mini Calculator 192
Mortality Annuities 199
Complex Array Formula (Worst) 199
Simple user defined function in VBA (Better) 200
Pre-calculated Table plus an NPV Formula (Maybe Best) 200
Financial Mathematics – Options 201
The binomial Method 201
Trinomial Trees and Finite Difference Methods 207
Monte Carlo Simulation 212
Generate all Permutations of an Array – Quickperm 222
Weight Calculation 224
Reasonable Extensions and Generalisations 226
A maintenance-free Database 233
Limitations 233
Responsibilities 233
System Documentation 234
User Documentation 234
Super User with Read/Write Access 234
Normal User with Read-Only Access 234
Appendix – SQL Code 235
Appendix – VBA Code 238
Excursus: Compare Correlation Matrices 241
Abstract 241
Implemention Approach 241
Parameters 241
Rounding Values Preserving Their Sum with RoundToSum 255
Abstract 255
Rounding Values Preserving Their Sum 255
Percentage Example 255
Example with Absolute Values 256
The User-Defined VBA Function RoundToSum 256
RoundToSum Program Code 257
Round2Sum Lambda Expression 258
Rounding Values Alters Their Sum 259
Usage Examples of RoundToSum 261
Allocation of Overheads 261
Example of an Exact Relation of Random Numbers 263
The User-Defined VBA Function sbExactRandHistogrm 264
Fair Staff Selection Based on Team Size – sbFairStaffSelection 266
Distribute a Sample Normally 268
Distribution of Budgets Among Remaining Staff 273
A Simple Approach 273
A Correct Calculation 273
Take Vacation When Less is Going on 274
Simple Example 274
More Complex Example 275
Assign Work Units Adjusted by Delivered Output 276
RoundToSum Versus Other Methods 277
RoundToSum Versus Other "Simple" Methods 277
RoundToSum Compared to the D’Hondt Approach 280
Literature 280
Random Number Generation (Excel / VBA) 281
Abstract 281
Random Integers 281
Natural Random Numbers – UniqRandInt 281
Random Integers – sbRandInt 283
Random Numbers with a Specified Sum 285
Minimum of Random Numbers given – sbLongRandSumN 285
Minimum and Maximum of Random Numbers given – sbRandIntFixSum 286
Usage Examples for Random Integers 288
Krabat, the Satanic Mill – How old can the apprentices become? 288
Generate a Math Test with Random Integer Inputs – Generate_Math_Test 289
Monte Carlo Simulation to Generate Teams Fairly – sbGenerateTeams 291
Monte Carlo Simulation for a Regatta Flight Plan – sbRegattaFlightPlan 295
Chances at Board Game Risk 299
A Simple Monte Carlo Simulation 303
Random Floating Point Numbers 305
Generate an Ideal Normal Distribution – sbGenNormDist 305
Generate Random Numbers with a Sum of 1 – sbRandSum1 307
Distributions of Random Floating Point Numbers 309
sbRandGeneral 309
sbRandHistogrm 312
sbRandTriang 315
sbRandTrigen 316
sbRandCauchy 320
sbRandCDFInv 321
sbRandPDF 322
sbRandCumulative 323
Brownian Bridges 325
sbGrowthSeries 325
Fix Sum from Random Corridors 327
Correlated Random Numbers 329
Cholesky Decomposition 329
Iman-Conover Method 331
Practical Applications of General Random Numbers 338
Generating Test Data – sbGenerateTestData 338
Random Numbers which do not reappear too soon – sbRandomNoRepeatBeforeN 348
Excursus 350
Calculating Probabilities – Drawing Cards With and Without Replacement 350
Fun without Practical Relevance for Advanced Learners 352
A Simple VBA Pivot Table – sbMiniPivot 352
TEXTJOIN 355
Index 356
Please read my Disclaimer.
Plumhoff_Excel_VBA_A_Collection.pdf [13.1 MB PDF file, open and use at your own risk]