VBA字典添加在阅读Excel表格标题失败，除非我使用修剪

Question

Function getHeaderRowDict(sht)
Dim rng As Excel.Range, dict As New Dictionary, i As Long
Set rng = sht.Range("A1").CurrentRegion.Rows(1)
For i = 1 To rng.Columns.Count
dict(Trim(rng.Cells(1, i))) = i
Next
Set getHeaderRowDict = dict
End Function

我使用这个短代码从工作表的currentRange的第一行读取单元格，以便在以后的代码中用作Enum的一种。例如dict("ID")返回以文本"ID"开头的列的列号。如果省略了Trim函数，并且它确实没有业务或需要在那里，则返回Dictionary，所有值为空。保留Trim()会得到预期的结果。请解释一下原因!

Answer 1

From the Dictionary docs:

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dictionary-object

键用于检索单个项，通常为整数也可以是字符串，但可以是数组以外的任何值。

所以你必须小心如何添加你的键。例如，使用带有标题"红色";黄色";蓝色";绿色"的行

Sub Tester()
Dim d, k

'without trim
Set d = getHeaderRowDict(ActiveSheet)
For Each k In d
Debug.Print k, TypeName(k), d(k)
Next k
'Keys and values look OK, but keys are actually Range objects
'    Red           Range          1
'    Yellow        Range          2
'    Blue          Range          3
'    Green         Range          4

Debug.Print "Red = ", d("Red") '...but not really working
'   Red =

'now using Trim()
Set d = getHeaderRowDictV2(ActiveSheet)
For Each k In d
Debug.Print k, TypeName(k), d(k)
Next k
'Now we have String-type keys
'   Red           String         1
'   Yellow        String         2
'   Blue          String         3
'   Green         String         4
Debug.Print "Red = ", d("Red") 'this does work
'  Red =          1
End Sub
Function getHeaderRowDict(sht)
Dim rng As Excel.Range, dict As New Dictionary, i As Long
Set rng = sht.Range("A1").CurrentRegion.Rows(1)
For i = 1 To rng.Columns.Count
dict(rng.Cells(i)) = i
Next
Set getHeaderRowDict = dict
End Function
Function getHeaderRowDictV2(sht)
Dim rng As Excel.Range, dict As New Dictionary, i As Long
Set rng = sht.Range("A1").CurrentRegion.Rows(1)
For i = 1 To rng.Columns.Count
dict(Trim(rng.Cells(i))) = i '<< added trim, but .Value would also work
Next
Set getHeaderRowDictV2 = dict
End Function

…这是养成习惯使用.Value而不是依赖默认属性的另一个原因。