0

Assume I have a .Net library with a class(TestClass) with a function 'Add'. I am accessing this in Excel by writing a VBA addin. I want to know the difference between the following two ways of accessing FUN1.

  1. Add the tlb as reference and use New as in

    Dim obj = New MyLibrary.TestClass

    returnval = obj.Add(5,5)

  2. Regasm that .Net dll and use Run method as in

    returnVal = Run("Add", 5, 5);

I have a lot of code where I see this similar way of accessing a function and am really confused how this works.

How does 2 work, will it work or what is neccesary to make 2 work like this.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
Everything Matters
  • 2,672
  • 4
  • 25
  • 42
  • It is pretty unclear how "Fun1" ends up calling a method named "Add". Regasm.exe is required in both cases so you need to remove it from case 2. VBA's Run() is just a helper function to run code late bound. It is very slow. – Hans Passant Aug 10 '11 at 11:50
  • Sorry, the RUN executes the same method. I wanted to understand, if calling the method using RUN would work in first place. I guess it does work and also wanted to understand the diff. I guess your comment answers that. – Everything Matters Aug 12 '11 at 08:39

1 Answers1

0

You need to replace the = with the As keyword for the Dim statement in your code snippet. This works (assuming that the class is registered properly with COM):

Sub AccessExternalUdf()
    Dim obj As New MyNamespace.MyClass
    result1 = obj.MyMethod    
    result2 = Run("MyMethod")
End Sub

The difference (as stated above by @Hans) is early versus late (Run) binding.

Frank
  • 3,029
  • 5
  • 34
  • 43
  • Thank you. But I actually wanted to know : For both of this to work, should the tlb be added as a reference in the VBA editior under 'Tools - References' or only regasm will do, especially for result2 ? – Everything Matters Aug 12 '11 at 08:41
  • Only running regasm.exe is required. See http://stackoverflow.com/questions/5397607/how-do-i-create-a-real-time-excel-automation-add-in-in-c-using-rtdserver/5697823#5697823 for info on using an installer class and RegistrationServices to perform the registration instead of running regasm.exe manually. – Frank Aug 12 '11 at 14:35