3

I have a very large amount of code written in VBA in Access .mdb files, containing forms and connecting to an Access data base. I need to migrate this code to a VB6 application. It isn't practical to rewrite all of it as there is just too much.

Is there a way to call that VBA file from a VB6 form with buttons, that would allow users to launch each one of these VBA modules, via a simple click on its corresponding button? What line of code in VB6, allows to launch a VBA file?

Update

I finally succeeded to figure out how to launch a VBA mdb file from my VB6 form using this instruction :

Call Shell("C:\Program Files (x86)\Microsoft Office\OFFICE11\MSACCESS.EXE C:\presto.mdb /runtime /cmd", 1)

So the VBA project is now launched from the VB6 form, but it crashes, at start up, showing this error "Runtime error ‘3024’ Could not find file ‘C:\db.mdb’". I feel like I'm half way to thee solution. But I don't know what am I missing here?

AndroWeed
  • 133
  • 2
  • 12
  • 2
    VBA files make no sense on their own. They must be inside a hosting application. If it happens so that you have a VBA file that does not refer to any features of the hosting app and is simply an abstract library, then you can paste it into a VB6 form as is. Otherwise it will fail anyway because it needs the host app running. – GSerg Oct 07 '15 at 20:04
  • What application is your VBA code for? – MatthewD Oct 07 '15 at 20:06
  • VBA and VB6 use the same runtime, so as @GSerg points out you shouldn't need to "rewrite", at least from the perspective of the core language. – Tim Williams Oct 07 '15 at 20:57
  • @TimWilliams - exactly; it'll depend on what features of the host application (Excel, Word, etc) you make use of. – NeepNeepNeep Oct 07 '15 at 22:23
  • So take a meaningful slab of the VBA and drop it into the VB6 IDE and see what happens. – bugmagnet Oct 08 '15 at 02:33
  • I'll explain; I have VBA files (.mdb files). Each one of them is actually a full VB project that contains forms and connects to an Access data base. I want to build a VB6 form with buttons, that would allow users to launch each one of these VBA modules, via a simple click on its corresponding button. So, what line of code in VB6, allows to launch a VBA file? – AndroWeed Oct 08 '15 at 12:20
  • 1
    MDB is not a VBA file. MDB is a MS Access database which contains data and (optionally) a VBA project. If you manage to rewrite all your code into VB6 (you will have to approach it seriously because data access mechanisms are different when you're inside of a database and when you're outside), then you will still have to carry the original mdb file around, because it is the database with the data. At which point you might just not touch the mdb file and use it as is. If you want to use it on computers that don't have Office installed, there's a free MS Access Viewer available from Microsoft. – GSerg Oct 08 '15 at 17:13
  • VBA and VB6 **do not** use the same runtime. – Bob77 Oct 09 '15 at 15:11
  • @Bob77 - I don't know about VBA7, but I believe it to be the case for VB6 and VBA6 For example see answers and comments here: http://stackoverflow.com/questions/993300/difference-between-visual-basic-and-vba – Tim Williams Oct 10 '15 at 03:54
  • I'm sure there was a ton of code sharing at the source level but they don't use the same runtime DLLs. The linked question was about source-level compatibility between VB6 and VBA source and says nothing of significance about the runtime components. Even with a version of Office installed VB6 programs won't run without the VB6 runtimes - though unlike the Office runtimes the VB6 runtimes come preinstalled as part of Windows these days. – Bob77 Oct 10 '15 at 11:57
  • Re: the update - the error is that it can't find "db.mdb" but your code is looking for "presto.mdb". Confused. – StayOnTarget Aug 13 '18 at 11:55

2 Answers2

1

Without knowing the structure of your VB6 application and your access files and what they do, it is virtually impossible to give any sort of useful answer.

What you want to do is not easily possible, as stated in comments VBA projects are not designed to work in this way.

However here are some suggestions.

  1. Investigate the RunMacro command and see if this is of use to you.
  2. You could try to use a tool like autohotkey to record mouse clicks where you want them and replay from your VB6 app.
  3. Look at pulling out sections of VBA that are business logic and directly copy and paste code into your VB6 app. This may or may not work depending on the code.
  4. Begin the slow process of rewriting (but don't rewrite in VB6, use VB.Net, C# or do a web app or something at least resembling a 2015 application).

If you want my honest advice begin to migrate to a more modern solution such as VB.NET so you won't have an even worse problem in 2020.

There is NO silver bullet for your problem.

Toby Allen
  • 10,997
  • 11
  • 73
  • 124
  • VB.Net mutates rapidly though so you might have to resign yourself to a far more costly cycle of ongoing maintenance. – Bob77 Oct 09 '15 at 15:15
0

It would be better to design a new form in VB6 with button as you said but also with some text(s)/list(s)/whatever(s) you want to display the results, then you copy/paste VBA code into button_click(s) event(s) to execute it and add to that vba code some output to the text(s)/list(s)/whatever(s) you decided to put on the VB6 form.

Edit : Ok, so taking your comment in consideration perhaps that trick could help avoiding re-write thousands of line code : add in the VBA code a while loop that trigger some Sub or Function on the presence of a flag on the system (a specific c:\temp\fileFlag.txt for example) so as soon as it appear the VBA code is called... if the VB6 control the flag would it do the job ? :)

Fabien
  • 458
  • 3
  • 17
  • I appreciate your suggestion. But it's impossible to make. As I said, each VBA file is a full project apart. We don't have time to re-write a 10 years of work in VB6. So what we need is simply call those VBA files from a VB6 form buttons each. – AndroWeed Oct 08 '15 at 13:25