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
During Editing 11
During Program Execution 13
Error Handling 13
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 15
Log Your Program Execution 15
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
sbExportRange2Picture Program Code 29
ExportAllPics Program Code 30
List Named Ranges – sbNamedRanges 32
Show Excel Version – ApplicationVersion 33
Number of Dimensions of an Array – ArrayDim 34
Retrieve Cell Information – sbGetCell 34
Parameters 34
sbGetCell Program Code 36
Next Floating Point Number – sbNextFloat 40
Calling Other Windows Programs Using the Example sbZip 41
Excel Don’t’s – What You Should not be Doing with Excel 43
A table of what you should avoid 43
Number Systems, Formats, and Transformations 44
Abstract 44
Transformations and Calculations of Numbers 44
Spell Numbers in English Words – sbSpellNumber 44
Convert a Decimal Number into its Binary Equivalent or Vice Versa – sbDec2Bin / sbBin2Dec 48
Identify German Bank Holidays – IstFeiertag 53
Present the Full-Length Number – sbNum2Str 57
Return the Number for a Month’s Name – sbMonthNumber 58
Calculation of the Circle Constant π 61
The Calculation of Euler's Number e 63
Return a Shortened Representation of a Number Sequence – sbParseNumSeq 66
Rational Numbers = Fractions 68
Compute 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
Abstract 86
Testing Date Formulas 86
Budget Control 88
Increment Lowest Significant Digit 89
Linear Breakdown 90
Merge two columns justified into one 93
Minimum Truck Load Problem 94
Count Trailing Zeros 95
Minimax Interpolation 95
Interpolation with PERCENTILE / PERCENTRANK 97
Interpolation with TREND 99
REFA Time Slicing 100
Roles and Rights 102
Rounding is Fun 103
Tip Distribution 104
Smoothing Inner Period Values 106
Cell Based Charts 107
Simple VBA Programs 109
Abstract 109
Eliminate Outliers – sbORB 109
Fair Distribution of a Limited Budget – sbDistBudget 111
Compute Collatz Length – sbCollatz 113
Rank Item Uniquely – sbUniqRank 114
sbUniqRank Program Code 115
Eliminate Points of a Graph with Small Slope Changes – sbReducePoints 116
sbReducePoints Program Code 117
Birthday List – sbBirthdayList 118
sbBirthdayList Program Code 119
Accumulated Trade Blotter – sbAccumulatedTadeBlotter 120
sbAccumulatedTradeBlotter Program Code 120
Interpolate – sbInterp 122
sbInterp Program Code 123
Combinations with Subsets k of n 124
Combinations with k subsets of n Program Code 125
Lookup Variants 126
Lookup Program Codes 126
Minimal Number of Banknotes and Coins to make up an Amount – sbMinCash 129
sbMinCoins Program Code 129
sbMinCash Program Code 130
Optimal Pitstops 132
Optimal_Pitstops Program Code 132
Optimal Usage of Vacation Days 134
sbOptimalVacationDays Program Code 135
Create a Round Robin Tournament – sbRondRobin 136
Further Reading 136
sbRoundRobin Program Code 137
Advanced VBA Programs 139
Abstract 139
Accounts Receivable Problem 139
Calculate and Combinations Program Code 140
Data Analysis – sbDatastats 142
System Handbook 142
Overview 142
Parameters in Sheet Param 143
User Handbook 143
Summary 143
Config File FileSpecs.csv 144
Numstats Output 144
Numstats Move Output 145
Textstats Output 145
Textstats Move Output 145
Output Limits File 146
Output Limits Move File 147
sbDatastats Program Code 147
Module Input_Data 148
Module Output_Data 150
Module Workflow 151
Financial Mathematics – Options 176
The binomial Method 176
Chapter 2 Program Code 177
Trinomial Trees and Finite Difference Methods 182
Chapter 3 Program Code 182
Monte Carlo Simulation 187
Monte Carlo Simulation Program Code 187
Generate all Permutations of an Array – Quickperm 197
Quickperm Program Code 198
Weight Calculation 199
Reasonable Extensions and Generalisations 201
AllFirstDraws Program Code 201
AllFirstDraws Monte Carlo Program Code 204
CombinationsWithMinRemainingWeight Program Code 206
Excursus: Compare Correlation Matrices 208
Abstract 208
Implemention Approach 208
Parameters 208
Sample Program Call 209
compareCM.pl Program Code 209
Rounding Values Preserving Their Sum with RoundToSum (Excel / VBA) 222
Abstract 222
Rounding Values Preserving Their Sum 222
Percentage Example 222
Example with Absolute Values 223
The User-Defined VBA Function RoundToSum 223
RoundToSum Program Code 224
Round2Sum Lambda Expression 225
Rounding Values Alters Their Sum 226
Usage Examples of RoundToSum 228
Allocation of Overheads 228
Example of an Exact Relation of Random Numbers 230
The User-Defined VBA Function sbExactRandHistogrm 231
Fair Staff Selection Based on Team Size – sbFairStaffSelection 233
Distribute a Sample Normally 235
Distribution of Budgets Among Remaining Staff 240
A Simple Approach 240
A Correct Calculation 240
Take Vacation When Less is Going on 241
Simple Example 241
More Complex Example 242
Assign Work Units Adjusted by Delivered Output 243
RoundToSum Versus Other Methods 244
RoundToSum Versus Other “Simple” Methods 244
RoundToSum Compared to the D’Hondt Approach 247
Literature 247
Random Number Generation (Excel / VBA) 248
Abstract 248
Random Integers 248
Natural Random Numbers – UniqRandInt 248
Random Integers – sbRandInt 250
Random Numbers with a Specified Sum 252
Minimum of Random Numbers given – sbLongRandSumN 252
Minimum and Maximum of Random Numbers given – sbRandIntFixSum 253
Usage Examples for Random Integers 255
Monte Carlo Simulation to Generate Teams Fairly – sbGenerateTeams 255
Monte Carlo Simulation for a Regatta Flight Plan – sbRegattaFlightPlan 259
Chances at Board Game Risk 263
Krabat, the Satanic Mill – How old can the apprentices become? 267
A Simple Monte Carlo Simulation 268
Random Floating Point Numbers 270
Generate an Ideal Normal Distribution – sbGenNormDist 270
Generate Random Numbers with a Sum of 1 – sbRandSum1 272
Distributions of Random Floating Point Numbers 274
sbRandGeneral 274
sbRandHistogrm 277
sbRandTriang 280
sbRandTrigen 281
sbRandCauchy 285
sbRandCDFInv 286
sbRandPDF 287
sbRandCumulative 288
Brownian Bridges 290
sbGrowthSeries 290
Fix Sum from Random Corridors 292
Correlated Random Numbers 294
Cholesky Decomposition 294
Iman-Conover Method 296
Practical Applications of General Random Numbers 303
Generating Test Data – sbGenerateTestData 303
Excursus 313
Calculating Probabilities – Drawing Cards With and Without Replacement 313
Fun without Practical Relevance for Advanced Learners 315
TEXTJOIN 315
TEXTJOIN Program Code 315
Index 316
Please read my Disclaimer.
Plumhoff_Excel_VBA_A_Collection.pdf [11.8 MB PDF file, open and use at your own risk]