Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Distribute an Add-in that also requires a DLL (97 / 2000)

    I just completed an add-in which uses a DLL. To use this DLL, I need to specify this on in Tools/References (so all its calls and enumerated variables become visible and can be used).
    Next step is to distribute this add-in to the users. How do I do that?? I'm assuming I should ask them to copy both the Add-in and the DLL to their system. However, asking them to go out to the VBE, select Tools/References, browse to the DLL and add it [if that's even possible with a locked project...] seems too much...

    Anyone who could advise how to tackle this one? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Erik Jan

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distribute an Add-in that also requires a DLL (97 / 2000)

    I don't know of a way to automatically install the dll, but there is - at least - a way to control if the external link library is installed. This is quite important as Excel will display an error which may be confusing.
    The VBIDE References collection provides a method of checking that all the application's references are functioning correctly (I found this code in the book of John Green: Excel 2000 VBA Programmer's Reference, which has a complete chapter dealing with "Programming the VBE"). The code to check this should be put in the Auto_Open routine and the module that contains the Auto_Open must not contain any code that uses the external object libraries. I don't know if there is a way to really declare a reference via code, but maybe there is.

    <pre>Sub auto_open()
    Dim obRef As Object
    Dim bBroken As Boolean
    Dim stDescn As String
    'is the link broken?
    For Each obRef In ThisWorkbook.VBProject.References
    If obRef.isbroken Then
    On Error Resume Next
    stDescn = "Not Known"
    stDescn = obRef.Description
    On Error GoTo 0
    MsgBox "Missing reference to:" & vbCrLf & _
    " Name: " & stDescn & vbCrLf & _
    " Path: " & obRef.fullpath & vbCrLf & _
    "Please reinstall this file"
    bBroken = True
    End If
    Next
    'if everything present and correct, carry on with the initialising code
    If Not bBroken Then
    '.... continue
    End If
    End Sub
    </pre>


    To distribute your applications, when you have more than one file, you can use a setup programme (like Setup Specialist 2001, can't find the url right now, but it must not be difficult to find). But, that does not help you declaring the reference, but it will help you putting the files, including the dll, in the right directory.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distribute an Add-in that also requires a DLL (97 / 2000)

    Erik Jan,

    It must be possible to programmatically add a reference using either of two methods of the Reference class: AddFromFile adds a reference if you know its filename and storage path, AddFromGuid adds a reference if you know the reference's globally unique identifier (GUID). Refer to the online help for complete details.
    I haven't tried it out, but I think you have to declare a variable e.g. Ref as an object and then apply the AddFromFile method.

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distribute an Add-in that also requires a DLL (97 / 2000)

    Thanks Hans,

    I quickly tried this one (in EXCEL97) and it still creates an error on the missing DLL.
    From the help, I cannot see references to the IsBroken and Description properties -is that EX2000? However the loop does seem to work (incl. the Description property...) until I hit the missing DLL... [Runtime error 48 - Error in loading DLL]...
    Note also the if I look in Tools/References the DLL is checked but with the word MISSING in front...

    Just read your later answer on how to add the ref if it isn't loaded, that would wrap it up IF (!) the above check would work...

    Erik Jan

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distribute an Add-in that also requires a DLL (97 / 2000)

    Yes, Erik jan, I am using Excel 2000, sorry for this.

    Just want to add, that you can find some more information on the VBE on Chip Pearson's website, more specifically on this page: <A target="_blank" HREF=http://www.cpearson.com/excel/vbe.htm> http://www.cpearson.com/excel/vbe.htm </A>

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distribute an Add-in that also requires a DLL (97 / 2000)

    Try something like (can't test it out right now, but this - or something very close to it - should declare a reference to a file; I am not sure about the 'ThisProject' object, instead you can try ActiveWorkbook):

    Sub AddReference()
    refstring = "crogram filesMicrosoft officeofficeexcel8.olb"
    On Error Resume Next
    ThisProject.VBProject.References.AddFromFile refstring
    End Sub

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distribute an Add-in that also requires a DLL (97 / 2000)

    Hans,

    Yes, I haven't tried but agree that adding a file like this should work (actually, I prepared some code already to try this out)...

    However..... I must have that 'check'-part run first since ONLY in the case that the reference is illegal/not present shoudl I add it. In all other cases ('the normal situation') no action is required.

    I hope someone, could give this check-thing a try...

    Erik Jan

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distribute an Add-in that also requires a DLL (97 / 2000)

    The following code should check to see if a reference is there:

    <pre>Dim oRef As Object
    On Error Resume Next
    Set oRef = ThisWorkbook.VBProject.References("VBA")
    On Error GoTo 0
    If oRef Is Nothing Then
    MsgBox "Reference is missing"
    End If
    </pre>

    Legare Coleman

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distribute an Add-in that also requires a DLL (97 / 2000)

    Erik Jan,

    I can't figure out the 'IsBroken' full significance: it is a read only property of the Reference object. It returns true if the reference is broken (i.e. not available on the machine). But then, it can still be not checked, I think. Anyhow, I experimented a little bit with the code below, (Excel 2000) and this works fine on my PC. There are 3 routines: AddReference adds the 'MSForms' reference (I just took one out of the list that worked for my example). RemoveReference removes the 'MSForms' reference and the third routine is a slightly modified version of what Legare proposed:

    <pre>Sub AddReference()
    Dim oRef As Object
    Dim RefString As String
    RefString = "c:WindowsSystemFM20.dll"
    On Error Resume Next
    ThisWorkbook.VBProject.References.AddFromFile RefString
    Set oRef = ThisWorkbook.VBProject.References("MSForms")
    MsgBox oRef.Name
    End Sub
    </pre>


    <pre>Sub RemoveReference()
    Dim oRef As Object
    On Error Resume Next
    Set oRef = ThisWorkbook.VBProject.References("MSForms")
    ThisWorkbook.VBProject.References.Remove oRef
    End Sub
    </pre>


    <pre>Sub CheckRef()
    Dim oRef As Object
    On Error Resume Next
    Set oRef = ThisWorkbook.VBProject.References("MSForms")
    On Error GoTo 0
    If oRef Is Nothing Then
    MsgBox "Reference is missing"
    AddReference
    Else
    MsgBox "Reference is available"
    End If
    End Sub
    </pre>


    I think it must be feasible to use a setup programme to install the files in the right directory and then use code like above to add and check the references. Hope this also works for a dll.

  10. #10
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distribute an Add-in that also requires a DLL (97 / 2000)

    Hans & Legare,

    Thanks for your help. I think I've cracked it using what you suggested combined with what I created myself.

    One final remark is that I also had to make a difference between the DLL being present but not checked and the DLL not being present at all... In the first case, get a bit different situation than in the second case were a blunt error-trap is really needed.

    Again, it works like a charm.

    Thanks again!

    Erik Jan

  11. #11
    New Lounger
    Join Date
    Sep 2003
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distribute an Add-in that also requires a DLL (97 / 2000)

    Erik, could you post your code? I've been fighting with this for quite dome time now, and still have not gotten it to work reliably.

  12. #12
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distribute an Add-in that also requires a DLL (97 / 2000)

    Extracted this from my code. Call Check_Ref to run...

    HTH,

    Erik Jan

    Sub Check_Ref()
    Dim oRef As Object
    On Error Resume Next
    Set oRef = ThisWorkbook.VBProject.References("Your DLL Name")
    On Error GoTo 0
    If oRef Is Nothing Then Add_Ref
    End Sub

    Sub Add_Ref()
    On Error GoTo NoDLL
    ThisWorkbook.VBProject.References.AddFromFile ThisWorkbook.Path & "YourDLL.DLL"
    On Error GoTo 0
    MsgBox "A reference to YourDLL was added to your project." & vbCrLf & _
    "This message should occur only once.", vbExclamation, "YourProject"
    Exit Sub
    NoDLL:
    MsgBox "Could not find and/or load YOURDLL file." & vbCrLf & _
    "It should be located in the same directory as this file (" & ThisWorkbook.Path & ")." , vbCritical, "YourProject"
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •