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]