Convert Excel 4 macros to VBA

Question

I have an old Excel 4 macro that I use to run monthly invoices. It is about 3000 lines and has many Excel 5 Dialog Box sheets (for dialog boxes). I would like to know what the easiest way would be to change it into VBA and if it is worth it. Also, if once I have converted it to VBA, how to create a standalone application out of it?

Answer 1

I have attempted this before and in the end you do need to rewrite it as Biri has said.

I did quite a bit of this work when our company was upgrading from Windows NT to Windows XP. I often found that it is easier not to look at the old code at all and start again from scratch. You can spend so much time trying to work out what the Excel 4 did especially around the "strange" dialog box notation. In the end if you know what the inputs are and the outputs then it often more time effective and cleaner to rewrite.

Whether to use VBA or not is in some ways another question but VBA is rather powerful and extensible so although I would rather use other tools like .NET in many circumstances it works well and is easy to deploy.

In terms of is it worth it? If you could say that you were never ever going to need to change your Excel 4 macro again then maybe not. But in Business there is always something that changes eg tax rates, especially end of year things. Given how hard it is to find somone to support Excel 4 and even find documentation on it I would say it is risky not to move to VBA but that is something to balance up.

Answer 2

(Disclaimer: I develop the Excel-DNA library)

Instead of moving the macro to VBA, which uses a COM automation object model that differs from the Excel macro language, rather move it to VB.NET (or C#) running with the Excel-DNA library.

Excel-DNA allows you to use the C API, which mirrors the macro language very closely. For every macro command, like your dialogs, there is a C API function that takes the same parameters. For example, the equivalent of DIALOG.BOX would be xlfDialogBox - there is some discussion and example in this thread: http://groups.google.com/group/exceldna/browse_thread/thread/53a8253269fdf0a5.

One big advantage of this move is that you can then gradually change parts of your code to use the COM automation interface - Excel-DNA allows you to mix and match the COM interfaces and C API.