Looking for someone to convert a VBA application into OO

[wassteve]

Hi

I have a business application - a production planning and scheduling system - which was written in VBA and excel 2003.

Its pretty sophisticated and has a number of sizeable manufacturing users in the UK. The algorithms used in calculating the production schedule and the overall logic and structure are unique and my own IP. There are also some unique things I developed in excel like the ability to drag and drop elements of a bar chart from one stacked bar to another.

I now need either to upgrade it to excel 2007 or take a different tack.

This is not just a few macros, last time I counted was in excess of 20,000 lines of code. Like most programmes it has evolved and there is therefore a good amount of trimming and optimisation that should be done as part of the project.

To date it has been locked code but I would like to make the whole thing open source and therefore consider that OO might be a better route - if the functionality can be achieved.

I am looking for someone who might want to take on the task of translation - I have a budget of several £thousands.

The timescale if I can find the right person will be quite tight.

Anyone interested please either pm me or reply here.

wS

[Villeroy]

No, thanks.
I'm just curious. Your Excel 2003 application stopped working with Excel 2007? Why? Has it something to do with the ribbon-interface, did they change document-related APIs or what else?

[wassteve]

Hi villeroy

They changed all sorts but the primary stuff causing problems is the menus. We have lots of custom menus and in 2007 it does not seem possible to have these sit properly in the menu area. It appears you have to have them as custom form windows which is not something we want.

We have looked at rewriting in a variety of platforms but none have so far filled me with confidence about the ability to be open source in any real sense for this sort of application. I am really keen to do this as the sell for this type of software is very tough....you're competing with really big players all the time and a good quality open source application will really get up the noses of the like of SAP, SKEP etc. if it takes off. ( a big if)

Seems a good enough reason to me....lol

[twright]

Just wondering, would it work with open office's vba support? There might be some incompatibilies but it would probably be the easiest option + more cross platform.

[Villeroy]

Just wondering, would it work with open office's vba support? There might be some incompatibilies but it would probably be the easiest option + more cross platform.

From what I've seen in the forums, OOo's interpretation of Excel-VBA is hardly any smarter than Calc's macro recorder with it's stupid dispatch calls. Java or Python with OpenOffice.org would be "cross platform" while still sticking to a distinct application. VBA is intentionally designed to depend on one platform AND a distinct set of applications from the same vendor (COM-object Excel.Application). OOo can map a small set of Excel's API calls to it's own API calls where this mapping is possible.

[twright]

Just wondering, would it work with open office's vba support? There might be some incompatibilies but it would probably be the easiest option + more cross platform.

From what I've seen in the forums, OOo's interpretation of Excel-VBA is hardly any smarter than Calc's macro recorder with it's stupid dispatch calls. Java or Python with OpenOffice.org would be "cross platform" while still sticking to a distinct application. VBA is intentionally designed to depend on one platform AND a distinct set of applications from the same vendor (COM-object Excel.Application). OOo can map a small set of Excel's API calls to it's own API calls where this mapping is possible.

You have to bear in mind that I am no VBA programmer - by cross platform I really meant that you might have a chance of using the same code for both Microsoft office and OpenOffice and as long as it works it could be a lot less hassle than rewriting from scratch. Of course both Python and Java are much better languages but does anyone really want to change language when you already have so much code to port. It is a matter of initial pain verses total pain of ownership

[Villeroy]

Yes, of course someone wants to change the programming language. Many serious VBA-projects use class modules (object orientation). OOo Basic is strictly procedural. Using a mature, object-oriented programming language is the only way to program in a similar style as one did in VBA. The main effort lies in the different APIs. I can translate cleanly written OOo-Basic code with the correct API-calls into Python macros very quickly (and quite often I can reduce the complexity in a second step).

And anybody who knows Basic AND other programming languages like Python will confirm that Basic is extremely difficult to learn with all it's idiosyncrasies and limitations. It is a complete mess from the early 90ies that has become unnecessary complex and obsolete. Even MS dropped that language.

[twright]

Yes, of course someone wants to change the programming language. Many serious VBA-projects use class modules (object orientation). OOo Basic is strictly procedural. Using a mature, object-oriented programming language is the only way to program in a similar style as one did in VBA. The main effort lies in the different APIs. I can translate cleanly written OOo-Basic code with the correct API-calls into Python macros very quickly (and quite often I can reduce the complexity in a second step).

And anybody who knows Basic AND other programming languages like Python will confirm that Basic is extremely difficult to learn with all it's idiosyncrasies and limitations. It is a complete mess from the early 90ies that has become unnecessary complex and obsolete. Even MS dropped that language.

Well, I would never use basic by choice I think this discussion is best left to those who know what they are talking about, my main experience is in C++, Java and PHP!

[wassteve]

Interesting discussion.

The original requirement has not gone away - although I am now considering doing this in house. Got busy over the past few months with 3 new implementations of the system - we have now solved all of the excel 2007 issues.

If I am understanding correctly Villeroy seems to be saying that Oos basic is not an advance dobject oriented language - he quite rightly points out that anything of any sophistication in VBA uses classes etc.

Should I be looking at Python as a route instead ?

One of the key advantages of the current architecture is that many corporate supply chain or planning managers have a knowledge of VBA so that they can easily take advantage of our "code hooking" mechanisms to tune the system to their requirements without changing the core code. I really want to maintain and enhance this ability and do wonder if a non MS language eg Python might be something that fewer of them would feel comfortable with.

S

[twright]

Python is certainly a much nice language but it depends whether you want to go through the hassle of rewriting and using something which while more programmers overall will be knowledgeable of and prefer will be alien to those who have scripted only scripted for office in an enterprise environment. Ultimately it depends on whether you want better code or greater backwards compatibility.

[DKean]

Probably the very best OOP implementation is Visual FoxPro and it can act exactly as VBA. The great advantage of VFP is that it is a super rich language which includes a relational database to boot. I would never dream to use VBA and its crappy syntax to do a serious app. VFP be can grab EXCEL and WORD and any other Application object based app and do miracles with it. There is no faster or better implementation of the Application Object Model.