Write model output to the summary worksheets - Microsoft Excel Tutorial

- [Instructor] When you run a Solver simulation with multiple trials, you should use VBA to write the output of each trial to one or more summary worksheets. In this movie, I will show you how to do that for the model we've created. My sample file is 02_02_WriteResults and you can find it in the exercise files folder. On the ModelSheet worksheet, I have the small transportation model we're working with. I have added a bit though and it's at the bottom of the worksheet, so I'll scroll down. So we have a random demand, which we've had before. But below, I have written the values from the Solver solution, which is in cells H11 to H13 over here in yellow to a row. And so we have cells B29 through G29. We're going to be writing those Solver Solution values to the Shipments worksheet into cells B2 through G2, at least for the first row. So you can see that it would be easier to do that with the values in a row, as opposed to pulling them from a three-by-two array. And of course, the demand worksheet is laid out as before. Okay, with everything in place, I will press Alt + F11 to move over to the Visual Basic Editor and we can add the code that we need to write our values. There are some new elements that I have added here. At the top, I have defined some new variables and those include intSolverResults and that is a six-item array. It has a five next to it but because we're writing a computer program, we start counting at zero instead of at one. And then below that, I have two new integers. RowOffset and ColumnOffset, which will allow us to use the base cell and refer to it using the offset property of the active cell. Another command I've added below after we set calculation to manual is to set ScreenUpdating to false. Because we're going to be writing to different worksheets and moving among them programmatically, the screen would flash or at least appear to flash when we move from one to another through activation. Turning ScreenUpdating off stops that flashing, which is very, very annoying, especially if it goes on for a long time. With that, I activate the starting cells that I'll use as references in each of my worksheets. So on ModelSheet, that's C4 and then on Demand and Shipments, it's B2 for each of them. Then we have code that we have worked with before, which allows us to get the number of iterations and then run that number. And below that, we read the values from cell C21 to C24 and write them up to the values in C4 through C7. Right, so the first new bit of code that we will add is to write our demand values, which we've already read into an array to the demand table on the demand worksheet. So I'll scroll down a bit, so this is closer to the top. And underneath the comment that I have identified here, I need to add several lines of code. First, we need to activate the demand worksheet, so that'll be ActiveWorkbook.Worksheets and then the name of it is Demand. I'll use demand instead of its number because it might be different in your workbook. Period and Activate. So we'll activate that worksheet. Now we need to write values from the intReadValues array to to our ActiveCell as defined by our offset on the Demand worksheet. So basically what we're doing is we're writing values into the first blank row on the demand worksheet. So that'll be for intElement and that's just an integer counter variable that I declared earlier equals LBound, that's the lower bound, which in this case will be zero of the ReadValues array to the upper bound and in this case, that would be three because we have a for item array of intReadValues. Then I'll move in, pressing the space bar twice to indicate we're inside of a loop. And then I will write the value to the active cell offset by the value of the intElement variable with the corresponding value from the ReadValues array. So I have ActiveCell.Offset zero. So that'll be zero rows offset but we do want to move over the number of columns equal to the integer element. So that'll be anywhere from zero to three. So we have zero intElement.Value equals. So we're assigning that cell value to intReadValues intElement. Looks good. So then I will go down. Type Next and I like to put in a comment indicating which variable we're iterating on. So I have intElement there. All right, everything looks good here. So I'll just walk through the rest of the code 'cause remember that we also need to write values to the shipment or volume table. But I've added that code below and I'll comment on it briefly but it's pretty much a repeat of what I showed you here. So I'll scroll down. After we write the values that we did above, we activate the next cell down. That is the cell in the row below the active cell in column B. We do that do the data will be written below the table and when you add data below an Excel table, Excel incorporates it into the table automatically. So that saves us a step. Then below, we run the Solver model and then here we read the Solver solution into our array. So remember, we have formulas that bring the data into the row, starting at B29 on the model sheet and if I press Alt + F11 and go to ModelSheet, you can see those values starting there. Alt + F11 again. So we read that value into our array. And then down here, we go to the Shipments worksheet and you can see that the code is almost exactly the same. We read the values into the Solver results array and write them out into the worksheet and then at the bottom, I'll turn calculation back on and also, I will turn ScreenUpdating back on. Technically you don't have to turn ScreenUpdating back on. It should happen automatically but in this case, I like to do it just to be sure.