Excel VBA 数组 - 写入事务数据

Question

我正在使用一个Excel模型，用于说明养老金的生命周期。这种特殊模型可能需要 100k 笔交易，其中交易被定义为余额的变动，与银行账户交易非常相似。

我能够使用字典脚本对象存储余额，并且效果很好。但是，我想以最有效的方式存储单个事务，并在计算完成后将它们写入工作表。

为此，我再次使用了一个字典(我认为如果需要在更高版本中引用可能会很方便(与类模块项，但是一旦在"for each"循环中完成，将所有信息写入工作表是非常慢的。

使用带保留的数组会更好还是有更好的选择？可以定义一个大的说法，100,000 * x，如果我达到边界，则增加大小。

我以前使用过字符串字典键(由许多静态信息组成(与单个单元移动字典项，但这又有点慢。任何建议将不胜感激。

谢谢

Public Sub pb_sbSaveToUnitTransactionDict( _
lgCaseId As Long, _
lgEventId As Long, _
strTransactionType As String, _
lgInvCovSeqNo As Long, _
lgInvCovSegNo As Long, _
lgBasketId As Long, _
lgTransactionMonth As Long, _
dblPremIndexRate As Double, _
strUnitFundType As String, _
dblFundGrowthRate As Double, _
strUnitFundCode As String, _
dblUnitFundPrice As Double, _
dblUnitMovement As Double, _
dblUnitMovNomValue As Double, _
strTransactionBasisCode As String, _
dblTransactionAmount As Double, _
dblTransactionPercentage As Double, _
dblTotalNomSplit As Double, _
dblKeyNomSplit As Double)
'The purpose of this function is to save a unit transaction record to the unit
'transaction dictionary.
'Key: TRANSACTION ID
'Item: Class Module containing the following information
'CASE_ID
'EVENT_ID
'TRANSACTION_ID
'TRANSACTION_TYPE
'INV_COV_SEQ_NO
'INV_COV_SEG_NO
'BASKET_ID
'TRANSACTION_MONTH
'PREM_INDEX_RATE
'UNIT_FUND_TYPE
'GROWTH_RATE
'UNIT_FUND_PRICE
'UNIT_MOVEMENT
'UNIT_MOV_NOM_VALUE
'TRANS_NOM_VALUE
'TOT_NOM_SPLIT
'KEY_NOM_SPLIT
'Version Control
'Version        Description                         Author      Date
'0.14           Creation of Policy Anniversary and  A.King      23/08/2018
'               Guarantee Maturity Events
Dim vntTransactionId As Variant
Dim lgEndRow As Long
Dim strSheetName As String
'Set up the Dictionary Item as a Class Module
Set clsMdlUnitTransactionItem = New clsUnitTransaction
'Get the end Row in the in the Unit Transaction Table
strSheetName = "Unit Transaction"
lgNextRow = Worksheets(strSheetName).Range("A8").End(xlDown).row
'Get the next transaction ID
vntTransactionId = pb_objUnitTransaction.Count
vntTransactionId = vntTransactionId + 1 + lgNextRow - gblConst_OuputStartRow
'Populate the Unit Transaction Dictionary Class Module Item
clsMdlUnitTransactionItem.lgCaseId = lgCaseId
clsMdlUnitTransactionItem.lgEventId = lgEventId
clsMdlUnitTransactionItem.lgTransactionId = vntTransactionId
clsMdlUnitTransactionItem.strTransactionType = strTransactionType
clsMdlUnitTransactionItem.lgInvCovSeqNo = lgInvCovSeqNo
clsMdlUnitTransactionItem.lgInvCovSegNo = lgInvCovSegNo
clsMdlUnitTransactionItem.lgBasketId = lgBasketId
clsMdlUnitTransactionItem.lgTransactionMonth = lgTransactionMonth
clsMdlUnitTransactionItem.dblPremIndexRate = dblPremIndexRate
clsMdlUnitTransactionItem.strUnitFundType = strUnitFundType
clsMdlUnitTransactionItem.dblFundGrowthRate = dblFundGrowthRate
clsMdlUnitTransactionItem.strUnitFundCode = strUnitFundCode
clsMdlUnitTransactionItem.dblUnitFundPrice = dblUnitFundPrice
clsMdlUnitTransactionItem.dblUnitMovement = dblUnitMovement
clsMdlUnitTransactionItem.dblUnitMovNomValue = dblUnitMovNomValue
clsMdlUnitTransactionItem.dblTotalNomSplit = dblTotalNomSplit
clsMdlUnitTransactionItem.dblKeyNomSplit = dblKeyNomSplit
clsMdlUnitTransactionItem.strTransactionBasisCode = strTransactionBasisCode
clsMdlUnitTransactionItem.dblTransactionAmount = dblTransactionAmount
clsMdlUnitTransactionItem.dblTransactionPercentage = dblTransactionPercentage
'Add to the Unit Transaction Dictionary
pb_objUnitTransaction.Add _
key:=vntTransactionId, _
item:=clsMdlUnitTransactionItem
End Sub

Public Sub pb_sbUnloadUnitTransactionDict()
'The purpose of this sub routine is to unload all unit transactions from the
'unit transaction dictionary that are no longer required to be stored in temporary
'memmory
'This routine will be called in the processing of a Policy Anniversary
'and Guarantee Maturity Investment Events
Dim lgPolicyYear
Dim dtmPolicyYearStartDate As Date
Dim dtmPOlicyYearEndDate As Date
Dim vntTransactionId As Variant
Dim strSheetName As String
Dim lgNextRow As Long
'Get the next row on the Unit Transaction Table
strSheetName = "Unit Transaction"
lgNextRow = Worksheets(strSheetName).Range("A8").End(xlDown).row
'Loop through each of the unit transactions during this policy year and
'populate the unit transaction table
For Each vntTransactionId In pb_objUnitTransaction
ThisWorkbook.Sheets(strSheetName).Cells(lgNextRow + vntTransactionId, 1).Value = pb_objUnitTransaction(vntTransactionId).lgCaseId
ThisWorkbook.Sheets(strSheetName).Cells(lgNextRow + vntTransactionId, 2).Value = pb_objUnitTransaction(vntTransactionId).lgEventId
ThisWorkbook.Sheets(strSheetName).Cells(lgNextRow + vntTransactionId, 3).Value = pb_objUnitTransaction(vntTransactionId).lgTransactionId
ThisWorkbook.Sheets(strSheetName).Cells(lgNextRow + vntTransactionId, 4).Value = pb_objUnitTransaction(vntTransactionId).strTransactionType
ThisWorkbook.Sheets(strSheetName).Cells(lgNextRow + vntTransactionId, 5).Value = pb_objUnitTransaction(vntTransactionId).lgInvCovSeqNo
ThisWorkbook.Sheets(strSheetName).Cells(lgNextRow + vntTransactionId, 6).Value = pb_objUnitTransaction(vntTransactionId).lgInvCovSegNo
ThisWorkbook.Sheets(strSheetName).Cells(lgNextRow + vntTransactionId, 7).Value = pb_objUnitTransaction(vntTransactionId).lgBasketId
ThisWorkbook.Sheets(strSheetName).Cells(lgNextRow + vntTransactionId, 8).Value = pb_objUnitTransaction(vntTransactionId).lgTransactionMonth
ThisWorkbook.Sheets(strSheetName).Cells(lgNextRow + vntTransactionId, 9).Value = pb_objUnitTransaction(vntTransactionId).dblPremIndexRate
ThisWorkbook.Sheets(strSheetName).Cells(lgNextRow + vntTransactionId, 10).Value = pb_objUnitTransaction(vntTransactionId).strUnitFundType
ThisWorkbook.Sheets(strSheetName).Cells(lgNextRow + vntTransactionId, 11).Value = pb_objUnitTransaction(vntTransactionId).dblFundGrowthRate
ThisWorkbook.Sheets(strSheetName).Cells(lgNextRow + vntTransactionId, 12).Value = pb_objUnitTransaction(vntTransactionId).strUnitFundCode
ThisWorkbook.Sheets(strSheetName).Cells(lgNextRow + vntTransactionId, 13).Value = pb_objUnitTransaction(vntTransactionId).dblUnitFundPrice
ThisWorkbook.Sheets(strSheetName).Cells(lgNextRow + vntTransactionId, 14).Value = pb_objUnitTransaction(vntTransactionId).dblUnitMovement
ThisWorkbook.Sheets(strSheetName).Cells(lgNextRow + vntTransactionId, 15).Value = pb_objUnitTransaction(vntTransactionId).dblUnitMovNomValue
ThisWorkbook.Sheets(strSheetName).Cells(lgNextRow + vntTransactionId, 16).Value = pb_objUnitTransaction(vntTransactionId).strTransactionBasisCode
ThisWorkbook.Sheets(strSheetName).Cells(lgNextRow + vntTransactionId, 17).Value = pb_objUnitTransaction(vntTransactionId).dblTransactionAmount
ThisWorkbook.Sheets(strSheetName).Cells(lgNextRow + vntTransactionId, 18).Value = pb_objUnitTransaction(vntTransactionId).dblTransactionPercentage
ThisWorkbook.Sheets(strSheetName).Cells(lgNextRow + vntTransactionId, 19).Value = pb_objUnitTransaction(vntTransactionId).dblTotalNomSplit
ThisWorkbook.Sheets(strSheetName).Cells(lgNextRow + vntTransactionId, 20).Value = pb_objUnitTransaction(vntTransactionId).dblKeyNomSplit

Next vntTransactionId

'Remove all records
pb_objUnitTransaction.RemoveAll
End Sub

Answer 1

我选择了选项 1，它节省了大量时间。

感谢您的帮助@chrisNeilsen

Public pb_aryUnitTransaction(100000, 20) As Variant

Public Sub pb_sbUnloadUnitTransactionDict()
'The purpose of this sub routine is to unload all unit transactions from the
'unit transaction dictionary that are no longer required to be stored in temporary
'memmory
'This routine will be called in the processing of a Policy Anniversary
'and Guarantee Maturity Investment Events
Dim lgPolicyYear
Dim dtmPolicyYearStartDate As Date
Dim dtmPOlicyYearEndDate As Date
Dim vntTransactionId As Variant
Dim strSheetName As String
Dim lgNextRow As Long
Dim lgTransactionId As Long
'Get the next row on the Unit Transaction Table
strSheetName = "Unit Transaction"
'lgNextRow = Worksheets(strSheetName).Range("A8").End(xlDown).row

'Loop through each of the unit transactions during this policy year and
'save to unit transaction table
For Each vntTransactionId In pb_objUnitTransaction
lgTransactionId = vntTransactionId
pb_aryUnitTransaction(lgTransactionId - gblConst_OutputStartRow - 1, 0) = pb_objUnitTransaction(vntTransactionId).lgCaseId
pb_aryUnitTransaction(lgTransactionId - gblConst_OutputStartRow - 1, 1) = pb_objUnitTransaction(vntTransactionId).lgEventId
pb_aryUnitTransaction(lgTransactionId - gblConst_OutputStartRow - 1, 2) = pb_objUnitTransaction(vntTransactionId).lgTransactionId
pb_aryUnitTransaction(lgTransactionId - gblConst_OutputStartRow - 1, 3) = pb_objUnitTransaction(vntTransactionId).strTransactionType
pb_aryUnitTransaction(lgTransactionId - gblConst_OutputStartRow - 1, 4) = pb_objUnitTransaction(vntTransactionId).lgInvCovSeqNo
pb_aryUnitTransaction(lgTransactionId - gblConst_OutputStartRow - 1, 5) = pb_objUnitTransaction(vntTransactionId).lgInvCovSegNo
pb_aryUnitTransaction(lgTransactionId - gblConst_OutputStartRow - 1, 6) = pb_objUnitTransaction(vntTransactionId).lgBasketId
pb_aryUnitTransaction(lgTransactionId - gblConst_OutputStartRow - 1, 7) = pb_objUnitTransaction(vntTransactionId).lgTransactionMonth
pb_aryUnitTransaction(lgTransactionId - gblConst_OutputStartRow - 1, 8) = pb_objUnitTransaction(vntTransactionId).dblPremIndexRate
pb_aryUnitTransaction(lgTransactionId - gblConst_OutputStartRow - 1, 9) = pb_objUnitTransaction(vntTransactionId).strUnitFundType
pb_aryUnitTransaction(lgTransactionId - gblConst_OutputStartRow - 1, 10) = pb_objUnitTransaction(vntTransactionId).dblFundGrowthRate
pb_aryUnitTransaction(lgTransactionId - gblConst_OutputStartRow - 1, 11) = pb_objUnitTransaction(vntTransactionId).strUnitFundCode
pb_aryUnitTransaction(lgTransactionId - gblConst_OutputStartRow - 1, 12) = pb_objUnitTransaction(vntTransactionId).dblUnitFundPrice
pb_aryUnitTransaction(lgTransactionId - gblConst_OutputStartRow - 1, 13) = pb_objUnitTransaction(vntTransactionId).dblUnitMovement
pb_aryUnitTransaction(lgTransactionId - gblConst_OutputStartRow - 1, 14) = pb_objUnitTransaction(vntTransactionId).dblUnitMovNomValue
pb_aryUnitTransaction(lgTransactionId - gblConst_OutputStartRow - 1, 15) = pb_objUnitTransaction(vntTransactionId).strTransactionBasisCode
pb_aryUnitTransaction(lgTransactionId - gblConst_OutputStartRow - 1, 16) = pb_objUnitTransaction(vntTransactionId).dblTransactionAmount
pb_aryUnitTransaction(lgTransactionId - gblConst_OutputStartRow - 1, 17) = pb_objUnitTransaction(vntTransactionId).dblTransactionPercentage
pb_aryUnitTransaction(lgTransactionId - gblConst_OutputStartRow - 1, 18) = pb_objUnitTransaction(vntTransactionId).dblTotalNomSplit
pb_aryUnitTransaction(lgTransactionId - gblConst_OutputStartRow - 1, 19) = pb_objUnitTransaction(vntTransactionId).dblKeyNomSplit

Next vntTransactionId
'Copy the array to the Unit Transaction Spread Sheet
lgNextRow = Worksheets(strSheetName).Range("A8").End(xlDown).row
'Paste to worksheet
Worksheets(strSheetName).Activate
Worksheets(strSheetName).Range(Cells(lgNextRow + 1, 1), Cells(UBound(pb_aryUnitTransaction), 20)) = pb_aryUnitTransaction
'Remove all records
Erase pb_aryUnitTransaction
pb_objUnitTransaction.RemoveAll
End Sub