我有下面的代碼來創建字典并從 3 列 (i,1) 鍵和日期陣列中分配值getdates()fromstartdate(i,2)和enddate(i,3)。
我的目標是按鍵列印每個日期（一個鍵可以有多個日期），但下面的代碼給了我一個型別不匹配的錯誤。
Sub Test_Dates()
Dim TESTWB As Workbook
Dim TESTWS As Worksheet
Set TESTWB = ThisWorkbook
Set TESTWS = TESTWB.Worksheets("TEST")
Dim Dict As New Scripting.Dictionary
For i = 2 To TESTWS.Cells(1, 1).End(xlDown).Row
Dict.Add TESTWS.Cells(i, 1).Value, getDates(TESTWS.Cells(i, 2), TESTWS.Cells(i, 3))
Next i
For Each Key In Dict.Keys
Dim idate As Variant
For Each idate In Dict.Items
Debug.Print idate 'this line is where the error is
Next idate
Next Key
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
End Function
uj5u.com熱心網友回復：
而不是 2 回圈通過你的字典，你可以只使用一個
Dim i As Long
For i = 0 To Dict.Count - 1
Debug.Print Dict.Keys(i), Join(Dict.Items(i), "|")
Next i
或者，如果您需要將每個日期作為單個專案，則需要 2 個回圈
Dim i As Long
For i = 0 To Dict.Count - 1
Dim DateItem As Variant
For Each DateItem in Dict.Items(i)
Debug.Print Dict.Keys(i), DateItem
Next DateItem
Next i
使用字典的好來源：Excel VBA 字典 - 完整指南
uj5u.com熱心網友回復：
遍歷字典的專案
- 查看這兩個字典資源：
- 字典物件（MS Docs）
- Excel VBA 字典（Excel 宏掌握）
要遍歷字典的每個專案并連接專案陣列的元素，請使用：
Dim Arr As Variant
For Each Arr In Dict.Items
Debug.Print Join(Arr, ", ")
Next Arr
要遍歷包含從零開始的陣列的字典的每個元素，請使用：
Dim Arr As Variant
Dim i As Long
For Each Arr in Dict.Items
For i = 0 To Ubound(Arr)
Debug.Print Arr(i)
Next i
Next Arr
或者，使用雙For...Each回圈：
Dim Arr As Variant
Dim Item As Long
For Each Arr in Dict.Items
For Each Item in Arr
Debug.Print Item
Next Item
Next Arr
uj5u.com熱心網友回復：
我發現在 idate 上添加另一個回圈可以解決問題：
For Each Key In Dict.Keys
Dim idate As Variant
For Each idate In Dict.Items
Dim bdate As Variant
For Each bdate In idate
Debug.Print bdate
Next bdate
Next idate
Next Key
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/440545.html
上一篇：陣列操作期間下標超出范圍
下一篇：ExcelVBA-組合框