Excel VBA - Dll reference problem

[bad_boyu]

I have a tool made in Excel with VBA, that uses a DLL that was made
with VB6. I created a installer program with NSIS, that install the
tool and in the same directory has the dll file. The dll is registered
by the installer. I have problems with the reference to this dll in my
VBA code, because sometimes(From unknown reason... only on some
computers, one computer from 20 ) the reference is "Missing"... In
order to resolve this problem I have to uncheck the "missing" reference
and add it back manually. My solutions to this problems are:
1. add the reference from code and remove it when you close the
workbook(but than you have to save the workbook before closing) I tried
to remove the reference when you load the workbook, but if the
reference is missing you get an error.Is there a solution to remove it
from the code if the reference is missing but is checked?
2. use late-binding, but I do not really know how it works. Let's say I
have 2 dll files and I want to point to the first dll, how can I
specify this, because the CreateObject method does not have a "Path"
parameter. From what I saw when you use late-binding first is searching
for a registered dll and than is searching in the current directory. Am
I right?
Are there other solutions to my problem?

Thank you!

 

[RB Smissaert]

See if this function helps you out.
It needs a reference to Microsoft Visual Basic for Applications
Extensibility.

Function AddRemoveReference(strRef As String, _
Optional strFilePath As String, _
Optional bRemove As Boolean = False, _
Optional strWorkbook As String = "") As Boolean

'returns True if adding or removing was successfull
'or if the action was adding and the ref was already there
'---------------------------------------------------------
On Error GoTo ERROROUT

Dim R As Reference

If Len(strWorkbook) = 0 Then
strWorkbook = ThisWorkbook.Name
End If

With Workbooks(strWorkbook).VBProject
For Each R In .References
If R.Name = strRef Then
If bRemove Then
.References.Remove R
AddRemoveReference = True
Exit Function
Else
If R.IsBroken Then
.References.Remove R
Exit For
Else
AddRemoveReference = True
Exit Function
End If
End If
End If
Next

If bFileExists(strFilePath) Then
.References.AddFromFile strFilePath
AddRemoveReference = True
Else
MsgBox "Couldn't add the " & strRef & " reference as the file:" & _
vbCrLf & _
strFilePath & vbCrLf & _
"is missing." & vbCrLf & vbCrLf & _
"Run the installer on this PC", vbExclamation, _
"adding " & strRef & " reference"
End If
End With

Exit Function
ERROROUT:

End Function

Function bFileExists(ByVal sFile As String) As Boolean

Dim lAttr As Long

On Error Resume Next
lAttr = GetAttr(sFile)
bFileExists = (Err.Number = 0) And ((lAttr And vbDirectory) = 0)
On Error GoTo 0

End Function


RBS

 

[bad_boyu]

Thanks for this function!

It did not solved my problem, because it does not remove the reference
when is "Missing", but it did gave me a idea of how to hack the problem
When the woorkbook is opened I add the reference to dll from file (I
have a installer for woorkbook so this add is done only once, when the
workbook is first opened. I hope it will work Of course in office
Xp or later you can not access VBProject Property if you do not check
in Trusted sources tab

Thanks again for your reply it was a great help!

 

[RB Smissaert]

I think you could just add another argument, bRemoveMissing and if that is
true then do:

If R.IsBroken Then
.References.Remove R

on any reference.

RBS

 

[bad_boyu]

Is not working because when a reference is checked and is "Missing"
than: References.Remove R does not work, you get an error! From some
unknown reason, you can remove this reference only from menu.. I did
not find any function for doing that from code.. Is there a function
to test if a reference is checked but is "Missing"? I could do that
only by catching the error and test the Err.Number.

 

[RB Smissaert]

So, your problem is with a checked, but missing reference.
I think there should be a way to handle that. Will have a look.

RBS

 

[RB Smissaert]

Just tried it out and there is no problem removing a reference that is
ticked and missing.
I think you will need the full filepath for the first argument: strRef.

RBS

 

[bad_boyu]

Strange, because I tried what you said and when is here: "For Each R In
..References" you get an error: "could not load dll" and you go to error
handle: "ERROROUT"... You can not have access to refence if it is
checked and "missing".

Thanks for your help!

 

[bad_boyu]

Do you know why on some computers I get the following error: "Err.429 -
ActiveX component can't create object"? From what I read is because
some dll file is not registered correctly...(office dll/my dll file).

Thanks