Using a dll in vba

[Guest]

I created a dll in vb 5. I am trying to use the dll in a spreadsheet but I am
getting the runtime error 453 "Can't find DLL entry Point" (using excel 2000)

I have the following code in a module:
Declare Sub cleanup Lib "f:\final inspection\master
templates\final_inspection" ()

I have the following code in a button on the sheet:
Sub cmdcleanup_Click()
cleanup
End Sub

The sub cleanup is public in the dll and I have also created referances in
the workbook to the dll.

David

 

[Chip Pearson]

You cannot create normal Windows DLLs in Visual Basic. You can
create ActiveX DLLs. You can then call them with code like

Dim MyObject As DLLProjectName.MyObjectClass
Set MyObject = New DLLProjectName.MyObjectClass
MyObject.ProcedureName

You'll need to set a reference to the DLLProjectName. In VBA, go
to the Tools menu, choose References, and select your
DLLProjectName from the list.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

The reference to an ActiveX DLL is PROJECTNAME.CLASSNAME.

The methods are all public Subs and Functions, and the properties are
Property Let/Get and Variables declared as public.

API declarations and variables declared as Private are NOT exposed.

I am not sure what your CleanUp is going to do but you need to encapsulate
the API in a public SUB (Or Function)

On the machine that created the DLL:

Set MyDLL = CreateObject(project.class) ' substitute as necessary

Sub cmdcleanup_Click()
MyDll.cleanupCF
End Sub

where cleanupCF is the wrapper (Sub or Function) around cleanup.

 

[Guest]

It works...but now I am getting a subscript out of range error. The first
part of the cleanup sub is:

For z = 1 To 10
For x = 16 To 45
If Workbooks(3).Worksheets(2).Range("A" & x).Value = "" Then
Rows(x).Select
Selection.Delete
End If
Next x
Next z

x and z are declared as public and the workbook and worksheet numbers are
correct. None of the other buttons work either. Would it help if I used with
statements?

 

[Chip Pearson]

What line of code is giving you the error? Also, when deleting
rows, it is always best to delete from the bottom up, rather than
top down. Otherwise, you'll end up deleting rows you don't want
to or skipping rows.

For x = 16 To 45
to
For x = 45 To 16 Step -1


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

The if statement is giving me the error. Does it have anything to do with not
activating the cell before I test it?
I will change the loop also.

 

[NickHK]

David,
You sub "cleanup" is in the DLL ?
If so, then it has no idea what Workbooks(3) refers to, as it is NOT in
Excel.
At the least, you would have to pass a reference to the DLL of the Excel
instance.
e.g.
'In the DLL
Public sub CleanUp(argWhichExcel As Excel.Application)
With argWhichExcel.Workbooks(3).Worksheets(2).
....etc

But it may be better to create a class (or extra sub arguments) to
explicitly set the Excel instance, WB, WS, range etc, then call .CleanUp.
Otherwise your DLL code is limited to a single fixed task, which kind of
defeats the point of putting it in a separate DLL in the first place.

NickHK

 

[Guest]

I never done this before. Could you tell me where I could find some source
code so I can figure out how to do this?

David