none
How is possible to add a .dll file as reference in a VBA project? RRS feed

  • Question

  • Hi,

    I have 2 questions:

    1.I'm trying to figure whats the difference between a custom c# dll and one of the .dll's that can be added as reference into VBA project.

    To be more explicit if I create a custom .dll in c# and try to add it as reference into a word VBA project il get this error ""Can't add a reference to the specified file." But there are many .dll that can be added as reference e.g. "msxml6.dll" I know it has to be registered as COM but will add reference to the .tlb file in the references list. 

    2. Also a second question regarding com registration. If I try to deploy a dll to a target machine using an installer is enough to generate the tlb on my dev machine and just add it to the installer project or it has to be registered on the target machine also?

    Ty for the time.

    Monday, August 13, 2018 1:36 PM

All replies

  • There are a number of structure that needs to be present to be recognized as COM library.

    First, there needs to be at least DllRegisterServer() and DllUnregisterServer() function exports to be called by COM registration machanisms like "regsvr32.exe".

    C:\Windows\System32>dumpbin /exports msxml6.dll
    Microsoft (R) COFF/PE Dumper Version 14.14.26433.0
    Copyright (C) Microsoft Corporation.  All rights reserved.
    
    
    Dump of file msxml6.dll
    
    File Type: DLL
    
      Section contains the following exports for MSXML6.dll
    
        00000000 characteristics
        5A499414 time date stamp Mon Jan  1 09:51:16 2018
            0.00 version
               1 ordinal base
               5 number of functions
               5 number of names
    
        ordinal hint RVA      name
    
              1    0 00002B49 DllCanUnloadNow
              2    1 0000ABC2 DllGetClassObject
              3    2 000255FD DllRegisterServer
              4    3 000C3CB1 DllSetProperty
              5    4 000255FD DllUnregisterServer
    
      Summary
    
            D000 .data
            E000 .reloc
           30000 .rsrc
          10B000 .text
    
    C:\Windows\System32>

    And the for any object that needs to be exposed as COM object, there are a number of infrastructures that needs to be implemented.

    There is a property in installer project that is named SelfRegistering or the sort (depending on the installer packager you use). It'll add the reference of the file to SelfReg table of the MSI file and Windows Installer will know it register the file during install.

    There are also similar machanism for INF or VB6 Setup project INI .LST file. For other mean of installation you'll have to register the DLLs yourself manually or with installation scripts.

    Tuesday, August 14, 2018 2:06 AM
    Answerer
  • Hi Bogdan_Sorin,

    Thank you for posting here.

    For your question, if you want to add reference of C# dll to VBA project, you could try the steps below.

    Make a C# dll.

    1.Run Visual Studio as Administrator. (You need elevated permissions to let Visual Studio register the DLL properly. You can use regasm to do this yourself but it’s easier if Visual Studio does it for you.)

    2.Create a new class library project.

    3.Add a class to the project and give that class the methods that you want the VBA code to be able to execute.

    • Use the ComVisible attribute to allow the VBA code to use the class and methods.
    • Use the ClassInterface attribute to create a description that VBA’s IntelliSense can read so it can “see” the methods.

    Here is a simple code for your reference.

    using System.Runtime.InteropServices;
    namespace howto_dll_for_excel
    {
        [ComVisible(true)]
        [ClassInterface(ClassInterfaceType.AutoDual)]
        public class CSharpTools
        {
            [ComVisible(true)]
            public string AddBrackets(string value)
            {
                return "[" + value + "]";
            }
        }
    }

    4.Set the project properties.

    • Open the project’s property pages.
    • On the Application page, click “Assembly Information” and check the “Make assembly COM visible” box. (The VBA code will use COM to communicate with the DLL.)
    • On the Build property page, check the “Register for COM Interop” box.

    5.Build the project. Visual Studio should create .dll and .tlb files.

    After that add reference of this C# dll to VBA project to invoke.

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 14, 2018 8:13 AM
    Moderator
  • Ty so much for sugestions Cheong and Wendy.

    @Wendy, @Cheong00

    I know those steps but I get an error when I try to instantiate the object of that type "

    Error 429 : ActiveX component can't create the object.

    Here are the details(sorry I can;t post a link yet so I copy paste the whole question): 

    So, I have registered a dll as COM component to be used in a VBA project. Problem is when I run the project i keep getting this error Error 429. Active X component can't create object at the following line of code: 

    Dim myObject
    Set myObject = New AttachIt.AttachIt1

    I checked and I don't miss any references or at least this is what I think. Here are the references :

    • Visual basic for Applications
    • Microsoft Word 16.0 Object Library
    • Ole Automation
    • Microsoft Office 16.0 Object Library
    • Microsoft Forms 2.0 Object Library
    • AttachIt
    • Microsoft XML, v6.0
    • Microsoft ActiveX Data Objects 2.1 Library

    What is funny is that I isolate that instance another project like this and works fine: 

    Public Sub Special_Initialization()

        MsgBox "aaa"
        Dim myObject
        Set myObject = New AttachIt.AttachIt
        MsgBox "bbbb"

    End Sub

    Some work around that I found is if I move Attachit as last priority reference will work(no ideea why).

    Also if I add some random reference I will not get that error. If I uncheck it will also work. Is so weird because I cannot find any logic on whats happening.

    Also I have to mention that I register the DLL as COM trough an installer project and has an option to register the dll as COM.

    Thanks again.


    • Edited by Bogdan-Sorin Tuesday, August 14, 2018 8:42 AM Editing
    Tuesday, August 14, 2018 8:30 AM
  • Create a new project in Visual Studio and then add reference for your COM library to it. Then open "View" -> "Object Browser". Do you see the "AttachIt.AttachIt" in there?

    If yes, maybe some dependent library cannot be loaded. Enable fusion log and see if anything relevant can be found. (Actually if you try to add the class in your project in the same solution and instantiate it, there's high chance the IDE can trap the exception and tell you what is wrong)

    If not, something is wrong with your COM class registration. Check this guide to see if you've missed some attributes.


    Tuesday, August 14, 2018 1:19 PM
    Answerer
  • Hey @cheong00,

    Thanks again for answering.

    1. So in first case to check the object browser. Yes AttachIt is there.

    2. I enabled fusion log and there is no failed operation. (I have an older installer from another release "which works", only operation failed is from that installer). 

    Here is what I did. I installed AttachIt using the old installer and saved the logs from fusion. Installed AttachIt with actual version and saved the logs. Compared them and there is no difference except host configuration file. 

    Also saved the log when I execute the vba code from old installer and new installer and compare them.

    I never used fusion log before so I'm not sure if this is the right practice.

    3. If there would be something wrong with the COM registration would not work even when I isolate that instance.


    • Edited by Bogdan-Sorin Tuesday, August 21, 2018 10:12 AM editing
    Tuesday, August 21, 2018 10:11 AM
  • If COM registration data is okay, then the only problems I can think of is:

    1) Path problem. Only DLLs in path (say, the System32 folder) and those in "trust center" -> "trusted locations" can be loaded. Add-ons are COM DLLs so these fall into category here. However since you mentioned the change in "Add reference" order can fix it, this is unlikely.

    2) Something caused the constructor to fail. Try dump error log on constructor of AttachIt then. Is your component uses MSXML or ADO within constructor?

    Btw, I tend to make constructors simple, and create objects that reference unmanaged resources only on methods because it let me debug easier.

    Tuesday, August 21, 2018 4:09 PM
    Answerer