Assume we have an "application" workbook and a "library" workbook
called repectively application.xls and library.xls, with
application.xls using many public variables, functions and subs from
library.xls.

I would like to be able to deploy both as a package to different
users.

Since the exact path to library.xls will differ for each user, the
reference to it needs to be recreated thorugh some initialization
routine run first thing upon opening application.xls.

I have created such a routine, called

GetMyOwnLibrary

that runs fine when launched stand-alone.

Now what I would like is to launch GetMyOwnLibrary as the first thing
in a larger initialization routine in application.xls called

ActivateMyLibrary

which subsequently needs to call on many of the public variables,
functions and subs in library.xls.

Here is the rub:

Running GetMyOwnLibrary removes any preexisting reference to
library.xls and if necessary closes library.xls, which is critical for
it to be able to then add a new reference to the correct copy of
library.xls. As I said, this all works flawlessly in GetMyOwnLibrary
when run as a stand-alone routine.

However the result is that all public variables, functions and subs
from library.xls become unusable in application.xls without first
stopping and then restarting VBA. That is why running GetMyOwnLibrary
stand-alone followed by seperately running a version of
ActivateMyLibrary that includes everything but GetMyOwnLIbrary works.

However incorporating GetMyOwnLibrary as part of ActivateMyLibrary
crashes almost instantly after concluding GetMyOwnLibrary and in very
unpredictable and unpretty ways.

Is there anyway to be able to properly incorporate something like
GetMyOwnLibrary into ActivateMyLibrary? What seems to be needed is
some way to dynamically bind application.xls to all the public
variables, subs and functions in library.xls right after creating the
new reference to it. Does VBA give you that much control?

Sample code for GetMyOnwLibrary and ActivateMyLibrary
in application.xls (application.xls expects library.xls in parent
folder):

Option Explicit
Sub GetMyOwnLibrary()
Dim ThisReference As Reference, TheseReferences As References
Dim LocalPathName As String
LocalPathName = ThisWorkbook.Path
Set TheseReferences = ThisWorkbook.VBProject.References
For Each ThisReference In TheseReferences
If ThisReference.Name = "Library" Then
TheseReferences.Remove ThisReference
Exit For
End If
Next
Workbooks("Library.xls").Close
Workbooks.Open LocalPathName & "\..\Library.xls"
TheseReferences.AddFromFile LocalPathName & "\..\Library.xls"
End Sub

Sub ActivateMyLibrary()
Dim pn As Integer, lastpn As Integer
Dim RootDir As String, RootinLibName As Integer
Dim ThisReference As Reference, TheseReferences As References
Dim LocalPathName As String, LibPath As String
LocalPathName = ThisWorkbook.Path
LibPath = Workbooks("Library.xls").Path
lastpn = 0
pn = InStr(1, Mid(LocalPathName, lastpn + 1), "\", vbTextCompare)
While pn 0
lastpn = lastpn + pn
pn = InStr(1, Mid(LocalPathName, lastpn + 1), "\", vbTextCompare)
Wend
RootDir = Mid(LocalPathName, 4, lastpn - 4)
RootinLibName = InStr(LibPath, RootDir)
If RootinLibName = 0 Or _
Len(LibPath) - RootinLibName + 1 < Len(RootDir) Then
MsgBox "Alert - application.xls is not in a subfolder of the " & _
"Library's folder" & Chr(13) & Chr(10) & _
" - Nothing will run !!!" & Chr(13) & Chr(10) & _
" - To fix this state of affairs, correct the
Application's reference to the Library" & Chr(13) & Chr(10) & _
" by running ""GetMyOwnLibrary"" from the " & _
"Application."
Application.StatusBar = "application.xls is not in a " & _
"subfolder of the " & _
"Library's folder - " & _
"Nothing will run !!! - Please " & _
"reference correct the " & _
"Application's to the Library."
Exit Sub
'
' The method below would be nicer if it could work without crashing _
in ActivateMyLibrary
'
'
MsgBox "Alert - application.xls is not in a subfolder of " & _
"the Library's folder" & Chr(13) & Chr(10) & _
" - I will try to fix this state of affairs"
Application.StatusBar = "application.xls is not in a " & _
"subfolder of the " & _
"Library's folder - I will try to " & _
"fix this state of affairs"
GetMyOwnLibrary
End If

Application.StatusBar = "I am Activating the Library"
'
' The following are all Library public variables and subs which need _
to be recreated after closing a version of the Library
'
' InitializetheKeyBoard
' ActivatetheCommandstoSelecttheVisibleSheet
' ActivatetheCommandstoEdittheVisibleSheet

' PublicVariablesAreOK = False
' InitializePublicVariables "ActivatetMyLibrary": If ExitFlag Then
Exit Sub

' ShowMessage "I have Activated the Library"
End Sub

|\|.