Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting VBA reference programmatically
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? |\|. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting VBA reference programmatically (with code)
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 |\|. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting VBA reference programmatically (with code)
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" wrote in message ... 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 |\|. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting VBA reference programmatically (with code)
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 On Thu, 19 Oct 2006 11:01:37 +0100, "Peter T" <peter_t@discussions wrote: 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" wrote in message ... 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 |\|. |\|. ================================================== ============================== 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting Data Validation Programmatically | Excel Programming | |||
Setting Solver Reference-programmatically | Excel Programming | |||
Setting HotKey Programmatically | Excel Programming | |||
WhatsThisHelp in VBA - setting the help file path programmatically | Excel Programming | |||
Setting Printer Options Programmatically | Excel Programming |