你正在编写一个用户定义函数(UDF),即在公共标准模块中编写的Public Function
,可以从工作表单元格调用 - 而这种特定类型的函数具有一组限制条件。例如,不允许有副作用。UDF接受一些输入,处理它们,然后返回结果。
因此,UDF的签名应该如下所示:
Public Function {name}({args}) As {type}
编写函数时需要首先考虑的是,需要返回什么,换句话说,在计算完成后,单元格中应该包含什么内容,例如,如果您编写了一个将两个Double
值相加的Add
函数,则需要返回一个Double
:
Public Function Add(ByVal value1 As Double, ByVal value2 As Double) As Double
函数的主体通过给定的参数计算结果:
Dim result As Double
result = value1 + value2
在函数返回/退出之前,您需要赋值给函数的标识符来指定函数的返回值:
Add = result
Excel的计算引擎会接受到这个结果,这就是一个带有公式的单元格(例如=Add(2, 2)
)得出值为4
的方式。
你的STATUS
函数依赖于ActiveCell
,也就是当前选定的ActiveSheet
上的任何单元格: 它不是调用该函数的单元格。如果重新选择一些随机的单元格来重算工作簿,则可能产生错误的结果。
作为UDF,它不允许对单元格进行.ClearContents
操作(或以任何其他方式影响其他单元格)。这就是为什么该函数对进入条件块的执行路径返回#NAME?
错误,并且由于从未分配任何返回值,因此其他执行路径生成0
,这是Empty
变量的数值表示形式,这是您当前函数返回的内容。
如果UDF需要了解另一个单元格的值,则最好的方法是将该单元格的值作为参数传入: 这样函数就可以正常工作,而无需关心工作表的布局。如果VLOOKUP
没有使用lookup_value
参数,而是从.Offset(0, 1)
单元格获取该值,那么它会有多有用? 人们会暴动!
当您的需求是执行某些操作而不是计算/推算某些内容时,您需要的不是UDF,而是宏。
宏是公共标准模块(或Worksheet
模块)中的无参数Public Sub
过程,可以从“宏”窗口调用,或在用户单击Shape
、ActiveX CommandButton
或将其分配到某个自定义菜单项的OnAction
属性时执行 - 随你喜欢。
Sub
过程执行操作。 它们可以访问和更改全局状态,修改任何单元格、工作表或工作簿; 他们甚至可以生成一个PowerPoint实例,并将图表粘贴为图片到一个新的Slide
上——你想得到的任何东西,天空就是极限!
由于您在这里需要做一些事情,因此您需要编写的代码更像宏。不要称之为STATUS
;使用一个动词并描述它正在进行的操作:根据给定的条件,您正在将值从一列移动到另一列。当您编写一个Sub
过程时,首先考虑如何调用它。
我认为像这样的代码会很整洁:
MoveValues Sheet1.Range("$B$2:$B$22"), "System"
所以签名看起来应该是这样的:
Private Sub MoveValues(ByVal Target As Range, ByVal Criteria As String)
现在,body可以遍历指定的Target
范围,评估右侧单元格是否与Criteria
匹配,并相应地将值移动到左侧。或者更好的是 - 我们根本不假设工作表布局,像这样调用它:
With Sheet1
MoveValues .Range("A2:A22"), .Range("B2:B22"), .Range("C2:C22"), "System"
End With
现在,如果我们需要在A和B之间或B和C之间插入一列,我们只需要更改传递给我们的过程的参数,而不是过程本身!
Private Sub MoveValues(ByVal Source As Range, ByVal Target As Range, ByVal Status As Range, ByVal Criteria As String)
首先,我们需要验证我们的假设,并在我们的预期未达到时决定要做什么——我们需要具有相同行数的单列范围!
在许多情况下,引发运行时错误是最好的选择。错误#5“无效的过程调用或参数”似乎是一个相当好的选择:
If Source.Columns.Count <> 1 Or Target.Columns.Count <> 1 Or Status.Columns.Count <> 1 Or _
Source.Rows.Count <> Target.Columns.Count Or _
Status.Rows.Count <> Target.Columns.Count _
Then
Err.Raise 5
End If
我们甚至可以自定义错误消息,以帮助我们在调用代码后进行调试,当我们在未来几个月更改参数并忘记该MoveValues
过程的假设时。
If Source.Columns.Count <> 1 Or Target.Columns.Count <> 1 Or Status.Columns.Count <> 1 Or _
Source.Rows.Count <> Target.Columns.Count Or _
Status.Rows.Count <> Target.Columns.Count _
Then
Err.Raise 5, "MoveValues", _
"Source, Target, and Status ranges must be 1 column and the same number of rows."
End If
我们还需要验证我们的 Criteria
不是空的,也不只是空白字符!
If Trim$(Criteria) = vbNullString Then
Err.Raise 5, "MoveValues", "Criteria string cannot be empty or whitespace."
End If
现在我们已经验证了输入,因此剩下的过程可以安全地假设源
、目标
和状态
范围都是单列范围,它们的大小都相同,并且我们有一个有效的条件可供使用。因此,我们可以继续迭代单元格并执行我们的操作:
Dim current As Long
For current = 1 To Target.Rows.Count
If Status.Cells(current).Value = Criteria Then
Target.Cells(current).Value = Source.Cells(current).Value
Source.Cells(current).ClearContents
End If
Next
现在需要做的就是编写一个调用它的宏:
Public Sub MoveSystemValues()
With Sheet1
MoveValues .Range("A2:A22"), .Range("B2:B22"), .Range("C2:C22"), "System"
End With
End Sub
现在,我们可以从Excel的宏窗口运行MoveSystemValues
宏,或将其分配给某些Shape
或按钮...然后意识到对于少量行它运作得非常好,但对于更大的范围而言则相当慢- 但是现在我们已经有足够的内容来处理了。
STATUS
应该在代码中返回一些内容,但是这并没有被设置。一个函数只能对输入的值进行操作,所以它不会按照你想要的方式工作,你需要在一个范围上运行子程序。继续使用你已经有的代码,但是你需要在传入的每个单元格上运行该函数。例如:for each c in range("a1:a10").cells:status(c.value):next c
,但是你需要将范围传递给c
,因为它将在偏移量等方面被使用。你可以在函数中获取.value
。 - Nathan_Sav