A problem when trying to run a dll from Excel / VBA

[Matt Matt]

Hi,
A decade ago, I last ran dlls from the VBA environment. I'm tryin
again, and my day of reading, hacking and googling has yeilded only
failure for me.

When I use Application.Run, it appears that the executable dll is
being read as a text file input to the spreadsheets.

Here is a test case code, in the .xls file, VBA environment, in the
ThisWorkbook module:

Option Explicit
'be sure to go to Tools-->References and add in the reference to the
desired dll/exe/ocx
Private Declare Sub Main Lib "C:\Documents and Settings\TestCase
\Project1.dll" ()
Sub TestCase()
Application.Run ("C:\Documents and Settings\TestCase\Project1.dll!
Main")
End Sub


I have a Project1.vbp, with a single class module in it, Class1. It
is compiled to Project1.dll.
The code of Class1 is:
Public Sub Main()
MsgBox "Main"
End Sub


When I run TestCase, I get a message box indicating the following
lines:
This file is not in a recognizable format.
If you know the file is from another program which is incompatible
with Microsoft Office Excel, click Cancel, .......
If you suspect the file is damaged, click Help for more information
about solving the problem.
If you still want to see what text is contained in the file, click
OK.....


If I hit OK, the binary of the dll is read in to a spreadsheet.

What am I screwing up?


Other info:
Excel VBA environment
About Microsoft Visual Basic
Microsoft Visual Basic 6.3
Version 9972
VBA: Retail 6.4.9972

VB6 environment
Microsoft Visual Basic 6.0
For 32-bit Windows Development
Version 8176
VBA: Retail 6.0.8169

I'm on an XP system.
thanks,
Matt

 

[NickHK]

You seem to be confusing the 2 methods of calling dll.
If the dll is a standard windows dll (not ActiveX), then use the "Declare"
method and no reference, then call it:
Call Main 'as there are no parameters or return value

If the dll is ActiveX (which it looks like as it is created from a .vbp,
unless you are doing something fancy to force a standard dll), set a
reference, then create an instance:
Dim MyClass as Project1.Class1
set myclass=new Project1.Class1
myclass.main

In neither case do you need Application.Run.
You may want to look at Excel Addins (xla) or possibly COM addins also.

NickHK

 

[Matt Matt]

NickHK!
I would like to thank you and I would like to confirm for anyone else
that this is what works when using Excel VBA to call a VB6 dll created
via vbp:

'set a reference, then create an instance:
Dim MyClass as Project1.Class1
set myclass=new Project1.Class1
myclass.main

best regards,
Matt