从Excel VBA函数返回的值始终为null [英] Returning value from Excel VBA function is always null

问题描述

我试图运行excel vba宏并返回结果，但我总是返回null(对我的无知，我是这个宏的新手)

I'm attempting to run excel vba macro and get back result but I'm always getting back null (excuse my ignorance, I'm new to this macros)

宏

Public Function TestMacro() As Boolean
    If Len(Range("A1").Value) <> 9 Then
        TestMacro = False
    Else
        TestMacro = True
    End If
End Function

调用它的C#代码

Excel.Application excelApp = new Excel.Application { DisplayAlerts = false };
object misValue = Missing.Value;
excelApp.Visible = false;
Excel.Workbook ExcelWorkBook = excelApp.Workbooks.Open(filePath);
try
{
    var result = excelApp.Run("Sheet1.TestMacro");
    ExcelWorkBook.Close(false, misValue, misValue);
}
catch (Exception e)
{
    Console.WriteLine(e);
}
finally
{
    excelApp.Quit();
    if (ExcelWorkBook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelWorkBook); }
    if (excelApp != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); }
}

推荐答案

用作UDF(用户定义函数)的函数必须位于模块中，而不是工作簿对象(例如工作表).

The Function to be used as a UDF (User Defined Function) must be in a Module, not a workbook object (like a Worksheet).

此外，在可能的情况下，您的UDF不应触发任何用户交互(例如MessageBox).请记住，您的UDF可以在任何阶段重新计算(很好，使用了容易理解的触发器，有时甚至是不太了解的触发器)，并且您不希望用户在明显的无休止循环中被消息框淹没(例如，尝试在UDF中放置一个消息框)然后可以复制到1000个单元格！)

In addition, while it appears possible, your UDF should not trigger any user interaction (such as a MessageBox). Remember that your UDFs can be recalculated at any stage (well, with well understood and sometimes less understood triggers) and you do not want your user to be inundated with message boxes on an apparent endless loop (e.g. try having a message box in an UDF that is then replicated over a 1000 cells!)

当我执行VBA编码时，我创建一个名为(想象中！)"UDF"的模型，其中存储了所有UDF.这使我更容易理解哪些功能是后端代码的一部分，以及哪些功能专门设计用于前端.

When I do VBA coding, I create a model that is named (imaginatively!) "UDF" in which I store all my UDFs. This makes it easier for me to understand what functions are part of the back end code, and which functions are designed specifically to be used in the front end.

最重要的是，UDF代表一种功能-简单的输入而只有一个输出.以下示例应放在模块中:

The bottom line is, an UDF represent a function - simple inputs and only one output. The following example should be put in a module:

Option Explicit

Public Function TestMacro(inputcell as range) As Boolean
    If Len(inputcell.Value) <> 9 Then
        TestMacro = False
    Else
        TestMacro = True
    End If
End Function

在单元格中(不是A1，您要在其中获得结果的单元格):

In the cell (not A1, the one where you want the results):

=TestMacro(A1)

您可以做一些更复杂的事情，包括其他错误检查并返回Excel错误，例如#Value，#Ref，#Name-但这又是一个问题.

You can do some more complex things, and include additional error checking and return Excel errors such as #Value, #Ref, #Name - but that is a question for another day.

这篇关于从Excel VBA函数返回的值始终为null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！