Convert from vba to vb?

[Duncan]

Hi Guys,

I have always wondered, being fairly new to programming, how possible
it is to get something ive made in excel vba and re-create it in vb6 or
similar to make a standalone program?

The main reason is that I have made a menu system and data input
program through forms in vba but the fact that its tied to a
spreadsheet ties it down a bit, i would love to be able to remove it
from the spreadsheet and make it stand on its own. It uses the
spreadsheet to hold the data but im sure it could do all the same stuff
faster by just accessing a database instead of being within one.

I think VB would be the best option for me as i am learning vba
commands and it would be brill if i could easily take my vba code and
compile it in a vb project.

is this dream stuff? would i need to write it all again?

Duncan

 

[Guest]

First of all, there is no straight forward way or even standard procedure to
actually "convert" a VBA project to a VB project/solution. Especially
because you're new to programming, the rebuild process could be too complex
for you. It may require some advanced solution management skills.

Also, what you need to do is clear:
- to separate your VBA code from the data file.

So, you can simply move your code to an empty Excel VBA file (project), and
save the file as an add-in. Or, you don't even need to save as an add-in, you
can simply keep the separate project as a normal Excel file.

The trick is to be careful with how you refer to the workbooks, worksheets,
and ranges in your code. For example, you may need to make sure you're
referring to the activeworkbook and to the activesheet, instead of just
referring to the range.

Regards,
Edwin Tam
(e-mail address removed)
http://www.vonixx.com

 

[MattShoreson]

Duncan, it's not dream stuff, and you have a good start if you have a
good understanding of VBA and database concepts.

However, there is no conversion perse. This should not hold you back.
Take your time and get a good book/s.

I use Database Access with VB6 (ISBN 0-672-31422-3)
and Pure Visual Basic (ISBN 0-672-31598-X)

Dive on in and give it a go even if you dont roll out the software you
want this time, at least you'll a generate a better skillset and you
may decide that next time you start a project VB6 might just be a
better starting point.

 

[duncan.mutchblache]

If I was to move it to an empty file and have all the references to the
'data' file, 2 things.

Would it need to open the 'data' file to make the amendments every time
and if so would that take ages every time you pressed 'submit'

Would doing that make it 'multi-user'? like have generic copies of the
blank 'input' file and all of them referring to the 'data' file?

Duncan

 

[NickHK]

Duncan,
From your description it sounds likes the Excel worksheet part of is for
data/setting storage and your "program" is mainly form based. If you first
move data/setting part out of Excel (to ini file, DB etc), you will be most
of the way towards compatibility.
Whilst your forms may appear similar in VBA as they do in VB5/6, they are
not directly equivalent. You can however save your VBA forms as text, then
compare that to a similar form created in VB and compare the difference; or
design them from scratch in VB.
You should not have too much difficulty converting, as the logic of
sub/functions will remain the same.
Of course I have no idea what you achieve with you app, but it's certainly
feasible.
...As long as you get a copy of VB.

NickHK

 

[duncan.mutchblache]

Thank you Matt for the vote of confidence!

VB6 has always been my ambition as I like the language, I will
definately move on to that as it is the next logical step for me and I
am always willing to learn.

I do think that you can learn a lot though from starting off copying
recorded macro code and then attempting to write it, i am suprised by
the amount of resources available to guide people on their way! even
the help within excel vba is good once you start to understand the code
and this group has been excellent for bouncing ideas and learning.

I might remove the data from the file as Edwin suggests above and try
to iron out any user issues, then I'm definately getting hold of vb6!

Duncan

 

[duncan.mutchblache]

Hmmm..

Just read your nick,, could I push the data to an ini file and still
use it in the same way? ive never ever used ini files.

I wonder if i would need to either bounce it through a cell to
calculate or re-write the formulas within the code......stepping into
unmarked territory now for me!

Its hard to get a real picture as I cant paste all the code in, its
pages and pages long now. but its basically a tracking file to track
movement of a form through departments using the barcodes printed on
the form to decode and suchlike, ive also added basic reporting to it,
just some percentages, it gets a username of who sent it etc, shows a
trail report for where its been and when.. this file will be used for
over 50,000 forms being sent all differant places, I know i am trying
to do too much with just excel vba but its the only code i know! (or
nearly know!)


Duncan

 

[MattShoreson]

Duncan,

If you're going to move the data to a database and still use excel ,
I'd advocate using another table in the DB to contain the settings
rather than an ini file as nick suggests.

 

[Peter T]

Whilst your forms may appear similar in VBA as they do in VB5/6, they are

not directly equivalent. You can however save your VBA forms as text, then
compare that to a similar form created in VB and compare the difference; or
design them from scratch in VB.

Indeed whilst superficially similar they are very different. If you (Duncan)
already have large complicated vba Userform(s) that work well you can simply
drag the *.frm into the vb6 project and use these. It/they will appear under
"Designers" but you can use as-is subject to changing non form related code
to suit your vb6, such as converting "implicit" references to excel objects
and functions to explicit. The only other thing that will be different is
the units for top/left form position.

For
Defer learning the VB6 Form until later and get on with other stuff
immediately.
Automatically brings in the correct ref to Office MSForms which might be
required for other things.

Against
One day you will probably want to convert to a vb6 form and take advantage
of a lot that's not available with a vba userform (though userforms have a
few advantages). Best to do that from scratch. Control event arguments are
different (eg byRef not byVal) and so are many of the control properties. eg
a simple thing like -

myCheckbox.Value = True
will fail because VB6 expects 1, not -1 (true)

Regards,
Peter T