VBA中快速定位存储周各天游戏数变量最高值对应项的实现方法

你已经完成的单变量场次统计逻辑不需要做任何改动，直接在代码预留的最高值定位块补全逻辑即可，以下两种实现都无额外依赖、代码量极低。 #

方案1：零依赖基础实现（最稳定） #

直接补在原来的???位置：

' 建立统计值与日期标签的一一映射
Dim dayCount(0 To 6) As Integer
Dim dayLabel(0 To 6) As String
dayCount(0) = Mon: dayLabel(0) = "Mon"
dayCount(1) = Tue: dayLabel(1) = "Tue"
dayCount(2) = Wed: dayLabel(2) = "Wed"
dayCount(3) = Thu: dayLabel(3) = "Thu"
dayCount(4) = Fri: dayLabel(4) = "Fri"
dayCount(5) = Sat: dayLabel(5) = "Sat"
dayCount(6) = Sun: dayLabel(6) = "Sun"

' 遍历定位最大值的位置
Dim maxPosition As Integer, maxValue As Integer
maxValue = dayCount(0)
maxPosition = 0
For i = 1 To 6
    If dayCount(i) > maxValue Then
        maxValue = dayCount(i)
        maxPosition = i
    End If
Next i

替换原有MsgBox的占位符即可，注意把字符串拼接的+换成VBA更稳定的&，避免类型不匹配报错：

MsgBox "BC played the most number of games on " & dayLabel(maxPosition) & "."

方案2：精简代码实现 #

可以直接借用Excel内置的最大值函数减少手动遍历的代码量：

' 把日期标签和对应场次打包成配对数组
Dim dayMap As Variant
dayMap = Array( _
    Array(Mon, "Mon"), _
    Array(Tue, "Tue"), _
    Array(Wed, "Wed"), _
    Array(Thu, "Thu"), _
    Array(Fri, "Fri"), _
    Array(Sat, "Sat"), _
    Array(Sun, "Sun") _
)

' 直接算出最高场次数值
Dim topCount As Integer
topCount = WorksheetFunction.Max(Mon, Tue, Wed, Thu, Fri, Sat, Sun)

' 匹配对应最高场次的日期标签
Dim resultDay As String
Dim tempItem As Variant
For Each tempItem In dayMap
    If tempItem(0) = topCount Then
        resultDay = tempItem(1)
        Exit For
    End If
Next

MsgBox直接引用resultDay变量即可：

MsgBox "BC played the most number of games on " & resultDay & "."

小提示：如果出现多个日期场次并列最高的情况，上面的逻辑会优先返回数组顺序里排在最前面的日期；如果需要展示所有并列最高的日期，只需要去掉循环里的Exit For，把所有匹配到的日期标签用拼接符连起来即可。

内容的提问来源于stack exchange，提问作者Angel