Application.OnKey with Parameters

[Mad1523]

Hey all,

I'm trying to setup a shortcut with Application.OnKey that will send a
parameter to the procedure when the shortcut is pressed. I understand
macros cannot have parameters, but UDF's can in ExcelDna. I created a
UDF with IsMacroType=true, IsHidden=true so that the function is not
visible by the user but parameters can be received. However, I get an
error from Excel even though the function is called and sent the
proper parameters:

XL.OnKey("^z", "Test(10,15)");

[ExcelFunction(IsMacroType=true, IsHidden=true)]
public static bool Test(object arg1, object arg2)
{
double i = (double)arg1;
double x = (double)arg2;

System.Windows.Forms.MessageBox.Show("Param1: " +
i.ToString() + ", Param2: " + x.ToString());

return true;
}

When I set a breakpoint and press the shortcut, the function is called
with the correct parameters and the MessageBox is shown. However,
after the function runs, excel shows a messagebox saying "Cannot run
the macro 'Test(10,15)'. The macro may not be available in this
workbook or all macros may be disabled."

Does anyone know what may be happening here? The Test function is
being called with the correct parameters, but after the function
exits, the error message pops up. Any way to prevent that message?

Thanks!

[Govert van Drimmelen]

Hi,

I don't think this is an Excel-DNA issue.

I tried to call a VBA macro with parameters via Application.OnKey and
got the same error. Calling from the Run Macro dialog fails with a
different error.

The only way I know of that you can call macros with parameters is
with Application.Run.
Even though your code runs, it cannot access Excel - if you tried to
write to a cell from your macro it fails.

(In the latest Excel-DNA check-in on CodePlex, I have added support
for defining macros with parameters. But I don't think this resolves
your issue.)

Regards,
Govert

[gobansaor]

Hi,

I've come across a similar problem in assigning macros with parameters
to Excel Shape objects and the like in the past. The solution in those
cases was ...

MyShape.OnAction = "'test 10,20'"

Note the enclosing 's (single quotes; always a good get-out-of-jail-
card in Excel) and the non use of brackets round paras (as is the
"style" of VBA called subs).

Worth a try.

Tom

[Mad1523]

Great, thanks for looking into that guys. I guess there is no elegant
solution.

It seems that using "'test 10,20'" doesn't work in a C# add-in, but it
does in VBA.

Nonetheless, Exceldna is amazing. Keep up the good work!

Thanks!!

[Naju Mancheril]

Francois,

The addin will definitely let you define macros which take params, but I don't think it is possible to wire them up (with fixed params) to a menu item.

Is that the goal? If so, then I think the easiest solution is to define a "wrapper macro" which takes no params and calls the other macro with ExcelDna's QueueAsMacro functionality. This will give Test full access to the workbook.

In this example, "TestWithMenu" is calling Test with 10 and 15. In fact, you don't even need to export Test as a macro to make this work. ExcelDna is calling it as a .NET function.

    [ExcelCommand(Name="TestWithMenu")]
    public static void TestWithMenu() {
      ExcelAsyncUtil.QueueAsMacro(() => Test(10, 15));
    }

    //[ExcelCommand(Name="Test")] // don't need to make this a macro, but we can
    public static void Test(int x, int y) {
      XlCall.Excel(XlCall.xlcAlert, String.Format("Got {0}, {1}", x, y));
      var refB1=new ExcelReference(0, 0, 0, 0, "Sheet1");
      refB1.SetValue("Set value!");
    }

I think the situation will be slightly more complicated if Test (the parameterized macro) is not defined in your .NET addin. For example, it it's exposed by a VBA addin or some other .NET addin, then you'll need to call it by name. Please post back if that is what you're trying to do.

[Naju Mancheril]

BTW, I notice this thread also mentions using .OnKey(). The no-param wrapper (TestWithMenu) will work with that as well.