Excel VBA to VB6 Conversion

[Guest]

Hi,

I have an Excel Add-in .xla file that is working fine. Would like to convert
it to VB6 code to create a stand alone application. Is it possible to do? Any
help on how to start would be appreciated.

Thanks,
MB

 

[Bob Phillips]

In many ways there is little conversion that needs to be done, but there are
three important areas that you have to address

workbook and worksheet event code don't work in VB (although they will still
work if embedded in an Excel workbook that you open)

userforms will need to be recut, they VBA can't be imported into VB

most importantly, you need to create an instance of Excel, or point to an
existing instance, and qualify all books, sheets with the parent objects,
like this

Dim oApp As Object
Dim oWB As Object
Dim oWS As Object

On Error Resume Next
Set oApp = GetObject(, "Excel.Applicatioin")
On Error GoTo 0

If oApp Is Nothing Then
Set oApp = CreateObject("Excel.Application")
End If

Set oWB = oApp.Workbooks.Open("C:\myfile.xls")

Set oWS = oWB.Worksheet("Summary")


--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Nick Hebb]

Here's a reference on creating COM Add-Ins with VB6:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/
odeopg/html/deovrworkingwithaddindesigners.asp

There are several other good tutorials somewhere in the innards of the
MSDN site, but all my bookmarks are on another PC.

I would highly recommend buying a copy of Professional Excel
Development by Bullard, Bovey, and Green. There's a useful chapter on
COM Add-Ins.

Lastly, you can tap into Excel events, but you have to use early
binding. If you do that, make sure you develop against the the Excel
2000 object library. If you don't need to tap into events, use early
binding during development so you can have the benefit of
intellisense, then change all your declarations at compile time.

HTH,

Nicholas Hebb
http://www.breezetree.com

 

[Chip Pearson]

workbook and worksheet event code don't work in VB (although they will

still
work if embedded in an Excel workbook that you open)

Just define in a class

Public WithEvents XL As Excel.Application

and you'll get all the events.

userforms will need to be recut, they VBA can't be imported into VB

Actually, they can be, but not as real forms. They're designer objects with
the same limited functionality as VBA UserForms. They don't "upgrade" to VB
Forms.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[NickHK]

Bob,
Actually VBA forms can be imported in a VB5/6 project, but loose the form
aspect and become designers.
However, controls from the Forms2.dll are not supprted in VB5/6 and will
(probably) lead to erratic behaiour and various "Out of Memory" errors.

So your comment "userforms will need to be recut" is advisable due to the
above limitations.

NickHK

 

[Bob Phillips]

I appreciate that Nick, but I was trying to keep it (relatively) simple to
help the OP rather than confuse them. That is not the sort of information
that is helpful when starting this journey IMO.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Peter T]

Hi Nick,

I use Userforms (Forms2) without any problem at all in VB6, at least none
I've ever been aware of. Even a complex form with many controls and a lot of
code.

Whilst there is vastly more scope with the VB form (and learning curve)
there are just a few things a userform does better. Having said that only
I've only used in a dll to be called from Excel, either as a Com-addin or
plain dll.

With the 'userform' in the designer can edit normally, add controls from the
toolbox, etc. The only difference is the form's external size & position
units are in VB 'style'.

There may well be problems in a stand alone Exe, I've never tried. At the
very least would require Office is installed on user's system. Without
Office it would mean distributing the Forms2.dll which, if that's possible,
I assume (?) would contravene license.

Regards,
Peter T

 

[NickHK]

Peter,
I have not had the need to use Forms2 control in VB6, but judging by the
number of thread in VB6 NGs (e.g. microsoft.public.vb.general.discussion)
that begin:
"I am using Forms2 in my VB6 app and I get this weird behaviour...."
they certainly cause some problems.

Licensing is of course another issue, as these controls are not
distributable, so Office would need to be present.

NickHK

 

[Peter T]

Nick,
I can only hope and assume those problems you've read relate to where
someone has added "Microsoft Forms 2 Object Library" (FM20.dll, not
Forms2.dll as I mistakenly mentioned) to the VB toolbox and gone on to add
Forms2 type controls. Some might be tempted to add controls which don't
exist in VB, eg toggle button or multipage. That's very different from doing
this -

Export an empty or complete userform from VBA to file
Drag the *.frm into VB's project explorer, it'll drop into 'Designers'
Don't add any additional references relating to forms2

If anyone has experience of problems with this method I'd be pleased to
know. Actually I wouldn't be pleased at all, but you know what I mean!

Regards,
Peter T

 

[Jon Peltier]

There may well be problems in a stand alone Exe, I've never tried. At the

very least would require Office is installed on user's system.

In 90% of the cases in which I've used VB6, it was because the end user
might not have Office installed. So my very brief excitement about using VBA
forms in a quick and dirty VB6 app was quickly squelched. Thanks for
nothin'!

- Jon