如何调试Excel中的自定义函数

正如你已经知道的那样 UDFs (我希望你也尝试过在你的Excel中应用它们），让我们再深入一点，看看在你的用户定义函数不工作的情况下可以做些什么。

为了解决创建自定义函数时的一些问题，你很可能需要运行一个调试。然后你就可以确定该函数是正确工作的。

我们将探讨以下调试技术。

当你创建一个自定义函数时，总是有可能会犯错。自定义函数通常相当复杂。而且它们并不总是马上就开始正常工作。公式可能会返回一个不正确的结果或#VALUE！错误。与标准Excel函数不同，你不会看到任何其他信息。

有没有办法一步一步地通过自定义函数来检查它的每个语句是如何工作的？当然可以!调试就是用于此。

我将为你提供几种调试自定义函数的方法，你可以选择适合你的方法。

作为一个例子，我们使用自定义函数 获取MaxBetween 从 我们以前的文章之一 它计算了指定数值范围内的最大数字。

Function GetMaxBetween(rngCells As Range, MinNum, MaxNum)
	Dim NumRange As Range
	Dim vMax
	Dim arrNums()
	Dim i As Integer

	ReDim arrNums(rngCells.Count)
	For Each NumRange In rngCells
		vMax = NumRange
		Select Case vMax
			Case MinNum + 0.01 To MaxNum - 0.01
				arrNums(i) = vMax
				i = i + 1
			Case Else
				GetMaxBetween = 0
		End Select
	Next NumRange

	GetMaxBetween = WorksheetFunction.Max(arrNums)
End Function

函数参数是写入数字的单元格范围，以及数值的上限和下限。

将MsgBox函数放在重要位置

为了监控计算的执行情况，你可以在屏幕上适当的地方显示最重要的变量的值。这可以用弹出式对话框来实现。

MsgBox 是一个对话框，你可以用它来向用户显示某种信息。

MsgBox的语法与其他VBA函数类似。

MsgBox(prompt [, buttons] [, title] [, helpfile, context])

提示 是一个必要的参数。它包含你在对话框中看到的信息。它也可以用来显示各个变量的值。

所有其他的参数都是可选的。

[buttons] – 决定了哪些按钮和图标会显示在 MsgBox.例如，如果我们使用选项 vbOkOnly，则只有 认可 按钮将被显示。即使你错过了这个参数，这个按钮也是默认使用的。

[title] – 这里你可以指定消息框的标题。

让我们从文字转换到实践，开始调试。为了显示消息，在代码中添加以下一行 GetMaxBetween 前的用户定义的函数。 案例 Else 经营者。

	MsgBox vMax,, "Count -" & i

下面是我们将得到的结果。

Function GetMaxBetween(rngCells As Range, MinNum, MaxNum)
	Dim NumRange As Range
	Dim vMax
	Dim arrNums()
	Dim i As Integer

	ReDim arrNums(rngCells.Count)
	For Each NumRange In rngCells
		vMax = NumRange
		Select Case vMax
			Case MinNum + 0.01 To MaxNum - 0.01
				arrNums(i) = vMax
				i = i + 1
				MsgBox vMax,, "Count -" & i
			Case Else
				GetMaxBetween = 0
		End Select
	Next NumRange
	GetMaxBetween = WorksheetFunction.Max(arrNums)
End Function

使用 vMax 对话框中的变量，我们将看到哪些数字符合选择标准，这样我们就可以选择其中最大的数字。通过标题栏中的表达式 “计数-“& I，我们表明我们已经选择了多少个数字来确定最大值。每一个新的数值，计数器就会增加。

一旦我们设置了UDF，我们就将下面的公式应用于日期范围。

= GetMaxBetween (A1:A6,10,50)

在 进入 按钮，你会看到如下截图中的信息。

这是A1: A6范围内第一个符合条件的数字：大于10但小于50。

在你点击 “确定 “后，出现了第二条信息，其中有一个数字14。其余的数字都不符合选择条件。因此，该函数退出并返回两个数值中最大的一个，即17。

该 MsgBox 函数可以在你的自定义函数中最重要的地方使用，以控制各个变量的值如何变化。当你有一个大的函数和大量的计算时，消息框就会非常有用。在这种情况下，你将很容易确定错误发生在代码的哪个部分。

确定停止点并逐步执行

你可以在你的函数的代码中添加断点，在那里代码执行将停止。因此，你可以一步一步地跟踪计算过程。这样做，你可以看到变量的值如何变化。

要添加一个断点，将光标放在包含你选择暂停的语句的行上。然后右键单击并选择 Debug -> 切换断点 或直接按 F9.你也可以在功能代码左边的垂直灰色区域中点击需要的地方。

一个红色的圆圈会出现，正如你在下面的截图中看到的那样。将停止计算的那行代码用红色突出显示。

现在，当函数运行时，VBA编辑器窗口将被打开。光标将被定位在你停止的那一点上。

如果你将鼠标光标悬停在函数代码中的任何一个变量上，你可以看到它们的当前值。

按 F5 来继续计算。

注意： 在断点之后，你可以开始一步一步地跟踪计算的进度。如果你按下 F8 按钮，只有VBA代码的下一行会被执行。带箭头的黄线也将移动到最后执行的代码位置。

由于函数的执行又暂停了，你可以用鼠标光标查看函数所有变量的当前值。

下一次按动 F8 将使我们向前迈出一步。所以你可以按 F8 直到计算结束。或者按 F5 来继续计算，直到下一个断点。

如果发生错误，光标将停在代码中发生错误的那一点。而且你还会看到一个弹出的错误信息。这使得我们很容易确定问题的原因。

你指定的断点将被应用，直到你关闭文件。当你重新打开它时，你将需要再次设置它们。这不是最方便的方法，你不觉得吗？

然而，这个问题是可以解决的。插入一个 停止 在必要的位置将语句加入到函数代码中，你可以用与使用断点时相同的方式停止程序的执行。

当VBA遇到一个 停止 语句，它将停止程序的执行，等待你的行动。检查变量的值，然后按 F5 来继续。

或按 F8 来一步步实现上述功能。

的 停止 语句是程序的一部分，因此不会被删除，就像断点的情况一样。当你完成调试后，可以自己删除它。或者在它前面加上一个单引号（’），把它变成一个注释。

使用Debug.Print操作符进行调试

你可以把 Debug.Print 在函数代码中的正确位置。这对于检查周期性变化的变量的值很有用。

你可以在下面的截图中看到Debug.Print的性能实例。

声明 Debug.Print i, vMax 打印数值和它们的序号。

在 “立即 “窗口中，你可以看到来自选定范围的两个数字（17和14），它们与设定的极限值相对应，其中的最大值将被选中。数字1和2意味着函数已经完成了2个循环，其中的数字被选中。我们看到最重要的变量的值，就像我们之前看到的那样 MsgBox.但这并没有停止该功能。

从过程中调用一个函数

你可以不从工作表中的单元格调用一个用户定义的函数，而是从一个过程中调用。在这种情况下，所有的错误都会显示在Visual Basic编辑器窗口中。

下面是如何从过程中调用用户定义的函数GetMaxBerween。

Sub Test()
	Dim x

	x = GetMaxBetween(Range ("A1:A6"), 10, 50)
	MsgBox(x)
End Sub

将光标放在代码中的任何地方，然后按 F5.如果函数中没有错误，你会看到一个弹出的窗口，里面有计算结果。

如果出现了错误，你将在VBA编辑器中看到相应的信息。计算将被停止，发生错误的那一行代码将以黄色高亮显示。你可以很容易地识别错误发生的位置和原因。

这就是全部。现在你已经创建了自己的插件，将其添加到Excel中，你可以在其中使用UDF。如果你想使用更多的UDF，只需在VBA编辑器中编写add-in模块的代码并保存即可。