使用c#向按钮添加excel vba代码

I have a question about creating excel button and adding vba code function on it. I have created a button and module code but don't know how to make relation between them. Can anyone show me how?

我有一个关于创建excel按钮并在其上添加vba代码功能的问题。我已经创建了一个按钮和模块代码，但是不知道如何在它们之间建立关系。谁能告诉我怎么做?

my code for Button:

我的代码按钮:

 Excel.Shape btn = xlWorkSheet5.Shapes.AddOLEObject("Forms.CommandButton.1", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 300, 10, 150, 22);

 Excel.OLEObject sheetBtn = (Excel.OLEObject)xlWorkSheet5.OLEObjects(btn.Name);
            sheetBtn.Object.GetType().InvokeMember("Caption", System.Reflection.BindingFlags.SetProperty, null, sheetBtn.Object, new object[] { "Calculate Bus Load" });

and code for module:

模块和代码:

 String sCode = "Sub main()\r\n" +
                "   MsgBox \"Hello world\"\r\n" +
                "end Sub";

 VBA.VBComponent oModule = xlWorkBook.VBProject.VBComponents.Add(VBA.vbext_ComponentType.vbext_ct_StdModule);
            oModule.Name = "Module1";
            oModule.CodeModule.AddFromString(sCode);

            xlWorkBook.VBProject.VBComponents.Item(1).CodeModule.AddFromString(sCode);

2 个解决方案

#1

I have searched in Internet but didn't find anything usefull, so i cleared my mind and focused once more with c# help and I found an answer how to do it properly.

我在网上搜索过，但是没有发现任何有用的东西，所以我在c#的帮助下理清了思路，再次集中精力，找到了正确的答案。

My Code:

我的代码:

String updateBT "...// macro code for button";   
VBA.VBComponent oModule1 = xlWorkBook.VBProject.VBComponents.Add(VBA.vbext_ComponentType.vbext_ct_StdModule);
oModule1.Name = "Update";
oModule1.CodeModule.AddFromString(updateBT);

Excel.Shape btn2 = xlWorkSheet1.Shapes.AddFormControl(Excel.XlFormControl.xlButtonControl, 150, 5, 150, 22);
btn2.Name = "Update";
btn2.OnAction = "... // name of your macro code";
btn2.OLEFormat.Object.Caption = "... // Button name";

#2

As far as I understand, you need the intermediate call to code/macro module from the button when you click on the button. So the code gets triggered and does what you want it to do.

据我所知，您需要在单击按钮时从按钮中调用代码/宏模块。代码被触发，然后做你想做的事。

In usual manner, for e.g.

以通常的方式，例如:

we add a button in Excel Sheet
我们在Excel表格中添加了一个按钮
choose on_click event
选择on_click事件
add a code like call mySub
添加像调用mySub这样的代码

You need to do that within C#.

您需要在c#中这样做。

Please adjust for your module and control names. Here is a sample.

请调整您的模块和控件名称。这是一个样本。

//Within your above code add,

sheetBtn.Click += new MSForms.CommandButtonEvents_ClickEventHandler(sheetBtn_Click);

}

//button click event triggers

void sheetBtn_Click()
{
     call subMain
     // try a test using : MessageBox.Show("button test!");  
}

** PLEASE TRY THIS TUTORIAL OUT It has pretty much what you need.

** *请尝试本教程，它基本上符合您的需要。

As per the subject on just invoking a sheet sub or module sub written in Excel from C#, you may use run macro method.

根据主题，只要调用一个从c#用Excel编写的表子或模块子，您可以使用run宏方法。

//instead of this.application, you call refer to the Excel app object
this.Application.Run("yourMacroName",missing,missing........)

Reference:

参考:

#1