none
Missing C# assembly reference in Excel VBA Project. RRS feed

  • Question

  • I created test Excel (Office 2007) VSTO project. Compile and published it. Run setup.exe Then open Excel VBA project. References dialod MISSING reference to C# assembly can be seen. How this can be fixed.

    My environment is VS 2010 on Win7 x64.

    Thanks. 

    Wednesday, December 28, 2011 7:26 PM

Answers

  • Hi Alex

    OK, I understand the problem, and I sort of know the steps required to fix it, but it's not something I've ever done before and I currently don't have time to sit down and actually do it...

    To summarize: you need to fix the broken reference by setting the reference to the correct location of the DLL after it's been installed. This code could be VBA code in the workbook, or code in your VSTO solution. (That is, it should work if you're not getting an error through VSTO when the workbook is opened.)

    The API for references in VBA projects is the Microsoft Visual Basic for Applications Extensibility 5.3 COM library. You should find that in this dialog box as well as in the Add References list for your VSTO project, in the COM tab.

    To remove a broken reference, you need code that basically does as follows: Remove the broken reference; add a reference to the file in the new location.

    Dim refs as VBE.References
    Dim oRef as VBE.Reference
    Dim pathToDLL as String
    pathToDll = "C:\etc."
    Set refs = Workbook.VBE.ActiveVBProject.References
    With refs
      For Each oRef in refs
        If oRef.IsBroken Then
          .Remove oRef
          .AddFromFile pathToDll
        End If
      Next
    End With


    Cindy Meister, VSTO/Word MVP
    Saturday, December 31, 2011 10:53 AM
    Moderator

All replies

  • Hi Alex

    You don't provide enough information.

    By default, nothing in a VSTO project is associated with a VBA project. How did you create this reference in the first place and what is it that you're referencing?


    Cindy Meister, VSTO/Word MVP
    Thursday, December 29, 2011 9:44 AM
    Moderator
  • Hi Cindy,

    I created macro-based Excel workbook(ExcelWorkbook3.xlsm) and added VBA module with 1 test macro: 

    Public Sub Test1()
        MsgBox "Hello World!!!"
    End Sub
    

    Then saved the book and closed it. Now Opened VS2010 and created Excel Office 2007 Workbook C# Project and added my macro-based excel to this C# project.

    Then set property of workbook ReferenceAssemblyFromVBAProject=True inside of VS2010. Added C# COM visible class:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.Office.Tools.Excel;
    using System.Windows.Forms;
    
    namespace ExcelWorkbook4
    {
        [System.Runtime.InteropServices.Guid("3840C7DB-5523-4E80-BE66-19AB696AE98F")]
        [System.Runtime.InteropServices.ComVisible(true)]
        [System.Runtime.InteropServices.ClassInterface(System.Runtime.InteropServices.ClassInterfaceType.None)]
        public class ClassTest1 : ExcelWorkbook4.IClassTest1
        {
            public ClassTest1() 
            {
            }
    
            public int Sum2Numbers(int a, int b)
            {
                return (a + b);
            }
    
            public bool CompareValues()
            {
                bool bResultFlag = false;
    
                bResultFlag = (Globals.Sheet1.Range["A1"].Value == Globals.Sheet2.Range["A1"].Value);
    
                return bResultFlag;
            }
        }
    }
    

    Also refactor/extract inteface ExcelWorkbook4.IClassTest1


     In ThisWorkbook.cs added override method:

    protected override object GetAutomationObject()
    {
         return (new ClassTest1());
    }
    


    Compile and published C# project.

    Ran setup.exe file from "publish" folder successfully. Opened ExcelWorkbook3.xlsm and saw in MISSING reference.

    This is full info.

     

     

     

     

     

    Thursday, December 29, 2011 2:15 PM
  • Hi Alex

    OK, I understand the problem, and I sort of know the steps required to fix it, but it's not something I've ever done before and I currently don't have time to sit down and actually do it...

    To summarize: you need to fix the broken reference by setting the reference to the correct location of the DLL after it's been installed. This code could be VBA code in the workbook, or code in your VSTO solution. (That is, it should work if you're not getting an error through VSTO when the workbook is opened.)

    The API for references in VBA projects is the Microsoft Visual Basic for Applications Extensibility 5.3 COM library. You should find that in this dialog box as well as in the Add References list for your VSTO project, in the COM tab.

    To remove a broken reference, you need code that basically does as follows: Remove the broken reference; add a reference to the file in the new location.

    Dim refs as VBE.References
    Dim oRef as VBE.Reference
    Dim pathToDLL as String
    pathToDll = "C:\etc."
    Set refs = Workbook.VBE.ActiveVBProject.References
    With refs
      For Each oRef in refs
        If oRef.IsBroken Then
          .Remove oRef
          .AddFromFile pathToDll
        End If
      Next
    End With


    Cindy Meister, VSTO/Word MVP
    Saturday, December 31, 2011 10:53 AM
    Moderator
  • Hi Cindy,

         Thanks for the idea. I know it will work I've done something like this before. 

    But DLL path is actually 

    pathToDll = "C:\etc."
    

    not known. Assembly is installed by VSTO as side-by-side. How do I get the path in this case?

     

    Tuesday, January 3, 2012 2:25 PM
  • Hi Alex

    The starting point would probably be

    Environment

    .CurrentDirectory

    which will return the path of the add-in's dll.


    Cindy Meister, VSTO/Word MVP
    Wednesday, January 4, 2012 9:07 AM
    Moderator