locked
Can I use VBE 2005 classes in Excel 2003 VBA code? RRS feed

  • Question

  • I have a class library that I'm developing in Visual Basic Express 2005. I can use the classes in other projects in VBE, but I would also like to use them in VBA code in Excel 2003 (i.e. in the VBA code I want to be able to create instances of the classes from the class library). 

    If that is possible, can someone please give me step by step instructions?

    Sunday, August 13, 2006 12:34 AM

Answers

  • Try to run regasm with the /codebase option. Note that you might need to Strong Name your assembly.

     

    Friday, August 18, 2006 12:06 AM
  • Yes, that seems to be the same error.

    Make sure that you sign the assembly with a strong name, and use the /codebase option when registering. If that still doesn't work, you could try adding the assembly to the GAC using the gacutil.exe tool.

    Blackwood, depending on how you intend to use/deploy your class, you should also consider adding the assembly to the GAC (instead of using the /codebase option)

    Hope this helps,

     

    Friday, August 25, 2006 8:34 PM

All replies

  • You can't directly use the VB2005 classes from VBA. I'm not sure if the following would work, but you could expose your classes as COM objects and then instantiate them from VBA.

    Hope this helps,

    Tuesday, August 15, 2006 12:42 AM
  • Thanks. Can you help me do that? Here is what I've tried so far.

    In the Application tab of the class library's Properties page, there is an Assembly Information button.  Clicking on this brings up a window that includes a checkbox labeled "make assembly COM-visible". That sounded useful, so I checked it and rebuilt the project. But when I went to Excel/VBA, I still couldn't create a reference to the class library.

    Searching around I found references to a need to use tlbexp.exe to create a .tlb file. I managed to find tlbexp.exe in Visual Studio's  \SDK\v2.0\bin folder and using a DOS box was able to create a .tlb file from the class library's .dll.  Then I could go to Excel/VBA and choose Add Reference, browse to the .tlb file and create the reference. In the VBA editor I coded a DIM statement to define an instance of one of my classes and Intellisense was aware of the library and the class, so we seem to be getting somewhere.  But when I tried to code a statement that used the instance of the class I found that Intellisense was not aware of any of the members of the class. And when I tried to execute the code, I got an error 429 "ActiveX component can't create object" when it tried to create the instance of the class.

    Looks like I'm part way there. Can you help me get the rest of the way?

    Tuesday, August 15, 2006 3:56 AM
  • That's a good start!

    Let's create a simple code sample to get things working.  Create a classlibrary project that has the following code:

    Imports System.Runtime.InteropServices

    <ComVisible(True)> _
    <ComClass()> _
    Public Class Class1
    Public Property GetValue() As Integer
    Get
    End Get
    Set(ByVal value As Integer)
    End Set
    End Property
    End
    Class

    Once you have the code in place, go to the "Project Properties" and in the "Compile Tab" check the "Register for COM-Interop" checkbox. Once you build, the TLB file and dll registration will happen automatically, so, you should be able to consume the class from VBA.

    You can find the TLB file along with the DLL. Import this in Excel/VBA and you should be good to go.

    Hope this helps,

     

    Wednesday, August 16, 2006 1:10 AM
  • Thanks again for your help.  I set up a test project using the code in your post (I didn't know about the ComVisible and ComClass attributes).  Unfortunately I do not see a "Register for COM-Interop" checkbox on the Compile tab of the Project Properties. The only things I can set on the Compile tab are Build output path, Compile options (Explicit/Strict/Compare), and notification types (error, warning, etc) for various conditions.  Perhaps "Register for COM-Interop" is not provided in the Express version?

    Just in case it had the same effect as "Register for COM-Interop", I went ahead and checked "Make assembly COM-visible" in the Assembly Information window accessed from the Application tab of the project properties.  But when I build, no .tlb file is created.

    Wednesday, August 16, 2006 2:32 AM
  • Are you using a classlibrary project? If you are then the command is most likely not supported in VB Express.

    At any rate, you'll need to do the following two steps manually (from the visual studio command prompt):

    • Export the TypeLibrary
    • Register the DLL

    You'll have to run the tlbexp and regasm commands respectively. They should accomplish the same thing as the "Register For COM-Interop"

    Wednesday, August 16, 2006 2:51 AM
  • Yes, the project is a "ClassLibrary" project.

    To use tlbexp and regasm, I did the following:

    • Opened a Command Prompt box
    • Used the CD command to navigate to the \bin\release folder of my project directory
    • Used "C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\tlbexp" testclasslibrary1.dll to create the .tlb file
    • Used "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\regasm" testclasslibrary1.dll to register the assembly

    tlbexp and regasm both said they completed OK. Then I opened Excel and the VBA editor. Using Tools|Add References, I was able to add a reference to TestClassLibrary1. In a Sub in a new module, I was able to enter:

    Dim testClass As New TestClassLibrary1.Class1
    testClass1.GetValue = 1

    Intellisense was aware of TestClassLibrary1, Class1, and even the GetValue property (which is further than I have been able to get so far). But when I ran the Sub, I got the following error:

    Run-time error '-2147024894 (80070002)'
     
    Automation error
    The system cannot find the file specified

    In case it made any difference, I tried unregistering the assemble ("regasm testclasslibrary1.dll /u"), deleting the .tlb file and then using "regasm testclasslibrary1.dll /tlb" to create the .tlb file and register the assembly in one step, but the result was the same.

    Thursday, August 17, 2006 2:02 AM
  • Which line gave the error?

    Anyway, try copying the tlb file and the dll file, to the location of your vba application and running the code.

    Hope this helps,

     

    Thursday, August 17, 2006 3:16 AM
  • The line giving the error is

    Dim testClass As New TestClassLibrary1.Class1

    You suggested copying the .dll and .tlb files to the same folder as the VBA application. The VBA application wasn't in a folder as I hadn't saved it (VBA debug does not require code to be saved before running it). To follow your suggestion, I saved the Excel file in My Documents and copied the .dll and .tlb files there, but the result was the same.

    Thursday, August 17, 2006 4:24 AM
  • Try to run regasm with the /codebase option. Note that you might need to Strong Name your assembly.

     

    Friday, August 18, 2006 12:06 AM
  • Thanks very much for your help. That did it.

    To summarise, this is what I did to be able to use my class library in Excel 2003 VBA

    • On the References tab of the class library's Properties page, I checked "System.Runtime.InteropServices" in the Imported Namespaces list.
    • I added the <ComVisible(True)><ComClass()> attributes to each Public Class definition
    • On the Signing tab of the class library's Properties page, I checked Sign the Assembly and used the Choose a Strong Name Key File box to create a new key file.
    • After building the solution, I opened a Command Prompt box and:
      • Changed the current directory to the \Bin\Release folder of my project directory
      • Entered "c:\windows\microsoft.net\framework\v2.0.50727\regasm" TestClassLibrary1.dll /tlb /codebase

    This has been quite a learning experience.

    Saturday, August 19, 2006 2:09 PM
  • Hello

    Thank you for this post it is very helpfull. I carefully followed your instructions but I still get an error in Excel VBA.

    I created your class, made it COM visible, enabled the interop etc. as you mentionned, but when I tried to instanciate my class I got an automation error :

    something like "impossible to find the specified file" (traduced from French)

    My vba code is:

    Sub test()

    dim a as Class1

    set a = new Class1

    debug.print a.GetValue

    end sub

    the error occurs at the set statement... I did a regasm with /codebase, save the xls file in the same location as the dll and tlb files.

     

    Could you help me please ?

     

    Thursday, August 24, 2006 10:11 PM
  • I'm hoping Abel will reply as I'm only just learning this. But I think the error you are getting is the one I got before I strong-named the assembly. Did you definately check "sign the assembly" on the Signing tab of the project's properties page, and then use the Choose a Strong Name Key File box to create a key? When you do that, the key file should appear as an item in the Solution Explorer window.
    Friday, August 25, 2006 3:27 AM
  • Yes, that seems to be the same error.

    Make sure that you sign the assembly with a strong name, and use the /codebase option when registering. If that still doesn't work, you could try adding the assembly to the GAC using the gacutil.exe tool.

    Blackwood, depending on how you intend to use/deploy your class, you should also consider adding the assembly to the GAC (instead of using the /codebase option)

    Hope this helps,

     

    Friday, August 25, 2006 8:34 PM
  • The GAC was the answer. Thank you !

    Saturday, August 26, 2006 5:15 PM
  • Okay, I have this exact same problem, BUT, in my case, even registering with /codebase and inserting the library into the GAC didn't help.

    Here's my code:

    using System;
    using System.Runtime.InteropServices;
    namespace FDC.Powerlink.ComWrappers
    {
        [Guid("c896a295-612a-4a5e-829d-ba882486de56")]
        [ComVisible(true)]
        public class ComNode
        {
            string code;
            Guid guid;
            int id;

            bool withEvents;

            public ComNode()
            {
                this.withEvents = false;
            }

            [ComVisible(true)]
            public string Code
            {
                get
                {
                    return this.code;
                }
                set
                {
                    this.code = value;
                }
            }

            [ComVisible(true)]
            public Guid Guid
            {
                get
                {
                    return this.guid;
                }
                set
                {
                    this.guid = value;
                }
            }

            [ComVisible(true)]
            public int ID
            {
                get
                {
                    return this.id;
                }
                set
                {
                    this.id = value;
                }
            }
        }
    }

    Pretty simple class, no dependencies on anything else.

    I compiled just this class to assembly named AAA1.dll, with a strong name key.

    Then ran regasm aaa1.dll /tlb /codebase

    This completes successfully, creating tlb and registering the type. I can see the registrations in regedit, with correct codebase path, and I can view it with OleView.

    The VB code to attempt to instantiate this object follows:

    Sub main()
        Dim test As AAA1.ComNode
       
        Set test = New AAA1.ComNode
       
        test.Code = "This"
       
    End Sub

    ... no joy instantiating the COM from VB. Fails on the set statement with error 80070002, file not found.

    Then ran gacutil -i aaa1.dll to put the assembly into the cache, ran regasm aaa1.dll /u followed by regasm aaa1.dll /tlb to reregister the dll without a codebase tag.

    ... still no joy instantiating the COM from VB, fails in the same spot.

    Any ideas what I might have missed here?

    FYI, the .net code is being compiled in VS Professional 2005, with the 2.x framework. The VB host is Excel 2000 9.0.3821 SR1.

    This should work.


    Sunday, August 27, 2006 5:33 AM
  • It doesn't look like you are exposing anything to COM in your C# code. Can you paste the contents that you see in OleView?

    You will most likely need to either expose and implement your own interface, or use the ClassInterfaceType Attribute to let the CLR generate one for you.

     

    Monday, August 28, 2006 3:37 PM
  • I figured out what was wrong. Apparently there is some problem between Microsoft Office and .Net 2x that requires some kind of patch in order to allow VBA to instantiate the COM. Once I installed the patch, it worked fine.

     

    However, now, with that same code, I can't seem to set the string field on the instantiated object. The int field works fine, and the Guid fails with a type error,which is fine as well.

     

    Monday, August 28, 2006 3:43 PM
  • Blackwood,

    Thanks for your post which you wrote in detail, our issue of two weeks has been resolved.  I started another thread to which I have not received the answer I got from your thread.

    The issue was that without the Shared Add In Support for NET Framework 2.0 (KB908002) my DLL (COM) did not work.  It only worked in Framework 1.0.  After applying the patch Excel on my development machine was able to talk to the DLL.  I was thrilled until I found that this only worked on machines that had VS 2005 installed.  This was not going to work on client machines without VS 2005.

    Then I read your reply/question.  I tried it and it worked, I think the key maybe is signing and copying the .snk file to the client machine, as well as using the /codebase switch.  These are the only things I did not do previously.

    I must add though that the target machine also had the distributable KB908002 installed through another VS 2005 deployment.

    Thanks again for remarks by all who commented on this thread.

    KrisFB_APPS

    Monday, August 28, 2006 8:17 PM
  • I tried to follow all the steps above, but I kept receiving the error Run-time error '-2147024894 (80070002) when I was trying to initialize the vb.net class in vba. Then I continued searching and found the article http://support.microsoft.com/default.aspx/kb/908002. However, after following the steps in this article, initializing a vb.net class in vba through com didn't seem to work either. I was tired, shut my computer down and went to sleep. The next evening I was trying again to run the VBA program to reproduce the error to post my question about it on this forum. And then, miraculously, the program worked. Hence, I don't know what happened, but for all those who followed the steps in this forum and didn't succeeded in accessing a vb.net class in vba through com, maybe additionally following the instructions in the link I found and restarting the computer will help.

     

    Wednesday, November 14, 2007 8:48 PM
  • I had similar problems using .NET classes in Excel 2003 VBA code when the classes were used on a colleague's PC .

     

    After much investigation, I found the issue was that my colleague had .NET Framework 3.0 installed.

     

    The key was telling EXCEL to use .NET Framework 2.0 by creating a file EXCEL.exe.config and putting it in the same folder as EXCEL.EXE.

     

    The contents of the file are

     

    <?xml version="1.0" encoding="utf-8" ?>

    <configuration>

    <startup>

    <supportedRuntime version="v2.0.50727" />

    </startup>

    </configuration>

     

    The other points raised earlier are also relevant

    (1) Make your whole project ComVisible or use ComVisible for individual classes and interfaces. You must also make the classes and interfaces public.

     

    (2) Register the COM objects on your development PC, choose 'Register for COM Interop' on the project's Build properties.

     

    (3) To register the COM objects on other PC's, use regasm.exe. If you've 'strongly signed' your class library DLL, you can install it in the GAC. Otherwise, use the /codebase parameter for regasm.

     

     

     

     

    Thursday, November 22, 2007 11:15 AM
  •  

    it helped me a lot !!

     

    Can i add user control to Excel VBA ?

    I tried to add in the similar way as mentioned above, I could get it as COM but not found in user control list.

     

    Thanks

    Manjun

    Monday, November 26, 2007 6:30 AM
  • i using dal layer for connection and web service layer for the fetch records from oracle database. i want to use the web service interface in com so that i can it with excel vba. can i use this above written application to build com object?

     

     


    Vipin Bhardwaj GSPANN India
    Thursday, December 23, 2010 3:37 PM