"run-time error '453' can't find dll entry point" from vb.net compiled dll referenced by vba

I am coding in vb.net through visual studio 2008. I have successfully compiled a dll file from my code but I continue t开发者_开发百科o get "Run-time error '453'" when I try to reference the dll in vba. I understand there is some sort of compiling error that occurs when using vb.net. Does anyone have any suggestions to fix/overcome this issue? I would like to avoid translating the code to another language as much as possible.

Here is a simple sample code that I have been trying to get functioning:

Example.dll:

Public Class Class1

    Function Square(ByVal x As Double, ByRef y As Double)

        y = x * x

        Return 0

    End Function

End Class

Macro in Example.xlsx:

Private Declare Function Square Lib "\Example.dll" (ByRef x As Double, ByRef y As Double)

Sub Test()

Dim x, y As Double

x = 2
y = 0

Call Square(x, y)

MsgBox (y)

End Sub

Thank you, Katlynn

---

Ok, a truckload of issues here and assumptions are incorrect.

First up, when you use declare in VBA to a .dll, you cannot use relative path names.

So you have:

Private Declare Function Square Lib 
"\Example.dll" (ByRef x As Double, ByRef y As Double)

You can’t use \Example.dll

You have to specify the FULL path name. Eg:

C:\mytestApp\Example.dll

So even if vb.net did produce a working .dll, you have to use a full path name – no relative path name is allowed.

You can place the .dll in the standard windows path names that are searched for .dll’s (so c:\windows\system32), and thus NOT have to use a full path name.

However, these days with security, adding or just tossing a .dll into a windows system folder tends to have a LOT of security issues and HUGE hassles. (windows makes this hard, and so does any virus software). So you have to use a full path name.

Next up:

Using declare in VBA is a means to call the windows API, or what we call windows x32 bit library code. This is not COM (ActiveX), but has to be raw windows x32 (or x64) native windows code.

So this is not a ActiveX, or “com” object, but raw code that you call. A standard vb.net (or any .net language) does NOT produce windows native code. The correct term is

Managed code = .net code. This code is NOT native windows code.

Un-managed code = raw windows code.

So you have to create un-managed code to use DECLARE in VBA.

So you have to use: X86 assembler C++ VB6 Or ANY development tool that can produce raw x32 (or if using access x64, then raw x64 native windows code).

In visual studio these days you "can" produce native windows code, but you have to use un-managed code, and that thus means c++. (c#, or vb.net don't produce native code or dll's).

So you can use c++, or VB6 or even a assembler to create these .dll’s.

Such .dlls do NOT require registration. And as noted you don’t create an “instance” of the object in VBA code, but call it directly.

These types of .dll’s (simple external library code calls) thus do not appear as a COM object. And if you in VBA editor go tools->references, then you not see these libraries appear. However, they can be ease to use, since you don't have to create a instance of the object I VBA code - you just call such code like your example.

Because the path name is hard coded with this approach, then it often a pain to use the DECLARE statement (since it is determined at VBA compile time, and not runtime). This would mean you have to place that .dll in the SAME location for each computer you deploy to. If you move or change the folder, then your code would fail.

To get around this issue, you can use the LOADLIBARY call in VBA. This will allow you at runtime to set (determine) the location of the .dll. So most developers will thus simple place the .dll in the SAME folder as the running application (same folder as the Access or in this case Excel file).

However, this assumes you create a standard windows .dll.

Can I create such native windows .dll’s in .net with vb.net?

It turns out you can with some external add-in’s for VS. I often use this approach to create these dll’s, since then I don’t have to build COM interface code, and it is VERY simple to use.

I then combine the above with VBA using a LOADLIBRARY call, and that works quite well. So in VS you can use a NuGet package (add in) and pick a .dll export add-in utility. There are several, but I used the RGeseke DLLimport with great success. These are free 3rd party choices here.

While the above reduces some hassles, you will have to adopt the VBA loadlibrary code to make this a practical choice (or always place the .dll in the same location). I also recommend this approach if you have some existing .dll’s, and want to interface to that code but don’t want to learn say c++. So I do use this approach to consume existing dll’s in vb.net, and thus in turn from Access. In effect this trick allows you to write interfaces to windows .dll code in a nice familiar language like vb.net.

However, if you not used LOADlibrary from VBA (and VB6) in the past, and are not up to speed, then I will suggest you choose the next option.

Creating COM (ActiveX) from vb.net

This is likely the best approach. And it is the most common approach. However, you have to trade off having to create a COM object in vb.net, and THEN register it on the target computer.

So, the only hassle is this requirement to register the .dll on the target computer.

Having to register (regasm.exe) the COM object on the target machine often requires elevated rights. And with companies so security conscious these days, then this installing requirement can be a hassle, but it still likely the best option.

Here is a working example of your code in vb.net.

Imports System.Runtime.InteropServices

 <ClassInterface(ClassInterfaceType.AutoDual)>
Public Class Class1
    Function Square(ByVal x As Double) As Double

       Dim y As Double
       y = x * x

       Return y

   End Function

End Class

Note the above including of interop, and the autoDuel setting in code. This is what allows .net to create a standard windows COM object interface that can be consumed by VBA, VB6, windows scripting, or just any platform that supports COM objects.

Settings you need for this to work:

FORCE the .net project to x86. (you using Excel x32).

So in VS go build->configuration manager. Click on platform in the grid, and choose new, choose x86 and click ok.

You should now have this:

Next up, ensure that the project is made com visible. This check box is USUALLY checked for you by default. All .net classes tend to be marked this way. But I seen it become un-checked when you mess with the previous step of changing the build to x86. So do a quick look at this setting.

Project->properties

Now on Application area, click on assembly information box.

You need to ensure this check box is set:

So above takes only a few seconds.

So check the above (they are usually are already set for you).

Next, we have to tell VS to register this COM object on our development computer. (this is that regasm.exe requirement).

So, in compile area, check this box:

Now, the above check box in VS is ONLY for your development computer and convenience. It simply does a regasm for you, and this is the magic that exposes the class as a COM object for use in VBA or ANY system that supports COM.

Note that the above check box is ONLY for your convenience on your development machine. It does NOT change, modify or do ANYTHING to the code or project.

Now to deploy this code to other machines, you have to provide a batch file or some other means to execute the regasm.exe command. As I pointed out, these does make deployment a greater hassle then the .dll approach, but you trade off other ease of coding here.

As this point, compile (build) the above.

Once you done so, you find now in the VBA editor, you can go tools->references and you find your project as a registered COM object for consuming in VBA.

You see this: (my project was call Testcom2.

And note that once you do set this reference, you MUST EXIT excel if you make changes or re-compile the .net project. (because EXCEL will have locked and have that .dll in use once you set this reference). So just remember if you going to re-compile the .net code, make sure you exit Excel.

And I changed you code a bit, since a function returns a value, and you always returning 0. You could use a call and use a Sub if you wanted, but you HAVE function defined in vb.net and did not define a sub.

So in VBA, we now have this:

Sub TEst55()

  Dim cMySquare  As New TestCom2.Class1

  Dim a    As Double
  Dim b    As Double

  a = 10

  b = cMySquare.Square(a)

  Debug.Print b


End Sub

Note how even intel-sense works as I type, so the function show up as a method in VBA.

And even the types and parms show up while you type in VBA such as:

And hitting f5 in VBA to run the code, we get this as output:

100

---

Its been a while since I've done this so I'm not sure if its necessary anymore but did you try adding all of the COM attributes?

http://support.microsoft.com/Default.aspx?kbid=817248