调用sub时需要excel错误424对象 [英] excel error 424 object required when calling sub

问题描述

我花了几个小时研究我的代码，但我不知道出了什么问题.

I spent hours looking into my code but I can't figure out what is wrong.

我一直没有出现明显的原因而收到此424错误.这是我的代码.

I keep getting this 424 error, for no obvious reason to me. here is my piece of code.

我只想将row作为参数提供给mySub来处理它.

I just want to give row as a parameter to mySub to process it.

Option Explicit

Private Sub mySub(row As Range)
    Debug.Print ("mySub") ' not printed
    Dim line As Collection

    Set line = New Collection
End Sub

Private Sub CalcClients()
    Dim data_sheet As Worksheet
    Dim last_row As Long
    Dim last_col As String
    Dim line As Long
    Dim cols As Range
    Dim row As Range

    Set data_sheet = Worksheets("DATA")
    Let last_row = data_sheet.Range("A1").End(xlDown).row
    Let last_col = Col_Letter(data_sheet.Range("A1").End(xlToRight).column)
    Set cols = data_sheet.Range("A2:" & last_col & last_row)

    For Each row In cols.Rows
        ' type_name(row) => "Range"
        Debug.Print (row.Cells(1, 1).Value) '=> THEEXPECTEDVALUE
        mySub (row) ' Error 424, object required
    Next

End Sub

推荐答案

这是观察到的行为的原因.

Here is the reason for the observed behavior.

您的子例程mySub()将一个参数作为范围类型.

Your subroutine, mySub() takes one parameter as a range type.

这意味着您必须将其传递一个范围.

This means you must pass it a range.

在您的代码中，将对象变量row设置为一系列范围，一次一次.

In your code you set an object variable row to a series of ranges, one at a time.

要将该范围变量row用作mySub的参数，语法应如下所示:

To use that range variable row as a parameter for mySub the syntax should be like this:

mySub row

或者...

Call mySub(row)

...但是您正在执行此操作:

...but instead you are doing this:

mySub (row)

那么，有什么区别?当您在任何单独存在的变量周围加上括号时(如上所示)，该变量将立即进行评估，并且优先于您打算对其进行的任何处理.

So, what's the difference? When you place parentheses around any variable that is standing alone (as in the above), that variable gets evaluated immediately and prior to whatever you plan to do with it.

括号是重写过程的ByRef参数并强制一次性传递ByVal参数的一种常用方法.这是因为括号强制对变量进行求值，并传递结果VALUE而不是对变量的引用.

The parentheses are a common way to override a procedure's ByRef argument and instead force a one-time ByVal parameter pass. This is because the parentheses force an evaluation of the variable and the resultant VALUE is passed instead of a reference to the variable.

对于您而言，您不想这样做(实际上，在大多数情况下，您不想这样做). 将row与括号夹在中间时，范围对象不再传递给例程.而是对其求值，并将其值作为变量数组传递.

In your case, you do not want to do this (in fact, in most cases you do not want to do this). When you sandwich row with parentheses, the range object is no longer passed to the routine. Instead it is evaluated and its values are passed as a Variant Array.

并且由于mySub定义要求使用范围对象参数，因此会出现错误424.mySub抱怨:嘿，这不是范围对象，需要范围对象！"

And since the mySub definition calls for a range object parameter, you get error 424. mySub is complaining, "Hey, this is not a range object and a range object is required!"

这篇关于调用sub时需要excel错误424对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！