Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.vb.general.discussion
|
|||
|
|||
AddIn Project for Excel
What is the value of the VB6 Addin project over a regular COM object if I
simply want to expose 6 functions for use in Excel? It appears that I need to create 6 stub functions in Excel in either case. I could simply build all of the logic in Excel (instead of the two options above), but I would like to secure the code. Can anyone share their insights on this matter? Thanks, Craig Buchanan |
#2
Posted to microsoft.public.excel.programming,microsoft.public.vb.general.discussion
|
|||
|
|||
AddIn Project for Excel
Excluding the Function question, you may care to consider
the security question. It is my understanding that VB6 offers greater protection than any Excel version. Excel addins can be circumvented via password breaking programs (readily available on-line). On the other hand, VB6 / COM Add Ins can be de-compiled (I believe). I guess it's down to how valuable you perceive your code to be. Ultimately, I suspect you will lose your code. Regards. "Craig Buchanan" wrote in message ... What is the value of the VB6 Addin project over a regular COM object if I simply want to expose 6 functions for use in Excel? It appears that I need to create 6 stub functions in Excel in either case. I could simply build all of the logic in Excel (instead of the two options above), but I would like to secure the code. Can anyone share their insights on this matter? Thanks, Craig Buchanan --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.542 / Virus Database: 336 - Release Date: 18/11/2003 |
#3
Posted to microsoft.public.excel.programming,microsoft.public.vb.general.discussion
|
|||
|
|||
AddIn Project for Excel
Putting the code within VBA is an option, the advantage of this is that it
is for a lot of people the most intuitive form of VB, largely due to the facility to record macros and then view them (even though the macro recorder doesn't always output the most efficient, robust code), together with the fact that your code is always compiled on demand and can be debugged easily. A VBA project also exists as an entity that is attached to a workbook, giving you the added intuitiveness of storing data and code that are related together - the utilisation of this feature is likely the biggest reason for using an Excel-VBA project. This can also be seen as a disadvantage however, as you have to create a workbook in order to have a VBA project, so if for example you've got code that you might want to use whatever workbook you're in, you always have to open the workbook containing the code you want to run. This is where you would be most likely to be best served by building an Excel Add-In with VB6. If you want to create an Excel add-in using VB6 like this, it is compiled as a DLL which once installed on a system will take on the relevant behaviour as set in the designer window of 'Startup', 'On Demand', etc. It can be loaded (on demand) by customizing the toolbars, then adding the COMAddins icon from the tools section. Another three things you'll need to do: a) Change the Application property drop down in the Connect module's designer window to MS Excel b) Change the declaration Public VBInstance As VBIDE.VBE to Public WithEvents xlApp as Excel.Application and update the related code. The Application object that gets passed to the OnConnection event will then be an Excel.Application when Excel loads the addin. c) Change the code that adds an icon to the toolbar to check whether it's already there first - this is clearly something they overlooked when designing the template! Then you can compile it and test it by clicking the COMAddins button in Excel and loading it (if you didn't set it to startup automatically). You can apparently debug a COM Addin by putting it in run mode, and then loading it from Excel. You can apparently also write an Excel Addin by going into Excel, creating some VBA code and saving it as an .xla file. You can create a standard ActiveX DLL, but this is more for when you're offering a set of 'programmer's functions' rather than 'analysts functions' that can just be invoked from a menu command, as, say, you might be able to when creating an add-in. "Craig Buchanan" wrote in message ... What is the value of the VB6 Addin project over a regular COM object if I simply want to expose 6 functions for use in Excel? It appears that I need to create 6 stub functions in Excel in either case. I could simply build all of the logic in Excel (instead of the two options above), but I would like to secure the code. Can anyone share their insights on this matter? Thanks, Craig Buchanan |
#4
Posted to microsoft.public.excel.programming,microsoft.public.vb.general.discussion
|
|||
|
|||
AddIn Project for Excel
"Craig Buchanan" wrote in message ... What is the value of the VB6 Addin project over a regular COM object if I simply want to expose 6 functions for use in Excel? In SHORT though, the fact that it can be easily invoked from a toolbar button. The main beauty of add-ins is that although possibly slightly tricky and unintuitive to create, they *are* just that - an add-in - you can write an 'extra-bit' to a standard, useful application like Excel - and seamlessly integrate it with the running of that application. You can even use them for patches - I recently had a problem with Word 2000, whereby it used to crash whenever I used the mouse wheel. No warning, no tolerance - the second you used the mousewheel on Word it would INSTANTLY throw a GPF. No questions asked. No amount of installing the latest mousedrivers, using a different mouse or restarting the machine worked - until I had the idea to write an add-in that subclasses whatever window has the focus and just see if I could get it to ignore the WM_MOUSEWHEEL message. I thought it wouldn't work as I was going to intercept it and pass the CallWindowProc function the WM_VSCROLL instead - but that didn't work as I couldn't figure out how to determine the correct parameters for WM_VSCROLL. So I thought, well doing nothing would be better than crashing. So, trying my luck, I simply put an 'Exit Function' in the case of any WM_MOUSEWHEEL messages, so it exited and returned zero without calling CallWindowProc. And sure enough to my amazement, not only did it stop it crashing, but the mousewheel worked perfectly! And it does anytime the addin is loaded, but not when it isn't - so it always is, and it's obviously the work of the addin that prevents it from crashing. My only conclusion is that XP sends messages to parent windows and child windows, while Windows 2000 (which Word 2000 was designed to run on) sends messages to only child windows (Word and Excel have three levels of windows, even though Word 2000 is an SDI app) and I can only presume that Word 2000 was only programmed to handle messages to one of its windows, and was getting in some sort of deadlock situation, but Word XP would be able to handle the multiple messages that its operating system sends. No prize for guessing what MS's solution to my problem would have been! It appears that I need to create 6 stub functions in Excel in either case. I could simply build all of the logic in Excel (instead of the two options above), but I would like to secure the code. Can anyone share their insights on this matter? Thanks, Craig Buchanan |
#5
Posted to microsoft.public.excel.programming,microsoft.public.vb.general.discussion
|
|||
|
|||
AddIn Project for Excel
how will he 'lose' it?
"Stuart" wrote in message ... Excluding the Function question, you may care to consider the security question. It is my understanding that VB6 offers greater protection than any Excel version. Excel addins can be circumvented via password breaking programs (readily available on-line). On the other hand, VB6 / COM Add Ins can be de-compiled (I believe). I guess it's down to how valuable you perceive your code to be. Ultimately, I suspect you will lose your code. Regards. "Craig Buchanan" wrote in message ... What is the value of the VB6 Addin project over a regular COM object if I simply want to expose 6 functions for use in Excel? It appears that I need to create 6 stub functions in Excel in either case. I could simply build all of the logic in Excel (instead of the two options above), but I would like to secure the code. Can anyone share their insights on this matter? Thanks, Craig Buchanan --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.542 / Virus Database: 336 - Release Date: 18/11/2003 |
#6
Posted to microsoft.public.excel.programming,microsoft.public.vb.general.discussion
|
|||
|
|||
AddIn Project for Excel
Ben-
Let me summarize to ensure that I understand the options: Assumptions: 1). I want to expose 6 functions that can be referenced like any other worksheet function. 2). The logic in each function is a company asset that I want to protect as much as possible 3). I want to make the usage/installation of these 6 functions as each as possible 4). I want to maximize the computational speed of each function. Options: 1). Write the code in VBA and save it as a .xla (Excel Addin) document. Benefits: a). .xla file is easy to distribute and requires no COM registration. b). .xla file is listed in the Tools | Add-ins... menu, so usage is simplified Compromises: a). code is compiled on demand, so performance would be less than compiled code. b). code is "protected" by a password. cracking this password is trival. 2). Create a VB6 COM object Benefits: a). code is compiled on demand, so performance would be maximized. b). code is compiled, which it is more difficult to examine, thus protecting our intellectual property Compromises: a). DLL must be registered in order to be used. b). code is not listed in the Tools | Add-ins... menu, making usage more complicated. c). a stub function must be created in Excel for each function exposed by the DLL; these stub functions could be saved as an Excel .xla file. 3). Create a VB6 Addin Benefits: a). code is compiled on demand, so performance would be maximized. b). code is compiled, which it is more difficult to examine, thus protecting our intellectual property c). DLL is automatically loaded in Excel. Functions don't appear to be automatically added to the list of worksheet functions (this is what is really would like). Compromises: a). DLL must be registered in order to be used. b). code is not listed in the Tools | Add-ins... menu, making usage more complicated. c). a stub function must be created in Excel for each function exposed by the DLL; these stub functions could be saved as an Excel .xla file. (or so it appears). Am I missing anything? Thanks, Craig "Ben Taylor" <nosp@mpleasewerebritish wrote in message ... Putting the code within VBA is an option, the advantage of this is that it is for a lot of people the most intuitive form of VB, largely due to the facility to record macros and then view them (even though the macro recorder doesn't always output the most efficient, robust code), together with the fact that your code is always compiled on demand and can be debugged easily. A VBA project also exists as an entity that is attached to a workbook, giving you the added intuitiveness of storing data and code that are related together - the utilisation of this feature is likely the biggest reason for using an Excel-VBA project. This can also be seen as a disadvantage however, as you have to create a workbook in order to have a VBA project, so if for example you've got code that you might want to use whatever workbook you're in, you always have to open the workbook containing the code you want to run. This is where you would be most likely to be best served by building an Excel Add-In with VB6. If you want to create an Excel add-in using VB6 like this, it is compiled as a DLL which once installed on a system will take on the relevant behaviour as set in the designer window of 'Startup', 'On Demand', etc. It can be loaded (on demand) by customizing the toolbars, then adding the COMAddins icon from the tools section. Another three things you'll need to do: a) Change the Application property drop down in the Connect module's designer window to MS Excel b) Change the declaration Public VBInstance As VBIDE.VBE to Public WithEvents xlApp as Excel.Application and update the related code. The Application object that gets passed to the OnConnection event will then be an Excel.Application when Excel loads the addin. c) Change the code that adds an icon to the toolbar to check whether it's already there first - this is clearly something they overlooked when designing the template! Then you can compile it and test it by clicking the COMAddins button in Excel and loading it (if you didn't set it to startup automatically). You can apparently debug a COM Addin by putting it in run mode, and then loading it from Excel. You can apparently also write an Excel Addin by going into Excel, creating some VBA code and saving it as an .xla file. You can create a standard ActiveX DLL, but this is more for when you're offering a set of 'programmer's functions' rather than 'analysts functions' that can just be invoked from a menu command, as, say, you might be able to when creating an add-in. "Craig Buchanan" wrote in message ... What is the value of the VB6 Addin project over a regular COM object if I simply want to expose 6 functions for use in Excel? It appears that I need to create 6 stub functions in Excel in either case. I could simply build all of the logic in Excel (instead of the two options above), but I would like to secure the code. Can anyone share their insights on this matter? Thanks, Craig Buchanan |
#7
Posted to microsoft.public.excel.programming,microsoft.public.vb.general.discussion
|
|||
|
|||
AddIn Project for Excel
Hi Craig,
Am I missing anything? If you're using Excel XP or more recent, 2c is no longer an issue, as you can instal your COM DLL as an 'Automation Addin', using Tools Addins Automation Addins. Option 4 is to write the addin using C and Excel's C API and compile it as an xll addin, which is much more complicated, but gives the best performance and greatest degree of control over the function's attributes when viewed in the Function Wizard. Regards Stephen Bullen Microsoft MVP - Excel www.BMSLtd.co.uk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XLA addin attached in settings does not show up in project explorer | Excel Discussion (Misc queries) | |||
XLA addin attached in settings does not show up in project explorer | Excel Discussion (Misc queries) | |||
remove name of addin in Tools menu: "Document Project" | Excel Discussion (Misc queries) | |||
How to convert MS Project to MS Excel. I don't have MS Project. | Excel Discussion (Misc queries) | |||
Excel COM Addin | Excel Programming |