Reference to dll object in VBA

[JohnS-BelmontNC]

I've written a VB6 app that is a dll (FinV4.dll) for an Excel application.
When I re-make the dll and then open Excel, the reference always comes back
as "MISSING: DLL for FinV4".

I can uncheck the reference, exit references dialog, re-enter References,
find the new reference, check it, exit References and all runs well.

Since this application will be used predominantly by others, what I would
like to do is delete the missing reference and connect to the proper
reference programatically. It will facilitate future updates to both the
workbook and the dll when sent to others.

What I've done so far is:

Public Sub ConnectToFinPlannerDLL()

with ThisWorkbook.VBProject.References
.Remove "C:\Program Files\FinV4.dll"
.AddFromFile "C:\Program Files\FinV4.dll"
end with

End Sub

The remove statement doesn't remove the reference although if I manually
remove the Missing reference and just run the .AddFromFile line it connects
to the new dll. I've tried different forms of the Remove statement but have
been unable to make it work.

I've also removed early binding to prevent errors on workbook startup for
the global variables used to connect to the classes in the dll.

Any help will be appreciated.

End Sub

 

[Peter T]

Best approach is avoid the problem altogether.

With Compatibility set to none or Project, compile a dll in say
c:\myProject\Compat\
(could do that with your existing project)

For all future dlls compile with Compatibility set to Binary and into a
different folder, eg
c:\myproject\dll\

This way, providing you do not "break" compatibility you should be able to
distribute updated dll's to your users without the need to change any
references.
You'll get a warning if you are about to break compatibility when you
compile, eg you remove something public or change it's 'type' (even an
argument in a routine in a public interface). However no problem to add new
public stuff or do whatever to anything that's not publicly exposed.

For the Compatibility options look in project properties / Component. With
the Binary option, browse to the dll in the 'compat' folder.

For now, probably best distribute an updated xls (with the dll reference
included) together with the new binary compatibility dll. In future just the
updated dll should be OK.
If you need to "break" compatibility you'll need to think about the
reference again, try and avoid.

Regards,
Peter T

 

[JohnS-BelmontNC]

Peter, Thanks.

Do you have a reference article I might read to better understand what
you've describe below.

 

[Peter T]

http://www.vbsight.com/BinaryComp.htm

Regards,
Peter T

 

[JohnS-BelmontNC]

Thanks. I'll read the article and implement the changes. This way the
workbooks will always be backward compatible for this major version of the
dll.