您的位置: 首页  >  技术问答  >  为什么这两个创建宏的smilar宏中只有一个起作用?

为什么这两个创建宏的smilar宏中只有一个起作用?

分类: 技术问答 • 2022-04-20 09:09:16
问题描述：

我有一张没有边界的桌子.我想为行标题所在的所有单元格创建边框.

I have a table without borders. I want to create borders for all the cells where the titles of the rows are.

我正在使用宏.

Sub BorderTable()
    Dim r As Range
    Set r = RangeTitelsAxisX
    r.Borders.LineStyle = xlContinuous 'a1
    r.Borders.Weight = xlMedium        'a2
End sub

此宏有效.但是以下宏因该错误而失败

This macro works. But the following macro fails with this error

运行时错误424'必需的对象'"

"Runtime Error 424 'Object Required'"

Sub BorderTable()
    Dim r As Range
    Set r = RangeTitelsAxisX
    BoldBorderAllCellOfRAnge (r) 'b
End Sub

对我来说，这2个宏是等效的.(我仅将21，a2替换为b，您可以在上面看到b = a1，a2

And for me this 2 macros are equivalent. ( I only replace 21, a2 by b and you can see above that b= a1, a2

Private Sub BoldBorderAllCellOfRAnge(r As Range)
    r.Borders.LineStyle = xlContinuous
    r.Borders.Weight = xlMedium 'xlThick
End Sub



Function RangeTitelsAxisX() As Range
    Range("B3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Set RangeTitelsAxisX = Selection
End Function

为什么做宏的第二种方法不起作用?

Why the second way to do the macro doesn't work?

答 

 BoldBorderAllCellOfRAnge (r)

这正在调用 BoldBorderAllCellOfRAnge ，但是传递的参数不是对象.

That's invoking BoldBorderAllCellOfRAnge, but the argument being passed is not an object.

(r)作为参数的意思是让一个强制的 r 成为一个值，然后将该值传递给被调用的过程" –在这种情况下不起作用，但是请注意，由于调用代码未保存对该表达式的结果的引用(局部变量)，因此有效地暂存了 ByVal 值.

(r) as an argument means "let-coerce r into a value, then pass that value to the invoked procedure" - no impact in this particular case, but note that this effectively pases the value ByVal, since the calling code isn't holding a reference (a local variable) to the result of that expression.

请注意 BoldBorderAllCellOfRAnge 和(r)之间的空格:这是VBE告诉您括号是 argument 的一部分，而不是过程调用(即，它们不分隔逗号分隔的参数列表)

Note the space between BoldBorderAllCellOfRAnge and (r): that's the VBE telling you the parentheses are part of the argument, not the procedure call (i.e. they're not delimiting a comma-separated list of arguments)

这将是编译时语法错误:

This would be a compile-time syntax error:

BoldBorderAllCellOfRAnge (r, 42)

由于无法计算表达式(r，42)，因此对VBA毫无意义.

Because the expression (r, 42) can't be evaluated, it means nothing to VBA.

r 是一个 Range 对象，let-coercion隐式调用其隐藏的 [_ Default] 成员，在这种情况下，该成员返回2D变量数组- BoldBorderAllCellOfRAnge 正在接收该2D变量数组，并说我要求对象引用"，因此出现需要对象"错误.

r being a Range object, let-coercion is implicitly invoking its hidden [_Default] member, which in this case returns a 2D variant array - BoldBorderAllCellOfRAnge is receiving that 2D variant array and says "dude I asked for an object reference", hence the "object required" error.

因此，要纠正该错误，只需删除括号:

So, to fix the error, simply remove the parentheses:

 BoldBorderAllCellOfRAnge r

或者，引入一个 Call 关键字(Rubberduck将将其标记为过时和提供以将其干净地删除):

Alternatively, introduce a Call keyword (Rubberduck will flag it as obsolete and offer to cleanly remove it):

 Call BoldBorderAllCellOfRAnge(r)

请注意，VBE现在将不再在过程名称和(开头括号之间强制使用空格，这意味着(...)现在正在定界参数列表，而不是强制对表达式求值.

Note that the VBE will no longer force a whitespace between the procedure name and the ( opening parenthesis now, and that means the (...) is now delimiting an arguments list, rather than forcing the evaluation of an expression.

关于此功能:

Function RangeTitelsAxisX() As Range
    Range("B3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Set RangeTitelsAxisX = Selection
End Function

请考虑避免使用 Range.Select .这应该等效并且效率更高-请注意，该函数是隐式公开，但应该如果仅由同一模块中的其他过程调用，则为私有.

Consider avoiding Range.Select whenever possible. This should be equivalent, and much more efficient - note that the function is implicitly public, but should be private if it's only ever invoked by other procedures in the same module.

此外，不合格的 Range 调用是隐式指的是 ActiveSheet (如果该代码是在工作表模块的代码隐藏之外的任何地方编写的:

Also, the unqualified Range call is implicitly referring to ActiveSheet if that code is written anywhere other than in a worksheet module's code-behind:

Private Function RangeTitelsAxisX() As Range
    Set RangeTitelsAxisX = ActiveSheet.Range("B3").End(xlDown)
End Function

如果代码是在工作表模块中编写的，则需要这样:

If the code is written in a worksheet module, you'll want it like this:

Private Function RangeTitelsAxisX() As Range
    Set RangeTitelsAxisX = Me.Range("B3").End(xlDown)
End Function

始终使用适当的 Worksheet 对象限定 Range 调用:您将避免很多错误.

Always qualify Range calls with a proper Worksheet object: you'll avoid lots of bugs.

相关推荐