calling Excel sub/function via DDE - "Argument not optional"

[rolex]

I'm trying to call an Excel sub or function from another application via DDE. So far I had succeeded calling any sub that has no parameters, via Run method, like this:

ch = DDEInitiate("excel", "system")
DDEExecute ch, "[Run(""test.xls!Test"")]"

Now I'm trying to call a sub with parameters:

DDEExecute ch, "[Run(""test.xls!Test"",123)]"

where Test is a sub with one Integer parameter.

When called, this results in Excel error: "Argument not optional". Any ideas what could be wrong there?

 

[Andrew Poulsom]

Just guessing, but try:

DDEExecute ch, "[Run(""test.xls!Test(123)"")]"

 

[rolex]

On 2002-11-26 04:14, Andrew Poulsom wrote:
Just guessing, but try:

DDEExecute ch, "[Run(""test.xls!Test(123)"")]"

nah, tried that already...
Macro "test.xls!Test(123)" cannot be found

 

[Andrew Poulsom]

Is it as simple as adding a space before the comma?

DDEExecute ch, "[Run(""test.xls!Test"" ,123)]"

Or could you use the Call keyword like this:

DDEExecute ch, "[Call test.xls!Test(123)]"

 

[rolex]

Alas, that doesn't work (and looks like it shouldn't), according to documentation on Run method, syntax is Run(macro_name,arg1,arg2...)

 

[Andrew Poulsom]

In the Run Method example in Help there is:

mySum = Application.Run("MYCUSTOM.XLM!My_Func_Sum", 1, 5)

Notice the spaces after (not before, my mistake) the commas. So maybe:

DDEExecute ch, "[Run(""test.xls!Test"", 123)]"

Also note that normally arguments for methods are enclosed in brackets only if the result of the method is assigned to a variable. So:

Result = Application.Run("MyMacro")

but

Application.Run "MyMacro"

Maybe try:

DDEExecute ch, "[Run ""test.xls!Test"", 123]"

Sorry this is taking so long.

 

[rolex]

Tried all possible variations, no dice..
I've noted that calling standard methods like New, select etc., arguments accepted without any problem, so this might not be the problem of syntax at all.

I wish someone who had called user functions successfully via DDE could help..

Thanks anyway

 

[Watever]

I know I am resurrecting a 2002 post, but now in 2024, I am facing the same situation.

Did anyone ever figured out how to do it ?
So far, the only way I was able to work "around" it, is to write the value in a cell and use that cell in my function instead of a parameter. (if the argument are option, you could do "if missing" then read that cell, but difficult if you want to run the function without any arguments at all).

Hopefully someone know the way to do it....

I shouldn't be using DDE and XML 4.0 (excel macro) anymore but that's the only possibility with what I am facing.