Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Late binding to .XLA library??
In my spreadsheet I have code that references functions and classes in an
..XLA file. When a user who does not have the .XLA file available opens the spreadsheet they see a compile error when the Workbook Open event fires (even though the Open event itself does not call or even directly reference any XLA resources). This actually makes perfect sense to me ... but that does not mean I have to like it. Anyone know of a reliable way to prevent the compile error in this situation? Something comparable to late binding for functions in an XLA library would be nice. Then I could check if the library exists before I decide whether to make the function calls. I have discovered that if arrange my code /just right/, I can avoid the error by convincing VBA not to compile the modules that reference the non-existent library -- however that is not reliable enough to release to the users. I am tempted to port my .XLA into a Visual Basic and compile it into an ActiveX DLL, but I figured I would check with the group to see if anyone had a good idea on this. Thanks! Lee |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Late binding to .XLA library??
Could you not check for tha addin being instgalled in the Workbook_Open
(wkith the other code arrangements), and exit nicely if not? Dim sResult On Error Resume Next sResult = Application.AddIns("CFPLus").Installed If sResult Then MsgBox "Addin exists" Else MsgBox "Addin needs tro be installed" End If -- HTH RP (remove nothere from the email address if mailing direct) "Lee S" wrote in message ... In my spreadsheet I have code that references functions and classes in an .XLA file. When a user who does not have the .XLA file available opens the spreadsheet they see a compile error when the Workbook Open event fires (even though the Open event itself does not call or even directly reference any XLA resources). This actually makes perfect sense to me ... but that does not mean I have to like it. Anyone know of a reliable way to prevent the compile error in this situation? Something comparable to late binding for functions in an XLA library would be nice. Then I could check if the library exists before I decide whether to make the function calls. I have discovered that if arrange my code /just right/, I can avoid the error by convincing VBA not to compile the modules that reference the non-existent library -- however that is not reliable enough to release to the users. I am tempted to port my .XLA into a Visual Basic and compile it into an ActiveX DLL, but I figured I would check with the group to see if anyone had a good idea on this. Thanks! Lee |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Late binding to .XLA library??
How about:
ThisWorkbook.VBProject.References.AddFromFile "c:\book1.xla" -- Jim Rech Excel MVP "Lee S" wrote in message ... | In my spreadsheet I have code that references functions and classes in an | .XLA file. When a user who does not have the .XLA file available opens the | spreadsheet they see a compile error when the Workbook Open event fires | (even though the Open event itself does not call or even directly reference | any XLA resources). | | This actually makes perfect sense to me ... but that does not mean I have | to like it. | | Anyone know of a reliable way to prevent the compile error in this | situation? | | Something comparable to late binding for functions in an XLA library would | be nice. Then I could check if the library exists before I decide whether | to make the function calls. | | I have discovered that if arrange my code /just right/, I can avoid the | error by convincing VBA not to compile the modules that reference the | non-existent library -- however that is not reliable enough to release to | the users. | | I am tempted to port my .XLA into a Visual Basic and compile it into an | ActiveX DLL, but I figured I would check with the group to see if anyone had | a good idea on this. | | Thanks! | Lee | | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Late binding to .XLA library??
Bob / Jim,
Thanks for the ideas. In my particular situation, I don't mind if the add-in does not exist -- I will simply disable any features that reference the add-in. However, the problem is that if VBA attempts to compile any code that references the add-in BEFORE running the code that checks if the add-in exists, the application will still halt with a compile error. EXAMPLE 1: In this example, only the local class clsLocalClass makes any reference to the add-in. However if the add-in does not exist the Workbook_Open event will never run because VBA will first attempt to compile BOTH the ThisWorkbook and clsLocalClass. If the add-in is not loaded then the clsLocalClass compile will fail, and Workbook_Open will never run. In ThisWorkbook class: ================================================== ========== Dim objLocal As clsLocalClass Sub Workbook_Open() Dim sResult On Error Resume Next sResult = Application.AddIns("MyAddIn").Installed If sResult Then '' Add-in exists -- make use of it Set objLocal = New clsLocalClass Else '' Add-in does not exist -- don't use features from Addin End If End Sub ------------------------------------------------------------ In clsLocalClass (inside current project) ------------------------------------------------------------ Dim objAddin As AddIn.clsSomething Private Sub Class_Initialize() Set objAddin = CreateSomething() End Sub ================================================== ========== EXAMPLE 2: The code below WILL ACTUALLY WORK, but it seems risky. I made two changes: changed the objLocal definition to Object instead on clsLocalClass, and I moved the New clsLocalClass line out of Workbook_Open. In this particular scenario, VBA will not attempt to compile clsLocalClass, so the Workbook_Open event will run. It SEEMS RISKY trying to second-guess when/if VBA will decide to compile clsLocalClass. My real-world scenario is obviously more complex, and if VBA were to attempt to compile clsLocalClass at any time for any reason, my application would halt. In ThisWorkbook: ================================================== ========== Dim objLocal As clsLocalClass Sub Workbook_Open() Dim sResult On Error Resume Next sResult = Application.AddIns("MyAddIn").Installed If sResult Then '' Add-in exists -- make use of it IntializeApp Else '' Add-in does not exist -- don't use features from Addin End If End Sub Private Sub IntializeApp() Set objLocal = New clsLocalClass End Sub ------------------------------------------------------------ In clsLocalClass (inside current project) ------------------------------------------------------------ Dim objAddin As AddIn.clsSomething Private Sub Class_Initialize() Set objAddin = CreateSomething() End Sub ================================================== ========== "Jim Rech" wrote in message ... How about: ThisWorkbook.VBProject.References.AddFromFile "c:\book1.xla" -- Jim Rech Excel MVP "Lee S" wrote in message ... | In my spreadsheet I have code that references functions and classes in an | .XLA file. When a user who does not have the .XLA file available opens the | spreadsheet they see a compile error when the Workbook Open event fires | (even though the Open event itself does not call or even directly reference | any XLA resources). | | This actually makes perfect sense to me ... but that does not mean I have | to like it. | | Anyone know of a reliable way to prevent the compile error in this | situation? | | Something comparable to late binding for functions in an XLA library would | be nice. Then I could check if the library exists before I decide whether | to make the function calls. | | I have discovered that if arrange my code /just right/, I can avoid the | error by convincing VBA not to compile the modules that reference the | non-existent library -- however that is not reliable enough to release to | the users. | | I am tempted to port my .XLA into a Visual Basic and compile it into an | ActiveX DLL, but I figured I would check with the group to see if anyone had | a good idea on this. | | Thanks! | Lee | | |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Late binding to .XLA library??
However, the problem is that if VBA attempts to compile any code that
references the add-in BEFORE running the code that checks if the add-in exists, the application will still halt with a compile error Sorry, Lee, if I didn't spell out exactly what I was driving at... - Do not set a reference in the VB project. Result- No compile error. - At run time, check to see if Add-in exists. - If yes, run code similar to my first post to create the reference. - If no, disable your functions, etc. -- Jim Rech Excel MVP "Lee S" wrote in message ... | Bob / Jim, | | Thanks for the ideas. | | In my particular situation, I don't mind if the add-in does not exist -- I | will simply disable any features that reference the add-in. | | However, the problem is that if VBA attempts to compile any code that | references the add-in BEFORE running the code that checks if the add-in | exists, the application will still halt with a compile error. | | EXAMPLE 1: | In this example, only the local class clsLocalClass makes any reference to | the add-in. However if the add-in does not exist the Workbook_Open event | will never run because VBA will first attempt to compile BOTH the | ThisWorkbook and clsLocalClass. If the add-in is not loaded then the | clsLocalClass compile will fail, and Workbook_Open will never run. | | In ThisWorkbook class: | ================================================== ========== | | Dim objLocal As clsLocalClass | | Sub Workbook_Open() | Dim sResult | | On Error Resume Next | sResult = Application.AddIns("MyAddIn").Installed | If sResult Then | '' Add-in exists -- make use of it | Set objLocal = New clsLocalClass | Else | '' Add-in does not exist -- don't use features from Addin | End If | End Sub | | ------------------------------------------------------------ | In clsLocalClass (inside current project) | ------------------------------------------------------------ | Dim objAddin As AddIn.clsSomething | | Private Sub Class_Initialize() | Set objAddin = CreateSomething() | End Sub | | ================================================== ========== | | EXAMPLE 2: | The code below WILL ACTUALLY WORK, but it seems risky. I made two changes: | changed the objLocal definition to Object instead on clsLocalClass, and I | moved the New clsLocalClass line out of Workbook_Open. In this particular | scenario, VBA will not attempt to compile clsLocalClass, so the | Workbook_Open event will run. | | It SEEMS RISKY trying to second-guess when/if VBA will decide to compile | clsLocalClass. My real-world scenario is obviously more complex, and if VBA | were to attempt to compile clsLocalClass at any time for any reason, my | application would halt. | | In ThisWorkbook: | ================================================== ========== | Dim objLocal As clsLocalClass | | Sub Workbook_Open() | Dim sResult | | On Error Resume Next | sResult = Application.AddIns("MyAddIn").Installed | If sResult Then | '' Add-in exists -- make use of it | IntializeApp | Else | '' Add-in does not exist -- don't use features from Addin | End If | End Sub | | Private Sub IntializeApp() | Set objLocal = New clsLocalClass | End Sub | | ------------------------------------------------------------ | In clsLocalClass (inside current project) | ------------------------------------------------------------ | Dim objAddin As AddIn.clsSomething | | Private Sub Class_Initialize() | Set objAddin = CreateSomething() | End Sub | | ================================================== ========== | | | | "Jim Rech" wrote in message | ... | How about: | | ThisWorkbook.VBProject.References.AddFromFile "c:\book1.xla" | | -- | Jim Rech | Excel MVP | "Lee S" wrote in message | ... | | In my spreadsheet I have code that references functions and classes in | an | | .XLA file. When a user who does not have the .XLA file available opens | the | | spreadsheet they see a compile error when the Workbook Open event fires | | (even though the Open event itself does not call or even directly | reference | | any XLA resources). | | | | This actually makes perfect sense to me ... but that does not mean I | have | | to like it. | | | | Anyone know of a reliable way to prevent the compile error in this | | situation? | | | | Something comparable to late binding for functions in an XLA library | would | | be nice. Then I could check if the library exists before I decide | whether | | to make the function calls. | | | | I have discovered that if arrange my code /just right/, I can avoid the | | error by convincing VBA not to compile the modules that reference the | | non-existent library -- however that is not reliable enough to release | to | | the users. | | | | I am tempted to port my .XLA into a Visual Basic and compile it into an | | ActiveX DLL, but I figured I would check with the group to see if anyone | had | | a good idea on this. | | | | Thanks! | | Lee | | | | | | | | |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Late binding to .XLA library??
Lee S wrote: In my spreadsheet I have code that references functions and classes in an .XLA file. When a user who does not have the .XLA file available opens the spreadsheet they see a compile error when the Workbook Open event fires Take a look at this KB article (written by our very own Tushar Mehta MVP): http://support.microsoft.com/default...b;en-us;555159 Extract: Sub UseExportedClass_LateBinding() Dim anEmployee As Object Set anEmployee = Application.Run("'g:\temp\class provider.xls'!new_clsEmployee") anEmployee.Name = "Tushar Mehta" MsgBox anEmployee.Name End Sub Jamie. -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Late binding to .XLA library??
Jim,
(First copy went out as direct email reply -- sorry. I have this bad habit of pressing Ctrl+R when I should do Ctrl+G) You wrote: - Do not set a reference in the VB project. Result- No compile error. If I don't set a reference, then I DO get a compile error. In my example #1, which I left intact below, if there is no reference to "MyAddIn" then I get a compile error in the clsLocalClass module. This compile error hits BEFORE my Workbook_Open() event starts to run, and before any code in clsLocalClass is ever called. The compile error hits even if I declare objLocal as: Dim objLocal As Object That said, I have sidestepped the whole issue. For my specific needs, I found that by trapping Application events in my .XLA, I was able to move all code out of the data spreadsheet and keep it all in the .XLA. Now, if the user installs the add-in they get a new toolbar with my tools, otherwise they simply don't get the toolbar. However, I've run into some issues maintaining state information when multiple workbooks are referencing my add-in...I might be opening up a new thread on that one. Lee "Jim Rech" wrote in message ... However, the problem is that if VBA attempts to compile any code that references the add-in BEFORE running the code that checks if the add-in exists, the application will still halt with a compile error Sorry, Lee, if I didn't spell out exactly what I was driving at... - Do not set a reference in the VB project. Result- No compile error. - At run time, check to see if Add-in exists. - If yes, run code similar to my first post to create the reference. - If no, disable your functions, etc. -- Jim Rech Excel MVP "Lee S" wrote in message ... | Bob / Jim, | | Thanks for the ideas. | | In my particular situation, I don't mind if the add-in does not exist -- I | will simply disable any features that reference the add-in. | | However, the problem is that if VBA attempts to compile any code that | references the add-in BEFORE running the code that checks if the add-in | exists, the application will still halt with a compile error. | | EXAMPLE 1: | In this example, only the local class clsLocalClass makes any reference to | the add-in. However if the add-in does not exist the Workbook_Open event | will never run because VBA will first attempt to compile BOTH the | ThisWorkbook and clsLocalClass. If the add-in is not loaded then the | clsLocalClass compile will fail, and Workbook_Open will never run. | | In ThisWorkbook class: | ================================================== ========== | | Dim objLocal As clsLocalClass | | Sub Workbook_Open() | Dim sResult | | On Error Resume Next | sResult = Application.AddIns("MyAddIn").Installed | If sResult Then | '' Add-in exists -- make use of it | Set objLocal = New clsLocalClass | Else | '' Add-in does not exist -- don't use features from Addin | End If | End Sub | | ------------------------------------------------------------ | In clsLocalClass (inside current project) | ------------------------------------------------------------ | Dim objAddin As AddIn.clsSomething | | Private Sub Class_Initialize() | Set objAddin = CreateSomething() | End Sub | | ================================================== ========== | | EXAMPLE 2: | The code below WILL ACTUALLY WORK, but it seems risky. I made two changes: | changed the objLocal definition to Object instead on clsLocalClass, and I | moved the New clsLocalClass line out of Workbook_Open. In this particular | scenario, VBA will not attempt to compile clsLocalClass, so the | Workbook_Open event will run. | | It SEEMS RISKY trying to second-guess when/if VBA will decide to compile | clsLocalClass. My real-world scenario is obviously more complex, and if VBA | were to attempt to compile clsLocalClass at any time for any reason, my | application would halt. | | In ThisWorkbook: | ================================================== ========== | Dim objLocal As clsLocalClass | | Sub Workbook_Open() | Dim sResult | | On Error Resume Next | sResult = Application.AddIns("MyAddIn").Installed | If sResult Then | '' Add-in exists -- make use of it | IntializeApp | Else | '' Add-in does not exist -- don't use features from Addin | End If | End Sub | | Private Sub IntializeApp() | Set objLocal = New clsLocalClass | End Sub | | ------------------------------------------------------------ | In clsLocalClass (inside current project) | ------------------------------------------------------------ | Dim objAddin As AddIn.clsSomething | | Private Sub Class_Initialize() | Set objAddin = CreateSomething() | End Sub | | ================================================== ========== | | | | "Jim Rech" wrote in message | ... | How about: | | ThisWorkbook.VBProject.References.AddFromFile "c:\book1.xla" | | -- | Jim Rech | Excel MVP | "Lee S" wrote in message | ... | | In my spreadsheet I have code that references functions and classes in | an | | .XLA file. When a user who does not have the .XLA file available opens | the | | spreadsheet they see a compile error when the Workbook Open event fires | | (even though the Open event itself does not call or even directly | reference | | any XLA resources). | | | | This actually makes perfect sense to me ... but that does not mean I | have | | to like it. | | | | Anyone know of a reliable way to prevent the compile error in this | | situation? | | | | Something comparable to late binding for functions in an XLA library | would | | be nice. Then I could check if the library exists before I decide | whether | | to make the function calls. | | | | I have discovered that if arrange my code /just right/, I can avoid the | | error by convincing VBA not to compile the modules that reference the | | non-existent library -- however that is not reliable enough to release | to | | the users. | | | | I am tempted to port my .XLA into a Visual Basic and compile it into an | | ActiveX DLL, but I figured I would check with the group to see if anyone | had | | a good idea on this. | | | | Thanks! | | Lee | | | | | | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB Extensibility library and "late binding" | Excel Discussion (Misc queries) | |||
Late Binding help, Please | Excel Programming | |||
Late Binding | Excel Programming | |||
VBA References - when is Office Object Library Reference set? Best practice re. Early/Late binding ... | Excel Programming | |||
EARLY binding or LATE binding ? | Excel Programming |