excel - 显示最小值

Question

代码说明:使用 For Next 编写 VBA 宏来接受十个数字并显示(通过消息框)总计、平均、最高和最低
问题:只有当 1 出现在选择中时，它才会显示最小值，否则结果为空白。

Sub Codes()

    For counter = 1 To 10
        ActiveCell = InputBox("Please enter 10 numbers", " ")
        vartotal = vartotal + ActiveCell
        varaverage = vartotal / counter
        
        If ActiveCell > varhighest Then varhighest = ActiveCell
        If ActiveCell = 1 Then varlowest = ActiveCell Else If ActiveCell < varlowest Then varlowest = ActiveCell
    
        ActiveCell.Offset(1, 0).Select
    Next counter
        MsgBox "SumTotal = " & vartotal & vbNewLine & "Average = " & varaverage & vbNewLine & "Highest = " & varhighest & vbNewLine & "Lowest = " & varlowest
End Sub

Answer 1

另一种方法是加载一个数组，然后只使用工作表公式:

Sub Codes()
    Dim counter As Long
    Dim activecell(9) As Double
    Dim vartotal As Double
    Dim varhighest As Double
    Dim varlowest As Double
    Dim varaverage As Double

    For counter = 0 To 9
        activecell(counter) = Application.InputBox("Please enter 10 numbers", " ", , , , , , 1)
    Next counter
    vartotal = Application.Sum(activecell)
    varhighest = Application.Max(activecell)
    varlowest = Application.Min(activecell)
    varaverage = Application.Average(activecell)
    MsgBox "SumTotal = " & vartotal & vbNewLine & "Average = " & varaverage & vbNewLine & "Highest = " & varhighest & vbNewLine & "Lowest = " & varlowest
End Sub