private void button1_Click(object sender, System.EventArgs e)
{
Excel.Application oExcel;
Excel.Workbook oBook;
VBIDE.VBComponent oModule;
Office.CommandBar oCommandBar;
Office.CommandBarButton oCommandBarButton;
String sCode;
Object oMissing = System.Reflection.Missing.Value;
// Create an instance of Excel.
oExcel = new Excel.Application();
// Add a workbook.
oBook = oExcel.Workbooks.Add(oMissing);
// Create a new VBA code module.
oModule = oBook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
sCode =
"sub VBAMacro()\r\n" +
" msgbox \"VBA Macro called\"\r\n" +
"end sub";
// Add the VBA macro to the new code module.
oModule.CodeModule.AddFromString(sCode);
try
{
// Create a new toolbar and show it to the user.
oCommandBar = oExcel.CommandBars.Add("VBAMacroCommandBar",oMissing, oMissing,\);
oCommandBar.Visible = true;
// Create a new button on the toolbar.
oCommandBarButton = (Office.CommandBarButton) oCommandBar.Controls.Add(
Office.MsoControlType.msoControlButton,
oMissing, oMissing, oMissing, oMissing);
// Assign a macro to the button.
oCommandBarButton.OnAction = "VBAMacro";
// Set the caption of the button.
oCommandBarButton.Caption = "Call VBAMacro";
// Set the icon on the button to a picture.
oCommandBarButton.FaceId = 2151;
}
catch(Exception eCBError) {
MessageBox.Show("VBAMacroCommandBar already exists.","Error");
}
// Make Excel visible to the user.
oExcel.Visible = true;
// Set the UserControl property so Excel won't shut down.
oExcel.UserControl = true;
// Release the variables.
oCommandBarButton = null;
oCommandBar = null;
oModule = null;
oBook = null;
oExcel = null;
// Collect garbage.
GC.Collect();
}
7.
按 F5 键生成并运行该程序。
8.
单击 Button1 启动 Microsoft Excel,插入 Visual Basic for Applications (VBA) 代码,然后添加一个新的 CommandBar。单击 CommandBar 上的按钮以运行 VBA 宏。
Microsoft Office 2003 和 Microsoft Office XP 应用程序具有一个安全选项,以允许对 VBA 对象模型进行编程访问。如果此设置为
关(默认设置),可能会在运行该代码示例时出现一个错误。 有关此设置和如何纠正该错误的其他信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
282830 (http://support.microsoft.com/kb/282830/) PRB:Programmatic Access to Office XP VBA Project Is Denied