|
How to map a shortcut key to a routine in COM AddIn for Excel 2007
1. Background:
I have a COM addins developed in Visual basic 6.0 for EXCEL 2007. The COM addin has its toolbar visible. It exposes customized 'Copy' as a button on the toolbar, which will call MyCopyAction routine in the COM addin. 2. What I want: I want to map a shortcut key for the customized 'copy' operation, which indeed invoke the same routine as that on toolbar, i.e. MyCopyAction. 3. What I tried in the COM addin: I added the following line in Private Sub AddinInstance_OnStartupComplete(custom() As Variant) .... Application.OnKey "+^c", "MyCopyAction" .... End Sub Problem was: Message box from EXCEL "cannot run the macro MyCopyAction ...". MyCopyAction is not a macro. It's a routine in the COM AddIn. What options do I have to make it work? Thanks! |
How to map a shortcut key to a routine in COM AddIn for Excel 2007
buddylake used his keyboard to write :
1. Background: I have a COM addins developed in Visual basic 6.0 for EXCEL 2007. The COM addin has its toolbar visible. It exposes customized 'Copy' as a button on the toolbar, which will call MyCopyAction routine in the COM addin. 2. What I want: I want to map a shortcut key for the customized 'copy' operation, which indeed invoke the same routine as that on toolbar, i.e. MyCopyAction. 3. What I tried in the COM addin: I added the following line in Private Sub AddinInstance_OnStartupComplete(custom() As Variant) ... Application.OnKey "+^c", "MyCopyAction" ... End Sub Problem was: Message box from EXCEL "cannot run the macro MyCopyAction ...". MyCopyAction is not a macro. It's a routine in the COM AddIn. What options do I have to make it work? Thanks! I use COMAddins but I don't build my menus there; I use a xla for this and redirect all controls' OnActions through a single procedure that serves as an entry point to the COMAddin's procedures. It uses the appropriate calls from within Excel to use the procedures inside the COMAddin. (No code is in the xla other than what's required to create/remove menus/toolbars. The first thing I see in your code is that Application is not referring to your COMAddin. (Assumes you have 'Set' a global variable initialized to ref Excel in the OnConnection routine) So.., if appXL is your COMAddin's global variable that holds its ref to Excel then the line of code from your COMAddin should be: appXL.OnKey... Also, I don't see where you tell Excel that "MyCopyAction" is located in your COMAddin. In this case, I think it will have to be a public method in order for Excel to access it. I know that from Excel, we must ref the COMAddin same as we would ref executing a macro in another workbook: Application.COMAddIns(gsAPP_NAME & ".Connect").Object.MyCopyAction Here again, you need to replace 'Application' with your object var. So give this a try: With appXL .OnKey "+^c", _ .COMAddins("gsAPP_NAME & ".Connect").Object.MyCopyAction End With I don't know if it will work because I set these up in my xla file to go through the common entry point procedure. Good luck Garry -- |
How to map a shortcut key to a routine in COM AddIn for Excel 2007
Unfortunately it's not possible to directly assign a shortcut for a routine
in an ActiveX dll, such as a ComAddin. One way or another will need a bit of help from VBA. As your ComAddin has a button to call your routine the simplest way would be to trigger its click event, which I assume you are already trapping with WithEvents in your ComAddin. In VBA, Sub MyCopyAction() dim cbt as commandbarbutton set cbt = commandbars.findcontrol(tag:=myTag) ' or maybe say set cbt = myBar.Controls(myCaption) ' and the fire the button cbt.Excecute End Sub Another way would again from a VBA macro to call a public method in a public class (with it's instancing set to MultiUse or GlobalMultiUse). You'd probably also want to set a reference to the dll in the VBA project, though not necessary if use CreateObject to instanciate the class. You could also use the Connect class if Public though I'd suggest use an ordinary class. Regards, Peter T "buddylake" wrote in message ... 1. Background: I have a COM addins developed in Visual basic 6.0 for EXCEL 2007. The COM addin has its toolbar visible. It exposes customized 'Copy' as a button on the toolbar, which will call MyCopyAction routine in the COM addin. 2. What I want: I want to map a shortcut key for the customized 'copy' operation, which indeed invoke the same routine as that on toolbar, i.e. MyCopyAction. 3. What I tried in the COM addin: I added the following line in Private Sub AddinInstance_OnStartupComplete(custom() As Variant) ... Application.OnKey "+^c", "MyCopyAction" ... End Sub Problem was: Message box from EXCEL "cannot run the macro MyCopyAction ...". MyCopyAction is not a macro. It's a routine in the COM AddIn. What options do I have to make it work? Thanks! |
How to map a shortcut key to a routine in COM AddIn for Excel 2007
"GS" wrote in message
... I use COMAddins but I don't build my menus there; I use a xla for this and redirect all controls' OnActions through a single procedure that serves as an entry point to the COMAddin's procedures. It uses the appropriate calls from within Excel to use the procedures inside the COMAddin. (No code is in the xla other than what's required to create/remove menus/toolbars. Curiosity, why bother making it a ComAddin if it's only going to be called from a UI created by your xla, with Onactions to your xla. IOW simply the xla as a wrapper to call the aX dll. You could dispense with the Connect class altogether. Regards, Peter T |
How to map a shortcut key to a routine in COM AddIn for Excel 2007
Peter T has brought this to us :
"GS" wrote in message ... I use COMAddins but I don't build my menus there; I use a xla for this and redirect all controls' OnActions through a single procedure that serves as an entry point to the COMAddin's procedures. It uses the appropriate calls from within Excel to use the procedures inside the COMAddin. (No code is in the xla other than what's required to create/remove menus/toolbars. Curiosity, why bother making it a ComAddin if it's only going to be called from a UI created by your xla, with Onactions to your xla. IOW simply the xla as a wrapper to call the aX dll. You could dispense with the Connect class altogether. Regards, Peter T Hi Peter, The only thing i use the xla for is to create/remove the menus/toolbar. The UI Setup is created/removed by the COMAddin. There are 2 reasons I use the xla to handle menus/toolbar[s]: 1. I use Rob Boveys commandbar builder table; 2. I provide plugin support for my apps. These are 'addins for my addin' so to speak, whereby clients can add their own user-specific features and functionality to the core app to enhance it specific to their needs. I haven't yet figured out the VB6 mechanics of how to get this to work in-process to my COMAddins and so using an xla to modify the menus/toolbar with its own menus facilitates this easily. Also, since these are paid for by the client then they actually own the source as well, and so this facilitates them being able to work with that more easily than VB6 source. Why I 'bother' making COMAddins: 1. Security! Much of what I do comprises proprietary stuff of mine OR my clients. (ie: business logic, dbase access passwords, user/password logins, etc) 2. Separate Threading As you know, VBA stops when Excel starts working. COMAddins don't have this limitation. 3. Multiple Designer Class support Not a major whistle but I do get requests for solutions that work in Excel and Word. (Though, there's nothing much Word can do that can't be duplicated with Excel, and without the 'bloat'!<IMO) 4. A COMAddin is the only current means to set up the ribbon in v12 or higher via code. You must provide this through the designer. Sure, I could make a separate xlsm/xlam just for that purpose, need to use the Custom UI utility and all, but that's more bother than having a designer and less secure to boot. Since the COMAddin is essentially the same as a DLL, the advantages (for me at least) are worth it. (IOW, "the juice is worth the squeeze!"<g) 5. Menus we create in Excel need event hooking for callbacks, etc. and the single OnAction in the xla obviates need for this. So.., the trade off for this class is the designer, less code (and so smaller compiled size) since there's no control event hooking or menu/toolbar building, and no need to store button images/masks in a res. Plus all the perks that go with working in VB6 over VBA. (forms, control arrays, built-in MAPI support, ...) 'also available to DLL<g So the short answer to your Q is: It has way more advantages than a using a xla to call into a DLL. I suspect, though, that you already knew all this, right?<g regards, Garry |
How to map a shortcut key to a routine in COM AddIn for Excel 2007
"GS" wrote in message ... Peter T has brought this to us : "GS" wrote in message ... I use COMAddins but I don't build my menus there; I use a xla for this and redirect all controls' OnActions through a single procedure that serves as an entry point to the COMAddin's procedures. It uses the appropriate calls from within Excel to use the procedures inside the COMAddin. (No code is in the xla other than what's required to create/remove menus/toolbars. Curiosity, why bother making it a ComAddin if it's only going to be called from a UI created by your xla, with Onactions to your xla. IOW simply the xla as a wrapper to call the aX dll. You could dispense with the Connect class altogether. Regards, Peter T Hi Peter, The only thing i use the xla for is to create/remove the menus/toolbar. The UI Setup is created/removed by the COMAddin. There are 2 reasons I use the xla to handle menus/toolbar[s]: 1. I use Rob Boveys commandbar builder table; 2. I provide plugin support for my apps. These are 'addins for my addin' so to speak, whereby clients can add their own user-specific features and functionality to the core app to enhance it specific to their needs. I haven't yet figured out the VB6 mechanics of how to get this to work in-process to my COMAddins and so using an xla to modify the menus/toolbar with its own menus facilitates this easily. Also, since these are paid for by the client then they actually own the source as well, and so this facilitates them being able to work with that more easily than VB6 source. Why I 'bother' making COMAddins: 1. Security! Much of what I do comprises proprietary stuff of mine OR my clients. (ie: business logic, dbase access passwords, user/password logins, etc) 2. Separate Threading As you know, VBA stops when Excel starts working. COMAddins don't have this limitation. Actually I didn't know that, are you sure! And even if it does, how does it help in practice. 3. Multiple Designer Class support Not a major whistle but I do get requests for solutions that work in Excel and Word. (Though, there's nothing much Word can do that can't be duplicated with Excel, and without the 'bloat'!<IMO) If you are not using any of the functionality of a ComAddin, other than installing it as such, this is a nor issue (I would have thought) 4. A COMAddin is the only current means to set up the ribbon in v12 or higher via code. You must provide this through the designer. Sure, I could make a separate xlsm/xlam just for that purpose, need to use the Custom UI utility and all, but that's more bother than having a designer and less secure to boot. Since the COMAddin is essentially the same as a DLL, the advantages (for me at least) are worth it. (IOW, "the juice is worth the squeeze!"<g) I don't follow what you mean by ComAddin is the only way to set up the ribbon. Actually for me I found it quite a learning curve how to figure out how to do all that, the XML and the call-backs entirely in a ComAddin (much easier in an xlam!). I'm also confused, you say your xla handles all the menus in 2000/3 (even though can be done without an xla), yet in v12 you go the hard way and do it all in the ComAddin without an xlam. So are you saying are saying in v12 there is no VBA as the ribbon menu is entirely handled in the ComAddin. Or all the menu, callbacks etc is handled by VBA in an addin, if so I still done't follow why the need to install the ComAddin (vs simply an aX dll with xla wrapper). 5. Menus we create in Excel need event hooking for callbacks, etc. and the single OnAction in the xla obviates need for this. So.., the trade off for this class is the designer, less code (and so smaller compiled size) since there's no control event hooking or menu/toolbar building, and no need to store button images/masks in a res. Swings and roundabouts I suppose, the menu builder and Click event (or Ribbon + callback) has to go somewhere. Likewise the button images (if you use custom ones) have to go somewhere. Regards, Peter T |
How to map a shortcut key to a routine in COM AddIn for Excel 2007
Peter T submitted this idea :
"GS" wrote in message ... Peter T has brought this to us : "GS" wrote in message ... I use COMAddins but I don't build my menus there; I use a xla for this and redirect all controls' OnActions through a single procedure that serves as an entry point to the COMAddin's procedures. It uses the appropriate calls from within Excel to use the procedures inside the COMAddin. (No code is in the xla other than what's required to create/remove menus/toolbars. Curiosity, why bother making it a ComAddin if it's only going to be called from a UI created by your xla, with Onactions to your xla. IOW simply the xla as a wrapper to call the aX dll. You could dispense with the Connect class altogether. Regards, Peter T Hi Peter, The only thing i use the xla for is to create/remove the menus/toolbar. The UI Setup is created/removed by the COMAddin. There are 2 reasons I use the xla to handle menus/toolbar[s]: 1. I use Rob Boveys commandbar builder table; 2. I provide plugin support for my apps. These are 'addins for my addin' so to speak, whereby clients can add their own user-specific features and functionality to the core app to enhance it specific to their needs. I haven't yet figured out the VB6 mechanics of how to get this to work in-process to my COMAddins and so using an xla to modify the menus/toolbar with its own menus facilitates this easily. Also, since these are paid for by the client then they actually own the source as well, and so this facilitates them being able to work with that more easily than VB6 source. Why I 'bother' making COMAddins: 1. Security! Much of what I do comprises proprietary stuff of mine OR my clients. (ie: business logic, dbase access passwords, user/password logins, etc) 2. Separate Threading As you know, VBA stops when Excel starts working. COMAddins don't have this limitation. Actually I didn't know that, are you sure! And even if it does, how does it help in practice. Excerpt from Professional Excel Development, Ch21: Exploiting Separate Threading "One of the more interesting things about COM Add-ins is that each one is given its own execution thread. The vast majority of Excel and VBA is single-threaded, meaning that VBA code stops when Excel is working (such as showing one of its dialogs) and vice versa. COM Add-ins dont have this limitation. A COM Add-in can initialize a Windows timer callback, tell Excel to display a dialog (or Print Preview or whatever), then continue processing (in the callback function) while Excel is still displaying the dialog. This allows us to (a) pre-populate the dialog, (b) watch what the user is doing within the dialog (and respond to it) and even (c) change the layout of the dialog itself!" 3. Multiple Designer Class support Not a major whistle but I do get requests for solutions that work in Excel and Word. (Though, there's nothing much Word can do that can't be duplicated with Excel, and without the 'bloat'!<IMO) If you are not using any of the functionality of a ComAddin, other than installing it as such, this is a nor issue (I would have thought) Excerpt from Professional Excel Development, Ch21: Multi-Application Add-ins "A COM Add-in can contain multiple Designer classes, each handling the connection to a different Office application. Imagine an €˜Insert Customer Details add-in, which displayed a form allowing you select a customer from a central database and then inserted their name, address and/or telephone number in the current place in the document. By including multiple Designer classes in the add-in, we could easily make the add-in available to all the Office applications. Each classs OnConnection event would be used to add a standard menu item to the host applications command bars, with the click event handled by a single class. When clicked, it would display the form and would only branch into application-specific code when the €˜Insert button was clicked to insert the selected details into the cell, paragraph, field, presentation or web page." 4. A COMAddin is the only current means to set up the ribbon in v12 or higher via code. You must provide this through the designer. Sure, I could make a separate xlsm/xlam just for that purpose, need to use the Custom UI utility and all, but that's more bother than having a designer and less secure to boot. Since the COMAddin is essentially the same as a DLL, the advantages (for me at least) are worth it. (IOW, "the juice is worth the squeeze!"<g) I don't follow what you mean by ComAddin is the only way to set up the ribbon. Actually for me I found it quite a learning curve how to figure out how to do all that, the XML and the call-backs entirely in a ComAddin (much easier in an xlam!). I stipulated 'via code'! It's actually easier to do in the COMAddin, NOT harder, because I don't need to use a separate xlam file for v12+, I don't need to use M$'s special Custom UI editor, and the xml is secure (as opposed to being in a workbook that anyone can edit as they please). I'm saying that INSTEAD of editing xml in a xlam, I pass the xml to Excel VIA CODE in the Designer. This is purely to handle the ribbon config, which (in most cases) basically removes everything except the Addins tab. My xla merely provides the menus/toolbar used for the COMAddin's runtime UI. These have a common entry point into the COMAddin. No callbacks are used or required (but I could optionally do that if/when needed). This requires setting refs to MSO12 AND Excel12 LIBs in the VB6 COMAddin's References dialog, and using the special interface designed for this purpose: Implements IRibbonExtensibility and using this function to load the xml: IRibbonExtensibility_GetCustomUI(ByVal RibbonID As String) As String IRibbonExtensibility_GetCustomUI = SetRibbonXML End Function SetRibbonXML is basically the same as what would be in a xlam's xml. In summary: The COMAddin handles all aspects of the UI except menus/toolbar. The XLA only handles menus/toolbar creation/removal. (for now) No separate xlam required for v12+. I'd be happy to help you in any way I can if you have interest in exploring this further. Essentially, besides a lot of back-and-forth with Rob, my main learning resources we Professional Excel Development Excel 2007 VBA Developer's Reference and so I highly recommend these. Though, I suspect you may already have them.<g Most of the back and forth with Rob concerned issues relavent to my goals that weren't covered in either book. I also studied Ron de Brun's materials to get ideas for setting up different UI scenarios. (Ditto the recommend/suspect you already have...) I'm also confused, you say your xla handles all the menus in 2000/3 (even though can be done without an xla), yet in v12 you go the hard way and do it all in the ComAddin without an xlam. Nope! I'm saying I use the same xla for all versions. In earlier vers, my menus/toolbar are basically setup like dictator apps. In v12+ they appear on the Addins tab, which is the only tab I make available at runtime once the COMAddin passes the xml to Excel via the above function. Bear in mind that all my apps now use their own instance of Excel, except in cases where the client wants to add functionality to their default instance. In those cases I will usually provide a xla because that's likely going to be the only format they'll be able to access the source code in (if need be). Since they pay for it, it's their property. Every client, by default, has a VBIDE that they can use to access that source code. This is not likely to be the case for a VB6 COMAddin! So are you saying are saying in v12 there is no VBA as the ribbon menu is entirely handled in the ComAddin. Or all the menu, callbacks etc is handled by VBA in an addin, if so I still done't follow why the need to install the ComAddin (vs simply an aX dll with xla wrapper). I'm saying that in any version there is no VBA in my xla other than whats necessary to create/remove the menus/toolbar. That is the only purpose (for now) of using the xla at all. I haven't got the mechanics for using the table-driven methodology figured out yet for doing this in VB6, but working on it so as to (eventually) not need XLAs. Once I resolve that part it's all going to be packaged inside the COMAddin for all x32 versions. 5. Menus we create in Excel need event hooking for callbacks, etc. and the single OnAction in the xla obviates need for this. So.., the trade off for this class is the designer, less code (and so smaller compiled size) since there's no control event hooking or menu/toolbar building, and no need to store button images/masks in a res. Swings and roundabouts I suppose, the menu builder and Click event (or Ribbon + callback) has to go somewhere. No, that's not entirely true. If we create our menus for v12+ in our COMAddin then WE HAVE NO CHOICE but to implement a mechanism to handle hooking, callbacks, and icon images. Using the xla to manage our menus/toolbar obviates all need for that. They have a single entry point into our COMAddin. The COMAddin handles all its internal messaging as well as communications with/from Excel as per normal. So things like error handling and normal Excel events can be done by our COMAddin in a usual manner. The XLA commandbar builder utility has its own error handling and shutdown cleanup, and so our COMAddin doesn't need to be concerned with any of that either. Rob said he used to do commandbar building from a text file. I might do something similar with ADO and dump the file contents into a grid on a form, and just modify the code to walk through the grid as it now does a spreadsheet. Seems simple enough! Likewise the button images (if you use custom ones) have to go somewhere. Yes they do. They (if custom) currently exist on the worksheet table for building menus/toolbar. Once I figure how I want to duplicate this table-driven methodology inside a VB6 COMAddin I will move them into a res. Meanwhile, all works flawlessly as expected to work and so no interuption for getting solutions to my clients. Everything we do can always be updated (or replaced with a newer ver). Regards, Peter T One thing I did not elaborate on is that my apps use their own instance of Excel. I guess that means I'll be disclosing some of my app architecture... Automating my own instance allows me to lock it down however I want, design whatever UI elements I want, and prevent users from using my instance for any other purpose than what it was designed for. (As said previously, some clients don't even realize they are using Excel!) Implementing this requires using a VB 'FrontLoader.exe'. This does all kinds of tests before it even allows startup, and if all tests pass it creates our instance, loads the COMAddin, and passes a 'ValidStartup' flag so the user can access functionality via the menus/toolbar. some tests include making sure the respective MSO apps are installed, making sure all required files/runtime components are available, and license validation. What I mean by 'loads the COMAddin' is that its StartupBehavior in the designer is deliberately set to '(None)' so that it doesn't appear in any Excel dialogs, or if manually added to a user instance it will not persist. Additionally, the COMAddin will not execute any internal code without the var gbValidStartup (flag) set to TRUE. This flag is an encrypted key that the frontloader passes after loading the COMAddin. Since COMAddins are a DLL just like any other DLL, its methods can be accessed at any time by any app. The only way to prevent unauthorized use is to restrict execution of its methods by any other means than our project provides. (As I said, some of my clients are overly security cautious; some are obsessed!) One side benefit of this to me is I can use that to turn off access for any reason (like payment not received within 30 days, for example). So by building this capability into the project's structure it makes it easy to say to clients: "Try it out for 30 days. Let me know if you want any changes made within that trial period. Otherwise, I expect to be fully paid if you plan to continue using it!". Kind of puts my time and effort at risk somewhat, but it hasn't been fruitless so far, thankfully.<bg This feature is controlled by my licensing methodology, and so is included in the frontloader pre-satartup tests. Finally, the key to understanding why I use the xla at all is this: I fully develop in VBA in the earliest version expected to be used. since the commandbar builder components already exist, I just export all other mod/cls components to import into a COMAddin shell and go from there. All coding uses fully qualified refs (even with XL globals) so the only chore after importing the code is replacing any instances of 'Application' with my global var used for that (appXL). This only accounts for non-VB6 specific code. I continue development from their using the COMAddin from within whatever version I want to dev/test in, via a PERSONAL.XLS proc that mimics the frontloader startup routines. (Another side benefit of setting its StartupBehavior as mentioned) All code is made version-aware as required (by default). I can move from one ver to the other on my dev machine because I have it set up (currently) with v9 to v12 (no VM). I no longer dev for v9 and so I have 3 'clean' test machines (2x XPx32, 1 Win7x64) for stand-alone version testing. I did have a Vista machine but not long enough to bother setting it up as a test unit because I hated it and so gave it away to my daughter for business use. So, Peter, there you have it! Lots to digest, huh? Hey, I got nothing but time on my hands and so this is the sort of stuff I do with it. Regards, Garry |
How to map a shortcut key to a routine in COM AddIn for Excel 2007
"GS" wrote in message ... Peter T submitted this idea : "GS" wrote in message ... Peter T has brought this to us : "GS" wrote in message <snip 2. Separate Threading As you know, VBA stops when Excel starts working. COMAddins don't have this limitation. Actually I didn't know that, are you sure! And even if it does, how does it help in practice. Excerpt from Professional Excel Development, Ch21: Exploiting Separate Threading "One of the more interesting things about COM Add-ins is that each one is given its own execution thread. The vast majority of Excel and VBA is single-threaded, meaning that VBA code stops when Excel is working (such as showing one of its dialogs) and vice versa. COM Add-ins don't have this limitation. A COM Add-in can initialize a Windows timer callback, tell Excel to display a dialog (or Print Preview or whatever), then continue processing (in the callback function) while Excel is still displaying the dialog. This allows us to (a) pre-populate the dialog, (b) watch what the user is doing within the dialog (and respond to it) and even (c) change the layout of the dialog itself!" Ah yes, guess I've never had need to take advantage of that possibility. 3. Multiple Designer Class support Not a major whistle but I do get requests for solutions that work in Excel and Word. (Though, there's nothing much Word can do that can't be duplicated with Excel, and without the 'bloat'!<IMO) If you are not using any of the functionality of a ComAddin, other than installing it as such, this is a nor issue (I would have thought) Excerpt from Professional Excel Development, Ch21: Multi-Application Add-ins "A COM Add-in can contain multiple Designer classes, each handling the connection to a different Office application. Imagine an 'Insert Customer Details' add-in, which displayed a form allowing you select a customer from a central database and then inserted their name, address and/or telephone number in the current place in the document. By including multiple Designer classes in the add-in, we could easily make the add-in available to all the Office applications. Each class's OnConnection event would be used to add a standard menu item to the host application's command bars, with the click event handled by a single class. When clicked, it would display the form and would only branch into application-specific code when the 'Insert' button was clicked to insert the selected details into the cell, paragraph, field, presentation or web page." OK, but although it's possible to include multiple 'Connect' classes for different apps, and stylistically that's probably a good idea, it's not necessary. In the Connection events can start with say a Select Case to cater for differing 'Application' object passed in the original OnConnection event. 4. A COMAddin is the only current means to set up the ribbon in v12 or higher via code. You must provide this through the designer. Sure, I could make a separate xlsm/xlam just for that purpose, need to use the Custom UI utility and all, but that's more bother than having a designer and less secure to boot. Since the COMAddin is essentially the same as a DLL, the advantages (for me at least) are worth it. (IOW, "the juice is worth the squeeze!"<g) I don't follow what you mean by ComAddin is the only way to set up the ribbon. Actually for me I found it quite a learning curve how to figure out how to do all that, the XML and the call-backs entirely in a ComAddin (much easier in an xlam!). I stipulated 'via code'! It's actually easier to do in the COMAddin, NOT harder, because I don't need to use a separate xlam file for v12+, I don't need to use M$'s special Custom UI editor, and the xml is secure (as opposed to being in a workbook that anyone can edit as they please). I'm saying that INSTEAD of editing xml in a xlam, I pass the xml to Excel VIA CODE in the Designer. Sorry I don't follow "pass the xml". How/whereis the xml stored, how and when is it passed to Excel, then what does Excel do with it. FWIW, in the Connect class I do something like this Public Function IRibbonExtensibility_GetCustomUI(ByVal RibbonID As String) As String s = LoadResString(1001) IRibbonExtensibility_GetCustomUI = s where 1001 refers to the entire XML stored in th Resource This is purely to handle the ribbon config, which (in most cases) basically removes everything except the Addins tab. My xla merely provides the menus/toolbar used for the COMAddin's runtime UI. These have a common entry point into the COMAddin. No callbacks are used or required (but I could optionally do that if/when needed). This requires setting refs to MSO12 AND Excel12 LIBs in the VB6 COMAddin's References dialog, Indeed. That also means the if the dll is to cater for the Ribbon it means having different versions of the dll for 2000/3 and 2007+ (otherewise could simply set the reference to the lowest Excel version). and using the special interface designed for this purpose: Implements IRibbonExtensibility and using this function to load the xml: IRibbonExtensibility_GetCustomUI(ByVal RibbonID As String) As String IRibbonExtensibility_GetCustomUI = SetRibbonXML End Function SetRibbonXML is basically the same as what would be in a xlam's xml. In summary: The COMAddin handles all aspects of the UI except menus/toolbar. The XLA only handles menus/toolbar creation/removal. (for now) No separate xlam required for v12+. Sorry I'm confused again, in v12 do you have an XLA(m). If the ComAddin already handles the entire Ribbon why do you need old style menus & toolbar in v12 (in the Addins tab). I'd be happy to help you in any way I can if you have interest in exploring this further. Essentially, besides a lot of back-and-forth with Rob, my main learning resources we Professional Excel Development Excel 2007 VBA Developer's Reference I got the orignial version when it first came out, maybe I should look at the 2007 version! and so I highly recommend these. Though, I suspect you may already have them.<g Most of the back and forth with Rob concerned issues relavent to my goals that weren't covered in either book. I also studied Ron de Brun's materials to get ideas for setting up different UI scenarios. (Ditto the recommend/suspect you already have...) Ron's site is a valuable resource (thanks Ron). I confess though I am still not fully up to speed with the Ribbon )-: I'm also confused, you say your xla handles all the menus in 2000/3 (even though can be done without an xla), yet in v12 you go the hard way and do it all in the ComAddin without an xlam. Nope! I'm saying I use the same xla for all versions. In earlier vers, my menus/toolbar are basically setup like dictator apps. In v12+ they appear on the Addins tab, which is the only tab I make available at runtime once the COMAddin passes the xml to Excel via the above function. I'm probably being real dumb here but if all your menus etc are in the Addins tab (ie same code to create and handle them as in previous versions), why do you need to be concerned with the Ribbon, Implements IRibbonExtensibility etc. Bear in mind that all my apps now use their own instance of Excel, except in cases where the client wants to add functionality to their default instance. In those cases I will usually provide a xla because that's likely going to be the only format they'll be able to access the source code in (if need be). Since they pay for it, it's their property. Every client, by default, has a VBIDE that they can use to access that source code. This is not likely to be the case for a VB6 COMAddin! I guess your app's are designed with very specific purposes in mind. So are you saying are saying in v12 there is no VBA as the ribbon menu is entirely handled in the ComAddin. Or all the menu, callbacks etc is handled by VBA in an addin, if so I still done't follow why the need to install the ComAddin (vs simply an aX dll with xla wrapper). I'm saying that in any version there is no VBA in my xla other than whats necessary to create/remove the menus/toolbar. That is the only purpose (for now) of using the xla at all. I haven't got the mechanics for using the table-driven methodology figured out yet for doing this in VB6, but working on it so as to (eventually) not need XLAs. Once I resolve that part it's all going to be packaged inside the COMAddin for all x32 versions. Ah, so it's just a matter of not yet getting around to moving the old-style toolbar creation and OnAction entirely into the ComAddin. <snip No, that's not entirely true. If we create our menus for v12+ in our COMAddin then WE HAVE NO CHOICE but to implement a mechanism to handle hooking, callbacks, and icon images. Using the xla to manage our menus/toolbar obviates all need for that. They have a single entry point into our COMAddin. The COMAddin handles all its internal messaging as well as communications with/from Excel as per normal. So things like error handling and normal Excel events can be done by our COMAddin in a usual manner. Back to the my first thoughts again, apart from the Threading aspect there doesn't appear to be any need for the aX dll to be a ComAddin, right? Though I'm still unclear why you have some Ribbon stuff in a v12 version. The XLA commandbar builder utility has its own error handling and shutdown cleanup, and so our COMAddin doesn't need to be concerned with any of that either. Think I know roughly what you have there but I've never found it a problem to do entirely within VB6, and without cells to lay out the menu details Rob said he used to do commandbar building from a text file. I might do something similar with ADO and dump the file contents into a grid on a form, and just modify the code to walk through the grid as it now does a spreadsheet. Seems simple enough! Unless you've got a vast number of buttons simply a few har coded arrays. Or read from a text file. Likewise the button images (if you use custom ones) have to go somewhere. Yes they do. They (if custom) currently exist on the worksheet table for building menus/toolbar. Once I figure how I want to duplicate this table-driven methodology inside a VB6 COMAddin I will move them into a res. Meanwhile, all works flawlessly as expected to work and so no interuption for getting solutions to my clients. Everything we do can always be updated (or replaced with a newer ver). That of course is the main thing! One thing I did not elaborate on is that my apps use their own instance of Excel. I guess that means I'll be disclosing some of my app architecture... Automating my own instance allows me to lock it down however I want, design whatever UI elements I want, and prevent users from using my instance for any other purpose than what it was designed for. (As said previously, some clients don't even realize they are using Excel!) Implementing this requires using a VB 'FrontLoader.exe'. This does all kinds of tests before it even allows startup, and if all tests pass it creates our instance, loads the COMAddin, and passes a 'ValidStartup' flag so the user can access functionality via the menus/toolbar. some tests include making sure the respective MSO apps are installed, making sure all required files/runtime components are available, and license validation. What I mean by 'loads the COMAddin' is that its StartupBehavior in the designer is deliberately set to '(None)' so that it doesn't appear in any Excel dialogs, or if manually added to a user instance it will not persist. Additionally, the COMAddin will not execute any internal code without the var gbValidStartup (flag) set to TRUE. This flag is an encrypted key that the frontloader passes after loading the COMAddin. Since COMAddins are a DLL just like any other DLL, its methods can be accessed at any time by any app. The only way to prevent unauthorized use is to restrict execution of its methods by any other means than our project provides. (As I said, some of my clients are overly security cautious; some are obsessed!) One side benefit of this to me is I can use that to turn off access for any reason (like payment not received within 30 days, for example). So by building this capability into the project's structure it makes it easy to say to clients: "Try it out for 30 days. Let me know if you want any changes made within that trial period. Otherwise, I expect to be fully paid if you plan to continue using it!". Kind of puts my time and effort at risk somewhat, but it hasn't been fruitless so far, thankfully.<bg This feature is controlled by my licensing methodology, and so is included in the frontloader pre-satartup tests. Interesting But on the limited time use don't you still need some sort of license/key combination. Finally, the key to understanding why I use the xla at all is this: I fully develop in VBA in the earliest version expected to be used. since the commandbar builder components already exist, I just export all other mod/cls components to import into a COMAddin shell and go from there. All coding uses fully qualified refs (even with XL globals) so the only chore after importing the code is replacing any instances of 'Application' with my global var used for that (appXL). This only accounts for non-VB6 specific code. I continue development from their using the COMAddin from within whatever version I want to dev/test in, via a PERSONAL.XLS proc that mimics the frontloader startup routines. (Another side benefit of setting its StartupBehavior as mentioned) FWIW I find if the reference is set to v9 and it compiles it should work for all versions. For specific later version stuff say with the range object, Dim objRng As Object (not range), then objRng.NewMethod will also compile. So no real need to develop in VBA at all. That said, a lot of things need to be rewritten in v12 irrespective of the reference issue. All code is made version-aware as required (by default). I can move from one ver to the other on my dev machine because I have it set up (currently) with v9 to v12 (no VM). I no longer dev for v9 and so I have 3 'clean' test machines (2x XPx32, 1 Win7x64) for stand-alone version testing. I did have a Vista machine but not long enough to bother setting it up as a test unit because I hated it and so gave it away to my daughter for business use. So, Peter, there you have it! Lots to digest, huh? Hey, I got nothing but time on my hands and so this is the sort of stuff I do with it. Yep, a lot there and thanks. I'm still a bit confused though about what's in the xla & the dll, especially re v12 Ribbon stuff. Regards, Peter T |
How to map a shortcut key to a routine in COM AddIn for Excel 2007
After serious thinking Peter T wrote :
"GS" wrote in message ... Peter T submitted this idea : "GS" wrote in message ... Peter T has brought this to us : "GS" wrote in message <snip 2. Separate Threading As you know, VBA stops when Excel starts working. COMAddins don't have this limitation. Actually I didn't know that, are you sure! And even if it does, how does it help in practice. Excerpt from Professional Excel Development, Ch21: Exploiting Separate Threading "One of the more interesting things about COM Add-ins is that each one is given its own execution thread. The vast majority of Excel and VBA is single-threaded, meaning that VBA code stops when Excel is working (such as showing one of its dialogs) and vice versa. COM Add-ins don't have this limitation. A COM Add-in can initialize a Windows timer callback, tell Excel to display a dialog (or Print Preview or whatever), then continue processing (in the callback function) while Excel is still displaying the dialog. This allows us to (a) pre-populate the dialog, (b) watch what the user is doing within the dialog (and respond to it) and even (c) change the layout of the dialog itself!" Ah yes, guess I've never had need to take advantage of that possibility. 3. Multiple Designer Class support Not a major whistle but I do get requests for solutions that work in Excel and Word. (Though, there's nothing much Word can do that can't be duplicated with Excel, and without the 'bloat'!<IMO) If you are not using any of the functionality of a ComAddin, other than installing it as such, this is a nor issue (I would have thought) Excerpt from Professional Excel Development, Ch21: Multi-Application Add-ins "A COM Add-in can contain multiple Designer classes, each handling the connection to a different Office application. Imagine an 'Insert Customer Details' add-in, which displayed a form allowing you select a customer from a central database and then inserted their name, address and/or telephone number in the current place in the document. By including multiple Designer classes in the add-in, we could easily make the add-in available to all the Office applications. Each class's OnConnection event would be used to add a standard menu item to the host application's command bars, with the click event handled by a single class. When clicked, it would display the form and would only branch into application-specific code when the 'Insert' button was clicked to insert the selected details into the cell, paragraph, field, presentation or web page." OK, but although it's possible to include multiple 'Connect' classes for different apps, and stylistically that's probably a good idea, it's not necessary. In the Connection events can start with say a Select Case to cater for differing 'Application' object passed in the original OnConnection event. While this may be doable, I don't think the intent of the text was to suggest doing it that way. My understanding of adding a separate designer for each use is so the appropriate flags/headers are set up in the DLL during compile so each MSO app can know it's there AND what its startup behavior should be. What you propose suggests that Word would intuitively know the Excel designer is not a Word COMAddin, and so how does it determine where to find its COMAddin designer when it won't load an Excel COMAddin? 4. A COMAddin is the only current means to set up the ribbon in v12 or higher via code. You must provide this through the designer. Sure, I could make a separate xlsm/xlam just for that purpose, need to use the Custom UI utility and all, but that's more bother than having a designer and less secure to boot. Since the COMAddin is essentially the same as a DLL, the advantages (for me at least) are worth it. (IOW, "the juice is worth the squeeze!"<g) I don't follow what you mean by ComAddin is the only way to set up the ribbon. Actually for me I found it quite a learning curve how to figure out how to do all that, the XML and the call-backs entirely in a ComAddin (much easier in an xlam!). I stipulated 'via code'! It's actually easier to do in the COMAddin, NOT harder, because I don't need to use a separate xlam file for v12+, I don't need to use M$'s special Custom UI editor, and the xml is secure (as opposed to being in a workbook that anyone can edit as they please). I'm saying that INSTEAD of editing xml in a xlam, I pass the xml to Excel VIA CODE in the Designer. Sorry I don't follow "pass the xml". How/whereis the xml stored, how and when is it passed to Excel, then what does Excel do with it. FWIW, in the Connect class I do something like this Public Function IRibbonExtensibility_GetCustomUI(ByVal RibbonID As String) As String s = LoadResString(1001) IRibbonExtensibility_GetCustomUI = s where 1001 refers to the entire XML stored in th Resource You've answered your own question! You store it in a res; I store it in a function (as indicated below). The reason I store it this way is because <IMOit's easier to work with if I don't have to edit a res file. Another benefit to storing it in a function is that I can easily configure it for different startup scenarios. Using a res I would have to have a different one for each scenario. Keep in mind that we only get one shot at this, and thus we can't revise it after startup. I can change whatever I want on my menus/toolbars at runtime. This is purely to handle the ribbon config, which (in most cases) basically removes everything except the Addins tab. My xla merely provides the menus/toolbar used for the COMAddin's runtime UI. These have a common entry point into the COMAddin. No callbacks are used or required (but I could optionally do that if/when needed). This requires setting refs to MSO12 AND Excel12 LIBs in the VB6 COMAddin's References dialog, Indeed. That also means the if the dll is to cater for the Ribbon it means having different versions of the dll for 2000/3 and 2007+ (otherewise could simply set the reference to the lowest Excel version). Not really! I use the same DLL for all versions. Since the earlier versions don't recognize IRibbonExtensibility then it's ignored (without raising an error). What reference context are you referring to here? and using the special interface designed for this purpose: Implements IRibbonExtensibility and using this function to load the xml: IRibbonExtensibility_GetCustomUI(ByVal RibbonID As String) As String IRibbonExtensibility_GetCustomUI = SetRibbonXML End Function SetRibbonXML is basically the same as what would be in a xlam's xml. In summary: The COMAddin handles all aspects of the UI except menus/toolbar. The XLA only handles menus/toolbar creation/removal. (for now) No separate xlam required for v12+. Sorry I'm confused again, in v12 do you have an XLA(m). If the ComAddin already handles the entire Ribbon why do you need old style menus & toolbar in v12 (in the Addins tab). The COMAddin handles UI setup, which includes the ribbon. I do not have a separate xlam for v12. I use the same xla for all. In the earlier versions, my toolbar has IsMenubar set to TRUE so that the instance is only using my menus/toolbars. To duplicate this in v12 I need to get rid of the entire ribbon except for the Addins tab because that's where Excel put the menus/toolbars created by the xla. This then, mimics that the instance is only using my menus/toolbars. So in practice, why would I want to have separate COMAddins or XLAs for each when I have a working solution that does it all with one of each? Less to distribute, maintain and service! I'd be happy to help you in any way I can if you have interest in exploring this further. Essentially, besides a lot of back-and-forth with Rob, my main learning resources we Professional Excel Development Excel 2007 VBA Developer's Reference I got the orignial version when it first came out, maybe I should look at the 2007 version! and so I highly recommend these. Though, I suspect you may already have them.<g Most of the back and forth with Rob concerned issues relavent to my goals that weren't covered in either book. I also studied Ron de Brun's materials to get ideas for setting up different UI scenarios. (Ditto the recommend/suspect you already have...) Ron's site is a valuable resource (thanks Ron). I confess though I am still not fully up to speed with the Ribbon )-: I'm also confused, you say your xla handles all the menus in 2000/3 (even though can be done without an xla), yet in v12 you go the hard way and do it all in the ComAddin without an xlam. Nope! I'm saying I use the same xla for all versions. In earlier vers, my menus/toolbar are basically setup like dictator apps. In v12+ they appear on the Addins tab, which is the only tab I make available at runtime once the COMAddin passes the xml to Excel via the above function. I'm probably being real dumb here but if all your menus etc are in the Addins tab (ie same code to create and handle them as in previous versions), why do you need to be concerned with the Ribbon, Implements IRibbonExtensibility etc. Bear in mind that all my apps now use their own instance of Excel, except in cases where the client wants to add functionality to their default instance. In those cases I will usually provide a xla because that's likely going to be the only format they'll be able to access the source code in (if need be). Since they pay for it, it's their property. Every client, by default, has a VBIDE that they can use to access that source code. This is not likely to be the case for a VB6 COMAddin! I guess your app's are designed with very specific purposes in mind. Absolutely! Most of what I'm doing is task-driven and tailored to user-defined specifications. I also implement the same structure for my proprietary apps, though I haven't yet got them all converted over from workbook-based addins. I'd like to reach a point where I'm not dependant on M$O apps, but it's hard to do when clients insist on having that. The release of v12 and the ribbon concept is what set me off about continuing developing for M$O Excel. I plan to sever my proprietary apps over time, but may not also. (I have Farpoint Spread ActiveX, and so have already began the switch!) So are you saying are saying in v12 there is no VBA as the ribbon menu is entirely handled in the ComAddin. Or all the menu, callbacks etc is handled by VBA in an addin, if so I still done't follow why the need to install the ComAddin (vs simply an aX dll with xla wrapper). I'm saying that in any version there is no VBA in my xla other than whats necessary to create/remove the menus/toolbar. That is the only purpose (for now) of using the xla at all. I haven't got the mechanics for using the table-driven methodology figured out yet for doing this in VB6, but working on it so as to (eventually) not need XLAs. Once I resolve that part it's all going to be packaged inside the COMAddin for all x32 versions. Ah, so it's just a matter of not yet getting around to moving the old-style toolbar creation and OnAction entirely into the ComAddin. Basically, that's correct. As for calling the menus/toolbar creation 'old-style', <IMO Rob's table-driven system is 'state-of-the-art' compared to any others I've seen. So in terms of speaking 'old-style', constructing these line by line in a sub/function better qualifies as 'old-style', ..I would think! <snip No, that's not entirely true. If we create our menus for v12+ in our COMAddin then WE HAVE NO CHOICE but to implement a mechanism to handle hooking, callbacks, and icon images. Using the xla to manage our menus/toolbar obviates all need for that. They have a single entry point into our COMAddin. The COMAddin handles all its internal messaging as well as communications with/from Excel as per normal. So things like error handling and normal Excel events can be done by our COMAddin in a usual manner. Back to the my first thoughts again, apart from the Threading aspect there doesn't appear to be any need for the aX dll to be a ComAddin, right? Though I'm still unclear why you have some Ribbon stuff in a v12 version. You have just stated the primary reason why the DLL has to be a COMAddin. AFAIK, this is the only vehicle by which we can customize the ribbon via code. Otherwise, using an ordinary DLL would require also using separate addin workbooks. This will not fly with my security obsessed clients. I might use regular DLLs to add extensibility to my core apps if users want to enhance it with features/functionality specific to their use of my product. Since this use will vary between clients, I offer this by way of user-defined Plugins. If the app is Excel-based then it could also be provided as a xla that updates my menus/toolbar with their menus. In this respect, my addins also host other addins. This is harder to do in a VB6 project because VB doesn't have any built-in mechanism for hosting addins. The best solution to mimic this that I've been able to come up with so far is to call a DLL that displays a Form with menus/toolbar that looks and behaves like a floating toolbar. What I want is to be able to add menus to my menubar same as Excel does when we add menus to its menubar. Progress to this end is doubtful since VB is waining fast as a supported development language.<g I want an approach that offers me advantages as a developer while also catering to client needs. I just don't see any sense in doing lots of extraneous work to do simple things. I'm productivity oriented in my thinking and so I try to reflect this in my work. I'm in the business of creating productivity solutions for others, and so that mindset causes me to use that thinking for myself, too. The XLA commandbar builder utility has its own error handling and shutdown cleanup, and so our COMAddin doesn't need to be concerned with any of that either. Think I know roughly what you have there but I've never found it a problem to do entirely within VB6, and without cells to lay out the menu details I guess if you're used to doing this line by line in code then there's no change from normal for you. Line by line is how I started and converted to table-driven methodology as soon as I saw my first example. I can do line by line in a manner that's easy enough to follow and maintain, but it's going to be hard (now) for anyone to convince me that a table-driven system isn't a measureably better way to do this. Rob said he used to do commandbar building from a text file. I might do something similar with ADO and dump the file contents into a grid on a form, and just modify the code to walk through the grid as it now does a spreadsheet. Seems simple enough! Unless you've got a vast number of buttons simply a few har coded arrays. Or read from a text file. Well, I think storing the table data in a text file is the way I'll do it. I think using ADO to read that data as a recordset and dump it into a grid control might be best since the bBuildCommandbars routine is already design to walk through a table. I don't want to 're-invent the wheel'. I was thinking I'd move the code into the form class so it's an encapsulated component that I can drop into any VB6 COMAddin or Plugin.dll project so it serves both. Bear in mind that this is used only when complex menu structures are needed. Since I use it to modify built-in menus and create my own toolbars and popups, I'd like to have that flexibility for both core apps and Plugin DLLs. Likewise the button images (if you use custom ones) have to go somewhere. Yes they do. They (if custom) currently exist on the worksheet table for building menus/toolbar. Once I figure how I want to duplicate this table-driven methodology inside a VB6 COMAddin I will move them into a res. Meanwhile, all works flawlessly as expected to work and so no interuption for getting solutions to my clients. Everything we do can always be updated (or replaced with a newer ver). That of course is the main thing! One thing I did not elaborate on is that my apps use their own instance of Excel. I guess that means I'll be disclosing some of my app architecture... Automating my own instance allows me to lock it down however I want, design whatever UI elements I want, and prevent users from using my instance for any other purpose than what it was designed for. (As said previously, some clients don't even realize they are using Excel!) Implementing this requires using a VB 'FrontLoader.exe'. This does all kinds of tests before it even allows startup, and if all tests pass it creates our instance, loads the COMAddin, and passes a 'ValidStartup' flag so the user can access functionality via the menus/toolbar. some tests include making sure the respective MSO apps are installed, making sure all required files/runtime components are available, and license validation. What I mean by 'loads the COMAddin' is that its StartupBehavior in the designer is deliberately set to '(None)' so that it doesn't appear in any Excel dialogs, or if manually added to a user instance it will not persist. Additionally, the COMAddin will not execute any internal code without the var gbValidStartup (flag) set to TRUE. This flag is an encrypted key that the frontloader passes after loading the COMAddin. Since COMAddins are a DLL just like any other DLL, its methods can be accessed at any time by any app. The only way to prevent unauthorized use is to restrict execution of its methods by any other means than our project provides. (As I said, some of my clients are overly security cautious; some are obsessed!) One side benefit of this to me is I can use that to turn off access for any reason (like payment not received within 30 days, for example). So by building this capability into the project's structure it makes it easy to say to clients: "Try it out for 30 days. Let me know if you want any changes made within that trial period. Otherwise, I expect to be fully paid if you plan to continue using it!". Kind of puts my time and effort at risk somewhat, but it hasn't been fruitless so far, thankfully.<bg This feature is controlled by my licensing methodology, and so is included in the frontloader pre-satartup tests. Interesting But on the limited time use don't you still need some sort of license/key combination. Yes, that's true. I have a VB6-based licensing methodology worked out that can I use for both workbook-based addins and VB6 apps/dlls. I assume that your use of 'key' means the typical delimited serial-style text string used by many. I don't use these because for the amount of data that I store in a license code, they would be a paragraph rather than a limited length string of characters. Instead, I ship a License_Activator.exe that runs from within the app folder. Finally, the key to understanding why I use the xla at all is this: I fully develop in VBA in the earliest version expected to be used. since the commandbar builder components already exist, I just export all other mod/cls components to import into a COMAddin shell and go from there. All coding uses fully qualified refs (even with XL globals) so the only chore after importing the code is replacing any instances of 'Application' with my global var used for that (appXL). This only accounts for non-VB6 specific code. I continue development from their using the COMAddin from within whatever version I want to dev/test in, via a PERSONAL.XLS proc that mimics the frontloader startup routines. (Another side benefit of setting its StartupBehavior as mentioned) FWIW I find if the reference is set to v9 and it compiles it should work for all versions. For specific later version stuff say with the range object, Dim objRng As Object (not range), then objRng.NewMethod will also compile. So no real need to develop in VBA at all. That said, a lot of things need to be rewritten in v12 irrespective of the reference issue. Hmm! I think I prefer to develop for later version stuff in that version and using normal references as pertains to each. I guess it's just my nature to always be testing as I go, and so having multiple versions installed on my dev machine provides me the convenience to have instances of each version running simultaneously. In order to test a COMAddin we have to close all instances of Excel, compile it, reopen all instances of Excel to test. Again, why take the long way around when the code is portable between the two? All code is made version-aware as required (by default). I can move from one ver to the other on my dev machine because I have it set up (currently) with v9 to v12 (no VM). I no longer dev for v9 and so I have 3 'clean' test machines (2x XPx32, 1 Win7x64) for stand-alone version testing. I did have a Vista machine but not long enough to bother setting it up as a test unit because I hated it and so gave it away to my daughter for business use. So, Peter, there you have it! Lots to digest, huh? Hey, I got nothing but time on my hands and so this is the sort of stuff I do with it. Yep, a lot there and thanks. Sure! Bear in mind that the approach I've discussed here is my purposed method to mimic using dictator style addins as efficiently as possible across all versions. This does not discount that what we would do for a normal Excel addin isn't viable. I would be doing those same things the same way in those cases where my users want my solutions available in their standard instance of Excel. I guess because I was forced to take this approach to serve client needs, I've had to travel down a not too well travelled path to get here. I don't mind sharing that, or even hearing suggestions that might be an improvement. I'm still a bit confused though about what's in the xla & the dll, especially re v12 Ribbon stuff. Well, I certainly was confused about ribbon stuff too at first. I hope our dialog leaves you a bit less confused now, though. I guess what it ultimately boils down to is what you're trying to achieve in the end. In using workbook-based addins there's no choice if we want to customize the ribbon because we have to do that within the workbook xml. Bob Phillip's example is the best I've seen yet for handling that. Using automated instances just gives us more flexibility and overall control over tailoring the UI for our purposes<IMO because we're not messing with the user's default instance. (<FWIWI strongly support the notion to NEVER hijack a running instance for any reason whatsoever) Regards, Peter T regards, Garry |
How to map a shortcut key to a routine in COM AddIn for Excel 2007
"GS" wrote in message I agree sorting large xml in a resource is not the easiest but I've made my own little app to put the xml and anything else into a resource file. It is a bit of hassle, though could easly read the xml from say a text file. You say your strore the xml in a function 'below', I can't see it for looking and still not clear if you keep the xml in the VB6 (if so how) or somewhere else. Here's my OnConnection routine: IRibbonExtensibility_GetCustomUI(ByVal RibbonID As String) As String IRibbonExtensibility_GetCustomUI = SetRibbonXML End Function where SetRibbonXML is a function that assembles and returns the xml. This function is located in the designer as well, and is where I can edit it as desired for the intended use. I use a COMAddin shell template as the starting point for each new app I'm working on. Having the xml here is just a convenience. I could store this in a text file and just insert/edit it for each configuration, but why bother going through the steps when I can just leave it in there to begin with. The xml has every possible customization I would like use and so it's merely a matter of commenting out anything I won't use for any particular project. OK, effectively we do the same. Yet you do that without the v12 reference (from what you say below). This is purely to handle the ribbon config, which (in most cases) basically removes everything except the Addins tab. My xla merely provides the menus/toolbar used for the COMAddin's runtime UI. These have a common entry point into the COMAddin. No callbacks are used or required (but I could optionally do that if/when needed). This requires setting refs to MSO12 AND Excel12 LIBs in the VB6 COMAddin's References dialog, Indeed. That also means the if the dll is to cater for the Ribbon it means having different versions of the dll for 2000/3 and 2007+ (otherewise could simply set the reference to the lowest Excel version). Not really! I use the same DLL for all versions. Since the earlier versions don't recognize IRibbonExtensibility then it's ignored (without raising an error). I don't follow that at all. If the Office ref is v11 or less the following DO NOT compile Correct! You must set the ref to MSO12 and XL12 LIBs. It doesn't matter what you select in the designer setup. These refs need to be done via ProjectReferences dialog. Of course, you must have v12 installed on the dev machine to do this. But for your purposes you don't set the v12 refs as you just have the one ComAddin for all versions, right? As with any other addin, Excel will adjust any version refs to the running instance's version. For example, if you dev a xla in XL9 and test it in XL12: Open the References dialog in v12 and you'll see that the Excel library listed is v12, even though you developed it in v9 and set a ref to that. So then, I'm saying to set v12 refs for using IRibbonExtensibility; set up the designer for the earliest version of Excel you expect your COMAddin to be used in and let Excel handle the version refs at runtime. Implements IRibbonExtensibility Public Function MyRibbonButton(ByVal control As Office.IRibbonControl) 'code End Function Unless I'm missing something it the dll needs to handle the Ribbon and call backs it means making entirely separate versions for 2000/3 and 2007+, with references to Office/Excel v9 & v12 respectively. Not true, as per my above comments! You are right, though, if using custom menus on the ribbon. In this case a class for this is required in the COMAddin. This is outlined fairly well in Excel 2007 VBA Programmer's Reference. Alternatively, there's no problem for a ComAddin with the v9/2000 ref's to work in 2007 providing it only uses old-style commandbar type buttons which end up in 2007's addins tab. And this is what I do, basically. In the case of a dictator app I hide all the ribbon except for the Addins tab. Since I'm running my own instance, there are no other addins on the tab. The only difference between v12 and earlier Vs is I haven't figured out how to remove all evidence that the app is Excel for a dictator app. I do like Bob Phillips' idea of using a custom tab in place of the Addins tab, though. <snip The COMAddin handles UI setup, which includes the ribbon. I do not have a separate xlam for v12. I use the same xla for all. In the earlier versions, my toolbar has IsMenubar set to TRUE so that the instance is only using my menus/toolbars. To duplicate this in v12 I need to get rid of the entire ribbon except for the Addins tab because that's where Excel put the menus/toolbars created by the xla. This then, mimics that the instance is only using my menus/toolbars. I'm still missing something, not to worry, no doubt it'd all be obvious if I saw it. This, I suspect, would be the 'how' of implementing all this in a single COMAddin and standard xla. Let it sink in and one day you'll see how it all comes together. I will do my best to help you get there. Getting the books will go a long way toward that goal! I think I follow now what you are doing, but I was confused most of the way because one moment you were talking about things that need refs for earlier newer versins, then then the next an approach that only requires the single ref <snip <snip I might use regular DLLs to add extensibility to my core apps if users want to enhance it with features/functionality specific to their use of my product. Since this use will vary between clients, I offer this by way of user-defined Plugins. If the app is Excel-based then it could also be provided as a xla that updates my menus/toolbar with their menus. In this respect, my addins also host other addins. This is harder to do in a VB6 project because VB doesn't have any built-in mechanism for hosting addins. The best solution to mimic this that I've been able to come up with so far is to call a DLL that displays a Form with menus/toolbar that looks and behaves like a floating toolbar. What I want is to be able to add menus to my menubar same as Excel does when we add menus to its menubar. Progress to this end is doubtful since VB is waining fast as a supported development language.<g Your main ComAddin could also run a whole series of other aX dll's, some of which might display there own set of forms. If that's what you're aiming at. That's essentially what I'm stating here. Sorry if that wasn't clear. I want an approach that offers me advantages as a developer while also catering to client needs. I just don't see any sense in doing lots of extraneous work to do simple things. I'm productivity oriented in my thinking and so I try to reflect this in my work. I'm in the business of creating productivity solutions for others, and so that mindset causes me to use that thinking for myself, too. Sounds like you've got a pretty strong grip on your works Not sure I totally agree but I'll admit I have put some serious time and thought into it! I guess if you're used to doing this line by line in code then there's no change from normal for you. Line by line is how I started and converted to table-driven methodology as soon as I saw my first example. I know the table driven way but I don't have any problem 'laying out' a series of arrays of UI data which looks logical to me. I gave that a thought after you mentioned it earlier. I will look at doing this before I go the grid route. It would be just as easy (if not easier) to have the bBuildCaommandbars function iterate an array for each bar/menu. <snip FWIW I find if the reference is set to v9 and it compiles it should work for all versions. For specific later version stuff say with the range object, Dim objRng As Object (not range), then objRng.NewMethod will also compile. So no real need to develop in VBA at all. That said, a lot of things need to be rewritten in v12 irrespective of the reference issue. Hmm! I think I prefer to develop for later version stuff in that version and using normal references as pertains to each. I guess it's just my nature to always be testing as I go, and so having multiple versions installed on my dev machine provides me the convenience to have instances of each version running simultaneously. But I thouht you only wanted the one version of your aComAddin for all Excel versions, I must be misunderstanding again. Yes, that's why I have developed this approach. Not sure why you're thinking otherwise! Because of all the talk about the requirements for the v12 ref, which of course wouldn't work with earlier versions hence my confusion In order to test a COMAddin we have to close all instances of Excel, compile it, reopen all instances of Excel to test. Again, why take the long way around when the code is portable between the two? Testing a ComAddin is a bit of a pain because it needs to get the connection event, as you say it means starting a new instance of Excel each time (though no need to close all instances). A workaround is to add a temporary entry class that can be called from a small macro. Then call a public proc in the entry class to go to where ever in the dll you would normally go. But thinking about it, you are normally activating via VBA anyway, aren't you, so the ComAddin problem shouldn't exist for you, I would have thought. There's a couple of issues with testing a COMAddin: 1. We can't compile while an instance of Excel is open, whether the COMAddin is 'connected' or not because it runs 'in-process' with Excel and so Excel has a ref to it on startup. 2. We can't easily test without re-compiling every time. There is a work-around where we can set up so that Excel knows we're using the vbp rather than a dll but that's a complex issue I don't care to implement. I tried this a few times and found it's just easier to dev in VBA. If you mean need recompile the actual dll, no that's not necessary even with a ComAddin. Also, no problem to have one instance of Excel running with the ComAddin loaded (ie the compiled dll), then in the VB6 VBE press F5 (or Ctrl-F5), start a 2nd instance of Excel, and run the test in the VBE, stepping through just as you would in VBA. Regards, Peter T |
How to map a shortcut key to a routine in COM AddIn for Excel 2007
Peter T pretended :
"GS" wrote in message I agree sorting large xml in a resource is not the easiest but I've made my own little app to put the xml and anything else into a resource file. It is a bit of hassle, though could easly read the xml from say a text file. You say your strore the xml in a function 'below', I can't see it for looking and still not clear if you keep the xml in the VB6 (if so how) or somewhere else. Here's my OnConnection routine: IRibbonExtensibility_GetCustomUI(ByVal RibbonID As String) As String IRibbonExtensibility_GetCustomUI = SetRibbonXML End Function where SetRibbonXML is a function that assembles and returns the xml. This function is located in the designer as well, and is where I can edit it as desired for the intended use. I use a COMAddin shell template as the starting point for each new app I'm working on. Having the xml here is just a convenience. I could store this in a text file and just insert/edit it for each configuration, but why bother going through the steps when I can just leave it in there to begin with. The xml has every possible customization I would like use and so it's merely a matter of commenting out anything I won't use for any particular project. OK, effectively we do the same. Yet you do that without the v12 reference (from what you say below). Incorrect! What I say below is that the refs to M$O12 and XL12 MUST be set in project References, for the IRibbonExtensibility interface to be recognized IF the user happens to be running v12. All previous XL versions don't recognize this interface and so do nothing about it. This is purely to handle the ribbon config, which (in most cases) basically removes everything except the Addins tab. My xla merely provides the menus/toolbar used for the COMAddin's runtime UI. These have a common entry point into the COMAddin. No callbacks are used or required (but I could optionally do that if/when needed). This requires setting refs to MSO12 AND Excel12 LIBs in the VB6 COMAddin's References dialog, Indeed. That also means the if the dll is to cater for the Ribbon it means having different versions of the dll for 2000/3 and 2007+ (otherewise could simply set the reference to the lowest Excel version). Not really! I use the same DLL for all versions. Since the earlier versions don't recognize IRibbonExtensibility then it's ignored (without raising an error). I don't follow that at all. If the Office ref is v11 or less the following DO NOT compile Correct! You must set the ref to MSO12 and XL12 LIBs. It doesn't matter what you select in the designer setup. These refs need to be done via ProjectReferences dialog. Of course, you must have v12 installed on the dev machine to do this. But for your purposes you don't set the v12 refs as you just have the one ComAddin for all versions, right? Incorect! As stated above the refs MUST be set for project References so that IF the user's version of XL is v12+ the IRibbonExtensibility interface will be rocognized on connection. As with any other addin, Excel will adjust any version refs to the running instance's version. For example, if you dev a xla in XL9 and test it in XL12: Open the References dialog in v12 and you'll see that the Excel library listed is v12, even though you developed it in v9 and set a ref to that. So then, I'm saying to set v12 refs for using IRibbonExtensibility; set up the designer for the earliest version of Excel you expect your COMAddin to be used in and let Excel handle the version refs at runtime. Implements IRibbonExtensibility Public Function MyRibbonButton(ByVal control As Office.IRibbonControl) 'code End Function Unless I'm missing something it the dll needs to handle the Ribbon and call backs it means making entirely separate versions for 2000/3 and 2007+, with references to Office/Excel v9 & v12 respectively. Not true, as per my above comments! You are right, though, if using custom menus on the ribbon. In this case a class for this is required in the COMAddin. This is outlined fairly well in Excel 2007 VBA Programmer's Reference. Alternatively, there's no problem for a ComAddin with the v9/2000 ref's to work in 2007 providing it only uses old-style commandbar type buttons which end up in 2007's addins tab. And this is what I do, basically. In the case of a dictator app I hide all the ribbon except for the Addins tab. Since I'm running my own instance, there are no other addins on the tab. The only difference between v12 and earlier Vs is I haven't figured out how to remove all evidence that the app is Excel for a dictator app. I do like Bob Phillips' idea of using a custom tab in place of the Addins tab, though. <snip The COMAddin handles UI setup, which includes the ribbon. I do not have a separate xlam for v12. I use the same xla for all. In the earlier versions, my toolbar has IsMenubar set to TRUE so that the instance is only using my menus/toolbars. To duplicate this in v12 I need to get rid of the entire ribbon except for the Addins tab because that's where Excel put the menus/toolbars created by the xla. This then, mimics that the instance is only using my menus/toolbars. I'm still missing something, not to worry, no doubt it'd all be obvious if I saw it. This, I suspect, would be the 'how' of implementing all this in a single COMAddin and standard xla. Let it sink in and one day you'll see how it all comes together. I will do my best to help you get there. Getting the books will go a long way toward that goal! I think I follow now what you are doing, but I was confused most of the way because one moment you were talking about things that need refs for earlier newer versins, then then the next an approach that only requires the single ref <snip <snip I might use regular DLLs to add extensibility to my core apps if users want to enhance it with features/functionality specific to their use of my product. Since this use will vary between clients, I offer this by way of user-defined Plugins. If the app is Excel-based then it could also be provided as a xla that updates my menus/toolbar with their menus. In this respect, my addins also host other addins. This is harder to do in a VB6 project because VB doesn't have any built-in mechanism for hosting addins. The best solution to mimic this that I've been able to come up with so far is to call a DLL that displays a Form with menus/toolbar that looks and behaves like a floating toolbar. What I want is to be able to add menus to my menubar same as Excel does when we add menus to its menubar. Progress to this end is doubtful since VB is waining fast as a supported development language.<g Your main ComAddin could also run a whole series of other aX dll's, some of which might display there own set of forms. If that's what you're aiming at. That's essentially what I'm stating here. Sorry if that wasn't clear. I want an approach that offers me advantages as a developer while also catering to client needs. I just don't see any sense in doing lots of extraneous work to do simple things. I'm productivity oriented in my thinking and so I try to reflect this in my work. I'm in the business of creating productivity solutions for others, and so that mindset causes me to use that thinking for myself, too. Sounds like you've got a pretty strong grip on your works Not sure I totally agree but I'll admit I have put some serious time and thought into it! I guess if you're used to doing this line by line in code then there's no change from normal for you. Line by line is how I started and converted to table-driven methodology as soon as I saw my first example. I know the table driven way but I don't have any problem 'laying out' a series of arrays of UI data which looks logical to me. I gave that a thought after you mentioned it earlier. I will look at doing this before I go the grid route. It would be just as easy (if not easier) to have the bBuildCaommandbars function iterate an array for each bar/menu. <snip FWIW I find if the reference is set to v9 and it compiles it should work for all versions. For specific later version stuff say with the range object, Dim objRng As Object (not range), then objRng.NewMethod will also compile. So no real need to develop in VBA at all. That said, a lot of things need to be rewritten in v12 irrespective of the reference issue. Hmm! I think I prefer to develop for later version stuff in that version and using normal references as pertains to each. I guess it's just my nature to always be testing as I go, and so having multiple versions installed on my dev machine provides me the convenience to have instances of each version running simultaneously. But I thouht you only wanted the one version of your aComAddin for all Excel versions, I must be misunderstanding again. Yes, that's why I have developed this approach. Not sure why you're thinking otherwise! Because of all the talk about the requirements for the v12 ref, which of course wouldn't work with earlier versions hence my confusion I suppose it's indeed confusing since we realize we only need the v12 refs for use with v12. Fact is, the earlier versions don't recognize the IRibbonExtensibility interface and so they just ignore those refs. There's no penalty to have them there in this case <AFAICT. In order to test a COMAddin we have to close all instances of Excel, compile it, reopen all instances of Excel to test. Again, why take the long way around when the code is portable between the two? Testing a ComAddin is a bit of a pain because it needs to get the connection event, as you say it means starting a new instance of Excel each time (though no need to close all instances). A workaround is to add a temporary entry class that can be called from a small macro. Then call a public proc in the entry class to go to where ever in the dll you would normally go. But thinking about it, you are normally activating via VBA anyway, aren't you, so the ComAddin problem shouldn't exist for you, I would have thought. There's a couple of issues with testing a COMAddin: 1. We can't compile while an instance of Excel is open, whether the COMAddin is 'connected' or not because it runs 'in-process' with Excel and so Excel has a ref to it on startup. 2. We can't easily test without re-compiling every time. There is a work-around where we can set up so that Excel knows we're using the vbp rather than a dll but that's a complex issue I don't care to implement. I tried this a few times and found it's just easier to dev in VBA. If you mean need recompile the actual dll, no that's not necessary even with a ComAddin. Also, no problem to have one instance of Excel running with the ComAddin loaded (ie the compiled dll), then in the VB6 VBE press F5 (or Ctrl-F5), start a 2nd instance of Excel, and run the test in the VBE, stepping through just as you would in VBA. That would be the work-around I speak of. Problem is that it can be a little dicey when an events handler is in play, same as if testing/editing a VBA project with an events handler running. It's far more productive and much less confusing to dev in a single instance via VBA, and just test the COMAddin afterwards. As I said, at this point it's usually just an exercise in confirming that the code was correctly structured to be used in the VB6 project. What might be hard to grasp is that most folks who dev in VBA do so with a VBA coding mindset. I do so with the mindset of how the code needs to be structured for use from a VB6 COMAddin. Keep in mind that when we code in VBA we take a lot for granted. For example, we can ref a Range object without specifying its parent object hiearchy. We can't do that in VB6! Regards, Peter T Garry |
How to map a shortcut key to a routine in COM AddIn for Excel 2007
Sorry Gary but we seem to be going round in circles here, let's start afresh
with what it is I'm missing (sorry if you've tried to make it clear a dozen times!) 1. If the dll is to contain things like Implements 'IRibbonExtensibility' we both agree it must have the reference set to v12. 2. If the dll has the ref to v12 it will not work in earlier versions. To work in all of 2000,2 & 3 it must have the ref set to v9. That means to cater for all versions it means must make two dll's, with ref's v9 & v12 respectively. 3. You have repeatedly said you only make one ComAddin for all versions 2000 to 2007 (with an xla wrapper) It would seem at least one of the three statements is mutually exclusive. Or are you saying you make a single dll with the ref to v12, but because when in use with an earlier version no v12 stuff gets called therefore all works fine. Regards, Peter T PS yeah on the testing stuff, use whatever way suits you best, test in VBA and/or VB6 |
How to map a shortcut key to a routine in COM AddIn for Excel 2007
Peter T formulated on Tuesday :
Sorry Gary but we seem to be going round in circles here, let's start afresh with what it is I'm missing (sorry if you've tried to make it clear a dozen times!) 1. If the dll is to contain things like Implements 'IRibbonExtensibility' we both agree it must have the reference set to v12. The ref I speak of here is what we set in ProjectReferences, and must be for M$O12 Lib and Excel12 Lib. (Not to be confused with what version we set in the designer setup, which should be the earliest version we expect our COMAddin to be used with) 2. If the dll has the ref to v12 it will not work in earlier versions. To work in all of 2000,2 & 3 it must have the ref set to v9. That means to cater for all versions it means must make two dll's, with ref's v9 & v12 respectively. Incorrect! Since the earlier versions don't recognize the IRibbonExtensibility interface, they just ignore the code and so the ProjectRefs as well. No error is raised since the code that uses the interface is never executed by the earlier versions. 3. You have repeatedly said you only make one ComAddin for all versions 2000 to 2007 (with an xla wrapper) It would seem at least one of the three statements is mutually exclusive. Or are you saying you make a single dll with the ref to v12, but because when in use with an earlier version no v12 stuff gets called therefore all works fine. Exactly! Regards, Peter T PS yeah on the testing stuff, use whatever way suits you best, test in VBA and/or VB6 Sorry if I've been unable to make it clear in whole. I guess the confusion is mostly because I tried to answer each of your comments/questions in context. Given that you were confused about some of the stuff you were asking, I finally started qualifying your statements as 'correct' or 'incorrect' in hopes that things became more clear to you. Obviously it worked because points 1,3 pretty much summarize the COMAddin's structure. I wasn't sure it would work, myself, but I tried it out. Result is it works really well! regards, Garry |
How to map a shortcut key to a routine in COM AddIn for Excel 2007
"GS" wrote in message
... Peter T formulated on Tuesday : Sorry Gary but we seem to be going round in circles here, let's start afresh with what it is I'm missing (sorry if you've tried to make it clear a dozen times!) 1. If the dll is to contain things like Implements 'IRibbonExtensibility' we both agree it must have the reference set to v12. The ref I speak of here is what we set in ProjectReferences, and must be for M$O12 Lib and Excel12 Lib. (Not to be confused with what version we set in the designer setup, which should be the earliest version we expect our COMAddin to be used with) 2. If the dll has the ref to v12 it will not work in earlier versions. To work in all of 2000,2 & 3 it must have the ref set to v9. That means to cater for all versions it means must make two dll's, with ref's v9 & v12 respectively. Incorrect! Since the earlier versions don't recognize the IRibbonExtensibility interface, they just ignore the code and so the ProjectRefs as well. No error is raised since the code that uses the interface is never executed by the earlier versions. 3. You have repeatedly said you only make one ComAddin for all versions 2000 to 2007 (with an xla wrapper) It would seem at least one of the three statements is mutually exclusive. Or are you saying you make a single dll with the ref to v12, but because when in use with an earlier version no v12 stuff gets called therefore all works fine. Exactly! Regards, Peter T PS yeah on the testing stuff, use whatever way suits you best, test in VBA and/or VB6 Sorry if I've been unable to make it clear in whole. I guess the confusion is mostly because I tried to answer each of your comments/questions in context. Given that you were confused about some of the stuff you were asking, I finally started qualifying your statements as 'correct' or 'incorrect' in hopes that things became more clear to you. Obviously it worked because points 1,3 pretty much summarize the COMAddin's structure. I wasn't sure it would work, myself, but I tried it out. Result is it works really well! regards, Garry OK all clear now! That said I'm surprised. Actually I've never even bothered to test a dll with the ref set to v12 in earlier versions. Reason is when I've accidentally set the ref to v11, then tried it in Excel 2000/v9 problems have occurred (not surprisingly). I suspect though it depends on what the code is actually doing. Indeed sometimes ref's to set to later version than 'used-in' do version work, but not always. Obviously if everything has always worked this way for you then if it works it works. Intuitively though I'd be nervous, and don't think I'd do it that way even if it apparently works. Given that you were confused about some of the stuff you were asking, Don't think I was ever confused about my own questions, just not fully absorbing what you had in fact said but thinking it couldn't be, hence my confusion. Anyway, thanks for being patient! Regards, Peter T |
How to map a shortcut key to a routine in COM AddIn for Excel 2007
Peter T wrote on 5/19/2010 :
"GS" wrote in message ... Peter T formulated on Tuesday : Sorry Gary but we seem to be going round in circles here, let's start afresh with what it is I'm missing (sorry if you've tried to make it clear a dozen times!) 1. If the dll is to contain things like Implements 'IRibbonExtensibility' we both agree it must have the reference set to v12. The ref I speak of here is what we set in ProjectReferences, and must be for M$O12 Lib and Excel12 Lib. (Not to be confused with what version we set in the designer setup, which should be the earliest version we expect our COMAddin to be used with) 2. If the dll has the ref to v12 it will not work in earlier versions. To work in all of 2000,2 & 3 it must have the ref set to v9. That means to cater for all versions it means must make two dll's, with ref's v9 & v12 respectively. Incorrect! Since the earlier versions don't recognize the IRibbonExtensibility interface, they just ignore the code and so the ProjectRefs as well. No error is raised since the code that uses the interface is never executed by the earlier versions. 3. You have repeatedly said you only make one ComAddin for all versions 2000 to 2007 (with an xla wrapper) It would seem at least one of the three statements is mutually exclusive. Or are you saying you make a single dll with the ref to v12, but because when in use with an earlier version no v12 stuff gets called therefore all works fine. Exactly! Regards, Peter T PS yeah on the testing stuff, use whatever way suits you best, test in VBA and/or VB6 Sorry if I've been unable to make it clear in whole. I guess the confusion is mostly because I tried to answer each of your comments/questions in context. Given that you were confused about some of the stuff you were asking, I finally started qualifying your statements as 'correct' or 'incorrect' in hopes that things became more clear to you. Obviously it worked because points 1,3 pretty much summarize the COMAddin's structure. I wasn't sure it would work, myself, but I tried it out. Result is it works really well! regards, Garry OK all clear now! That said I'm surprised. Actually I've never even bothered to test a dll with the ref set to v12 in earlier versions. Reason is when I've accidentally set the ref to v11, then tried it in Excel 2000/v9 problems have occurred (not surprisingly). I suspect though it depends on what the code is actually doing. Indeed sometimes ref's to set to later version than 'used-in' do version work, but not always. Obviously if everything has always worked this way for you then if it works it works. Intuitively though I'd be nervous, and don't think I'd do it that way even if it apparently works. Given that you were confused about some of the stuff you were asking, Don't think I was ever confused about my own questions, just not fully absorbing what you had in fact said but thinking it couldn't be, hence my confusion. Anyway, thanks for being patient! Regards, Peter T No problem! I will admit to having my own reservations about how refs work in the context you state here, and so I tend to generally agree with your apprehensions. I also have not tested this outside the machines on my network. It would be nice to get some feedback for different scenarios, though. Best of luck in your endeavors, Garry |
How to map a shortcut key to a routine in COM AddIn for Excel 2007
Hi Peter,
Just following up to see if you're making any progress. Also, to ask if you're interested in continuing discussion outside this forum? Regards, -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
How to map a shortcut key to a routine in COM AddIn for Excel 2007
Hi Gary,
I am not currently involved in writing any new VB6/Excel projects, nor was I when this discussion began, so the question of whether or not I am making progress doesn't arise. I appreciate the thought though! That's not to say of course I may not want to write more in the future, and better adapted for 2007+ than my old ones are. By all means keep in touch and if you've got something you want (lightly) testing I'll have a look. My address is lightly disguised in the "Reply to" field, assuming you are using a newsreader. Regards, Peter T PS - what's that regroup link all about ? "GS" wrote in message ... Hi Peter, Just following up to see if you're making any progress. Also, to ask if you're interested in continuing discussion outside this forum? Regards, -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
How to map a shortcut key to a routine in COM AddIn for Excel 2007
on 5/28/2010, Peter T supposed :
Hi Gary, I am not currently involved in writing any new VB6/Excel projects, nor was I when this discussion began, so the question of whether or not I am making progress doesn't arise. I appreciate the thought though! That's not to say of course I may not want to write more in the future, and better adapted for 2007+ than my old ones are. By all means keep in touch and if you've got something you want (lightly) testing I'll have a look. My address is lightly disguised in the "Reply to" field, assuming you are using a newsreader. Regards, Peter T PS - what's that regroup link all about ? "GS" wrote in message ... Hi Peter, Just following up to see if you're making any progress. Also, to ask if you're interested in continuing discussion outside this forum? Regards, -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Hi Peter, Thanks for the update! I'm impressed with the amount of detail and tenacity with which you persued our discussion, given that you weren't 'actively' working with any of its content. I will note your email address. Mine is gesansom at netscape dot net. The regroup info is (currently) where the folks from the MS VB6 NG will be migrating to after the NGs are shut down. It requires a one-time registration on the server and works same as legacy NGs as far as news readers are concerned. The general consensus was to make use of an already existing NG rather than reinvent the wheel. It currently has low traffic but this is expected to change once these M$ NGs are gone. Meanwhile, we've agreed to 'kindly' advertise the move in our posts so as not to be spamming. Regards, -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
How to map a shortcut key to a routine in COM AddIn for Excel 2007
"GS" wrote in message Thanks for the update! I'm impressed with the amount of detail and tenacity with which you persued our discussion, given that you weren't 'actively' working with any of its content. :-) The regroup info is (currently) where the folks from the MS VB6 NG will be migrating to after the NGs are shut down. It requires a one-time registration on the server and works same as legacy NGs as far as news readers are concerned. The general consensus was to make use of an already existing NG rather than reinvent the wheel. It currently has low traffic but this is expected to change once these M$ NGs are gone. Meanwhile, we've agreed to 'kindly' advertise the move in our posts so as not to be spamming. I'm still not clear what will happen once the MS public news server closes down. Depending on what 'cancel' notifications MS send out maybe things will carry on petty much as before. Those like me, whose ISP doesn't carry newsgroups, would need to find another news server, google-groups perhaps. We will see! There seems to be a French guy who might (not clear) be planning to hi-jack the entire MS public ng hierarchy (w/out MS agreement also not clear). If he does, not sure if that would be a good thing (the service continues) or a bad thing (means other mirrors will break and everyone be forced to use his). Consensus amongst those who understand these things much better than me is it would be a bad thing. I will certainly make a note of the standby reserve! Regards, Peter T |
How to map a shortcut key to a routine in COM AddIn for Excel 2007
It happens that Peter T formulated :
"GS" wrote in message Thanks for the update! I'm impressed with the amount of detail and tenacity with which you persued our discussion, given that you weren't 'actively' working with any of its content. :-) The regroup info is (currently) where the folks from the MS VB6 NG will be migrating to after the NGs are shut down. It requires a one-time registration on the server and works same as legacy NGs as far as news readers are concerned. The general consensus was to make use of an already existing NG rather than reinvent the wheel. It currently has low traffic but this is expected to change once these M$ NGs are gone. Meanwhile, we've agreed to 'kindly' advertise the move in our posts so as not to be spamming. I'm still not clear what will happen once the MS public news server closes down. Depending on what 'cancel' notifications MS send out maybe things will carry on petty much as before. Those like me, whose ISP doesn't carry newsgroups, would need to find another news server, google-groups perhaps. We will see! There seems to be a French guy who might (not clear) be planning to hi-jack the entire MS public ng hierarchy (w/out MS agreement also not clear). If he does, not sure if that would be a good thing (the service continues) or a bad thing (means other mirrors will break and everyone be forced to use his). Consensus amongst those who understand these things much better than me is it would be a bad thing. I will certainly make a note of the standby reserve! Regards, Peter T I hope to see you there! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
| All times are GMT +1. The time now is 12:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com