Re: VBA and VSTO

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" <jos.vens@xxxxxxxxxxxxxx> wrote in message news:O7mU24eVGHA.1236@xxxxxxxxxxxxxxxxxxxxxxx
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

"RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx> schreef in bericht news:e9JJeweVGHA.5332@xxxxxxxxxxxxxxxxxxxxxxx
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" <jos.vens@xxxxxxxxxxxxxx> wrote in message news:%23sJqIqeVGHA.1688@xxxxxxxxxxxxxxxxxxxxxxx
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" <bartsmissaert@xxxxxxxxxxxxxxxx> schreef in bericht news:uxSMJfeVGHA.4900@xxxxxxxxxxxxxxxxxxxxxxx
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


"Jos Vens" <jos.vens@xxxxxxxxxxxxxx> wrote in message news:edLWhMeVGHA.1236@xxxxxxxxxxxxxxxxxxxxxxx
Another suggestion: can my project be too big? it is now more than 5 mb?

Jos


"Niek Otten" <nicolaus@xxxxxxxxx> schreef in bericht news:OUtDSkcVGHA.5580@xxxxxxxxxxxxxxxxxxxxxxx
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.....

--
Kind regards,

Niek Otten


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

















.