Conversion from Visual Basic for Applications (VBA)

[ChrisPRGreaves]

Hello; my first post here. Many thanks to Hagar for negotiating my entry here; I hope that he does not come to regret it.

I am a programmer with experience in language design and compiler writing.
Over the past six months I have reached the conclusion that my long-time (1984) relationship with Microsoft must come to an end. That suggests that I will be converting some 30 of my 160 MS VBA applications, about 90% in Word2003, 7% in Excel 20023, and the rest in Access and PowerPoint.

I have not explored alternate languages in any deep sense, but ten years ago managed to write an OpenOffice (I think) application to upload web pages (text and images) to the web, and at that time I was struck by the similarity of VBA to the macro code in OpenOffice (ODT) macro modules.

So I think that a switch to a Linux-like operating system (ten years ago I looked at Mint, Ubuntu) and Apache OpenOffice will be the least labor-intensive path for me to follow. There will be a learning-curve, but remember that I do have a background in writing programming language converters, and I already have VBA applications that analyze (and treat) VBA code. Details to follow if required.

I have two spare Win11 laptops at my disposal.

Question 1. Is this BBS one of the best BBS in which to discuss migration from MS VBA?
Question 2. If the answer to question 1 is "Possibly not", please suggest alternate BBS
Question 3. In which forum should I pose my questions and post my experience in migrating MS VBA to word processing documents (ODT?) and Calc documents? I am not worried about migrating the documents, but will want to migrate their embedded VBA code.
Question 4. What version of a Linux-based O/S and OpenOffice are the current versions for a home-based and retired applications programmer. I am not running a network, I do not produce work for paying clients, and apart from text-based web pages, have no real need for processing data to/from the internet.

Thanks again to Hagar, and thanks in advance to The Rest Of You.
Chris Greaves

[Zizi64]

Just some notes:

- I strongly suggest you to use the LibreOffice instead of the OpenOffice. The LO has a littlebit higher compatibility with the foreign file formats, and the VBA. The Linux distributions come with a bundled LibreOffice version.
- Do not try to CONVERT the VBA codes, but try REWRITE them based on the LibreOffice API, what is very similar to the Apache OpenOffice API: they are mostly same, but there are new and modified things, depreceated things in the LibreOffice. (In other words: Use LibreOffice macros for the LibreOffice.)
- Yes, it is a common Forum for the AOO and LO. There is an Ask... page for the LibreOfice https://ask.libreoffice.org/en/questions/ , but there are more usable informations on this Forum (yet, today).
- You can start with Andrew Pitonyak's free macro books: https://www.pitonyak.org/oo.php . And you can use the StarBasic Help, API descriptions, the informations of the Forums,
- and you can choose from more then one supported programming languages when you use the AOO or LO.

[ChrisPRGreaves]

Just some notes:

Zizi64, thank you for your notes.

- I strongly suggest you to use the LibreOffice instead of the OpenOffice. The LO has a little bit higher compatibility with the foreign file formats, and the VBA. The Linux distributions come with a bundled LibreOffice version.

Thanks; perhaps it was LibreOffice I used about ten years ago. I do recall that the macro code appeared to be similar to VBA, so much so that in one night’s work (on vacation!) I got my upload-to-web code to work, cleaned it up on the second night, and two years later used it again without a problem.
I have downloaded LibreOffice_7.6.4_Win_x86.msi to accompany Apache_OpenOffice_4.1.15_Win_x86_install_en-US.exe

- Do not try to CONVERT the VBA codes, but try REWRITE them based on the LibreOffice API, what is very similar to the Apache OpenOffice API: they are mostly same, but there are new and modified things, depreceated things in the LibreOffice. (In other words: Use LibreOffice macros for the LibreOffice.)

Please, why discourage conversion?
One application alone contains 791 procedures with a total of 14,407 lines of VBA code.
My library of utility procedures for MSWord contains 980 procedures with a total of 26,284 lines of VBA code.
In general my code has been working for 20+ years (yes, there are still bugs!). The prospect of re-writing even 30 of the 160 applications is sufficient to send me running back to Microsoft!

- Yes, it is a common Forum for the AOO and LO. There is an Ask... page for the LibreOffice https://ask.libreoffice.org/en/questions/ , but there are more usable informations on this Forum (yet, today).

Thanks for this. I have enrolled in the Ask system using this same Username "ChrisPRGreaves” for recognition.

- You can start with Andrew Pitonyak's free macro books: https://www.pitonyak.org/oo.php . And you can use the StarBasic Help, API descriptions, the informations of the Forums,

Thanks for these leads. I had already downloaded a slew of OO pages, and Andrew’s free online sources, the file “tutorial.pdf” and others.

- and you can choose from more then one supported programming languages when you use the AOO or LO.

This has confused me. I had thought that with, say, OO I would learn a language that looked similar to MSOffice VBA. And with LO I would learn a language that looked similar to MSOffice VBA.
That is, I would dabble a bit in both OO and LO then decide to use one or the other.

I do not yet see why I would then choose one or more other supported programming languages when I use the AOO or LO.

Perhaps that will become clear to me once I get started.
Cheers, Chris

[Zizi64]

And with LO I would learn a language that looked similar to MSOffice VBA.

There is not high level of "similarity"...
In some simple words: The MS VBA is a mixed thing. It is mixed a flavour of the Basic IDE and a library for control the office suite. The MS "library" is "embedded " into the VBA IDE.

The LO API functions are independent from the supported programming languages. You can call the LO API functions from all of the supported programming languages. And the StarBasic is only a very simple BASIC dialect. It can not control the AOO/LO directly, it only can do it trough the calling the API functions. As the Python can do it in same way too...

There is not equivalent for all of "commands", "functions", "subroutines", "interfaces" and other things in the VBA and AOO/LO API.

[Lupp]

See also my comment here: https://ask.libreoffice.org/t/conversio ... a/100085/4
(The questioner cross-posted there.)

[JeJe]

an OpenOffice (I think) application to upload web pages (text and images) to the web

So you have a whole load of applications like that written using VBA which don't necessarily utilise the Office suite - just the Basic language in the suite as a language to write them in? Is that right?

And you don't say why you want to ditch Microsoft? Sounds like a whole lot of hassle to replicate something in OO or LO that you already have. If it means recreating lots of forms (dialogs in OO) then that's going to be horrendous.

You could start with your simplest application and copy and paste it into a module with the VBA compatability options and see what runs with that.

viewtopic.php?t=56522

[Mountaineer]

...
Please, why discourage conversion?
One application alone contains 791 procedures with a total of 14,407 lines of VBA code.
...

Actually size and numbers are not the big problem. The question is, what your code is doing, and how much it depends on the api of VBA. You may have 1000 lines of calculations with no need to change anything or have an access to something not supported is every second line.

Expect a lot of "little" problems like this:
https://ask.libreoffice.org/t/in-vba-sh ... cro/100097

One thing to recommend is this guide, as it lists a lot of differences to VBA (just search for VBA).

... I had thought that with, say, OO I would learn a language that looked similar to MSOffice VBA. And with LO I would learn a language that looked similar to MSOffice VBA.
That is, I would dabble a bit in both OO and LO then decide to use one or the other.

I do not yet see why I would then choose one or more other supported programming languages when I use the AOO or LO.
...

Prefer LibreOffice in your case. There is not much difference between the two, when it comes to BASIC and the API, but LibreOffice is under more active developent, and they try to improve also compatibility to M$.

If you'd know python, I would recommend this, as the language has some advantages. But as your goal is porting BASIC-Code use BASIC... (However: For me it is sometimes easier to handle really different languages, than similiar variants of the same...

[ChrisPRGreaves]

Because I have progressed somewhat I thought to see where I am up to with the original questions.

Question 1. Is this BBS one of the best BBS in which to discuss migration from MS VBA?

The answer appears to be YES. If not "the best" it has certainly kick-started discussions, for which I am grateful. I may move to a different BBS once I am thigh-deep in conversion, but for now this BBS appears to satisfy my basic needs.

Question 2. If the answer to question 1 is "Possibly not", please suggest alternate BBS

I have registered in https://ask.libreoffice.org/t/conversio ... vba/100085 (as ChrisPRGreaves) and if LibreOffice is my choice I may do most of my questions over there.

Question 3. In which forum should I pose my questions and post my experience in migrating MS VBA to word processing documents (ODT?) and Calc documents? I am not worried about migrating the documents, but will want to migrate their embedded VBA code.

In terms of Apache OO I seem to be in the most suitable forum; no one has (yet) asked me to move this topic to a different forum.

Question 4. What version of a Linux-based O/S and OpenOffice are the current versions for a home-based and retired applications programmer. I am not running a network, I do not produce work for paying clients, and apart from text-based web pages, have no real need for processing data to/from the internet.

I have installed OpenOffice 4.1.15 and LibreOffice 7.6.

I have outstanding posts to respond to.

My next immediate tasks are to learn more about the macro-language in both suites, so:-
* Inspect macro language in both versions.
* Write and test a simple string function from UW.dot in both OO and LO.

UW.DOT is a massive library of 980 VBA procedures in two areas:-
[1] Common to ALL VBA applications, procedures such as string-handling, array-handling, file handling, and maintenance of INI files for Windows
[2] Application-specific procedures, such as dealing with Documents, Templates, Styles (as Word implements them), Table/Paragraph/Sentence/Word handling.

Now back to outstanding replies.
Cheers, Chris

[JeJe]

Your application-specific procedures will likely need rewriting.

If there aren't any and its just normal Basic then likely less. Basic in OO or LO is a clone more of less so nearly identical. Some things are missing in OO such as VarPtr, the Addressof operator - and you'll have to find another (likely less efficient) way or some things may not be possible to do at all - if you use those.

Edit: Or you could consider making an external dll for your Basic functions with VB6 if you have it, or .NET instead of trying to copy them over to OO or LO. You can call those from within OO or LO.

[Villeroy]

Run your program with a VB runtime without "A" and access Open/LibreOffice when needed: http://www.kalitech.fr/clients/doc/VB_APIOOo_en.html

[ChrisPRGreaves]

Just some notes: - I strongly suggest you to use the LibreOffice instead of the OpenOffice. The LO has a littlebit higher compatibility with the foreign file formats, and the VBA. The Linux distributions come with a bundled LibreOffice version.
- Do not try to CONVERT the VBA codes, but try REWRITE them based on the LibreOffice API, what is very similar to the Apache OpenOffice API: they are mostly same, but there are new and modified things, depreceated things in the LibreOffice. (In other words: Use LibreOffice macros for the LibreOffice.)
- Yes, it is a common Forum for the AOO and LO. There is an Ask... page for the LibreOfice https://ask.libreoffice.org/en/questions/ , but there are more usable informations on this Forum (yet, today).
- You can start with Andrew Pitonyak's free macro books: https://www.pitonyak.org/oo.php . And you can use the StarBasic Help, API descriptions, the informations of the Forums,
- and you can choose from more then one supported programming languages when you use the AOO or LO.

@ALL Apologies for starting a topic and then appearing to abandon it. My time has been fragmented, but now I can assign a block of time to a mammoth project: Choosing and Migrating to a Linux-based home for my MSOffice VBA applications.
@Zizi64
Thank you for your comments.
My only certainty to date is that after many years of frustration with Microsoft I am moved to look for greener pastures; I have the idea that a Linux-based OS and either LO or OO will be the solution.
To that end I have three streams of research:
(1) http://www.chrisgreaves.com//WindowsToL ... oLinux.htm
(2) http://www.chrisgreaves.com//MSOfficeTo ... office.htm
(3) http://www.chrisgreaves.com//MSOfficeTo ... office.htm
But these pages are but placeholders for now.

My first step appears to be to install Ubuntu 22.04 LTS, one of the world’s most popular Linux distributions, nicknamed “Jammy Jellyfish.” On the recommendation of Chris Husted of AskWoody fame. I will install on a spare Win11 laptop.
This gives me a platform to start learning basics of the O/s. Jammy Jellyfish might not be my best version, but it is a start.
My second step is to choose between OO and LO. To that end I have 160 VBA applications, of which perhaps 30 are candidates for conversion.
I have a plan of attack:
(1) Choose three elementary slave string-manipulation functions from my VBA utility library UW.DOT and implement them in both LO and OO.
(2) Choose from that library three support modules which appear to have use in my new world. Management of INI files (or their equivalent), File handling, and GUI forms (or their equivalent) suggest them selves.
(3) At this point I should have a good idea of which environment – OO or LO – seems to suit me best.
(4) So then I might be in a position to migrate a single simple application, my Styles handler, my Turing Machine interpreter, or my document utility “UNDER the hood”)

These are vague steps, but they are a goal with a plan. A dead-end will cause me to retrace my steps and strike out anew, but I hope that I will be settled in the decision of a new platform on which to express my urge to write programs.
I have mentioned here or elsewhere that my background is in writing translators, so in my quiver is a spare arrow that may help with a great deal of grunt work, but that decision can be made only with a full knowledge of my current programming style AND the likely platform.

Thanks again, Chris

[ChrisPRGreaves]

And with LO I would learn a language that looked similar to MSOffice VBA.

In some simple words: The MS VBA is a mixed thing. It is mixed a flavour of the Basic IDE and a library for control the office suite. The MS "library" is "embedded " into the VBA IDE.

I think so too, but at the high level my applications already reflect this. For that reason my massive UW.DOT has modules devoted to elementary string, integer, logical procedures, and another set devoted to (in UW.DOT) "document objects".
The first set of functions are mirrored in my Excel VBA library which is accompanied by modules devoted to the Excel "workbook objects".

It can not control the AOO/LO directly, it only can do it trough the calling the API functions. As the Python can do it in same way too...

I think I am on board with this too, if only by virtue of calling the Excel object from Word, and thus making use, in a WordVBA application, of my Excel library of utility functions.
We shall see!

There is not equivalent for all of "commands", "functions", "subroutines", "interfaces" and other things in the VBA and AOO/LO API.

I am not sure exactly what you mean by this, but my starting point will be that I have not yet met a programming language that does not contain "blocks of program code identified by a mnemonic label. FORTRAN II satisfied this, and my Turing Machine interpreter too satisfies this.
Cheers, Chris

[ChrisPRGreaves]

See also my comment here: https://ask.libreoffice.org/t/conversio ... a/100085/4
(The questioner cross-posted there.)
"There may be no guidance in the sense you are looking for, and you got good advice already. (Many contributors here are also active on forum.openoffice.org. So am I.) Since you seem to be insisting, I will also try a different approach."

Thank you LUpp.


I think that you are right. I made a preliminary search for folks migrating specifically from Windows/MSOffice programming to Linux/Open source office suites and came up with zero.
(I might change my username to Don Quixote )

There is a small chance that I will retreat and stick with MSOffice, but I am stubborn and tenacious and might end up developing diaries of my progress if only to provide explicit warnings for other folks.

I know so little about alternate PC systems for modern times, but the page you referenced seems to be full of novel (to me) concepts. They will, I am sure, start making sense to me in the months to come. In the meantime I am grateful to archives such as this BBS that preserve, for future reference, links and words such as yours!

Thanks and cheers, Chris

[ChrisPRGreaves]

So you have a whole load of applications like that written using VBA which don't necessarily utilise the Office suite - just the Basic language in the suite as a language to write them in? Is that right?

JeJe Thank you for these thoughts. Most of my Word/VBA and Excel/VBA applications run in and manipulate objects within the MSOffice suite. a few examples:-
(1) My one-click rules-based Indexer provides a means of extracting Interesting Words from any text; its storefront application builds an accurate Index table in seconds.
(2) My heuristic spell-checker is (IMHO!) superior to that of the MSWord spell-checker. It operates of course on documents of text.
(3) My Excel ZoomP application optimizes the zoom factor to produce only complete pages of printed material. (Back in the day when we printed hard-copy of our worksheets it was embarrassing to be holding up the line when the last forty pages erupted with only one cell in the top-left corner of each page!)
(4) My heuristic jukebox catalogue and player runs 24/7 at home.
These are a few examples; I have a mixture of aps-which-manipulate-MSOffice items, and apps-which-serve-the-wider-world.

Over the past 25 years I think I have plumbed the depths of MSOffice and its components.

I wrote a spider-chart manipulator in Excel for a client, one of the sample videos is here
The client wanted a presentation in which, as response to the audience, they could use the mouse to "get rid of all sales of Tomato Paste in the Bonavista Peninsula region" to see the impact on the national budget for the firm.
To that end I used the eight combinations of Shift/Ctrl/Alt with a mouse click to tap into mouse-click events anywhere at all on the chart.

Note that these videos were made solely to astonish the client with the varieties of control that could command over their presentations. being able to change the value of a single data cell by clicking on the chart was, for them, a useful tool.

Now this is NOT a good example of applications I will convert, but consider the thousands of combination: If I Shift+Alt+Click 2/3 the way across the TOP of the chart area, then this action will be taken.

I daresay that OO/LO does not allow me to capture click-actions to that level, but that will not be a great loss to me, as I do not plan to migrate that application.

And you don't say why you want to ditch Microsoft? Sounds like a whole lot of hassle to replicate something in OO or LO that you already have. If it means recreating lots of forms (dialogs in OO) then that's going to be horrendous.

I did not think it necessary to provide reasons, but they exist. I am looking forward here, not backwards to years of frustration at dealing with the changes that so often reduce the usefulness of the platform. We can discuss that elsewhere if you prefer, or else watch for it when it appears in one of my three new web pages.

Thanks again, Chris

[ChrisPRGreaves]

Expect a lot of "little" problems like this:
https://ask.libreoffice.org/t/in-vba-sh ... cro/100097

Mountaineer, thank you for your feedback. The link that you mentioned contains an interesting example whose bottom line is. "Forget it! There is simply no such possibility."

I understand this.

As an example, suppose that my heuristic jukebox cataloger and player just cannot be migrated to an alternate platform in its current form. (Today I don't know why it might not migrate, but let's assume that it absolutely can't be done). That presents a case where I can not migrate the application from Win7/Office2003 to an alternate platform.
I will have to accept that.
In which case the old Win7 laptop that right now is playing music will continue to operate and play music.
I just won't be spending resources migrating it.

The Turing Machine interpreter however depends on documents that support tables, and without looking I suspect the LO/OO support tables in documents.
And Hyperlinks to other documents with tables.
So today I have faith that that application can be migrated to LO/OO.

Yes, there will be winnowing going on, and perhaps much gnashing of teeth, but I can only migrate what I can migrate, and most this year's spare time will be spent discovering exactly which of my existing applications can move to greener pastures. That's life!

Cheers, Chris

[ChrisPRGreaves]

Your application-specific procedures will likely need rewriting.

Hello again JeJe,
Yes. I anticipate opportunities for completely re-designing and rewriting some applications. My Indexer was written in 1993, in the days of WordBasic (MSWord6.0) so that IF I decide to migrate it, that could be a good time to look closely and see just which features I use, and which were put in just because they looked cute.

Edit: Or you could consider making an external dll for your Basic functions with VB6 if you have it, or .NET instead of trying to copy them over to OO or LO. You can call those from within OO or LO.

I have a copy of VB6.0, but it is very old. Back in 1997 when I began in VBA I elected to build a library (UW.DOT) of utility procedures. I started developing UW.DOT then thought to use VB6 and compiled a UW.EXE.

The drawback then was that the UW.dot was distributed to many of my clients as part of their application, and installing a newer version of UW.EXE was much more difficult than automating the task with Word VBA.

So I reverted to UW.dot and my "Setup.dot" which could swap out older versions of templates and swap in the newer versions. And distribution package carried the latest UW.dot, and so the clients were kept up to date.

I may re-think that policy now that i am my only client!
Cheers, Chrsi

[ChrisPRGreaves]

Run your program with a VB runtime without "A" and access Open/LibreOffice when needed: http://www.kalitech.fr/clients/doc/VB_APIOOo_en.html

Villeroy , thank you for this. It is way above my head right now, but I believe it will make more sense once I start programming in OO/LO and/or in VB6.

I have, of course, noted in for Future Reference!
Cheers, Chris