Late binding to .XLA library??

L

Lee S

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
 
B

Bob Phillips

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)
 
J

Jim Rech

How about:

ThisWorkbook.VBProject.References.AddFromFile "c:\book1.xla"

--
Jim Rech
Excel MVP
| 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
|
|
 
L

Lee S

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

============================================================
 
J

Jim Rech

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
| 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
|
| ============================================================
|
|
|
| | > How about:
| >
| > ThisWorkbook.VBProject.References.AddFromFile "c:\book1.xla"
| >
| > --
| > Jim Rech
| > Excel MVP
| > | > | 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
| > |
| > |
| >
| >
|
|
 
J

Jamie Collins

Lee said:
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.aspx?scid=kb;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.

--
 
L

Lee S

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)
- 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top