When you are building advanced Excel applications, you occasionally need to call upon logic or features provided outside of Excel. This is done by adding a REFERENCE to an external application or library, by way of the VBA Editor's Tools menu. Just about every application on your computer shows up here, enabling your code to leverage highly specific functionality such as enhanced user interface elements, or internet data services.
In a previous post I explained how to get Outlook to send email from Excel. The code to do so requires a reference to the Microsoft Outlook 11 Object Library. A recent application I created does this, and also uses several other libraries, including an HTML Browser control to preview PDF files, an Access database, and has a set of Custom UserForms - all requiring project references. Each one of these represents an actual file - usually a DLL - installed and registered on the host system.
This makes Excel incredibly powerful. But using this mechanism can complicate the deployment of your software, because you can't always control what is or isn't installed and registered on the end-user's computer. This can also be a problem if you are a stand-alone developer, but need to move your work between home and office machines. If a referenced DLL or program is not available, you end up with a Broken Reference.
Another coder, Andrew Roberts, blogged about this very problem, and provided a technique to programatically re-add broken references, (for Outlook, in his example.) It's a good fix, but has limitations. For one, it depends on a specific Excel security setting to work, which is probably not something you're going to want to ask 30 novice operators to change on 30 different computers. And if your project code is password-protected, this approach may not work at all.
Protected Project and Protected References
In my project, I am using the Redemption library for Outlook. This toolset allows VBA to manipulate Outlook objects quietly, in the background, instead of requiring the user to approve a series of popup warnings shown as part of Outlook's security protocol.
Not all users need this extra level of functionality, so I wrote some code to "ask for Redemption" - users who do not install this extra software should not be show-stopped by a broken reference. The program should instead default to the standard Outlook experience.
I ran into a major snag: the VBA code that allows you to view and verify project references doesn't work when your VBA project is password-protected - unless the project is open in the VBA editor!
My code needs to be password-protected because of the nature of my programming work - I program for profit. I don't want every employee at my client's location to freely view and copy the code I have written - this is my sweat and blood, and I try to maintain at least an illusion of copyright protection on it. I also don't want operators to change or hack it.
Still, some code I share freely: here's a routine you can call from your workbook's open event to programatically inspect the project reference collection.
In the VBA development environment, it all works wonderfully. All the references I added showed up. But if I close Excel, and then re-open the model, (which calls this routine from it's Workbook Open event,) the references I added to the project become invisible to the code. All you see are the 4 standard references that all projects have:
VBA Visual Basic For Applications
Excel Microsoft Excel 11.0 Object Library
stdole OLE Automation
Office Microsoft Office 11.0 Object Library
My code can't tell if the Redemption reference is broken or not, and so can't handle the situation gracefully.
I believe Excel hides the extra References by design - these are part of your code's functionality and overall design strategy, which is the I.P. that your trying to protect by putting a password on it. However, since it's the code "asking", not a person via the IDE, I think it should be possible to enumerate the full collection of program references. This would allow the kind of self-diagnostic logic I'm looking for.
I'm not sure what the work-around is for this at present - I'll probably end up using some sort of in-function error trap. I'll report on my solution in a future post. I'm open, as always, to suggestions via this blog's comment facility.
One of the really good things about Microsoft Windows and the Office programs is the ability to interoperate and exchange data between applications. If you want to ensure portability and longevity of your code, just don't get carried away adding 3rd party libraries.
In a previous post I explained how to get Outlook to send email from Excel. The code to do so requires a reference to the Microsoft Outlook 11 Object Library. A recent application I created does this, and also uses several other libraries, including an HTML Browser control to preview PDF files, an Access database, and has a set of Custom UserForms - all requiring project references. Each one of these represents an actual file - usually a DLL - installed and registered on the host system.
This makes Excel incredibly powerful. But using this mechanism can complicate the deployment of your software, because you can't always control what is or isn't installed and registered on the end-user's computer. This can also be a problem if you are a stand-alone developer, but need to move your work between home and office machines. If a referenced DLL or program is not available, you end up with a Broken Reference.
Another coder, Andrew Roberts, blogged about this very problem, and provided a technique to programatically re-add broken references, (for Outlook, in his example.) It's a good fix, but has limitations. For one, it depends on a specific Excel security setting to work, which is probably not something you're going to want to ask 30 novice operators to change on 30 different computers. And if your project code is password-protected, this approach may not work at all.
Protected Project and Protected References
In my project, I am using the Redemption library for Outlook. This toolset allows VBA to manipulate Outlook objects quietly, in the background, instead of requiring the user to approve a series of popup warnings shown as part of Outlook's security protocol.
Not all users need this extra level of functionality, so I wrote some code to "ask for Redemption" - users who do not install this extra software should not be show-stopped by a broken reference. The program should instead default to the standard Outlook experience.
I ran into a major snag: the VBA code that allows you to view and verify project references doesn't work when your VBA project is password-protected - unless the project is open in the VBA editor!
My code needs to be password-protected because of the nature of my programming work - I program for profit. I don't want every employee at my client's location to freely view and copy the code I have written - this is my sweat and blood, and I try to maintain at least an illusion of copyright protection on it. I also don't want operators to change or hack it.
Still, some code I share freely: here's a routine you can call from your workbook's open event to programatically inspect the project reference collection.
Sub reftest()
'which references are visible and/or broken?
Dim chkRef As Variant
Debug.Print vbCrLf & vbCrLf & " - Reference Check -"
For Each chkRef In ThisWorkbook.VBProject.References
Debug.Print chkRef.Description & " " & _
Choose(chkRef.isBroken + 1, "", " - broken"
Next
End Sub
In the VBA development environment, it all works wonderfully. All the references I added showed up. But if I close Excel, and then re-open the model, (which calls this routine from it's Workbook Open event,) the references I added to the project become invisible to the code. All you see are the 4 standard references that all projects have:
VBA Visual Basic For Applications
Excel Microsoft Excel 11.0 Object Library
stdole OLE Automation
Office Microsoft Office 11.0 Object Library
My code can't tell if the Redemption reference is broken or not, and so can't handle the situation gracefully.
I believe Excel hides the extra References by design - these are part of your code's functionality and overall design strategy, which is the I.P. that your trying to protect by putting a password on it. However, since it's the code "asking", not a person via the IDE, I think it should be possible to enumerate the full collection of program references. This would allow the kind of self-diagnostic logic I'm looking for.
I'm not sure what the work-around is for this at present - I'll probably end up using some sort of in-function error trap. I'll report on my solution in a future post. I'm open, as always, to suggestions via this blog's comment facility.
One of the really good things about Microsoft Windows and the Office programs is the ability to interoperate and exchange data between applications. If you want to ensure portability and longevity of your code, just don't get carried away adding 3rd party libraries.
Posts
