VBA and VSTO

[Jos Vens]

Hi,

since I have serious problems in VBA, I consider to migrate to VSTO. I have
some questions about that, in order of importance:

1. Is my code in VBA in a high level portable to the new platform
2. Can I compile the project to an executable stand-alone file (.exe) so I
am not dependent to what the user have installed of dll's
3. Does it work with all versions of Office (starting with 2000)

Thanks for your help.
Jos Vens

PS my big problem in VBA is the difference of the mso.dll (mso9.dll for
office 2000) of the different office versions, which causes a crash on
startup when I build up my menu-bar. Recompilation on the specific excel
version solves the problem, but then I have to maintain at least 3 versions
(2000, XP and 2003). Even subversions (Service Packs and minor upgrades
cause those commilation problems so I cannot keep that much versions).

 

[Chip Pearson]

Assuming you know VB.NET,

1. Is my code in VBA in a high level portable to the new
platform

Only you can answer that question.

2. Can I compile the project to an executable stand-alone file
(.exe) so I am not dependent to what the user have installed of
dll's

With VTSO, you end up with a workbook and a managed code DLL.

3. Does it work with all versions of Office (starting with
2000)

No. 2003 only.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Bob Phillips]

But going back to your VBA problem, if you develop on your lowest deployment
version of Excel, that should work on all later versions. No need for 3
versions. I don't have 3 versions, and I bet Chip never does.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Chip Pearson]

versions. I don't have 3 versions, and I bet Chip never does.

I have 5 versions of Excel on my main box.

 

[Bob Phillips]

I meant of code Chip, not Excel <g>

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Jos Vens]

Hi Bob,

I try it out! You mean I should develop in office 2000 then...

I always did in XP, but maybe I did an upgrade and then it didn't work
anymore. It is also rare that my code of XP also crashes on office 2003!

Thanks
Jos

 

[Jos Vens]

Hi Chip,

I mean, I developped in VBA, and I don't want to spend to much time to set
my code in vb.net. Is the language comparable or do I have to learn a lot
(and debug a lot) to get my code back in vb?

I never used VB.NET! I assume you mean I cannot copy and paste my code from
vba to vb.net.

Jos

 

[Chip Pearson]

You'll find the syntax of VB.NET familiar if you know VBA, but
VB.NET is vastly larger and more complicated than VBA. Some code
will copy/paste, but most will require some degree of rewrite.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Bob Phillips]

Exactly Jos, that is what I mean.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

If you are deploying to Excel 2000 and Xp as well as 2003, VB.Net isn't
really an option anyway.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Jos Vens]

I know,

but if VSTO is stable, I can convince my customers to migrate to 2003, now
it's a mess: my excel application crashes on almost every machine, unless it
is upgraded to the right version (like for XP is it 10.6789.6735 SP3). All
other versions are crashing, unless I recompile. I've built a compiler (I
use the sendkeys method to unlock the project etc etc... It worked, but on
2003 I couldn't save it anymore - an error on saving appeared for an unknown
reason).

Thanks for all your suggestions!
Jos Vens

 

[RB Smissaert]

The simplest might be to have a good look at the code that
builds the menu bar and see if it can be altered.
Maybe you can do with a totally different way to let your
users access your application, without a menubar.
What bits of code cause the problem?

RBS

 

[RB Smissaert]

an error on saving appeared for an unknown reason

Forgot to say: Maybe the code cleaner can solve this problem:
http://www.appspro.com/Utilities/CodeCleaner.htm

RBS

 

[Niek Otten]

I don't think it is wise to take refuge in a new and unknown tool because the old one causes you problems. I really think you
should try to find out what's wrong with the present application. Only if you find out that there is something that VBA can't do
correctly and VSTO can, you could consider to migrate.
But I don't think that will happen; too many people manage get things right in VBA, albeit with some trouble sometimes.
I don't know what you mean exactly with "I've built a compiler " but it makes me shiver. Don't! Use standard and proven tools
whenever you can!

Just my opinion.....

 

[Jos Vens]

Thank you all for your reactions. Let's explain a bit more:

I just tried Bob's method: I opened my application in Office 2000 first
edition. Compiled it. Worked fine.
Then I opened it in office XP, crashed on startup.

I think Bob means: all commands of 2000 will work on XP and 2003. Yes, I
know, but thats not the problem. I use only 2000 commands. The problem is
the compilation. If I migrate from 2000 to XP or 2003, my compilation is
gone. I know that because if I open the VBE-environment, the menu-item to
compile is black, not greyed out, saying my project is not compiled yet. If
I compile then, It works fine.

What have I figured out by now: it is the mso.dll that causes the problem. I
build my menubar on startup, and there it crashes. If I use the mso.dll from
the excel version I have compiled with (by replacing the installed one), I
have no problem.

I know my project loses it compilation if it encounters another mso.dll. I
also know that if I forget to compile, my project crashes. So If I compile,
and go to another excel-version, compilation is gone and my project crashes.

Thanks, more suggestions are welcome!
Jos Vens

 

[Jos Vens]

Hi,

I tried the code cleaner, without result. Sorry.

Jos

 

[Jos Vens]

Hi Niek,

I agree to you, but only if the problem is code. Now, the problem is not
because of my code, but because of an internal dll in excel.

I find vba becomes inpredictable, and that is scarring me. I have had
thousands of workaronds and solutions to resolve problems, but this one is
beyond my possibilties, it is a microsoft problem, not my program (see other
response for more explanation).

Jos

 

[Jos Vens]

Another suggestion: can my project be too big? it is now more than 5 mb?

Jos

 

[RB Smissaert]

I think it is not so much the size that matters, but the total number of
modules; that is form modules, normal modules and class modules. My
impression is that if this number goes above 100 things can go pearshaped.
Maybe it might be an idea if you are not doing that already to move code to
VB6 ActiveX dll's.
The other thing is that if the size of exported modules goes to 64 Kb there
can be problems, although I don't think
that that is as important as the 100 modules.
Obviously if the total size is > 5 Mb then it might be difficult not to get
above 100 modules and keep the exported
size below 64 Kb and in that case I definitely would go for the 100 or less
modules.
In any case I would move code to VB6.

RBS

 

[RB Smissaert]

It may be worthwhile to explain what this application is about. Is it an
..xla add-in?
In that case you beat me as I thought that mine possibly was the biggest
commercial
..xla add-in at 3.9 Mb with all the non-code bits completely stripped out.
I started to get major troubles with > 100 modules, but now running perfect.

RBS