WIXLIB

MANAGEMENT OF PRODUCTION AND INVENTORY SYSTEMSMION01Tutorial and introductory exercise to Visual Basic(VBA) programming in ExcelIntroductionProgramming macro’s in Excel is a convenient way to build your own customized decision supporttools. A macro is essentially a computer program that can be controlled via Excel. It can be used toperform more advanced calculations that are difficult or even impossible to do in the actualspreadsheet. The programming language used by Excel is called Visual Basic. An advantage withusing VBA programming in Excel is that it comes with Excel, and no additional compiler orsoftware program is needed.In this introduction, we focus on how VBA/Macro programming in Excel can be used to implementmore advanced inventory control models, and to create useful decision support tools. You are morethan welcome to apply this in order to solve the assignments in the course, but it is not required!VBA Programming in general offers almost endless possibilities to build your own cool softwareapplications, but we will here limit ourselves to very basic functionality.This tutorial consists of 4 parts: A general introduction to VBA programming in Excel in terms of excerpts from the book Wølk(2010) available in Appendix A. This provides a general introduction to VBA programming andhow to get started. An illustrative inventory control problem to be implemented as an Excel/VBA program. Asolution to this problem is provided in the Excel file titled “VBA program template - (R,Q)fillrate evaluation and optimization” available on the course website. This program is designedas a template that can be used for programming other inventory control models. The solutionillustrates most of the basic VBA syntax that is needed to build programs of inventory models.By going through how the given program solves the problem at hand you will get a tutorial ofapplying VBA programming to solve inventory control problems. A small exercise which is based on extending the analysis of the illustrative control problemabove. A shortlist of useful VBA syntax and commands. This can be seen as a complement to the Helpfunction in the software, and the provided program template discussed above.1

To study this material on your own I recommend that you follow the order above. To get the mostout of studying the provided program you need to first recap the theoretical solution to the problemwhich is available, for example, in Axsäter (2006).The instructor led tutorial will focus on explaining how the provided program is constructed, andstep by step how the given problem is solved. To benefit from that you need to prepare by readingup on the analytical solution to the problem so that you can recognize how this is implemented inthe program.An illustrative inventory control problemWe consider a basic single-echelon inventory system with constant replenishment leadtime,complete backordering, normally distributed customer demand, controlled by a continuous review(R,Q)-policy. The problem is to construct a VBA program that will: (i) compute the fillrate for agiven policy (R,Q) and (ii) optimize R to minimize the inventory while meeting a specified targetfillrate (i.e., a service constraint model). The program should be designed so that an arbitrarynumber of single-echelon systems can be evaluated in a single program run.The input data required for the analysis encompass: The mean, µ, and standard deviation, , of the demand per time unit The order quantity, Q (and for (i) above the reorder point, R) The replenishment leadtime, L.This inventory problem is thoroughly analyzed in Axsäter (2006) pp. 91-94, and pp. 98-99. Youshould go through this analysis before continuing. When you have familiarized yourself with thetheoretical solution of the problem you can continue to study the Excel template program “VBAprogram template - (R,Q) fillrate evaluation and optimization” available on the course website. Thisprogram contains an implementation of the theoretical solution.The program consists of an Excel worksheet named “Interface” and an underlying VBA program.The interface sheet is where indata is entered and where the results are displayed. You run theexisting program by clicking on the button “Run”. The choice between evaluating the expectedfillrate of an existing policy, and minimizing the reorder point given a specified target fillrate isdone by setting the variable Choice to 0 or 1 respectively. The number of inventory systems (oritems in the same system) you want to evaluate during one program run is chosen by setting thevalue of N appropriately. Currently N 1 and only a single system is considered. Test and see whathappens in the sheet when you change the number, say to N 5.When studying the program, you should start by first running it once and see what happens. You runthe program by clicking on the “Run” button in the worksheet named Interface. The next step is toopen the Visual Basic Editor as explained in Appendix A. In the upper left side of the VBA editoryou can see that the program consists of 4 modules: Public variables: Contains declarations of all public variables. The values of publicvariables can be changed anywhere in the program and they do not need to be declared asinput and output parameters in subroutines and functions. It is convenient to use publicvariables but very dangerous for larger programs. Locally declared variables are generallypreferred2

Main program: Contains the code for the main program that is executed when the “Run”button in the worksheet “Interface” is clicked on.Input data module: Contains the code for the subroutine Input data. This subroutine readsthe input data specified in the Excel sheet “Interface” and assign it to variables defined in theprogram.Normal demand single stage R Q: Contains the code for the functions and subroutinescalled by the main program to solve the problem.When analyzing the program a recommendation is to do so by looking at the modules in the orderspecified above. The main logic is specified in the main program and the detailed calculations areperformed in the functions and subroutines called by the main program in order to complete the task.Simple ways of stepping through the code line by line are available under the “Debug” tab found onthe top of the VBA editor window. You can experiment with different ways to step through thecode, but one simple way is as follows:1) Double click on the module Main program2) Set a breakpoint by clicking on the gray border to the left of the lineSet interface Worksheets("interface")The entire line should then be highlighted in dark red.3) Run the program by clicking on the “Run” button in the Interface worksheet or by clickingon the green “play” icon directly below the “Debug” tab in the VBA Editor.4) The program will execute and stop at the breakpoint which will be highlighted in yellow. Tostep line by line through the program you just press the key F8.5) You can set as many break points you want, and you can run the program stepwise from onebreakpoint to the next (instead of stepwise line by line) by pressing the key F5.The code contains a lot of comments (text in green which starts with ') the first comment in the mainprogram is:'********Different types of variable declarations************************The comments are not executable code but simply explanations of what the code is currently doing.To document your code by writing explanatory comments about what the code is doing is a verygood strategy. It makes it much easier for others (and yourself) to read and understand the code.Hopefully the comments in the current program are sufficient for you to read the code andunderstand what it does without too much effort.A small exercise – extending the analysis of the considered inventory problemAssume that the holding cost per unit and time unit, h, and the shortage cost per unit and time unit,p, are available. Modify the provided VBA program so that it can be used for evaluating theexpected costs of the considered inventory system. The theoretical analysis of this problem isavailable in Axsäter (2006) pp. 103-105. In the resulting program you should be able to choose theoption of evaluating the expected costs in the Excel sheet “Interface”.3

Shortlist of useful VBA syntax and commandsThis incomplete list includes some basic VBA syntax and commands. Good book references forsomeone who wants to learn more include Wølk (2010), Walkenbach (2004), and more recenteditions of the same text. The list below should be seen as a complement to the template programwhich illustrates how most of the syntax works. To get help and information about how differentVBA commands work the “Help” tab at the top of the VBA Editor (to the right) is very useful. It isrecommended that it is used to get more thorough explanations of the items in the list below. If youhave a command that you want to know more details about, a tip is to mark it in the code and thenhit F1. This is a shortcut to the help function. Option Explicit: If entered at the very top of a module it prevents the code in that module to runif it contains undeclared variables Different types of loop statements: For-To-Next and Do-While-LoopFor integer var lower limit To upper limit Next‘Unconditional loopDo While statement True Loop‘Conditional loop If-Then-Else-End If statementsIf statement1 true Then ElseIf statement2 true Then Else Endif‘Optional condition level‘Optional condition level WorksheetFunction.object : VBA contains a large number of WorksheetFunction objects, whichcan be thought of as small programs and specialized functions that can be very helpful in buildingthe code. For example,y WorksheetFunction.NormDist(x, 0, 1, True)assigns the value of the cumulative distribution function for a normal distribution with mean 0and standard deviation 1 and the argument x, to the variable y. (y P(X x) when X N(0,1)).A list of the available WorksheetFunction objects can be obtained by pressing the key F2 whenthe marker is in the VBA Editor window. Int(number) and Fix(number) : Both Int and Fix remove the fractional part of number andreturn the resulting integer value. The difference between Int and Fix is that if number isnegative, Int returns the first negative integer less than or equal to number, whereas Fix returnsthe first negative integer greater than or equal to number. For example, Int converts -8.4 to -9,and Fix converts -8.4 to -8. Abs(x): Returns the absolute value of the number x4

Exp(x): Returns the value ex Log(x): Returns the natural logarithm of x MsgBox: Displays a message box and (optionally) returns a value (see Help in VBA Editor formore details). Now: Returns the current system date and time Rnd: Returns a uniformly distributed random number between 0 and 1 Sqr(x): Returns the square root of the number x Time: Returns the current system time And: Logical and that can be used for combining conditions, i.e., If (x 5) And (x 10) Then Or: Logical either or that can be used for combining conditions Stop: Stops the program at the line where this command is found and opens the VBA Editor fordebugging.ReferencesAxsäter S. Inventory Control, Second edition. Springer, New York, 2006Walkenbach, J., Excel VBA programming for Dummies, Wiley Publishing, 2004.Wølk, S., VBA Programming in Business Economics, DJØF Publishing, Copenhagen, 20105

Appendix AIntroduction to VBA programming in ExcelExcerpts fromWølk, S., VBA Programming in Business Economics, DJØF Publishing, Copenhagen, 20106

(VBA) programming in Excel Introduction Programming macro's in Excel is a convenient way to build your own customized decision support tools. A macro is essentially a computer program that can be controlled via Excel. It can be used to perform more advanced calculations that are difficult or even impossible to do in the actual spreadsheet.