「代码」客户销售额查询分析：ComboBox下拉列表，数组字典综合应用

Option ExplicitPublic dic As ObjectPublic dicYear As Object
Sub getDic()    Dim i As Long, j As Long    Dim key1, key2, key3    Dim ws As Worksheet    Dim arr(), customer, currYear As String, currMonth As Integer    Dim sales As Double    Set dic = CreateObject("Scripting.Dictionary")    Set dicYear = CreateObject("Scripting.Dictionary")        For Each ws In ThisWorkbook.Sheets        With ws            If InStr(.Range("A1"), "年销售数据") > 0 Then                arr = .Range("A1").CurrentRegion                For i = 3 To UBound(arr)                    customer = arr(i, 1)                    currYear = Year(arr(i, 2)) & "年"                    currMonth = Month(arr(i, 2))                    sales = arr(i, 8)                    dicYear(currYear) = ""                    If Not dic.exists(customer) Then                        Set dic(customer) = CreateObject("Scripting.Dictionary")                    End If                    If Not dic(customer).exists(currYear) Then                        Set dic(customer)(currYear) = CreateObject("Scripting.Dictionary")                        For j = 1 To 12                            dic(customer)(currYear)(j) = 0                        Next                    End If                    dic(customer)(currYear)(currMonth) = dic(customer)(currYear)(currMonth) + sales                Next            End If        End With    Next    End Sub

Sub checkDic()    If dic Is Nothing Or dicYear Is Nothing Then        Call getDic    ElseIf dic.Count = 0 Or dicYear.Count = 0 Then        Call getDic    End IfEnd Sub

Function monthText(currMonth As Integer, Optional mType As Integer = 0)    If mType = 0 Then        Select Case currMonth        Case Is = 1            monthText = "一月"        Case Is = 2            monthText = "二月"        Case Is = 3            monthText = "三月"        Case Is = 4            monthText = "四月"        Case Is = 5            monthText = "五月"        Case Is = 6            monthText = "六月"        Case Is = 7            monthText = "七月"        Case Is = 8            monthText = "八月"        Case Is = 9            monthText = "九月"        Case Is = 10            monthText = "十月"        Case Is = 11            monthText = "十一月"        Case Is = 12            monthText = "十二月"        Case Else            monthText = "无效月份"        End Select    ElseIf mType = 1 Then        Select Case currMonth        Case Is = 1            monthText = "一月份"        Case Is = 2            monthText = "二月份"        Case Is = 3            monthText = "三月份"        Case Is = 4            monthText = "四月份"        Case Is = 5            monthText = "五月份"        Case Is = 6            monthText = "六月份"        Case Is = 7            monthText = "七月份"        Case Is = 8            monthText = "八月份"        Case Is = 9            monthText = "九月份"        Case Is = 10            monthText = "十月份"        Case Is = 11            monthText = "十一月份"        Case Is = 12            monthText = "十二月份"        Case Else            monthText = "无效月份"        End Select                    ElseIf mType = 2 Then       monthText = currMonth & "月份"    End IfEnd Function

Sub SetDataValidation(arr(), rng As Range)    '//设置单元格数据验证    Dim i As Integer, t As Integer    Dim listStr As String    Dim rngValue As String    rngValue = rng.Value    For i = LBound(arr) To UBound(arr)        listStr = listStr & arr(i) & ","        If rngValue = CStr(arr(i)) Then            t = 1        End If    Next    If t = 0 Then rng = ""    '//去掉结尾的 ","    listStr = Left(listStr, Len(listStr) - 1)        With rng.Validation        '//删除已有的数据验证                .Delete        '//添加数据验证，源为listStr        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _            Operator:=xlBetween, Formula1:=listStr        .IgnoreBlank = True        .InCellDropdown = True        .ShowInput = True        .ShowError = False  '//不显示出错警告，改为TRUE则显示，同时不能输入不合验证的字符    End WithEnd Sub