Option Explicit

' Copyright 2015 Howard J Rudd
'
' Licensed under the Apache License, Version 2.0 (the "License");
' you may not use this file except in compliance with the License.
' You may obtain a copy of the License at
'
'    http://www.apache.org/licenses/LICENSE-2.0
'
' Unless required by applicable law or agreed to in writing, this software
' is distributed on an "AS IS" BASIS WITHOUT WARRANTIES OR CONDITIONS OF
' ANY KIND, either express or implied, not even for MERCHANTABILITY or
' FITNESS FOR A PARTICULAR PURPOSE. See the License for the specific language
' governing permissions and limitations under the License. You are free to use
' this code as you wish within the provisions of the license but it is your
' responsibility to test it and ensure it is fit for the use to which you
' intend to put it.
'
' _____________________________________________________________________________

Sub go()

' TIMER

Dim Start As Double, elatim As Double, elatim1 As Double, elatim2 As Double
Start = Timer

' GENERAL DECLARATIONS

' Counters
Dim i As Long, j As Long, k As Long, l As Long, m As Long, n As Long

' Uniform array
Dim U() As Double

' Temporary storage variables
Dim left As Double, middle As Double, right As Double
Dim tmpName As String, tmpSheet As String, tmpRange As String

' Create collections to hold sets of variables
Dim InputVariables As Collection, OutputVariables As Collection
Set InputVariables = New Collection
Set OutputVariables = New Collection

' Set number of "iterations"
n = 1000
ReDim U(1 To n)

' INPUT VARIABLES, SUBSET 1: Aggregate products

' Create new subset object
Dim AggProds As ClsRandomVariableSubset
Set AggProds = New ClsRandomVariableSubset

' Add subset object to input variables collection
InputVariables.Add AggProds

' Pass parameters to subset object
With AggProds
    .SubsetName = "Sand and aggregate"
    .NumVars = 2
    .NumIters = n
' Redim arrays inside subset object
    .Size
End With

'First variable in subset: price of sand
k = 1
tmpSheet = "Materials and labour"
With ThisWorkbook.Sheets(tmpSheet)
    left = .Range("E3").Value
    middle = .Range("F3").Value
    right = .Range("G3").Value
End With
With AggProds
    .VariableName(k) = "Sand"
    .VariableSheet(k) = tmpSheet
    .VariableRange(k) = "J3"
    U = unifun(n)
    For i = 1 To n
        .OrderedSample(i, k) = TriangularInv(U(i), left, middle, right)
    Next i
End With

'Second variable in subset: price of aggregate
k = 2
tmpSheet = "Materials and labour"
With ThisWorkbook.Sheets(tmpSheet)
    left = .Range("E4").Value
    middle = .Range("F4").Value
    right = .Range("G4").Value
End With
With AggProds
    .VariableName(k) = "Aggregate"
    .VariableSheet(k) = tmpSheet
    .VariableRange(k) = "J4"
    U = unifun(n)
    For i = 1 To n
        .OrderedSample(i, k) = TriangularInv(U(i), left, middle, right)
    Next i
End With

With AggProds
' Pass location of correlation matrix to subset object
    .CorrelationMatrixSheet = "Correlation matrix 1"
    .CorrelationMatrixRange = "A2:B3"
' Create correlated sample array inside subset object
    .GenerateCorrelatedSample
End With

' INPUT VARIABLES, SUBSET 2: High temperature industry products

' Create new subset object
Dim HighTempProds As ClsRandomVariableSubset
Set HighTempProds = New ClsRandomVariableSubset

' Add subset to input variables collection
InputVariables.Add HighTempProds

' Pass parameters to subset object
With HighTempProds
    .SubsetName = "High temperature industry products"
    .NumVars = 5
    .NumIters = n
' Redim arrays inside subset object
    .Size
End With

m = 5  'Start-row of block
tmpSheet = "Materials and labour"
For k = 1 To 5
    With ThisWorkbook.Sheets(tmpSheet)
        tmpName = .Cells(k - 1 + m, 1).Value
        left = .Cells(k - 1 + m, 5).Value
        middle = .Cells(k - 1 + m, 6).Value
        right = .Cells(k - 1 + m, 7).Value
        tmpRange = .Cells(k - 1 + m, 10).Address
    End With
    U = unifun(n)
    With HighTempProds
        .VariableName(k) = tmpName
        .VariableSheet(k) = tmpSheet
        .VariableRange(k) = tmpRange
        For i = 1 To n
            .OrderedSample(i, k) = TriangularInv(U(i), left, middle, right)
        Next i
    End With
Next k

With HighTempProds
' Pass location of correlation matrix to subset object
    .CorrelationMatrixSheet = "Correlation matrix 2"
    .CorrelationMatrixRange = "A2:E6"
' Create correlated sample array inside subset object
    .GenerateCorrelatedSample
End With

' INPUT VARIABLES, SUBSET 3: Timber products

' Create new subset object
Dim TimbProds As ClsRandomVariableSubset
Set TimbProds = New ClsRandomVariableSubset

' Add subset to input variables collection
InputVariables.Add TimbProds

' Pass parameters to subset object
With TimbProds
    .SubsetName = "Timber products"
    .NumVars = 4
    .NumIters = n
' Redim arrays inside subset object
    .Size
End With

m = 11  'Start row of block
tmpSheet = "Materials and labour"
For k = 1 To 4
    With ThisWorkbook.Sheets(tmpSheet)
        tmpName = .Cells(k - 1 + m, 1).Value
        left = .Cells(k - 1 + m, 5).Value
        middle = .Cells(k - 1 + m, 6).Value
        right = .Cells(k - 1 + m, 7).Value
        tmpRange = .Cells(k - 1 + m, 10).Address
    End With
    U = unifun(n)
    With TimbProds
        .VariableName(k) = tmpName
        .VariableSheet(k) = tmpSheet
        .VariableRange(k) = tmpRange
        For i = 1 To n
            .OrderedSample(i, k) = TriangularInv(U(i), left, middle, right)
        Next i
    End With
Next k

With TimbProds
' Pass location of correlation matrix to subset object
    .CorrelationMatrixSheet = "Correlation matrix 3"
    .CorrelationMatrixRange = "A2:D5"
' Create correlated sample array inside subset object
    .GenerateCorrelatedSample
End With

' INPUT VARIABLES, SUBSET 4: Labour hours

' Create new subset object
Dim LabourHours As ClsRandomVariableSubset
Set LabourHours = New ClsRandomVariableSubset

' Add subset to input variables collection
InputVariables.Add LabourHours

' Pass parameters to subset object
With LabourHours
    .SubsetName = "Labour hours"
    .NumVars = 5
    .NumIters = n
' Redim arrays inside subset object
    .Size
End With

m = 19  'Start row of block
tmpSheet = "Materials and labour"
For k = 1 To 5
    With ThisWorkbook.Sheets(tmpSheet)
        tmpName = .Cells(k - 1 + m, 1).Value
        tmpName = tmpName & " hours"
        left = .Cells(k - 1 + m, 3).Value
        right = .Cells(k - 1 + m, 4).Value
        tmpRange = .Cells(k - 1 + m, 9).Address
    End With
    U = unifun(n)
    With LabourHours
        .VariableName(k) = tmpName
        .VariableSheet(k) = tmpSheet
        .VariableRange(k) = tmpRange
        For i = 1 To n
            .OrderedSample(i, k) = UniformInv(U(i), left, right)
        Next i
    End With
Next k

LabourHours.GenerateIndependentSample

' INPUT VARIABLES, SUBSET 5: Labour rates

' Create new subset object
Dim LabourRates As ClsRandomVariableSubset
Set LabourRates = New ClsRandomVariableSubset

' Add subset to input variables collection
InputVariables.Add LabourRates

' Pass parameters to subset object
With LabourRates
    .SubsetName = "Labour rates"
    .NumVars = 5
    .NumIters = n
' Redim arrays inside subset object
    .Size
End With

m = 19  'Start row of block
tmpSheet = "Materials and labour"
For k = 1 To 5
    With ThisWorkbook.Sheets(tmpSheet)
        tmpName = .Cells(k - 1 + m, 1).Value
        left = .Cells(k - 1 + m, 5).Value
        middle = .Cells(k - 1 + m, 6).Value
        right = .Cells(k - 1 + m, 7).Value
        tmpRange = .Cells(k - 1 + m, 10).Address
    End With
    U = unifun(n)
    With LabourRates
        .VariableName(k) = tmpName
        .VariableSheet(k) = tmpSheet
        .VariableRange(k) = tmpRange
        For i = 1 To n
            .OrderedSample(i, k) = TriangularInv(U(i), left, middle, right)
        Next i
    End With
Next k

With LabourRates
' Pass location of correlation matrix to subset object
    .CorrelationMatrixSheet = "Correlation matrix 4"
    .CorrelationMatrixRange = "A2:E6"
' Create correlated sample array inside subset object
    .GenerateCorrelatedSample
End With

' INPUT VARIABLES, SUBSET 6: Miscellaneous products

' Create new subset object
Dim MiscProds As ClsRandomVariableSubset
Set MiscProds = New ClsRandomVariableSubset

' Add subset object to input variables collection
InputVariables.Add MiscProds

' Pass parameters to subset object
With MiscProds
    .SubsetName = "Miscellaneous products"
    .NumVars = 4
    .NumIters = n
' Redim arrays inside subset object
    .Size
End With

'First variable in subset: price of mortar mix
k = 1
tmpSheet = "Materials and labour"
With ThisWorkbook.Sheets(tmpSheet)
    left = .Range("E10").Value
    middle = .Range("F10").Value
    right = .Range("G10").Value
End With
With MiscProds
    .VariableName(k) = "Mortar mix"
    .VariableSheet(k) = tmpSheet
    .VariableRange(k) = "J10"
    U = unifun(n)
    For i = 1 To n
        .OrderedSample(i, k) = TriangularInv(U(i), left, middle, right)
    Next i
End With

'2nd variable in subset: price of aerated breeze blocks
k = 2
tmpSheet = "Materials and labour"
With ThisWorkbook.Sheets(tmpSheet)
    left = .Range("E15").Value
    middle = .Range("F15").Value
    right = .Range("G15").Value
End With
With MiscProds
    .VariableName(k) = "Aerated breeze blocks"
    .VariableSheet(k) = tmpSheet
    .VariableRange(k) = "J15"
    U = unifun(n)
    For i = 1 To n
        .OrderedSample(i, k) = TriangularInv(U(i), left, middle, right)
    Next i
End With

'3rd variable in subset: price of Roofing felt
k = 3
tmpSheet = "Materials and labour"
With ThisWorkbook.Sheets(tmpSheet)
    left = .Range("E16").Value
    middle = .Range("F16").Value
    right = .Range("G16").Value
End With
With MiscProds
    .VariableName(k) = "Roofing felt"
    .VariableSheet(k) = tmpSheet
    .VariableRange(k) = "J16"
    U = unifun(n)
    For i = 1 To n
        .OrderedSample(i, k) = TriangularInv(U(i), left, middle, right)
    Next i
End With

'4th variable in subset: price of nails
k = 4
tmpSheet = "Materials and labour"
With ThisWorkbook.Sheets(tmpSheet)
    left = .Range("E17").Value
    middle = .Range("F17").Value
    right = .Range("G17").Value
End With
With MiscProds
    .VariableName(k) = "nails"
    .VariableSheet(k) = tmpSheet
    .VariableRange(k) = "J17"
    U = unifun(n)
    For i = 1 To n
        .OrderedSample(i, k) = TriangularInv(U(i), left, middle, right)
    Next i
End With

MiscProds.GenerateIndependentSample

' OUTPUT VARIABLES

Dim OutVar As ClsRandomVariableSubset
Set OutVar = New ClsRandomVariableSubset
OutputVariables.Add OutVar

OutVar.NumVars = 1
OutVar.NumIters = n
OutVar.Size

tmpSheet = "Materials and labour"

OutVar.VariableName(1) = "Total cost"
OutVar.VariableSheet(1) = tmpSheet
OutVar.VariableRange(1) = "K25"

elatim1 = Timer - Start

Call RunModel(InputVariables, OutputVariables)
Call Graphs(InputVariables, NumBins:=20, NumPoints:=100, SheetTitle:="Input variables")
Call Graphs(OutputVariables, NumBins:=20, NumPoints:=100, SheetTitle:="Output variables")

elatim2 = Timer - Start - elatim1

elatim = elatim1 + elatim2

MsgBox "Number of iterations = " & n & vbNewLine & _
       "Time to generate input sample = " & elatim1 & " seconds" & vbNewLine & _
       "Time to run spreadsheet model = " & elatim2 & " seconds" & vbNewLine & _
       "Total time = " & elatim & " seconds."

End Sub