使用 VBA 将 Excel 数据写入字典

Question

例如，我在 excel 表“MySheet”中有一些数据，如下所示:


萼片
花瓣


5
11

4
12

3
13


在调用名为 的 VBA 函数后，我需要将这些数据转换为字典。 dict= ex_dict () 我可以在其中访问每个键，例如:

dict=ex_dict(A1:B4)
dict= {"sepal": [5,4,3], "petal": [11,12,13]}

或者 dict ('sepal')= [5,4,3]最初，我以为我找到了解决方案。但后来我发现给定的解决方案输出为字符串而不是字典对象

Answer 1

列到字符串

Function ex_dict(ByVal rg As Range) As String
    
    Dim rCount As Long: rCount = rg.Rows.Count
    If rCount < 2 Then Exit Function
    
    ex_dict = "{"
    
    Dim crg As Range
    Dim r As Long
    
    For Each crg In rg.Columns
        ex_dict = ex_dict & """" & crg.Cells(1).Value & """: ["
        For r = 2 To rCount
            ex_dict = ex_dict & crg.Cells(r).Value & ","
        Next r
        ex_dict = Left(ex_dict, Len(ex_dict) - 1) & "], "
    Next crg
    
    ex_dict = Left(ex_dict, Len(ex_dict) - 2) & "}"

End Function