none
AddOLEObject does not work in VSTO Add-in called from VBA

    Question

  • I have an application level VSTO add-in. I need a buttons/images on a worksheet. These images when clicked should call my C# code.

    I create a Forms.Image.1 with worksheet.Shapes.AddOLEObject. Then bind a Microsoft.Vbe.Interop.Forms.Image using

    NewLateBinding.LateGet(). This works fine if I create only one button. If I add a loop to create 2 buttons, I receive this error from LateGet():

    Public member 'Image2' on type 'Worksheet' not found.

    This is the C# code in my VSTO Add-in that is called from VBA:

    using Excel = Microsoft.Office.Interop.Excel;
    using MSForms = Microsoft.Vbe.Interop.Forms;
    
    public void CreateImage()
            {
                MSForms.Image _imgButton
                Excel.Shape _combo;
                decimal width = 15;
                decimal height = 14;
    
     
    
                Excel.Worksheet ws;
     
                PictureBox pbImage;
                Image image, newImage;
                Icon icon;
                Bitmap bitmap;
    
                try
                {
                      image = Image.FromFile("Ellipsis_button.gif");
                      newImage = image.GetThumbnailImage(32, 32, null, new IntPtr());
    
                      pbImage = new System.Windows.Forms.PictureBox();
                      pbImage.Location = new System.Drawing.Point(2, 35);
                      pbImage.Name = "pbImage";
                      pbImage.Size = new System.Drawing.Size(255, 255);
                      pbImage.Image = newImage;
    
                      bitmap = new Bitmap(pbImage.Image);
                      bitmap.SetResolution(72, 72);
    
                    ws = _app.ActiveSheet;
                    System.Int32 left = 100;
                    System.Int32 top = 100;
    
                    for (int i = 0; i < 2; i++)
                    {
    
    
                        _combo = ws.Shapes.AddOLEObject("Forms.Image.1", Type.Missing, false, false, "Ellipsis_button.gif", 0, Type.Missing, left, top, width, height);
    
    
                        _imgButton = (MSForms.Image)NewLateBinding.LateGet(ws, null, _combo.Name, new object[0], null, null, null);
    
                        icon = System.Drawing.Icon.FromHandle(bitmap.GetHicon());
    
                        _imgButton.Picture = (stdole.StdPicture)Microsoft.VisualBasic.Compatibility.VB6.Support.IconToIPicture(icon);
    
                        _imgButton.PictureSizeMode = Microsoft.Vbe.Interop.Forms.fmPictureSizeMode.fmPictureSizeModeStretch;
                        _imgButton.BorderStyle = Microsoft.Vbe.Interop.Forms.fmBorderStyle.fmBorderStyleNone;
    
                    
                        _imgButton.Click += new Microsoft.Vbe.Interop.Forms.ImageEvents_ClickEventHandler(CmdBtn_Click);
                    }
    
                }
                catch (Exception ex)
                {
    
                    System.Windows.Forms.MessageBox.Show(ex.Message);
                }
    
            }
    
     void CmdBtn_Click()
            {
                //Adding the event code
                System.Windows.Forms.MessageBox.Show("I am called from C# COM add-in");
               
            }

    The VBA code called to create the buttons:

    Public Sub createBtn()
     Dim conn As ComServiceOleMarshal.AddinUtilities
     Dim addin As Office.COMAddIn
        Set conn = Application.COMAddIns.Item("ComServiceOleMarshal").Object
    
    
    conn.CreateImage
        
    End Sub
    

    Monday, February 20, 2012 6:06 PM

Answers

  • Hi,

    I think you need to have a look at this document for how to call code in VSTO add-in from other Office solution:

    http://msdn.microsoft.com/en-us/library/bb608621(v=vs.100).aspx,

    At a minimum, the class you want to expose must be public, it must have the ComVisibleAttribute attribute set to true, and it must expose the IDispatch interface. 

    If you want to dive deeper on this topic, you can refer to this blog:

    Why your COMAddIn.Object should derive from StandardOleMarshalObject

    In addition, I don't understand since you are working with VSTO add-in, which means the solution has .NET extenstion, why don't just use some WinForm control of .NET, in which way, you don't need to add these refereces and call these uncommon method.

    BTW,Microsoft.VisualBasic.Compatibility.VB6.Support.IconToIPicture(icon)

    don't work for all machines as:

    http://msdn.microsoft.com/en-us/library/ee839621(VS.100).aspx

    I hope this helps.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, February 21, 2012 2:01 AM
    Moderator
  • Thanks for the help.

    I will use  not use the AddOLEObject and LateBind. Instead, using the following:

    Microsoft.Office.Tools.Excel.Button btn;

    Microsoft.Office.Interop.Excel.Worksheet nativeWorksheet = Globals.ThisAddIn.Application.ActiveSheet;

    if (nativeWorksheet != null)

    {

    vstoSheet = Globals.Factory.GetVstoObject(nativeWorksheet);

    cc = vstoSheet.Controls;

    cc.AddControl(btn, 100, 100, 15, 14, "1");

    }


    • Marked as answer by E Benson Wednesday, February 22, 2012 7:29 PM
    Wednesday, February 22, 2012 7:28 PM

All replies

  • Hi,

    I think you need to have a look at this document for how to call code in VSTO add-in from other Office solution:

    http://msdn.microsoft.com/en-us/library/bb608621(v=vs.100).aspx,

    At a minimum, the class you want to expose must be public, it must have the ComVisibleAttribute attribute set to true, and it must expose the IDispatch interface. 

    If you want to dive deeper on this topic, you can refer to this blog:

    Why your COMAddIn.Object should derive from StandardOleMarshalObject

    In addition, I don't understand since you are working with VSTO add-in, which means the solution has .NET extenstion, why don't just use some WinForm control of .NET, in which way, you don't need to add these refereces and call these uncommon method.

    BTW,Microsoft.VisualBasic.Compatibility.VB6.Support.IconToIPicture(icon)

    don't work for all machines as:

    http://msdn.microsoft.com/en-us/library/ee839621(VS.100).aspx

    I hope this helps.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, February 21, 2012 2:01 AM
    Moderator
  • thank you for your reply.

    I previously read these articles amd implemented to get my add-in to this point.

    The class is exposed as I am successfully calling the c# method from vba.

    I am not calling put-of-process therefore no need for the StandardOleMarshalObject.

    You mentioned using Winforms. Can I add a button or image to a worksheet at a specific row/column using winforms? The button/image must be located within an Excel cell and  must have a click event/action. I have seen examples of Windows forms displaying when a sheet is active but it is not attached to the worksheet.

    http://windowsclient.net/learn/video.aspx?v=20412

    Tuesday, February 21, 2012 3:57 AM
  • Hi Benson,

    After thinking over your requirement again, I find a potential problem you may encounter even you have successfully added command button or WinForm button to your workbook. Think about this:

    You run the VBA procedure and the CreateImage method of the VSTO add-in is invoked. A command button is added to current workbook, also a button click event is registered for the command button. You click the button, it works. But here comes the problem: if you save the workbook, open it on either a machine with the add-in intalled or a clean machine, the button doesn't work this time!

    So we can see that adding controls onto document's surface via add-in doesn't make a lot of sense as the controls may loss its functionality. This is why we generally suggest to use Custom Task Pane instead of adding controls onto surface of document. Have a look at following documents:

    http://msdn.microsoft.com/en-us/library/aa942864.aspx

    http://msdn.microsoft.com/en-us/library/aa338197(v=office.12).aspx

    I hope this helps.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, February 22, 2012 8:48 AM
    Moderator
  • Thanks for the help.

    I will use  not use the AddOLEObject and LateBind. Instead, using the following:

    Microsoft.Office.Tools.Excel.Button btn;

    Microsoft.Office.Interop.Excel.Worksheet nativeWorksheet = Globals.ThisAddIn.Application.ActiveSheet;

    if (nativeWorksheet != null)

    {

    vstoSheet = Globals.Factory.GetVstoObject(nativeWorksheet);

    cc = vstoSheet.Controls;

    cc.AddControl(btn, 100, 100, 15, 14, "1");

    }


    • Marked as answer by E Benson Wednesday, February 22, 2012 7:29 PM
    Wednesday, February 22, 2012 7:28 PM
  • Glad to hear you got this works.

    Yes, this abosolutely works but you should consider the situation I mentioned in my last reply. The control wouldn't work if you save the workbook and open it again.

    Good day,


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us

    Thursday, February 23, 2012 2:25 AM
    Moderator