Re: VBA and VSTO

Hi Bart,

thanks again for all your effort. I just tested your suggestions. I stopped
my VB-experience because it was too difficult for some mater (mostly
variable-types vs. excel objects confusion).

I'm indeed a teacher and the program is used by teachers. Security is not a
problem with them but... it's about results of exams of pupils of 15 to 18
yrs old. If they crack the program, they can enter their own results, so
security is needed. I think I will follow your suggestions. I start with a
working xla en can afterwards build a small dll with the code for passwords
etc... so bypassing security becomes difficult.

The rebuilder of Andrew Baker is great (I started with the 14d trial version
but consider to buy it). The compiled code was not reduced by 50%, but
remains about 4,91 Mb (uncompiled, size was halved). I did use the free
codecleaner but it messed up my login-form, so I did not continue on the
cleaned project. Now, the login-form remains fine.

The single-quotes may be a reason of the crash? I wonder why this code is
working if it's wrong (it works on excel 2000 too!)? Maybe, compilation
get's rid of it? Anyway, it was hard to see such an error, but you did!

Final result: even compiled on XP, code works on a 2000-version of excel
(after cleaning the code). I think I can continue now with the real work. I
lost about 3 weeks by experimenting and confusion but now, I'm on the road
again. You spended your time to help me out of a mess. I (and in the name of
all the teachers who use my program), we will not forget that!

Many, many thanks
Jos


"RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx> schreef in bericht
news:%23aIlsgOXGHA.4484@xxxxxxxxxxxxxxxxxxxxxxx
OK, have managed now to get in and all seems to be loading nicely. This is
the 2000 .xla on XP.
Menubar loads no problem.
One thing is for sure, I wouldn't bother with VB6 yet, apart from maybe
code protection. This is
quite a complex add-in, but not that big really and I think moving this to
VB6 will be a very big job
for not that much gain.
Bypassing security is very easy, but I am not sure if that is such a big
problem with your users (primary school teachers?).

RBS


"RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx> wrote in message
news:O8IU85NXGHA.1352@xxxxxxxxxxxxxxxxxxxxxxx
Just 2 things for starters:

Firstly, both .xla files (the 2000 and the XP one) were still enormously
bloated.
Have run Andrew Baker's Workbook Rebuilder on both and both reduced by
over
50%. This is a commercial rebuilder, but for about $35 definitely worth
the money.
Not sure if you had run the freeware CodeCleaner, but if so it hadn't
worked.

Secondly, the reason the login didn't popup in the 2000 .xls file was a
simple coding error.
In the ThisWorkbook (LibraMain) Open event it should be:

If (IS_Libra) Then
CLR_Info
Application.Run "Libra.xla!LP_Before"
Else
MsgBox "Libra is niet geladen"
End If

You had added single quotes to Libra.xla and that doesn't work.
I am running the 2000 .xls file on XP and it runs nicely with the login
popping up.

All this confirms my suspicion that there are simple mistakes (plus
enormously bloated files)
explaining all your mishaps and that if these are fixed all should work
fine.

I haven't got the password, so not sure what else I can test, but if you
can mail it to me I can
see what else needs doing.


RBS




"Jos Vens" <jos.vens@xxxxxxxxxxxxxx> wrote in message
news:uGGa%232KXGHA.1084@xxxxxxxxxxxxxxxxxxxxxxx
Well, let's say I solved some problems (mistyping of some variables, and
I'm glad of doing the option explicit), but I'm not so convinced that my
problem is gone.

If it would be an XP-feature, recompilation on 2000 shouldn't solve the
problem.

I'm now testing the shift to VB, I post my progressions on that! If it
costs me too much time, I stay with VBA.

Jos

"RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx> schreef in bericht
news:ushQGRKXGHA.3448@xxxxxxxxxxxxxxxxxxxxxxx
Congratulations, so you made quite a bit of progress!
I am not sure why the version compiled on XP would crash on 2000, but I
can understand
why there would be an error as there could be a feature that is only
available on XP. Shouldn't
make it crash though.

As to moving to VB:
I would keep most code in the .xla file and only move bits to a VB6
ActiveX dll if there are
advantages in doing that, such as:
Code protection
Better VB6 forms
Some speed advantages

Not sure there is much point in moving everything to a VB6 .exe file,
but this is just a personal choice
and other posters to this NG may have other ideas about this.

I would still investigate why exactly it crashes on 2000 when compiled
in XP and if you send the .xla file
to me I will have a look.

RBS


"Jos Vens" <jos.vens@xxxxxxxxxxxxxx> wrote in message
news:%23ZxX5CKXGHA.1084@xxxxxxxxxxxxxxxxxxxxxxx
Hi,

compiled on office2000, the xla works now for every version of excel.
(but the opposite is not true: compiled on XP crashes on 2000).
Recompilation on 2000 solves the problem for all office-versions.

I would like to shift now to VB. I'd like to hear from you what my
best choice is:
- put all code in vb (exe-file). Since all my worksheets are
referenced with pointers, I think it is not so much work to do that.
Debugging is possible.
- put as much as possible code in vb (dll-file): how can I debug my
code in the dll?

Jos





"RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx> schreef in bericht
news:%23kV9vDDXGHA.3848@xxxxxxxxxxxxxxxxxxxxxxx
Hi Jos,

I would first concentrate on getting the .xla file in good shape.
Using VB6 is a good thing in any case and I would definitlely buy
that and have a play with it.
The bulk of the code will remain in the .xla though especially in
your case as you do lots of work in the ***.
Have you got the add-in working now in all Excel versions?

RBS


"Jos Vens" <jos.vens@xxxxxxxxxxxxxx> wrote in message
news:eHM3wqCXGHA.3440@xxxxxxxxxxxxxxxxxxxxxxx
Hi,

I just took a few days off, in the meantime, I read a book about VB.
I think I will switch to VB since I guess it offers much more
functionality and is more stable (works with all office-versions on
an equal way).

I still have one concern:
- I can make an activeX dll, and reference it in excel
- I can make a standard exe file, and open excel from within it.

What would you suggest. I'd rather used the dll-option, but I think
it is much complexer to debug (you cannot trace a dll?). Functions
however are immediately visible to excel.

Thanks for your interest in my problem.
Jos

"RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx> schreef in bericht
news:u0oGyL8WGHA.1480@xxxxxxxxxxxxxxxxxxxxxxx
Just wondering how you were getting on with this.
The offer to look at your workbook(s) still stands.

RBS

"Jos Vens" <jos.vens@xxxxxxxxxxxxxx> wrote in message
news:%23OKZFvQWGHA.128@xxxxxxxxxxxxxxxxxxxxxxx
Hi,

after doing al steps, problems remain:

I've exported all modules and forms, imported them in a new
project
I've set on all modules: option explicit and declared all
variables
I've compiled thet project (XP) and set it up for Office 2000. It
crashes.
I recompiled the project in Office 2000. If I save it afterwards,
excel crashes while I'm saving.

Strange things are happening. I don't see any reason to not move
to VB6. It would be nice to have an EXE-file to start up, in stead
of a dll, adding to excel. Can I call a procedure of an exe-file
of VB in VBA (the event-cycle must stay in VBA I guess, so I would
like to call procedures in VB).

Thanks,
Jos

"RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx> schreef in bericht
news:e1UC4SrVGHA.5916@xxxxxxxxxxxxxxxxxxxxxxx
I wouldn't worry too much about that for now.
I think the main thing is to be able to compile the project with
Option Explicit in each and every module.
I have had some modules of those sizes and no problem at all.
I noticed you had lots of modules that were very small, so you
should be able to put modules together.
Still, worry about the other thing first.

RBS

"Jos Vens" <jos.vens@xxxxxxxxxxxxxx> wrote in message
news:OvW3%230qVGHA.6048@xxxxxxxxxxxxxxxxxxxxxxx
Hi,

after exporting everything, I have three critical modules: 87k,
75k and 64k. I split them, but so the number of increases...

Jos


"RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx> schreef in
bericht news:%23LiI9%23jVGHA.1356@xxxxxxxxxxxxxxxxxxxxxxx
Here is some code that will export all your modules, to look at
the size of them.
This doesn't seem the main problem, but interestingly your
module Libra_Toolbar is 74 kB.

You will need to set a reference to
Microsoft Visual Basic for Applications Extensibility
to run this code:

Sub ExportAllVBA()

'to export all the modules and forms to files
'--------------------------------------------

Dim VBComp As VBIDE.VBComponent
Dim Sfx As String
Dim strExportFolder As String

strExportFolder = "C:\Libra\Modules\"

'so overwrite old files without warning
'--------------------------------------
Application.DisplayAlerts = False

On Error Resume Next
MkDir strExportFolder
On Error GoTo 0

For Each VBComp In ThisWorkbook.VBProject.VBComponents
Select Case VBComp.Type
Case vbext_ct_ClassModule, vbext_ct_Document
Sfx = ".cls"
Case vbext_ct_MSForm
Sfx = ".frm"
Case vbext_ct_StdModule
Sfx = ".bas"
Case Else
Sfx = ""
End Select
If Sfx <> "" Then
VBComp.Export _
Filename:=strExportFolder & VBComp.Name & Sfx
End If
Next VBComp

Application.DisplayAlerts = True

End Sub


RBS


"Jos Vens" <jos.vens@xxxxxxxxxxxxxx> wrote in message
news:uLjX8RfVGHA.5588@xxxxxxxxxxxxxxxxxxxxxxx
Hi Bob,

you can try it yourselve: I stripped a lot of code and placed
it on my website. You can download Libra and extract it. You
start it up with Libra.xls which opens Libra.xla in the
Programma folder. It is compiled with office 2000 first
release, so in that case, it does not crash (in office 2000,
it never crashes on any servicepacks)

So the assumption of 100 modules is not right here, because I
deleted many modules and almost all forms. Maybe something
must be corrupted but the CleanProject did not resolve
anything...

http://www.puntenboek.be/Download/Crash.zip


Jos

"Bob Phillips" <bob.phillips@xxxxxxxxxxxxxxxxxxxx> schreef in
bericht news:uRLlf$eVGHA.4952@xxxxxxxxxxxxxxxxxxxxxxx
If that is the case, you should be able to strip it down to
something much
simpler than causes these crashes. Could you do that and then
offer the code
for review?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jos Vens" <jos.vens@xxxxxxxxxxxxxx> wrote in message
news:us7irJeVGHA.4764@xxxxxxxxxxxxxxxxxxxxxxx
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" <jos.vens@xxxxxxxxxxxxxx> schreef in bericht
news:uvxoCCcVGHA.5900@xxxxxxxxxxxxxxxxxxxxxxx
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



"Bob Phillips" <bob.phillips@xxxxxxxxxxxxxxxxxxxx> schreef
in bericht
news:eIFbDYbVGHA.2444@xxxxxxxxxxxxxxxxxxxxxxx
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" <jos.vens@xxxxxxxxxxxxxx> wrote in message
news:O1B0EUbVGHA.736@xxxxxxxxxxxxxxxxxxxxxxx
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" <chip@xxxxxxxxxxxx> schreef in bericht
news:uuJmpPYVGHA.1728@xxxxxxxxxxxxxxxxxxxxxxx
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



"Jos Vens" <jos.vens@xxxxxxxxxxxxxx> wrote in message
news:ubE1QEYVGHA.5020@xxxxxxxxxxxxxxxxxxxxxxx
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).


































.

Loading