VBA and VSTO

[Jos Vens]

Hi,

my project is a XLA (but I don't use it as a standard add-in, I just load it
by starting up an xls-file that opens the XLA.

Not compiled, it is about 2,5 Mb, compiled, it is 5,16 Mb

I counted my objects:

2 worksheets + Main (wb)
31 forms
86 modules

code sometimes is big in one module.

I will now delete some modules, but that will encrease the size of the
others.

Migration to VB6 is not as difficult as to VB.net?

Thanks,
Jos Vens

 

[RB Smissaert]

Moving code to VB6 can't be simpler and I am sure that is the best way to
go.
Just re-organise your modules and bring it down to 100 or
less than see what code could be moved to VB6.
Simplest to move at first are procedures that don't need Excel such as
string or
array manipulations or API code.
You will get the hang of it in a few days and you will be on the way to
salvage your app!

RBS

 

[Jos Vens]

Hi,

I already experimented, and I it looked very familiar to me, so it's
possible for me. Can you tell me how I can use a procedure of VB6 in VBA?

eg.

In VB, I program a new function

function Make_UCase(vString as string)

Make_UCase = UCase(vString)

end function

How can I now use it in my VBA-code? And which form must the vb-project
take: a dll, an exe file? How can VBA open it? By the shell method?

Thanks,
Jos

 

[Bob Phillips]

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)

 

[RB Smissaert]

You would make an ActiveX dll.
If you compile in VB6 with Global MultiUse than all you have to do is set a
reference in Excel to your dll via Tools, References and you can access your
functions in the dll directly. You will have to register the dll as well
with Windows with Regsvr32, but that can be done quite simply in VBA.

So, for example you coded a function in VB6 like this:

Function AddUp(lNumber1 as Long, lNumber2 as long) as Long
AddUp = lNumber1 + lNumber2
End Function

Then you can do in VBA:
MsgBox AddUp(1, 2)

As simple as that.

Note that you can also use VB6 forms in this dll and there are some
advantages to that as well.


RBS

 

[Jos Vens]

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]

I'll have a look Jens. I didn't think the module count would be the problem,
so I am not surprised you could cut it down and still get the problem.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[RB Smissaert]

Jos,

I had a look at this .xla file after downloading it from the regular Libra
website.
Basically the file is corrupted in a major way.
My suggestion is to really clean this project up like this:

1. for starters use Option Explicit in each and every module. See if it
still compiles after that.
My guess is it won't.
2. The file is bloated in a major way as the number of codelines and
forms/controls isn't that
much to explain the size being over 5Mb. You say you ran the code cleaner,
but I doubt if
that worked properly.
3. I would cut the number of modules down, although maybe that is not the
trouble in this case.
4. Make sure the size of the exported modules is below 64 Kb. Again this is
not a hard rule, but
many people that know Excel think this can be important.
5. Not essential in any way but use a code indenter to make it look neater:
http://www.oaltd.co.uk/Indenter/Default.htm
6. Add good error handlers in any procedure that is non-trivial.
7. Cut out procedures that don't do anything.

Only after doing this can you start looking at what is going wrong with the
menubar.
One thing is sure, moving to VSTO won't do any good at all, it is the code
that is at fault, not the tool.

RBS

 

[RB Smissaert]

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]

Hello Bart and Bob,

here I'm back again.

That it was running out that way, I was afraid of.

Thank you for providing me the code to easily export my modules. I think I
will not export my forms because there are some strange things (looks like
corruption) when I cleaned my project with a tool. I have to declare every
variable which will take me some time. You know how it goes: after a time,
you realise it should be like this, but time is so scarce so variants are
also an option. But I will follow your gidelines. And... I've deleted
modules which are much bigger than Libra_Toolbar!

I think visual basic can stay an option afterwards. The reference to a dll
is a possibility, I tried it out and it worked! -> I think, here is a
problem of debugging: if a problem/bug exists in the dll, how can it be
traced, debugged, because it's a compiled file. But, the better solution
would be that Libra.xls in conjunction with Libra.xla becomes an .exe-file,
which opens the various excel-files I need. I hope I can accomplish that in
a second step. Debugging stays possible here before final compilation.

Give me one to two days to realise your suggestions. Please keep in touch. I
give feedback on my progression. Thanks a lot for all your effort!
Jos Vens

 

[Jos Vens]

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]

I have to declare every variable

Definitely, not only that but you will have to pick the proper datatype as
well. Keep
in mind that there is no benefit (bit slower in fact) normally of declaring
integer compared
to longs, so normally pick long.

if a problem/bug exists in the dll, how can it be traced, debugged,
because it's a compiled file

Not really a problem. Just develop in Excel and keep testing there till you
are happy it is all working
fine, then move to VB6. If you still don't trust it add an error handler in
the VB6 code.

I think I will not export my forms because there are some strange things
(looks like corruption)
when I cleaned my project with a tool

There definitely is corruption and the fact you couldn't clean the form
modules should have set alarm
bells ringing straight away.
Just start with adding Option Explicit in every module (including forms) and
the rest will follow.

Good luck!

RBS

 

[RB Smissaert]

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]

My first step is done...

I exported everything, then made a new xla-file and imported manually all
objects. One form could not be imported, so I guess that can be the reason
of corruption. I copied-pasted everything of that form and so I could
rebuild my whole project. (still more than 5 mb!!!)

I started up and in XP and 2003, it worked fine (I did no test in 2000 by
now)

Tomorrow, I put the option explicit, but that will take more time. I had
already changed every integer in long, because I read about that.

Thanks for all your support, it was a great push for me to go that far...
and to resolve (I think I'm so far now) my major problem.
Eventually, I will put the definite result of all action here. Many many
thanks!

Jos

 

[Myles]

Smiss wrote:

... there is no benefit (bit slower in fact) normally of declaring
integer compared
to longs ...

I support the idea of going *Long* rather than* Integer* in al
situations. But is it really true that Integer is slower than Long
I'd had always thought the opposite plus the fact that Integer use
less memory resources than Long or Double.

Just curious.


Myle

 

[Tom Ogilvy]

I believe the thought is that
In 32 bit versions of windows, Long is the default length - so it takes
extra code to work with integers and doesn't save any memory since the
variables are not bitpacked.

 

[Jos Vens]

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]

Jos,

If you can send the workbook to me I will have a look.
Can't promise anything, but I wouldn't give up yet.

RBS

 

[RB Smissaert]

set it up for Office 2000

What do you mean with that?

RBS

 

[RB Smissaert]

Just wondering how you were getting on with this.
The offer to look at your workbook(s) still stands.

RBS