VBA字典获配置

Function qh_get_settings(qh_select_value0, Optional qh_sheet_name0 = "Settings", _
                                           Optional qh_range0 = "C3", _
                                           Optional qh_select_cloumn0 = 3, _
                                           Optional qh_key_cloumn0 = 4, _
                                           Optional qh_value_cloumn0 = 5, _
                                           Optional qh_star_cloumn0 = 4) As Dictionary
                                           
'                                  作者：阙辉
'                                  日期：20220404

Dim qh_settings_dic
Dim qh_sheet_name, qh_range01, qh_select_value As String
Dim qh_select_cloumn, qh_key_cloumn, qh_value_cloumn, _
    qh_star_cloumn, qh_last_row, qh_i As Long

qh_sheet_name = qh_sheet_name0               '配置表sheet名
qh_range = qh_range0                         '最后一行参考range
qh_select_value = qh_select_value0           '需要的配置的功能名称
qh_select_cloumn = qh_select_cloumn0         '功能名称字段列
qh_key_cloumn = qh_key_cloumn0               '配置字段列 key
qh_value_cloumn = qh_value_cloumn0           '配置值字段列 key
qh_star_cloumn = qh_star_cloumn0             '起始行

Set qh_settings_dic = CreateObject("Scripting.Dictionary")


With Sheets(qh_sheet_name)
    qh_last_row = .Range(qh_range).End(xlDown).Row    '获取最后一行
    For qh_i = qh_star_cloumn To qh_last_row
        qh_select_value01 = .Cells(qh_i, qh_select_cloumn)
        qh_key = .Cells(qh_i, qh_key_cloumn)
        qh_value = .Cells(qh_i, qh_value_cloumn)
        If qh_select_value01 = qh_select_value Then
            qh_settings_dic(qh_key) = qh_value
        End If
    Next
End With
Set qh_get_settings = qh_settings_dic
End Function

Sub test_dic()
qh_select_value = "扫描表格"
Set aa = qh_get_settings(qh_select_value)
MsgBox (aa("买单_柜号所在Range"))
End Sub

 

 勾要打上  否则函数不能传参；