vba - "Argument Not Optional"VBA Excel SUB

Question

我正在处理一些 VBA 代码，我得到了一个

Argument not optional

错误。我查看了这个问题的其他一些答案，但他们似乎没有回答我的问题。

代码在 Sub Finding_number () 的第一次迭代中卡住了这让我感到困惑。我没有向该 Sub 传递任何参数或从该 Sub 传递任何参数，那么为什么会出现错误？

我的代码 :

Sub Pedal_Actuations_per_hour()

Dim counter As Integer
Dim average_actuations As Single

counter = 1    
Do While IsEmpty(ActiveCell.Value) = False    
    Finding_number
    average_actuations = (average_actuations + Pedal_actuations()) / counter
    counter = counter + 1    
Loop

Range("J2").Value = average_actuations    

End Sub


Sub Finding_number()

    Dim index As Integer
    index = 1        
    Range("E2").Select

    Do While index = 1
        If ActiveCell.Value = 121 Then
            index = 0
        End If

        Range.Offset (1)
    Loop

End Sub

Function Pedal_actuations() As Integer

Dim time_sum As Single
Dim index As Integer

index = 1
time_sum = 0    
Do While time_sum < 1            
    If IsEmpty(ActiveCell.Value) = 0 Then 
        date_number = Int(ActiveCell(, -2).Value)
        ActiveCell(, 6).Value = ActiveCell(, -2).Value - date_number
        ActiveCell(, 7).Value = Abs(ActiveCell(, 6).Value - ActiveCell(2, 6)) *24 
    Else
        index = 0
    End If

    Pedal_actuations = Pedal_actuations + 1
    time_sum = time_sum + ActiveCell(, 7).Value                
Loop

End Function

Answer 1

最好避免使用 Select , ActiveCell ， ETC...
而是使用引用对象作为 Sheets和 Range s。

Sub Finding_number()

    Dim index As Integer
    index = 1

    Dim Rng As Range
    Set Rng = Range("E2")

    Do While index = 1
        If Rng.Value = 121 Then
            index = 0
        End If

        Set Rng = Rng.Offset(1)
    Loop

End Sub

下面的较短版本将为您提供相同的结果:

Sub Finding_number()

    Dim Rng As Range
    Set Rng = Range("E2")

    Do While Rng.Value <> 121
        Set Rng = Rng.Offset(1)
    Loop

End Sub