时间戳

首页 > 解决方案 > 除非我使用修剪，否则 VBA 字典添加无法读取 Excel 工作表标题

excel - 除非我使用修剪，否则 VBA 字典添加无法读取 Excel 工作表标题

问题描述

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() 给出预期的结果。请解释原因！

标签： excelvbadictionarytrim

解决方案

来自字典文档：

https://docs.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...养成始终使用而不是依赖默认属性的习惯的另一个原因。

推荐阅读