Setting VBA reference programmatically

[Nicholas Dreyer]

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?

|\|.

 

[Nicholas Dreyer]

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

|\|.

 

[Peter T]

I haven't looked at your code but just a few general comments.

When distributing & running code from a project that includes a missing ref
problems are likely to occur. You might be able to work around with an
approach along the following lines:

In the first inserted module & thisworkbook module ensure all VBA functions
& constants are fully qualified, I mean everything, eg

s = VBA.Strings.Left$("abc",2)

(find all the relevant libraries in Object browser)

Ideally do similar in all other modules though perhaps not necessary if you
don't compile (also means never run any code from those modules before
saving & distributing).

In the xla load check if some flag exists that you've previously set a ref.
If not ensure your library.xls is found & loaded, set the ref and re-save
the xla
Also set some flag to save time on future loads, eg a cell in the addin
(save), registry, text/ini file.
If the flag exists perform some simple test to check the ref is still valid
(perhaps simply check if the xls still exists in expected folder), if not
start again.
(do all above before running any code in the non compiled models)

Regards,
Peter T

 

[Nicholas Dreyer]

Thanks for the tips, Peter.

Some of your solutions are things I had thought of but was hoping to
avoid. Fully qualified references for commonly used things is quite
unpractical for my purposes.

I think I am going to just live with essentially what I have, which
just means a very brief "manual" first-time process after installation
that requires running GetMyOwnLibrary as I posted (only with a check
to see if library.xls is open before trying to close it, of course)
followed by a save of application.xls with the reeatablished reference
to the correct library.xls

Your insights are always greatly valued, so thanks once again, Nick

I haven't looked at your code but just a few general comments.

When distributing & running code from a project that includes a missing ref
problems are likely to occur. You might be able to work around with an
approach along the following lines:

In the first inserted module & thisworkbook module ensure all VBA functions
& constants are fully qualified, I mean everything, eg

s = VBA.Strings.Left$("abc",2)

(find all the relevant libraries in Object browser)

Ideally do similar in all other modules though perhaps not necessary if you
don't compile (also means never run any code from those modules before
saving & distributing).

In the xla load check if some flag exists that you've previously set a ref.
If not ensure your library.xls is found & loaded, set the ref and re-save
the xla
Also set some flag to save time on future loads, eg a cell in the addin
(save), registry, text/ini file.
If the flag exists perform some simple test to check the ref is still valid
(perhaps simply check if the xls still exists in expected folder), if not
start again.
(do all above before running any code in the non compiled models)

Regards,
Peter T

|\|.

================================================================================

Hey you know something people? They won't go for no more
I'm not black, but there's a whole lotsa times Great Midwestern hardware store
I wish I could say I'm not white. Philosophy that turns away
(1965, Frank Zappa in Trouble Every Day From those who aren't afraid to say
-> Freak Out) What's on their mind -- The left behinds
Of The Great Society.
(1965, Frank Zappa in Hungry Freaks, Daddy
-> Freak Out)