7

I have the following code in VBA (which resides in an Excel 2007 Workbook):

Public Function Multiply(a As Double, b As Double) As Double
    Multiply = a * b
End Function

If I invoke Multiply from other VBA code, it returns the correct value. However, when I call Multiply from C#:

var excel = new Application {Visible = true};
excel.Workbooks.Open(filename);
var returned = excel.Run("Sheet1.Multiply", (Double) a, (Double) b);

... the multiplication takes place (I can verify this by adding tracing to the Multiply function in VBA) but the returned value isn't available in my C# code; returned is always null.

Could someone please tell me how to get at the return value of Multiply from my C# code?

Duncan Bayne
  • 3,870
  • 4
  • 39
  • 64
  • I have been able to work around this by modifying Multiply() to set the value of a cell to the return value, and reading the value of that cell from C#. Ugly but it works. – Duncan Bayne Oct 14 '10 at 01:48
  • Any idea why it works from a module but not from the sheet? – anakic Apr 22 '15 at 16:21
  • None I'm afraid. I actually haven't done any significant work on Microsoft stuff since 2011, so this is probably going to go un-answered unless someone else out there can help ... – Duncan Bayne Apr 22 '15 at 22:43

3 Answers3

4

Have you tried moving your function to a regular module in Excel (not a sheet module)?

shA.t
  • 16,580
  • 5
  • 54
  • 111
Tim
  • 91
  • 1
  • I created a new Module, moved the Function to that, and changed "Sheet1.Multiply" to "MyModule.Multiply". Worked perfectly, thanks :-) Just one question: was this an insight you gained through experience, or is this actually documented somewhere? The information on MSDN etc. is pretty sparse for calling VBA via. interop. – Duncan Bayne Oct 15 '10 at 01:24
  • 1
    @DuncanBayne `Sheet1` is an *object*, an *instance* of a `Worksheet` class. In order to call into members of a class, you need an instance - and the `Sheet1` instance only lives in the VBA runtime, .net doesn't see it. Standard modules are just that: procedural modules exposing executable code through public members; you don't need (you couldn't anyway) to *instantiate* it to call its members. That's why it works in `Module1` and not in `Sheet1`. – Mathieu Guindon May 13 '16 at 13:51
0

A simple example :

The test spreadsheet.xlsm has Module1 containing:

Public Function test() As String
  test = "hello 1234"
End Function

With objExcel already set to the spreadsheet...

Dim result = objExcel.Run("Module1.test")
MsgBox(result)

... produces hello 1234 in the pop-up message.

Because I had originally tested this with ? module1.test() (successfully) in the VBA Immediate window, I was initially trying to get objExcel.Run("Module1.test()") to work - but I was getting an error: Cannot run the macro .... The macro may not be available in this workbook or all macros may be disabled. - removing the brackets did the trick.

AjV Jsy
  • 5,799
  • 4
  • 34
  • 30
-4

Try modifying your Multiply function to be like the code below:

Public Function Multiply(a As Double, b As Double) As Double
    return a * b
End Function
AeroX
  • 3,387
  • 2
  • 25
  • 39
Amarpreet
  • 137
  • 5
  • 9
  • That isn't even valid VBA. See: http://office.microsoft.com/en-in/excel-help/create-custom-functions-in-excel-2007-HA010218996.aspx – Duncan Bayne Oct 13 '10 at 02:46
  • 1
    You can't use the return keyword in VBA - you need to set the return value to the function name e.g. Multiply = a * b, which was in the original question. – Jazza Oct 20 '10 at 16:21
  • This is VB.Net, not VBA. – Icemanind Dec 30 '14 at 21:21