向Excel文档中嵌入VBA控件和UserForm并显示

实现环境：Visual Studio 2010, Excel 2010, VSTO 4.0

在文档中嵌入一个Commandbutton点击它会显示一个UserForm，UserForm上有一个CommandButton点击会弹出消息框然后CommandButton的Caption改变。当UserForm关闭是文档中的CommandButton的Caption改变。

VB.NET:

[vb]
Imports Microsoft.Office.Tools.Ribbon
Imports VBE = Microsoft.Vbe.Interop
Imports Forms = Microsoft.Vbe.Interop.Forms

Public Class Ribbon1
    Private WithEvents objCommandButton As Forms.CommandButton

    Private Sub Ribbon1_Load(ByVal sender As System.Object, ByVal e As _
                             RibbonUIEventArgs) Handles MyBase.Load

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e _
As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles Button1.Click
        Dim objApplicatin As Excel.Application = Globals.ThisAddIn.Application
        Dim objWorkbook As Excel.Workbook = objApplicatin.ActiveWorkbook
        Dim objWorksheet As Excel.Worksheet = objWorkbook.ActiveSheet
        Dim objShape As Excel.Shape
        Dim objOLEObject As Excel.OLEObject
        Dim strModuleSnippet As String
        Dim objVBAProject As VBE.VBProject
        Dim objVBComponent As VBE.VBComponent
        Dim objVBFormComponent As VBE.VBComponent
        Dim objObjectFormButton As Object

        objShape = objWorksheet.Shapes.AddOLEObject("Forms.CommandButton.1")
        objShape.Name = "btn1"
        objOLEObject = objWorksheet.OLEObjects("btn1")
        Try
            objCommandButton = TryCast(objOLEObject.Object, Forms.CommandButton)
            objCommandButton.Caption = "Click Me"
            strModuleSnippet = "private sub btn1_Click()" & Chr(13) & _
                "UserForm1.Show " & Chr(13) & "end sub"
            '当前的VBA工程
            objVBAProject = objApplicatin.VBE.VBProjects(0)
            '当前Worksheet的Componet
            objVBComponent = objVBAProject.VBComponents(0)
            '加入代码
            objVBComponent.CodeModule.AddFromString(strModuleSnippet)
            '加一个UserForm
            objVBFormComponent = objVBAProject.VBComponents.Add( _
                VBE.vbext_ComponentType.vbext_ct_MSForm)
            '加一个CommandButton
            objObjectFormButton = objVBFormComponent.Designer.Controls.Add( _
                "Forms.CommandButton.1")
            objObjectFormButton.Caption = "Form Button"
            objObjectFormButton.Name = "frmbtn1"
            '由于这个Button在UserForm内所以不能像上一个Button一样直接写事件控制
            '这个Button的事件必须用VBA代码控制
            strModuleSnippet = "private sub frmbtn1_Click()" & Chr(13) & _
                "Msgbox ""Hello World"" " & Chr(13) & _
                "frmbtn1.Caption = ""This is a Test""" & Chr(13) & "end sub"
            objVBFormComponent.CodeModule.AddFromString(strModuleSnippet)
        Catch ex As Exception
            MsgBox(ex.Message & Chr(13) & ex.StackTrace)
        End Try
    End Sub

    Private Sub objCommandButton_Click() Handles objCommandButton.Click
        objCommandButton.Caption = "Hello World"
    End Sub
End Class
C#:

[csharp]
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Tools.Ribbon;
using VBE = Microsoft.Vbe.Interop;
using Forms = Microsoft.Vbe.Interop.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelAddIn16
{
    public partial class Ribbon1
    {
        private Forms.CommandButton objCommandButton = null;
        private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
        {

        }

        private void button1_Click(object sender, RibbonControlEventArgs e)
        {
            Excel.Application objApplication = Globals.ThisAddIn.Application;
            Excel.Workbook objWorkbook = objApplication.ActiveWorkbook;
            Excel.Worksheet objWorksheet = objWorkbook.ActiveSheet;
            Excel.Shape ob

补充：Web开发 , ASP.Net ,