Converting xla to xl COM add-in

[hooksie2]

Is an xla still rqd to call the COM add-in?

I am (still) considering my options wrt converting my vba xl add-in to
a COM add-in (c/- Office Developer). As I have never actually seen a
COM add-in I am curious to know how this would work. For example, do I
still need an xla module to add my commandbar to xl and then call the
subs/functions in the COM add-in or can I directly compile the entire
xla as it is?

Thanks a lot,
Andrew

 

[Zoo]

To make a COM add-in ,
refer to this page:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q285337

 

[hooksie2]

I had a look at that link but it is specifically talking about calling
a function from the COM addin. It was interesting but isn't what I'm
trying to do. My current xl addin just displays a number of forms,
interacts with a third party software and reads values from/ writes
values to the workbook.

Thanks,
Andrew

 

[zoo]

What you said about the forms is true.
You have to remake a nurmber of forms,since Excel forms are not compatible
with VB.
But what you said about reading/writing value from workbooks is not true.

In that link, there's the function below:
Private Sub AddinInstance_OnConnection(ByVal Application As Object, ByVal
ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As
Object, custom() As Variant)
Set oApp = Application
End Sub

This oApp variable is Application object of Excel.
You can access the values to workbooks through oApp object.
i.e. oApp.Workbooks("xxxx").Worksheets("yyyy").Cells ....
(That is equivalent to
Application.Workbooks("xxxx").Worksheets("yyyy").Cells .... in VBA)

 

[hooksie2]

Thanks for sticking with this. I still have a couple of questions:

1) From the oApp object can I access all the xl application properties?
ie. can I simply replace
Application.CommandBars("Worksheet Menu Bar").Controls.Add...
with
oApp.CommandBars("Worksheet Menu Bar").Controls.Add...

2) It seems based on the above that I only need the COM addin - I don't
need any additional vba addin (for xl2002+). However, the article
states that a vba wrapper is required for xl2000. What does this
wrapper have to do? Is it relatively simple - just calling the COM
add-in, or does it also have to handle interfaces like the above
commanbar?

2) The article example uses late binding. Is it possible to declare
oApp as Excel so that I can still see the intellisence dropdowns when
writing any additional code? If not then I guess I can just write my
code in xl vba and then copy it over. (I am hoping to use MS Office
Developer Ed. which supposedly allows me to create COM add-ins rather
than vb6. That way I won't have to re-write my forms either).

3) Apparently when designing the add-in I need to select which version
of xl it is for (eg. xl2002, xl2003...). Does this mean I have to
distribute a different version of the add-in for each xl version? If I
use an xla it can in principle work accross any xl version.

Thanks a lot,
Andrew

 

[Nick Hebb]

1) Yes.

2). I'm not familiar with this but I assume that you need a macro in
the file and the macro creates an instance of the COM object to make
the function calls. So I guess you'd need an xla that calls the COM
add-in.

2) [sic: you have two 2)'s] I create a controller class that I pass a
reference to oApp. In the controller class I declare a variable oXLApp
As Excel.Application and set it equal to oApp. Similarly, if I want to
cpature workbook or worksheet events I create variables for those using
WithEvents.

3) Develop the application with the object library from the oldest
version you need to support. Typically this would be Excel 2000 (aka
9.0). I recently had to buy a copy of it just for this reason (~$200).

Alternately, you can use late binding. If you do, use early binding in
development to get the intellisense, then switch it before release.
Also, check the Excel XP & 2003 documentation "What's New" sections to
ensure you're not calling any objects that weren't available in Excel
2000. Then test, test, test. Lastly, Excel 97 doesn't support COM
add-ins as far as I know.

Buy Professional Excel Development by Bullen et al. for further
details.

HTH,

Nick Hebb
BreezeTree Software
http://www.breezetree.com

 

[hooksie2]

Hi Nick,

Thanks for that. It looks like I should be able to achieve what I want
without too much hassle. I've just ordered Stephen Bullens' book for
good measure as well though.

Andrew