VBA:当我使用变量作为条件时，为什么endif显示0作为结果

Question

我想添加一个变量作为条件，但当我这样做时，excel会显示Range("Dodawanie")=0。如果我用数字代替变量，一切都很好。

Option Explicit
Sub summer()
Dim dod As Integer
dod = [losowania].Value
Dim suma As Double

suma = WorksheetFunction.Sum(Range(Cells(2, 4), Cells(dod + 1, 4)))
Dim srednia As Double
srednia = suma / dod

Range("Dodawanie") = WorksheetFunction.SumIf(Range(Cells(2, 4), Cells(dod + 1, 4)), ">srednia")
End Sub

Answer 1

SumIf标准问题

• 由于srednia是一个变量，您不能在字符串中使用它，但您可以这样做：

  ">" & srednia
  

• 为了使其可读性更强，您可以实现一个范围对象(rng(。

代码

Option Explicit
Sub summer()
Dim dod As Long
dod = [losowania].Value

Dim rng As Range
Set rng = Range(Cells(2, 4), Cells(dod + 1, 4))

Dim suma As Double
suma = WorksheetFunction.Sum(rng)

Dim srednia As Double
srednia = suma / dod
Range("Dodawanie") = WorksheetFunction.SumIf(rng, ">" & srednia)
End Sub

为了避免一些意外行为，您可以改进：

Sub summerIf()
If IsNumeric([losowania].Value) Then ' Fails if 'losowania' contains string.
Dim dod As Long
dod = CLng([losowania].Value)
If dod >= 1 Then ' Prevent division by 0 ('srednia').
Dim rng As Range
Set rng = Range(Cells(2, 4), Cells(dod + 1, 4))
Dim suma As Double
On Error Resume Next ' 'Sum' fails if 'rng' contains error value.
suma = WorksheetFunction.Sum(rng)
If Err = False Then
On Error GoTo 0
Dim srednia As Double
srednia = suma / dod
Range("Dodawanie") = WorksheetFunction.SumIf(rng, ">" & srednia)
Else
On Error GoTo 0
MsgBox "'rng' contains an error value."
End If
Else
MsgBox "'dod' is less than 1."
End If
Else
MsgBox "'losowania' does not contain a number."
End If
End Sub