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

    Compile/Run if libraries not present (97/2000)

    I've been here before but this one still gives me problems....

    I have some VBA code which uses call to specific libraries/XLA's etc. which are loaded for SOME users. Regretfully, others do not have the additional sofware lib's etc. loaded. Obviously they are getting a compile-error if they load my sheet. What can I do about that?? Mind that the compile error is direct, before any code runs (... that's why it's a compile error of course).

    I've been looking at conditional compile constants [as I think that this should be the right approach] and I could find out (in e.g. another sheet) if the libraries are loaded for a specific user but but HOW can I 'transport' that knowledge into a conditional compile constant for my main code???

    Any other suggestions and/or help are of course equally welcome!

    Hope someone can help me!

    Thanks in advance...

    Erik Jan

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compile/Run if libraries not present (97/2000)

    If your code really needs the lib's, I guess all you can do is make sure they are installed on all systems. Once they are, the referenced libs should load automatically, whether or not the user has the add-in selected or not, provided you've set a reference to it/them (Tools, references in the VBE).

    Sometimes the challenge can be to NOT use the lib's and try another route. XLQUERY e.g. can be avoided by using ADO or DAO. (But that in turn is also not available on all systems <g>)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Compile/Run if libraries not present (97/2000)

    (SEE UPDATE BELOW!)

    Yeah, you are right of course. Regretfully, it is NOT possible for me to have them installed for all uesers. It is also NOT possible to do without these lib's as it brings additional & required flexibility to those users how do have this installed... (BTW, this is not about XLQUERY but about some commercial extra software that 5-20% of the users need).

    I still hope there's a way to pre-load and run a little application that merely searches for the DLL and/or XLA and, based on that:

    * comminucates a variable that is used for conditional compiling

    and

    * load the "main" tool

    But, again, all other options are equally welcome of course.

    Thanks Jan Karel for your reply, hope you do have additional suggestions for me!

    Erik Jan

    UPDATE:
    I might have already cracked this. What I did was create a dummy XLA file which supports the procedures called in the main-program but which (obviously) return dummy replies [my MAIN deals with that]. Strangely (?) enough, this seems to work!
    What I think happens is that for people who already have the extra software installed, the XLA is already loaded on startup and/or a path to the file is known. For those who do NOT have that extra software, EXCEL tries to locate the XLA, as it isn't loaded it seems to be looking also at the location where the MAIN software-tool is loaded from. That is also where I've placed my dummy-XLA...
    Don't know if it makes sense to the experts, but first tests at some users seem to indicate this DOES work!!!

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compile/Run if libraries not present (97/2000)

    I am not very good at this, but I think it should be possible to use functionality of a dll file without referencing it beforehand (late in stead of early binding), something like using createObject("whatever").

    But it is over my head.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compile/Run if libraries not present (97/2000)

    I stole some code from John Green's Excel 2000 VBA book (page 319) and this is what I use. It runs at workbook open and checks for any missing libraries. There's nothing you can do if they're missing, but you can at least warn the user and stop your program from running. I've made small changes to John's code but here it is:

    <pre>Public Function ExtReferencesValid(Optional dummyArg As Boolean = True) As Boolean
    Dim objRef As Object, stDescn As String

    ExtReferencesValid = True
    For Each objRef In ThisWorkbook.VBProject.References
    ' Debug.Print "Checking references for: " & objRef.Name
    If objRef.IsBroken Then
    'some broken links don't have descriptions, ignore
    On Error Resume Next
    stDescn = "<Not know>"
    stDescn = objRef.description
    On Error GoTo 0

    'display message to install missing item
    ExtReferencesValid = False
    MsgBox "Missing reference to: " & vbCrLf & "Name: " & _
    stDescn & vbCrLf & "Path: " & objRef.FullPath & vbCrLf & _
    "Please re-install this file. The model will not work without it", _
    vbCritical + vbOKOnly, "Missing Required File"
    End If
    Next objRef

    End Function
    </pre>

    HTH, Deb <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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