我正在使用一個函式來獲取開始日期和結束日期串列中兩個日期之間的所有日期：我希望將每個日期從開始日期到結束日期及其唯一 ID 存盤在一個陣列中。資料是第 1 列 ID，第 2 列開始日期，第 3 列結束日期。該陣列將是一個 ID 串列，其中包含從開始日期到結束日期的所有相關日期。以下是我必須獲取所有日期的代碼：
Sub Test_Dates()
'
Dim TESTWB As Workbook
Dim TESTWS As Worksheet
Set TESTWB = ThisWorkbook
Set TESTWS = TESTWB.Worksheets("TEST")
For i = 2 To TESTWS.Cells(1, 1).End(xlDown).Row
DatesTest = getDates(TESTWS.Cells(i, 2), TESTWS.Cells(i, 3))
Next i
End Sub
Function getDates(ByVal StartDate As Date, ByVal EndDate As Date) As Variant
Dim varDates() As Date
Dim lngDateCounter As Long
ReDim varDates(0 To CLng(EndDate) - CLng(StartDate))
For lngDateCounter = LBound(varDates) To UBound(varDates)
varDates(lngDateCounter) = CDate(StartDate)
StartDate = CDate(CDbl(StartDate) 1)
Next lngDateCounter
getDates = varDates
ClearMemory:
If IsArray(varDates) Then Erase varDates
lngDateCounter = Empty
uj5u.com熱心網友回復：
只需創建一個具有行大小的陣列，ReDim DatesTest(1 To LastRow - FirstRow 1)然后用您的結果填充它getDates。
Dim TESTWB As Workbook
Dim TESTWS As Worksheet
Set TESTWB = ThisWorkbook
Set TESTWS = TESTWB.Worksheets("TEST")
Const FirstRow As Long = 2
Dim LastRow As Long
LastRow = TESTWS.Cells(1, 1).End(xlDown).Row
Dim DatesTest() As Variant
ReDim DatesTest(1 To LastRow - FirstRow 1)
Dim i As Long
For i = FirstRow To LastRow
DatesTest(i - FirstRow 1) = getDates(TESTWS.Cells(i, 2), TESTWS.Cells(i, 3))
Next i
然后，您可以訪問第一個結果，getDates其中DatesTest(1)應該DatesTest(1)(1)為您提供第一組的第一個日期。
如果要使用ID(i,1)as 鍵，則需要使用 aCollection而不是陣列。
Dim TESTWB As Workbook
Dim TESTWS As Worksheet
Set TESTWB = ThisWorkbook
Set TESTWS = TESTWB.Worksheets("TEST")
Const FirstRow As Long = 2
Dim LastRow As Long
LastRow = TESTWS.Cells(1, 1).End(xlDown).Row
DatesTest As New Collection
Dim i As Long
For i = FirstRow To LastRow
DatesTest.Add getDates(TESTWS.Cells(i, 2), TESTWS.Cells(i, 3)), TESTWS.Cells(i, 1)
Next i
然后，您可以使用DatesTest(TESTWS.Cells(2, 1))獲取第一組日期，并DatesTest(TESTWS.Cells(2, 1))(1)為您提供該組的第一個日期。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/437771.html
上一篇：異步任務阻塞UI