Returning value from Excel VBA function is always null

Question

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)

the macro

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

the c# code invoking it

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); }
}

[This says](https://stackoverflow.com/questions/3920343/access-return-value-from-vba-function-in-net) it works better in a module than a sheet but I don't personally know. — Crowcoder, May 12 '19 at 00:57

score 0 · Accepted Answer · edited May 13 '19 at 06:13

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

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!)

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.

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

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

=TestMacro(A1)

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.

Returning value from Excel VBA function is always null

1 Answers1

Linked