Solver (SOLVER.XLAM) VBAProject (AM FYP.xlsm) -Microsoft Excel Objects Sheet1 (Applicant Inform Sheet2 (Sensitivity Testin Sheet3 (Sheet1) Sheet4 (CF illustrative tal Sheets (Model Points) Sheet6 (SML Illustrative 1 Sheet7 (EXIT PLAN) Sheet8 (DSR Testing SMI Microsoft Visual Basic for Applications - AM FYP.xlsm [design] - [Sheet5 (Code)] Eile Edit View Insert Format Debug Run Tools Add-Ins Window Help Project - VBAProject atpvbaen.xls (ATPVBAEN.XLA 52 deleteModelPointButton Private Sub deleteModel PointButton_Click() Dim tblExists As Boolean Dim ws As Worksheet Dim tableName As String Dim dataSource As String dataSource = "modelPoint" Set ws = ThisWorkbook.Sheets("Model Points") tableName = "Model Point Table" tblExists = Sheet1.TableExists (ws, tableName) If tblExists Then Ln 1, Col 1 Click Project - VBAProject Microsoft Visual Basic for Applications - AM FYP.xlsm [design] - [Sheet5 (Code)] Eile Edit View Insert Format Debug Run Iools Add-Ins Window Help XD BA atpvbaen.xls (ATPVBAEN.XLA Solver (SOLVER.XLAM) E-VBAProject (AM FYP.xlsm) generateModelPointButton Ln 39, Col 21 Set generatedData = generateData() Copy the generated data to the applicant Data dictionary Dim key As Variant For Each key In generatedData.Keys applicant Data (key) = generated Data (key) Microsoft Excel Objects Sheet1 (Applicant Inform Next key Sheet2 (Sensitivity Testin Sheet3 (Sheet1) Sheet4 (CF illustrative tal Sheet5 (Model Points) Sheet6 (SML Illustrative 1 Insert random data Sheetl.insertTable applicantData, dataSource, i Next i End Sub Sheet7 (EXIT PLAN) Sheets (DSR Testing SMI Sheet9 (DSR testing Prod Sheet1.DeleteTableContents ws, tableName ThisWorkbook Else -Forms Sheet9 (DSR testing Prod ThisWorkbook Forms Sheet1.RecreateTable ws, tableName, dataSource Modules Modules End If VBAProject (Book1) VBAProject (Book1) End Sub -Microsoft Excel Objects Sheet1 (Sheet1) Sheet2 (Sheet2) Sheet3 (Fluctuating Int Ri ThisWorkbook VBAProject (FUNCRES.XLAM) ' generate 100 model points Private Sub generateModelPointButton_Click() Dim dataSource As String Dim applicant Data As Object Dim generatedData As Object Microsoft Excel Objects Sheet1 (Sheet1) Sheet2 (Sheet2) Sheet3 (Fluctuating Int Ri ThisWorkbook. Private Function generateData() As Object Dim result As Object Set result = CreateObject("Scripting. Dictionary") 'Generate 7 digit applicant number Dim ws As Worksheet Dim tbl As ListObject Dim lastRow As Long +VBAProject (TAC0522-Projec VBAProject (FUNCRES.XLAM) VBAProject (TAC0522-Projec Dim i As Integer Dim duplicate As Boolean Dim randomApplicantNumber As Long, Set ws = ThisWorkbook.Sheets("Model Points") Set tbl = ws. ListObjects ("Model Point Table") If tbl.ListRows.count > 0 Then Else lastRow = tbl. ListColumns (2). DataBodyRange.Rows.count If the table is empty, set lastRow to 0 'Define data source dataSource = "modelPoint" 'Check data existed Sheet1.CheckAndRecreateTable dataSource 'delete model point before insert new model point deleteModelPointButton_Click Set applicantData = CreateObject("Scripting. Dictionary") For i = 1 To 100 'Get data from generateData function Set generatedData = generateData() Copy the generated data to the applicant Data dictionary Dim key As Variant lastRow 0 End If Seed the random number generator with current system time Randomize Timer Generate a random number between 0 and 9999999 randomApplicantNumber = Int((9999999 + 1) Rnd). Do Loop until a unique applicant number is generated Generate a random number between 0 and 9999999 randomApplicant Number = Int((9999999 + 1) * Rnd) Microsoft Visual Basic for Applications - AM FYP.xlsm [design] - [Sheet5 (Code)] Eile Edit View Insert Format Debug Run Iools Add-Ins Window Help 4 Ln 59, Col 24 to Click Project - VBAProject 2 X (General) Do atpvbaen.xls (ATPVBAEN.XLA Solver (SOLVER.XLAM) E-VBAProject (AM FYP.xlsm) Microsoft Excel Objects Sheet1 (Applicant Inform Sheet2 (Sensitivity Testin Sheet3 (Sheet1) Sheet4 (CF illustrative tal Sheet5 (Model Points) Sheet6 (SML Illustrative 1 Sheet7 (EXIT PLAN) Sheet8 (DSR Testing SMI Sheet9 (DSR testing Prod ThisWorkbook Forms +Modules VBAProject (Book1) 'Generate a random number between 0 and 9999999 randomApplicant Number = Int((9999999 + 1) * Rnd) Microsoft Visual Basic for Applications - AM FYP.xlsm [design] - [Sheet5 (Code)] Eile Edit View Insert Format Debug Run Iools Add-Ins Window Help BA generateData Project VBAProject 5 (General) Ln 150, Col 44 Check if the generated number already exists in the table duplicate = False For i = 1 To lastRow If tbl. DataBodyRange.Cells(i, 1).value = Format(randomApplicant Number, "0000000") Then duplicate True Exit For End If Next i +atpvbaen.xls (ATPVBAEN.XLA Solver (SOLVER.XLAM) VBAProject (AM FYP.xlsm) Microsoft Excel Objects Sheet1 (Applicant Inform Sheet2 (Sensitivity Testin Sheet3 (Sheet1) Sheet4 (CF illustrative tal Sheet5 (Model Points) Sheet6 (SML Illustrative 1 Do Sheet7 (EXIT PLAN) Sheets (DSR Testing SMI Sheet9 (DSR testing Prod ThisWorkbook Microsoft Excel Objects Sheet1 (Sheet1) Sheet2 (Sheet2) Sheet3 (Fluctuating Int Ri ThisWorkbook VBAProject (FUNCRES.XLAM) VBAProject (TAC0522-Projec Loop While duplicate Assign the generated applicant number to the result dictionary result ("ApplicantNumber") = Format (randomApplicant Number, "0000000") Dim minAge As Integer Dim maxAge As Integer 'Define range of ages minAge 18 maxAge 60 'Assign the generated applicant number to the result dictionary result ("ApplicantNumber") = Format (randomApplicant Number, "0000000") 'Generate age from 18 to 60 result ("Age") = Int((maxAge minAge + 1) Rnd + minAge) 'Generate smoker (True or False) result ("Smoker") = IIf (Rnd