Dr. Gerard M. Verschuuren - 100 Excel VBA Simulations: Using Excel VBA to Model Risk, Investments, Genetics. Growth, Gambling, and Monte Carlo Analysis

All simulations in this book are supported by files that you can download from the following website: http://www.genesispc.com/download/100VBAsimulations.zip . I assume that you are already familiar with many Excel features, including graphs or charts. In this book, I will only explain in more detail those features that are very helpful when you do what-if-analysis with simulations. For more basic features of Excel, I would refer you to some learning tools that you can find on www.mrexcel.com/microsoft-office-visual-learning.html . If you want to create simulations exclusively with Excel functions and formulas, without using VBA, I recommend another book: http://genesispc.com/tocsimulations100.htm . This book is not about the basics of Visual Basic (VBA) either.

It only uses VBA to make simulations faster, better, and more user-friendly. If you want to learn VBA from the bottom up, I would recommend my interactive CD-ROM: http://genesispc.com/tocvba2013CD.htm . Yet, here are a few basic rules for using VBA: To start a new command line in VBA, use ENTER. Never use ENTER inside a command line. (In this book lines may wrap to the next line, but in VBA that is not allowed.) A colon (:) can separate amd combine several commands on the same line Use an apostrophe () for a comment after, or at the end of, a command line. To create shortcuts in Excel for a macro (or Sub in VBA), you need the Developers tab (if that tab is missing, go to File Options | Macros | Options | Shift + a character.

Files with macros open with the message Enable Content. If you find that annoying place such files in a so-called Trusted Location : Files | Options | Trust Center | Trust Center Setting | Trusted Locations. To open VBA, you can use this shortcut: ALT F11. On the VBA screen, choose: Insert | Module. I always use Option Explicit in VBA: Tools | Options | Require Variable Declaration. This means you always have to declare variables with a Dim statement.

There are value type variables such as integer, double, string (see Appendix) and object type variables (Range, Sheet). The latter require the Set keyword. Type a dot (.) after an object such as Range or Chart in order to get access to its properties and methods. It is wise to use consistent indentation to make your code more readable and checkable. A With statement allows us to refer to it later with just a simple dot (.), followed by a property or method. Formulas are always strings in VBA, so they should be inside double quotes (...).

If there are double quotes inside those double quotes, they should be .... To split a long string into several lines, you use endquotes-space-ampersand-space-underscore-enter-openquotes. To interrupt running code, use Ctrl + Break . If your VBA code ever runs into trouble (and it will!), make sure you stop the Debugger before you can run the code again. You do so by clicking the Reset button:

We start with a very simple case of simulationcasting a die.

The code generates a random number. According to that outcome, the colored die shows the appropriate number of eyes at their proper locations. Each time the random number changes, the die adjusts accordingly. The code stops when you hit the number 6.

Excel has a volatile function called RAND. On each recalculation, this function generates a new random number between 0 and 1.

The equivalent of RAND in VBA is Rnd . In addition to these two operators, later versions of Excel also let you use RANDBETWEEN, which returns a random integer between two integers. Instead of using RANDBETWEEN, you can always use a more complicated formula. If you want numbers between 1 and 6, for instance, you multiply by 6, round the number down by using the INT function, and then add 1 to the end result. More in general: =INT((high-low+1)*RAND()+low). In VBA, you must replace RAND with Rnd .

Finally, we need to regulate which eyes should pop up for each new random number. This is done by using the IIf function in VBA. This function is a decision maker, which determines whether a specific eye should be on or off. GoTo allows the code to jump to a specific labelin this case called Again , followed by a colon. GoTo lets you jump forward or backward in code. A MsgBox can just have an OK button, or a combination of OK, Cancel, Yes, and No.

In case there is more than one option, an IF statement has to check what the users decided to click on.

Sub Dice() Dim i As Integer Again: 'this is called a label that we use at the end to go back to i = Int(Rnd * 6) + 1 Range("B3") = IIf(i > 1, "O", "") Range("D3") = IIf(i > 3, "O", "") Range("B5") = IIf(i = 6, "O", "") Range("C5") = IIf(i = 1 Or i = 3 Or i = 5, "O", "") Range("D5") = IIf(i = 6, "O", "") Range("B7") = IIf(i > 3, "O", "") Range("D7") = IIf(i > 1, "O", "") If i = 6 Then Exit Sub If MsgBox("Number " & i & vbCr & "Again?", vbOKCancel) = vbOK Then GoTo Again End Sub

This time we have six different dice. Each die listens to a random number in VBA. The settings for each die are similar to what we did in simulation 1. There is not much new on this sheet. The main difference is that we need 6 different cells with a RAND function in order to control the six die displays.

This is done with a For -loop in VBA, running from 0 to 5 (or 1 to 6). When there are at least 3 dice in a row with six eyes, all dice get marked at the same time.

A variable of the Variant type can hold an array of items. We fill the array here by using the Array function in VBA. This array starts at 0 (thats why the For -loop runs from 0 to 5 instead of from 1 to 6). Notice that cell rows and columns always start at 1 (not 0).

VBA can use almost all Excel functions by calling them with WorksheetFunction . In this case we use Excels COUNTBLANK function. The use of Range and Cells in VBA can be very powerful, but can also be rather confusing at first sight (see Appendix). Range(A1) is equivalent to Cells(1,1) , but the latter one is more flexible in loops because we can use a loop variable for the row and/or the column position. Sometimes, they are combined: Range(Cells(1,1),Cells(10,2)) would refer to A1:B10. Another important tool in VBA is Offset , with which you can specify the row offset and the column offset.

For instance, Range(A1).Offset(2,2) would evaluate to cell C3. Dont confuse End Sub with Exit Sub . Each Sub must close with End Sub . But if you want to prematurely end the Sub routine, you must use Exit Sub .