Register VB.NET DLL from Excel VBA

Question

I designed in VB.NET a class library to be used in Excel (interop, COM interface). DLL is working fine. Now what we want to do is to create a code that will be executed whenever spreadsheet opens: check if a new release of our custom DLL is ready, if necessary unregister the old one, and register new version of the DLL.
I can detect necessity to re-register, but registering from the VBA routine is not working (or at least is not stable).
To register assmbly one can run command line like this:    regasm c:\mydir\mysubdir\MYDLL.DLL /codebase

Here is the question: why running in VBA module this command line using function Shell:
Call Shell("regasm c:\mydir\mysubdir\MYDLL.DLL /codebase", vbNormalFocus)
is not producing stable results, while running this command outside Excel in Command Window or doubleclick the batch file containing the same command will result the immediate proper registration. How do I know about success or failure? After running regasm command from VBA code a CreateObject function will return an error, after running it outside Excell no errors in creating object from our assembly.

What is that? Security issues, some delays in buffers, or something else?

Two days later:
Maybe the whole idea of registering DLL from VBA is wrong or question is not clear? I really need to know how to register a new DLL from VBA and immediately use it in CreateObject calls.

Common, guys. 200 hits and no comments whatsoever. Earn your medals. Are you there, Microsoft wizards?
Seriously, solution might benefit lots of developers.

Answer 1

Below is one potential issue if you're attempting to do this on the fly:

FIX: "Access to the Registry Key Denied" Error Message When You Register .NET Assembly for COM Interop

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 2

Unlikely... I have administrative privileges.  Also, if I use Shell() function from VBA, output of the command redirected to the file shows message Types Registeredsuccessfully.

Answer 3

So it sounds like Regasm is running successfully. What is the CreateObject error (text)?

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 4

The very first attempt to call CreateObject("MyLib.MyClass") generates error 429 :"ActiveX component can't create object". This error is reproducable.
Reminder: If I run the same batch file (contains only one command line) by doubleclick in the directory list or running from command line - code immediately starts to work.

Answer 5

Is the CreateObject function called immediately after the Shell statement. If this is the case then I would suspect that the Shell operation (Regasm) has not completed when CreateObject executes. The important thing to remember about the Shell statement is that it does not wait for the launched process to finish/exit; subsequent lines of code will continue to execute.

If this appears to be the problem, the code at the below link should help you:

http://www.vb-helper.com/vba_shell_and_wait.html

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 6

I ran the Shell function with regasm operation only when worksheet opens in procedure Workbook_Open() (not every time it opens,  only if new DLL was generated). A call to CreateObject () do not included into this procedure.

User can later click on the button and run the procedure where classes are being created using function CreateObject(). So, functions Shell and CreateObject are located in different procedures and run not one after another.

Answer 7

You indicated in your first post that running it from VBA/Shell is not producing stable results. Can you elaborate on that? Also, have you verified that the Regasm operations are running successfully when executing via Shell? The results should be displayed in the command line window.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 8

1. The confusing part of this is that sometimes running Shell() function from VBA properly registering assembly, but at some point this stops working. And objects can not be created. This applies to developer's computer and to the regular workstation. I can't figure out why. The code is being developed now, so every time situation is different. This means that in general current approach is working, but some small part is missing.

2. Running regasm command via Shell() function just flashes command window (originally I ran it with the vbHide parameter). So, to get command output I used redirection:
            regasm c:\mydir\mysubdir\MYDLL.DLL /codebase > c:\temp\a.txt

Here is the actual output file contents:
Microsoft (R) .NET Framework Assembly Registration Utility 2.0.50727.42
Copyright (C) Microsoft Corporation 1998-2004.  All rights reserved.

Types registered successfully

Answer 9

Hi Arkady_ny,

If you can reproduce this scenario, please first register that .NET component. Then using any COM client(start one new process) to call that .NET component via COM Interop to see whether that .NET component is registered correctly in this scenario.

---

Best regards,
Riquel
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 10

Do you unregister the old version before installing and registering the new version?

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 11

Yes, every time I run the following code to unregister and register (a new build of DLL have different location):
    If old_dll <> "" Then
        comm_line = regasm_LOCATION & " " & old_dll & " /u  > C:\temp\unregDLL.txt"
        Call Shell(comm_line, vbHide)
    End If

  comm_line = regasm_LOCATION & " " & new_dll & " /codebase > C:\temp\regDLL.txt"
  Call Shell(comm_line, vbNormalFocus)

And, of course, on the developers workstation (only) I am cleaning registry manually using regedit.  Just to keep it clean and avoid confusion.

Thank you for your time. Please, keep trying. Can you suggest any other approach to solve this problem?.

Answer 12

Riquel,
If I first register .NET DLL component using REGASM from the command window and run COM client after this, I do not have any problems or errors. Component will be registered properly.
But the whole purpose of the approach I am trying to pursue is simple: how to make Excel detect a new build of .NET and re-register it on the fly (making it immediately available in VBA).  The detection part is simple and can be resolved in many ways. But re-registering in VBA suddenly is not working despite of initial success.

This way of handling registration can be very usefull in a small network of the very busy office (busy means 24/7 trading floor) where it is very difficult to force users to close or re-start applications, reboot or run any batches. Did I mention not enough of systems and network administration support? Now let's assume that one of the big guys wants some model changes immediately available in order to make some important decision. Here comes self-registering really handy.
Many years ago I implemented this for VB6 (using also late binding and  regsrv32 with built-in functions). This time .NET VB is very simple, but Excel 2003 with all Office inheritance is very stubborn.
Sorry for long explanation, but I have to explain my perseverance.

Answer 13

Hi Arkady_ny,

Based on your post, my understanding of this question is that you need to detect and use new Add-In in office application. You unregistered the old add-in and registered the new add-in. Then want to use the new add-in immediately without exiting the Office application.

Please check whether the old add-in is unregistered correctly. I am guessing that if the old add-in is used in Office process. Whether this will raise some unexpected result.

Second you also can have a look at WCF. We can call WCF service in VBA code. I would recommend that you use WCF to supply the services for Excel application calling. Host the service in IIS and modify the service for new requirement.
http://msdn.microsoft.com/en-us/library/ms733928.aspx

Hope this helps!

---

Best regards,
Riquel
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 14

Can anyone help me how I can call my simple WCF from Excel 2003?  My example-WCF look like:

I will use basicHttpBinding...

Look at the code down (my config shows last down),

Namespace Microsoft.ServiceModel.Samples

    <ServiceContract(Namespace:="http://Microsoft.ServiceModel.Samples")> _

Public Interface IService1

        <OperationContract()> _

        Function GetData(ByVal value As Integer) As String

        <OperationContract()> _

        Function GetDataUsingDataContract(ByVal composite As CompositeType) As CompositeType

        ' TODO: Add your service operations here

    End Interface

    ' Use a data contract as illustrated in the sample below to add composite types to service operations.

    <DataContract()> _

    Public Class CompositeType

        Private boolValueField As Boolean

        Private stringValueField As String

        <DataMember()> _

        Public Property BoolValue() As Boolean

            Get

                Return Me.boolValueField

            End Get

            Set(ByVal value As Boolean)

                Me.boolValueField = value

            End Set

        End Property

        <DataMember()> _

        Public Property StringValue() As String

            Get

                Return Me.stringValueField

            End Get

            Set(ByVal value As String)

                Me.stringValueField = value

            End Set

        End Property

    End Class

End Namespace

CONFIG:

  <system.serviceModel>

    <services>

      <service behaviorConfiguration="Microsoft.ServiceModel.Samples.Service1Behavior"

        name="Microsoft.ServiceModel.Samples.Service1">

        <endpoint address="" binding="basicHttpBinding" bindingConfiguration="Binding1"

          contract="Microsoft.ServiceModel.Samples.IService1" />

        <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange"/>

      </service>

    </services>

    <bindings>

      <!-- 

            Following is the expanded configuration section for a BasicHttpBinding.

            Each property is configured with the default value.

            See the TransportSecurity, and MessageSecurity samples in the

            Basic directory to learn how to configure these features.

            -->

      <basicHttpBinding>

        <binding name="Binding1"

                 hostNameComparisonMode="StrongWildcard"

                 receiveTimeout="00:10:00"

                 sendTimeout="00:10:00"

                 openTimeout="00:10:00"

                 closeTimeout="00:10:00"

                 maxReceivedMessageSize="65536"

                 maxBufferSize="65536"

                 maxBufferPoolSize="524288"

                 transferMode="Buffered"

                 messageEncoding="Text"

                 textEncoding="utf-8"

                 bypassProxyOnLocal="false"

                 useDefaultWebProxy="true" >

          <security mode="None" />

        </binding>

      </basicHttpBinding>

    </bindings>

    <!--For debugging purposes set the includeExceptionDetailInFaults attribute to true-->

    <behaviors>

      <serviceBehaviors>

        <behavior name="Microsoft.ServiceModel.Samples.Service1Behavior">

          <serviceMetadata httpGetEnabled="true" />

          <serviceDebug includeExceptionDetailInFaults="true" />

        </behavior>

      </serviceBehaviors>

    </behaviors>

  </system.serviceModel>

---

Alle

Answer 15

Hi Alle,

You can have  a look at Integrating with COM Applications. I have used VB6 to call the WCF service. Because I didn't write VBA code, so I didn't write VBA code. Windows Communication Foundation (WCF) services can be integrated directly into your existing code by using the WCF service moniker. The service moniker can be used from a wide range of COM-based development environments, such as Office VBA, Visual Basic 6.0, or Visual C++ 6.0.
http://msdn.microsoft.com/en-us/library/ms733928.aspx

---

Best regards,
Riquel
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 16

Hi,

 

Please try the VBA area:>>

http://social.msdn.microsoft.com/Forums/en/isvvba/threads

This EXCEL area on answers :>>

http://answers.microsoft.com/en-us/office/forum/excel

 

and the EXCEL area on social.technet :>>

http://social.technet.microsoft.com/Forums/en/excel/threads

 

Finally, here is an external ( independent forum ) area on VBA too:>>

http://www.programmersheaven.com/mb/vba/Board.aspx

---

---

Regards,

John

---

Click this link to see how to insert a picture into a forum post.

Installing VB6 on Windows 7

---