VBA and VSTO

[Jos Vens]

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]

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 sheet.
Have you got the add-in working now in all Excel versions?

RBS

 

[Jos Vens]

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

 

[Jos Vens]

Hi,

I made two links:

http://www.puntenboek.be/Download/Libra2000.zip -> Libra.xla compiled on
office 2000
http://www.puntenboek.be/Download/LibraXP.zip -> Libra.xla compiled on
office XP

Structure:

Libra.xls must be opened on root folder and opens Libra.xla in
programma-folder (menu.xls is opened as well to build the menubar).

The 2000-version does not open a login-form on office XP. You can choose the
Menu Libra>Login to accomplish this.

It's for those rareties that I loosing my confidence in vba over the
different versions...

Thank you!
Jos

 

[RB Smissaert]

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

 

[RB Smissaert]

Will have a look tonight.

RBS

Hi,

I made two links:

http://www.puntenboek.be/Download/Libra2000.zip -> Libra.xla compiled on
office 2000
http://www.puntenboek.be/Download/LibraXP.zip -> Libra.xla compiled on
office XP

Structure:

Libra.xls must be opened on root folder and opens Libra.xla in
programma-folder (menu.xls is opened as well to build the menubar).

The 2000-version does not open a login-form on office XP. You can choose
the Menu Libra>Login to accomplish this.

It's for those rareties that I loosing my confidence in vba over the
different versions...

Thank you!
Jos

 

[Jos Vens]

Hi,

thanks! I can only see the difference in what I posted already some times
earlier in this NG, without any result:

excel crashes if a (big?) file is not compiled. Moving to another excel
version, makes the compilation invalid (I can see it since the compile item
in VBE is black in stead of grey). Recompilation mostly solves the crash.

If you get rid of the compilation of the XP version (which reduces the file
size by 2Mb), and then you start the program, it crashes on XP too, which
for me, is the same problem.

Jos

 

[Jos Vens]

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]

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

 

[RB Smissaert]

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

 

[Jos Vens]

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]

I reduced the .xla file from a bit over 5Mb to 2.5 Mb.
Not sure why yours reduces so much less.
Different version of WB Rebuilder? Different Excel version?

Exam results etc.
Yes, then you definitely need better security than Excel can offer!
The results will have to be stored encrypted (in sheets or somewhere else)
and the crypting/decrypting routine will have to be in a VB6 dll (or a
normal dll if you know somebody who can make that for you).
Shouldn't be too difficult. I have such a routine, related to hardware, not
a
password, but the principle is the same.

The single-quotes may be a reason of the crash

It meant the file couldn't be found, causing an error, but I don't think it
could
be reason for the crash.

Yes, I think you are on the right path now and good luck again.

RBS

 

[Jos Vens]

Hi,

I think you did not recompile, so my reduction is equal without
recompilation.

What do you think: should I compile or not. I think if the user uses the
same version of excel, a compiled file is a bit faster when it starts
(compilation is on the fly). But if the user has a different office version,
compilation is not used and a compilation on the fly is made to his/her
excel version (you can see that on the compiled-item of the VBE environment
which is not disabled anymore). Without compilation, the xla is half as big,
so, in a network environment, it takes less time to get.

Jos

PS just to know, I rebuild my crashing version of Libra with the tool, and
it worked straigtaway. So, I'm sure it was garbage collection that corrupted
my file. In the meantime, I'm glad I did the effort of variable declaration.
PS2 your hardwareroutine sounds interesting, but is it possible if you have
many (unknown) users. My program is freeware for teachers. (is it hard for
you to provide me the code, so I can look if it's interesting for me?)

 

[bart.smissaert]

I did re-compile.
File was 5 Mb after compile and save and went down to 2.5 Mb after
running WB Rebuilder.
I always compile before save, just to make sure there are no errors. I
think most people on this NG will do the same.
It would be no trouble to pass you the code and I will do that tonight
after work.

RBS

 

[Jos Vens]

Hi Bart,

I did a time-check and compiled or not, the program starts equally fast. I
have also the habbit to compile. I think when I distribute, I will run WB
Rebuilder and do no recompilation.
If you want to send code directly: (e-mail address removed) (the
newsgroup-emailaddress was my old one and does not exist anymore).

Your a great person!
Jos