Passing by ref a parameter to macro calling a exce

问题:

I have a c# function defined through excel DNA :

[ExcelFunction(Description = "does stuff", IsVolatile = false, IsMacroType = true, IsThreadSafe = true)]
public object AFunction(long k \* other parameters *\)
{
    // do stuff
}

that is called in VBA as follows :

Dim v As Variant
v = Application.Run("AFunction", k)

Now I modify my excel DNA c# as follows :

[ExcelFunction(Description = "Retrieves valo folio", IsVolatile = false, IsMacroType = true, IsThreadSafe = true)]
public object AFunction(ref double x, long k \* other parameters *\)
{
    // do stuff
    // update x
}

the idea being that I will pass to it a double that will be updated, and that I will use after.

I call this in VBA as follows :

Dim v As Variant
v = Application.Run("AFunction", x, k)

But x ("dimed" as Double) is not updated. I tried a

Dim x() as Double
Redim x(1)

and a

Dim v As Variant
v = Application.Run("AFunction", x(1), k)

but here also x(1) is not updated.

Is there a problem with the ref in the c#, or is the problem caused by the Application.Run ?

回答1:

The problem was Application.Run indeed, as suspected, because it can't a priori pass parameters by reference. But in fact it can, see here :

http://www.tushar-mehta.com/publish_train/xl_vba_cases/1022_ByRef_Argument_with_the_Application_Run_method.shtml

where basically one wraps the UDF in a method of a VBA class of which an instance is passed using Application.Run

Cumbersome but working.