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
http://www.geeksengine.com/article/create-dll.html

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
http://www.geeksengine.com/article/register-dll.html

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
http://serverfault.com/questions/253096/how-to-register-a-dll-using-powershell-without-regasm

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