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
Same Weekday and Calendarweek last year 89
Budget Control 90
Increment Lowest Significant Digit 91
Linear Breakdown 92
Merge two columns justified into one 95
Minimum Truck Load Problem 96
Count Trailing Zeros 97
Minimax Interpolation 97
Interpolation with PERCENTILE / PERCENTRANK 99
Interpolation with TREND 101
REFA Time Slicing 102
Roles and Rights 103
Rounding is Fun 104
Tip Distribution 105
Smoothing Inner Period Values 107
Cell Based Charts 108
Simple VBA Programs 110
Abstract 110
Sum up Numbers with same Number Format – sbSumMyFormat 110
Count Cells with Certain Color – sbCountMyColor 111
Allocate Assets to a Portfolio – sbAllocate 112
Eliminate Outliers – sbORB 113
Fair Distribution of a Limited Budget – sbDistBudget 115
Compute Collatz Length – sbCollatz 117
Rank Item Uniquely – sbUniqRank 118
Eliminate Points of a Graph with Small Slope Changes – sbReducePoints 120
Birthday List – sbBirthdayList 122
Accumulated Trade Blotter – sbAccumulatedTadeBlotter 124
Most Frequent Pairs – sbMostFrequentPairs 126
Interpolate – sbInterp 128
Combinations with Subsets k of n 130
Lookup Variants 132
Minimal Number of Banknotes and Coins – sbMinCash 135
Rebalance AssetClass Weights of a Portfolio – sbRebalancedReturn 138
Optimal Pitstops 140
Optimal Usage of Vacation Days 142
Create a Round Robin Tournament – sbRondRobin 144
Further Reading 144
Test Access Rights 147
Advanced VBA Programs 149
Abstract 149
Keeping Track of extreme Cell Values – sbCellWatermarks 149
A Task List – sbTaskList 151
Accounts Receivable Problem 154
Data Analysis – sbDatastats 157
System Handbook 157
Overview 157
Parameters in Sheet Param 158
User Handbook 158
Summary 158
Config File FileSpecs.csv 159
Numstats Output 159
Numstats Move Output 160
Textstats Output 160
Textstats Move Output 160
Output Limits File 161
Output Limits Move File 162
Mini Calculator 191
Mortality Annuities 198
Complex Array Formula (Worst) 198
Simple user defined function in VBA (Better) 199
Pre-calculated Table plus an NPV Formula (Maybe Best) 199
Financial Mathematics – Options 200
The binomial Method 200
Trinomial Trees and Finite Difference Methods 206
Monte Carlo Simulation 211
Generate all Permutations of an Array – Quickperm 221
Weight Calculation 223
Reasonable Extensions and Generalisations 225
A maintenance-free Database 232
Limitations 232
Responsibilities 232
System Documentation 233
User Documentation 233
Super User with Read/Write Access 233
Normal User with Read-Only Access 233
Appendix – SQL Code 234
Appendix – VBA Code 237
Excursus: Compare Correlation Matrices 240
Abstract 240
Implemention Approach 240
Parameters 240
Sample Program Call 241
Rounding Values Preserving Their Sum with RoundToSum 254
Abstract 254
Rounding Values Preserving Their Sum 254
Percentage Example 254
Example with Absolute Values 255
The User-Defined VBA Function RoundToSum 255
RoundToSum Program Code 256
Round2Sum Lambda Expression 257
Rounding Values Alters Their Sum 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 298
A Simple Monte Carlo Simulation 302
Random Floating Point Numbers 304
Generate an Ideal Normal Distribution – sbGenNormDist 304
Generate Random Numbers with a Sum of 1 – sbRandSum1 306
Distributions of Random Floating Point Numbers 308
sbRandGeneral 308
sbRandHistogrm 311
sbRandTriang 314
sbRandTrigen 315
sbRandCauchy 319
sbRandCDFInv 320
sbRandPDF 321
sbRandCumulative 322
Brownian Bridges 324
sbGrowthSeries 324
Fix Sum from Random Corridors 326
Correlated Random Numbers 328
Cholesky Decomposition 328
Iman-Conover Method 330
Practical Applications of General Random Numbers 337
Generating Test Data – sbGenerateTestData 337
Random Numbers which do not reappear too soon – sbRandomNoRepeatBeforeN 347
Excursus 349
Calculating Probabilities – Drawing Cards With and Without Replacement 349
Fun without Practical Relevance for Advanced Learners 351
A Simple VBA Pivot Table – sbMiniPivot 351
TEXTJOIN 354
Index 355
Please read my Disclaimer.
Plumhoff_Excel_VBA_A_Collection.pdf [13.1 MB PDF file, open and use at your own risk]