Thank you for taking the time to read this thread.



I just finished building an Excel workbook (.xlsb) containing 4 worksheets, each of which have at least one command button to trigger various VBA modules. The VBA code is not used to define functions, it is code used to automate an Excel process (nothing to do with functions). I want to distribute this workbook as a COM add-in, not a .xla add-in because I want the extra security that a COM add-in offers (an XLA add-in is very weak, see WARNING at bottom of cpearson link 2 lines down). Maybe I should ask a simpler question, since protecting code with a project password is so weak, what is a better way? (maybe there is a better/simpler way than using a COM add-in?)


I have searched this forum and the internet for a good article explaining how to do this, I found several hits dealing with XLA add-in's (this site is excellent: http://www.cpearson.com/excel/createaddin.aspx ) but I could not find a comperable site for COM add-ins for Office 2007.

Would someone please point me to a good article explaining step by step how to do this. For example, I notice 'save as' does not include a COM options, there is a .xla option so I am stumped at the very first step.

Also I understand that I probably need to replace the active X buttons I am presently using for menu buttons (that a ThisWorkbook open event sets up and a ThisWorkbook before close event removes) but is there no way I can link these buttons to the COM add-in code modules? Going away from the buttons will change the look and feel of what I have built already, would rather not invest more time to rework this (if possible).

I am assuming that building a COM add-in is a valid task within Office 2007 suite, maybe I am mistaken? I seem to remember I did this once in Office 2003.

Any suggesting would be greatly appreciated.

Hi,

There's nothing built into Office which allows you to compile your workbook into a COM add-in. Let's look at a couple of the options which are available to you. I'm not going to go into great depth on each option (one could write a book doing so) but hopefully this will give you a starting point to do your own research.

Since your code does not contain any UDFs, you could use VSTO. VSTO is written in .Net (VB.Net/C#). VSTO is quite complicated, can be problematic to deploy, and it's important to understand that .Net code isn't secure either - freely available tools such as Redgate's .NET reflector can be used to disassemble code. You could use an obfuscator but you're adding a whole load of complication and learning which you're trying to avoid.

VB6 is syntatically identical to VBA (so not much of a learning curve for you) and can be used to write compiled code (dll/exe). It's also secure. The problem with VB6 is it is legacy (it has been superceded by VB.Net) and is no longer supported by MS. You also might have trouble getting hold of the Visual Studio 6 software you need to do this (it is available with an MSDN subsription).

3rd Party Tools. There are some 3rd party tools which claim to be able to compile VBA code. I've never used one so I can't make a recommendation and I don't know how much you'll have to pay.

VBA. Unless you have written something truly incredible, my honest recommendation is just to stick with your VBA. 99.99% of users do not know how to break the VBA project password, so it will stop them from meddling with your code. This is what I do.

__________________
Colin

RAD Excel Blog

Colin, thank you for the information, it is much appreciated. I will look into VSTO and I also discovered reference to 'Excel 2007 Developer Reference' that look promising. I will leave this thread open until Monday in the hopes that other comments will be added.