Add Excel VBA code to create three new columns to the existing code listed below. Add Standard deviation for X army in Column, add Standard deviation for Y army in Column K, add Number of runs in Column L.

Let me know if you need the Excel spreadsheet.

Existing VBA Code:

Function checkData(ByVal rowNum As Integer, colNum As Integer, dataValue, lanchester_inputs) As Boolean

'return value indicates whether a particular data point is >0 and numeric and non-blank

'Data is read into the variable dataValue

'these variables are private to this function (an example of information hiding)

Dim inputCell As Range

Set inputCell = lanchester_inputs.Cells(rowNum, colNum) 'this is for readability purpose

inputCell.ClearFormats 'removes any previous formatting.

If IsEmpty(inputCell) Then

checkData = False

ElseIf IsNumeric(inputCell) And inputCell.Value > 0 Then

checkData = True

dataValue = inputCell.Value

Else 'non-empty but bad data

checkData = False

End If

End Function 'end of check data

______________________________________________________________

Private Function getUserInputs(ByVal rowNum, ByRef x0, ByRef alphaMin, alphaMax, fatigueX, y0, betaMin, betaMax, fatigueY, ByRef deltaT As Double) As Boolean

'Note: pass by reference (changes made in this function will "stick") is VBA default and does not need to be specified.

'byVal is used for rowNum because it represents a row number which comes from an external for loop and thus cannot be

'changed by this function.

'all input variables must be > 0, non-blank and numeric, so use a function to check for that

'use of cells passed into the function allows the checkData function to check for numeric

'check data in each column on the specified row. All the results must be true for the whole row

'to be good data, which is why the "And" operator is used. A single false result from check data

'will cause the chain of Ands to be false. The _ at the end of the line signals a continuation to

'the next line.

'all reading is done from the "lanchester_inputs sheet

Dim lanchester_inputs As Worksheet

Set lanchester_inputs = Worksheets("lanchester_inputs")

With lanchester_inputs

getUserInputs = checkData(rowNum, 1, x0, lanchester_inputs) _

And checkData(rowNum, 2, alphaMin, lanchester_inputs) _

And checkData(rowNum, 3, alphaMax, lanchester_inputs) _

And checkData(rowNum, 4, fatigueX, lanchester_inputs) _

And checkData(rowNum, 5, y0, lanchester_inputs) _

And checkData(rowNum, 6, betaMin, lanchester_inputs) _

And checkData(rowNum, 7, betaMax, lanchester_inputs) _

And checkData(rowNum, 8, fatigueY, lanchester_inputs) _

And checkData(rowNum, 9, deltaT, lanchester_inputs)

If Not getUserInputs Then

Exit Function 'do not process further to avoid crashing when you check alpha and beta

End If

'now check to make sure that max < min

If (alphaMin > alphaMax) Then

getUserInputs = False

End If

If (betaMin > betaMax) Then

getUserInputs = False

End If

End With

End Function 'getUserInputs

___________________________________________________________

Sub LanchesterSimulation()

'This subroutine implements the Lanchester "modern" (squared law) battle

'simulation. In other words it "solves" the system of differential equations;

' dx/dt = -beta*y ; dy/dt = -alpha*x for alpha, beta, x, y > 0

'User inputs are troop starting levels (x0 and y0) and effectiveness coefficients

'(alpha and beta) for the respective sides of the battle.

'Error checking for inputs is included. Other errors go to a generic error message

'Expected output is a "display" of the time-history for each side

'The system of differential equations is "solved" using Euler's method as

'described in the Build assignment materials

Application.ScreenUpdating = False 'makes code run faster - must turn back on at the end of the sub

'input variables as described above - each declaration must be on a separate line

Dim alpha As Double

Dim alphaMin As Double

Dim alphaMax As Double

Dim fatigueX As Double

Dim beta As Double

Dim betaMin As Double

Dim betaMax As Double

Dim fatigueY As Double

Dim priorX As Double

Dim priorY As Double

Dim currX As Double

Dim currY As Double 'troop levels at current (priorX) and new time(currX)

Dim t As Double 'current time

Dim deltaT As Double 'time step

Dim iteration As Integer 'track how many times through the loop to prevent long running code

Dim i As Integer 'loop counter

Dim allGoodData As Boolean 'is there a problem anywhere in the data

Dim rowNum As Integer 'use to track which row in the inputs sheet i

Dim stalled As Boolean 'is the battle still progressing?

Dim baselineName As String 'name for constructing the worksheet names

Dim writeWS As Worksheet 'name of worksheet to write to

Dim outputHeadings As Variant

outputHeadings = Array("time", "X troops remaining", "Y troops remaining", "alpha", "beta") 'array for title row

Const TOL = 0.00001 'what counts as no more progress in the battle? Use tolerance test between iterations

'initialize variables which should be set later from user inputs

alpha = 0

beta = 0

priorX = 0 'prior troop level

priorY = 0

currX = 0 'current troop level

currY = 0

t = 0 ' battle starts at time zero

iteration = 0 'how many times through the simulation loop?

deltaT = 0.1 'note that there are no units

stalled = False

allGoodData = True 'assume that all of my data is good until proven otherwise.

'the ICD does not say that the worksheet has to be in a separate file, so this code assumes that

'it is being envoked from the activesheet, which is the named lanchester_inputs. To be more robust

'checking for the sheet name and if it is not there browsing for an external file should be done.

'Use functions to initialize variables and do the error checking

'the inputs are processed as a whole row (similar to the baseline doing all the inputs at once)

'a false return value from the getUserInputs indicates that there was

'a problem somewhere on that particular row. This design can be extended to multiple rows by

'reading in a loop.

'data starts on row 2 as per ICD.

allGoodData = getUserInputs(2, priorX, alphaMin, alphaMax, fatigueX, priorY, betaMin, betaMax, _

fatigueY, deltaT)

If Not allGoodData Then

MsgBox ("Your data sheet has problems")

Application.ScreenUpdating = True

Exit Sub

End If

'clean out prior simulation runs?

If MsgBox("Delete prior simulation runs?", vbYesNo) = vbYes Then

Application.DisplayAlerts = False 'do not prompt before each deletion

For Each writeWS In Worksheets 'cycle through all worksheets in active workbook

'don't delete inputs or test cases!

If (Not writeWS.Name Like "lanchester_inputs") And _

(Not writeWS.Name Like "Test cases") Then

writeWS.Delete

End If

Next

Application.DisplayAlerts = True

ElseIf MsgBox("You have chosen not to delete prior sheets. " & vbNewLine & _

"Any conflicting names will result in program crash.", vbOKCancel) = vbCancel Then

Exit Sub

End If

'get the name of the worksheet to write to and set up headers

baselineName = InputBox("Enter the baseline name for simulation runs")

Worksheets.Add

ActiveSheet.Name = baselineName

For i = 0 To 4 'arrays start at 0 in VBA

Cells(1, i + 1) = outputHeadings(i)

Next i

'Randomize ' - do NOT use the randomize function in a loop, if you use it at all. Most times it is unnecessary

'start time stepping

'even though the high level requirements are for > 0 troop levels, the tolerance

'stopping condition is better in case the battle becomes essentially stalled out

'The "stalling" may or may not be a tied condition. Numerical code should never

'use equality tests for floating point ("decimal" or "real") variables because that

'can lead algorithms to fail or not converge (run infinitely).

'Since Euler's method can overshoot 0, an additional stopping condition is needed

'otherwise, the armies will start growing.

While (priorX > 0 And priorY > 0) And Not stalled

iteration = iteration + 1

'use random number for alpha and beta modified by fatigue

alpha = Exp(-fatigueX * t) * ((alphaMax - alphaMin) * Rnd + alphaMin)

beta = Exp(-fatigueY * t) * ((betaMax - betaMin) * Rnd + betaMin)

currX = priorX - beta * priorY * deltaT 'compute troop levels after shots fired

currY = priorY - alpha * priorX * deltaT

stalled = (Abs(currX - priorX) < TOL And Abs(currY - priorY) < TOL) 'are both armies still declining?

'update for next step

priorX = currX 'swapping so that prior value becomes new value

priorY = currY

'supress last printout if negative troop value(s)

If priorX > 0 And priorY > 0 Then

'data starts on the second row (iteration =2)

Cells(iteration + 1, 1) = t

Cells(iteration + 1, 2) = priorX

Cells(iteration + 1, 3) = priorY

Cells(iteration + 1, 4) = alpha

Cells(iteration + 1, 5) = beta

End If

t = t + deltaT 'keep track of how much time has passed

Wend

'Note that plotting is not required

Application.ScreenUpdating = True

End Sub 'Baseline code