Thursday, November 24, 2011

Calling a .NET DLL Function from VBA

Recently i had to call .NET code from my VBA Macro in order to do something not available out of the box using VBA. i searched a lot of content on the web and found this wonderful web page

The article clearly explains how to create a dll and reference it in VBA.
There were a some issues I hit upon while following the instructions mentioned in the link
1. In my code I had the following line of code
    Set f1 = New myplugin.Class1

The VBA compiler complained invalid use of New Keyword. I then modified my code to the following based on a colleague's suggestion
Set f1 = CreateObject("myplugin.Class1")
and then it worked fine.

2. On trying to create an instance of the .NET class in my VBA code
When distributing the dll by using following link, I hit upon an issue

I was getting the following error on target machines
RegAsm: error RA0000 : Unable to locate input assembly 'myplugin.dll' or one of its dependencies

I searched the web again to find a solution to this error and found the following link that helped me out

The path to paste the dll and tlb files was C:\Windows\SysWOW64 since target machines were 64-bit .

Hope this helps

No comments:

Post a Comment