Prev: Sort by one column then another.
Next: Figuring out MAX LEN in a given column
From: GS on 14 May 2010 14:26
> 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.
>
>>>
>>>> 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
Project>References dialog. Of course, you must have v12 installed on
the dev machine to do this.

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.
>
>
>>
>> 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.
>
> 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!
>
>>
>> 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!
>
> Obviously it works for you but still not following as explained a couple of
> sections above.
>
> <snip>
>>>>
>>>> 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!)
>
> Without M$O apps, sounds ambitious though no doubt it depends on what you are
> doing.
>
> <snip>
>>>
>>> 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!
>
> Misunderstanding, by 'old-style' I meant commandbars etc as available to
> 97-2003 vs 'new-ribbon' in 2007, nothing to do with whatever approach you use
> to create them,

Ah, I see.
>
>>>
>>> <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!
>
>>
>> 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.

Developing in VBA eliminates the above issues until I get to the point
of testing the compiled COMAddin. At that point it's pretty much a
slam-dunk that it works as expected and so this is usually just a
verification exercise. I doubt your suggestion to use a temp entry
class would be any advantage. I already have VBA macros set up to mimic
loading the COMAddin as I would via a frontloader, and this is done
from within the version of Excel that I want to test.
>
> <snip>
>
> Regards,
> Peter T

regards,
Garry


From: Peter T on 17 May 2010 10:54

"GS" <GS(a)discussions.microsoft.com> 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
> Project>References 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


From: GS on 17 May 2010 14:48
Peter T pretended :
> "GS" <GS(a)discussions.microsoft.com> 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
>> Project>References 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


From: Peter T on 18 May 2010 10:53
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


From: GS on 18 May 2010 18:55
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 Project>References, 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
Project>Refs 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


First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Sort by one column then another.
Next: Figuring out MAX LEN in a given column