为什么这两个创建宏的smilar宏中只有一个起作用? [英] Why only one of these 2 smilar macros to create bordures works?

问题描述

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

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.

这篇关于为什么这两个创建宏的smilar宏中只有一个起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！