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
The Excel / VBA Programming Environment 6
Abstract 6
Basics 6
During Editing 6
During Program Execution 8
Good Programming Practices 9
Be a Good Programmer 9
Good Excel and VBA Knowledge 9
Programming Conventions 9
Clean Up Macro Recordings 9
Document Your Program Adequately 9
Test Your Program Thoroughly 9
Log Your Program Execution 9
Optimize Your Program 10
System Status Save and Restore – SystemState Class 11
System Status Variables 12
SystemState Code 13
Documenting Program Flow – Logging Class 15
Pros and Cons 15
Parameters 16
Sample Output 17
Modules 17
Class Modules 21
ShowExcel Version – ApplicationVersion 22
Number of Dimensionen of an Array – ArrayDim 23
Calling Other Windows Programs Using the Example sbZip 24
Number Systems, Formats, and Transformations 26
Abstract 26
Transformations and Calculations of Numbers 26
Spell Numbers in English Words – sbSpellNumber 26
Convert a Decimal Number into its Binary Equivalent or Vice Versa – sbDec2Bin / sbBin2Dec 30
Identify German Bank Holidays – IstFeiertag 35
Present the Full-Length Number – sbNum2Str 39
Return the Number for a Month’s Name – sbMonthNumber 40
Calculation of the Circle Constant π 43
The Calculation of Euler's Number e 45
Return a Shortened Representation of a Number Sequence – sbParseNumSeq 48
Rational Numbers = Fractions 50
Compute Nearest Rational Number to a Given Floating Point Number – sbNRN 50
Linear Equations with Rational Coeffizients 53
Present Quota Changes as Fractions 57
Monthly Fractions 58
Linear Combination of Integers 59
Extended Euklidean Algorithm – sbEuklid 59
Time Representations 61
Calculate Working Hours Between Two Time Points – sbTimeDiff 61
Add Working Hours to a Time Point – sbTimeAdd 64
Convert a Time to a Different Time Zone – ConvertTime 67
Check Digits 68
Calculate or Check a European Article Number – sbEAN 68
Ordinal Numbers 69
Rounding Values Preserving Their Sum with RoundToSum (Excel / VBA) 70
Abstract 70
Rounding Values Preserving Their Sum 70
Percentage Example 70
Example with Absolute Values 71
The User-Defined VBA Function RoundToSum 71
RoundToSum Program Code 72
Round2Sum Lambda Expression 73
Rounding Values Alters Their Sum 74
Usage Examples of RoundToSum 76
Allocation of Overheads 76
Example of an Exact Relation of Random Numbers 78
The User-Defined VBA Function sbExactRandHistogrm 79
Fair Staff Selection Based on Team Size – sbFairStaffSelection 81
Distribute a Sample Normally 83
Distribution of Budgets Among Remaining Staff 88
A Simple Approach 88
A Correct Calculation 88
Take Vacation When Less is Going on 89
Simple Example 89
More Complex Example 90
Assign Work Units Adjusted by Delivered Output 91
RoundToSum Versus Other Methods 92
RoundToSum Versus Other “Simple” Methods 92
RoundToSum Compared to the D’Hondt Approach 95
Literature 95
Random Number Generation (Excel / VBA) 96
Abstract 96
Random Integers 96
Natural Random Numbers – UniqRandInt 96
Random Integers – sbRandInt 98
Random Numbers with a Specified Sum 100
Minimum of Random Numbers given – sbLongRandSumN 100
Minimum and Maximum of Random Numbers given – sbRandIntFixSum 101
Usage Examples for Random Integers 103
Monte Carlo Simulation to Generate Teams Fairly – sbGenerateTeams 103
Monte Carlo Simulation for a Regatta Flight Plan – sbRegattaFlightPlan 107
Chances at Board Game Risk 111
Krabat, the Satanic Mill – How old can the apprentices become? 115
A Simple Monte Carlo Simulation 116
Random Floating Point Numbers 118
Generate an Ideal Normal Distribution – sbGenNormDist 118
Generate Random Numbers with a Sum of 1 – sbRandSum1 120
Distributions of Random Floating Point Numbers 122
sbRandGeneral 122
sbRandHistogrm 125
sbRandTriang 128
sbRandTrigen 129
sbRandCauchy 133
sbRandCDFInv 134
sbRandPDF 135
sbRandCumulative 136
Brownian Bridges 138
sbGrowthSeries 138
Fix Sum from Random Corridors 140
Correlated Random Numbers 142
Cholesky Decomposition 142
Iman-Conover Method 144
Practical Applications of General Random Numbers 151
Generating Test Data – sbGenerateTestData 151
Excursus 161
Calculating Probabilities – Drawing Cards With and Without Replacement 161
Index 163
Please read my Disclaimer.
Plumhoff_Excel_VBA_A_Collection.pdf [3,212 KB PDF file, open and use at your own risk]