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]