Excel Macro Language

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 Visual Basic and if it is worth it. I would also like to know how, if once I have converted it to VBA, how to create a standalone application of it.

Answer 1

Here is a good artikel about this topic: http://msdn.microsoft.com/en-us/library/aa192490.aspx

You can download VB2008-Express for free at: http://www.microsoft.com/express/default.aspx

Answer 2

AFAIK there is no possibility to somehow convert it. You have to basically rewrite in in VBA.

If you have converted to VBA, you cannot run as a standalone application. As VBA states Visual Basic for Application, it is living inside an application (Word, Excel, Scala, whatever).

You have to learn a standard language (not a macro-language) to create standalone applications. But you have to learn much more than the language itself. You have to learn different techniques, for example database handling instead of Excel sheet handling, printing instead of Excel printing, and so on. So basically you will lose a lot of function which is evident if you use Excel.

Answer 3

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.