VBA to COM

[RM]

I am trying to convert a large VBA addin into COM DLL.
I would like to do this with minimal effort, considering i would have to
redesign the addin for Office 2007.
which do you think is the best approach.

-use Office developer version and embed VBA code
-use VSTO and rewrite the code
-use VB.NET and rewrite the code

any help appreciated.

 

[Cindy M -WordMVP-]

Hi Rm,

I am trying to convert a large VBA addin into COM DLL.
I would like to do this with minimal effort, considering i would have to
redesign the addin for Office 2007.
which do you think is the best approach.

-use Office developer version and embed VBA code
-use VSTO and rewrite the code
-use VB.NET and rewrite the code

It's not clear where you're starting from, beginning with which application
and application version are you targeting? What, roughly, should the Addin
do? What is the motivation to move to a COM DLL?

VSTO is NOT an Addin. A VSTO project is document-specific and so
inappropriate for something that should "always" be available to the user.

All other things being equal, a COM-Addin created in VB6 is "better" than
one created using managed code (.NET). Managed code needs to work through
several layers for an Office COM-Addin. Going to .NET only makes sense if
you require something provided by the .NET framework (security, for
example).

The Office Developer version hasn't been available since Office XP. The
projects can't be opened/edited in later versions of Office. Better to use
VB6.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[RM]

Hi Rm,



It's not clear where you're starting from, beginning with which application
and application version are you targeting? What, roughly, should the Addin
do? What is the motivation to move to a COM DLL?

VSTO is NOT an Addin. A VSTO project is document-specific and so
inappropriate for something that should "always" be available to the user.

All other things being equal, a COM-Addin created in VB6 is "better" than
one created using managed code (.NET). Managed code needs to work through
several layers for an Office COM-Addin. Going to .NET only makes sense if
you require something provided by the .NET framework (security, for
example).

The Office Developer version hasn't been available since Office XP. The
projects can't be opened/edited in later versions of Office. Better to use
VB6.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)


This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

I have a application level addin collection (ppa xla files) written in
VBA. we thought of moving to COM as the addin size have become very
large and it takes a long time to load.

I thought VSTO had the ability to create application level addins, and
that XP developer version compiler could be referenced to Office 11
library, please correct me if i am wrong..

if these options do not work and i have to rewrite in VB6. Is there any
resource availabel to help in this conversion?

really appreciate your help.
regards

 

[Steve Rindsberg]

I have a application level addin collection (ppa xla files) written in
VBA. we thought of moving to COM as the addin size have become very
large and it takes a long time to load.

I think Excel may not be quite as bad, but PowerPoint's housekeeping is
horrible. PPAs just grow a bit every time you save them.

Try this:

Start a new blank presentation
Open your original PPT from which you made the PPA
Export each module, form and class in the original to a file.
Switch to the new blank presentation and import all of these exported files.

Set any necessary project properties and references on the new presentation.
Save it as a PPT then as PPA.

That should help the size/load time problem.

And something similar should work for Excel - but I believe one of the MVPs has
an addin to do the "code cleaning" for you automatically. If you don't get a
link here, ask in one of the XL dev groups.

 

[Cindy M -WordMVP-]

Hi Rm,

I thought VSTO had the ability to create application level addins, and
that XP developer version compiler could be referenced to Office 11
library, please correct me if i am wrong..

That is incorrect. This capability exists currently only for Outlook (VSTO
2005). It may well be added to the next version of VSTO, to work with
Office 2007, but the Office beta is only just moving into beta 2 phase, so
it won't be available for quite a few months, maybe a year.

if these options do not work and i have to rewrite in VB6. Is there any
resource availabel to help in this conversion?

Nothing concrete that I know of. FWIW, going from VBA to VB6 isn't any big
deal IF you've worked with the object models (and not with Selection and
ActiveCell and things like that). Then you can pretty much copy/paste the
procedures and just make sure you declare an appropriate Application
object to use with the objects in the code.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[RM]

Hi Rm,



That is incorrect. This capability exists currently only for Outlook (VSTO
2005). It may well be added to the next version of VSTO, to work with
Office 2007, but the Office beta is only just moving into beta 2 phase, so
it won't be available for quite a few months, maybe a year.



Nothing concrete that I know of. FWIW, going from VBA to VB6 isn't any big
deal IF you've worked with the object models (and not with Selection and
ActiveCell and things like that). Then you can pretty much copy/paste the
procedures and just make sure you declare an appropriate Application
object to use with the objects in the code.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)


This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

Thanks guys, your suggestions were really helpful.
Steve, the recreating PPA tip really worked, i was able to reduce the
size by ~30%. Thanks again.

tc