添加Excel VBA中的code到按钮使用C＃ [英] Add excel vba code to button using c#

问题描述

我有一个关于创建Excel的按钮，并在其上​​添加VBA code函数问题。我创建了一个按钮，模块code，但不知道如何使他们的关系。谁能告诉我怎么样？

我的code的按钮：

  Excel.Shape BTN = xlWorkSheet5.Shapes.AddOLEOb​​ject（Forms.CommandButton.1，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing ，Type.Missing，300，10，150，22）; Excel.OLEOb​​ject sheetBtn =（Excel.OLEOb​​ject）xlWorkSheet5.OLEOb​​jects（btn.Name）;
            。sheetBtn.Object.GetType（）InvokeMember（标题，System.Reflection.BindingFlags.SetProperty，空，sheetBtn.Object，新的对象[] {计算总线负载}）;
 

和code的模块：

 的String code =副主（个）\\ r \\ n+
                MSGBOX \\世界，你好\\\\ r \\ n+
                结束子; VBA.VBComponent oModule = xlWorkBook.VBProject.VBComponents.Add（VBA.vbext_ComponentType.vbext_ct_StdModule）;
            oModule.Name =模块1;
            oModule codeModule.AddFromString（S code）。            xlWorkBook.VBProject.VBComponents.Item（1）codeModule.AddFromString（S code）。
 

解决方案

我已经搜索在互联网，但没有发现任何有用的，所以我清理我的心灵，用C＃的帮助再次集中，我找到了答案怎么办正确。

我的code：

 字符串updateBT... //宏$ C $下按钮;
VBA.VBComponent oModule1 = xlWorkBook.VBProject.VBComponents.Add（VBA.vbext_ComponentType.vbext_ct_StdModule）;
oModule1.Name =更新;
oModule1 codeModule.AddFromString（updateBT）。Excel.Shape BTN2 = xlWorkSheet1.Shapes.AddFormControl（Excel.XlFormControl.xlButtonControl，150，5，150，22）;
btn2.Name =更新;
btn2.OnAction =... //你的宏code的名;
btn2.OLEFormat.Object.Caption =... //按钮名称;
 

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?

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);

解决方案

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.

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";

这篇关于添加Excel VBA中的code到按钮使用C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！