Translate Microsoft VBA to LibreOffice BASIC

06 juin 2017

Wanna try a translation test right now ?

Please, send me a VBA text through the command "Contact the author" and I'll send you back its translation in LibreOffice BASIC... Maybe will you have to wait a little because I don't connect to this blog every day but only from time to time.

This will be absolutely free without any financial expense or benefit !!!

See further what my automatic translator consist of.

Posté par gerardmarquer à 11:55 - Commentaires [0] - Permalien [#]
Tags : , , , , , , ,


Wanna give corrections to my english ?

I'm a poor french froggy trying to speak english and this blog in the translation in english of a previous blog written in french.

I hoped that, being written in english, it would find a larger public.

In case you find some ununderstanding message, or any langage mistake... please, tell me the good formulation through the point "Contacter l'auteur" near the header of that blog.

Posté par gerardmarquer à 12:02 - Commentaires [0] - Permalien [#]

Transcoding Microsoft-VBA-Code To LibreOffice-BASIC (or OpenOffice)

On the Internet-Forums there is a question that very often appears (always the same!!!):

Do VBA to BASIC automatic-translators exist ???

The answer is always: No, too rich, too complex, too many specific cases.

But now, ther is one !!! As I am fond of stupid bets, I've written one : It has been more and more successfuly used for 2 years and is able to translate about 50% of my VBA Code. Of course the Good Lord it beleaves in is my own way of programming!!! And I would be very curious and happy to make it work on some Code written by somebody else.

To solve some ambiguous instructions, it uses a real syntax analyzer. It is strongly oriented Excel to Calc Applications. As shown below, very often, a simple VBA instruction needs several lines for its translation. So, to remain concise, the BASIC text given will largely use a set of Functions that I'll be obliged to send with the translation.

Where are the difficulties ? You will find below some examples.

F
irst example:
VBA Instruction:
Private Cat_I_Lig, Cat_Last_Lig, Cat_Nbr As Integer
(In VBA all the variables in the list are typed "Integer")

BASIC Instruction generated:
Private Cat_I_Lig As Integer, Cat_Last_Lig As Integer, Cat_Nbr As Integer
(Since, in BASIC, each variable must be given its own type unless it would be typed "Variant" by default)

Second
example:
In VBA you can write the three following instructions:
    Cells(ilig, icol) = 3.1416
or
    Cells(ilig, icol) = "Pi Value"
or
    local_pi_value = Cells(ilig, icol)

and the VBA interpretor will be able to complete all missing informations (which are implicit):
   - The Cell aboslute reference (active document, active sheet)
   - Which property will be modified (by default: Value)

In BASIC all those informations must be completely specified:
          myDocument = ThisComponent
          mySheet = myDocument.Sheets.getByName("Statistics")
          ' "Statistics" is the name of the sheet the Cell belongs to
          myCell = mySheet.getCellByPosition(icol, ilig)

BASIC uses the normal mathematical order for coordinates (X first, Y after)
VBA waits for Y first (Line number) and then X (Column number)
Furthermore, in BASIC, Lines and Column numbers begin to zéro (but 1 in VBA)
BASIC Instruction generated:
myCell.Value = 3.1416
or
myCell.String = "Pi Value"
or
local_pi_value = myCell.Value

In BASIC, two different properties ("Value" or "String") are involved following the type of the data entered in the cell.
Of course, when several operations will occur on the same Sheet of the active Document, the variables named "myDocument" and "mySheet" may have been defined Public or Global and have received their value far back in a higher level of the Code.

So, in order to remain concise, the generated BASIC text will be:
Call SetCellValue(icol, ilig, 3.1416)
Call SetCellValue(icol, ilig, "Pi Value")
    local_pi_value = GetCellValue(icol, ilig)


where the "Set_" and "GetCellValue" Functions gather all the previons instructions

Third example (this one is typical):
VBA Instruction:
    Cat_Last_Lig = Sheets("Cat").UsedRange.Rows.Count

In BASIC, this instruction can't be translated in only one instruction
and here is the set of the generated instructions:
Dim local_cu rsor as object, local_sheet as object, local_cellule as Object Dim local_absolute_name as String Dim local_tab as Variant local_sheet = Active_Document.Sheets.getByName("Cat") local_cursor = local_sheet.createCursor() local_cursor.gotoStartOfUsedArea(0) local_cursor.gotoEndOfUsedArea(1) local_absolute_name = local_cursor.AbsoluteName if 0 = Instr(local_absolute_name, ":") then ' local_absolute_name looks like: $Sheet.$A$1 Cat_Last_Lig = 0 else ' local_absolute_name looks like: $Sheet.$A$1:$D$26 local_tab = split(local_absolute_name, ":") local_cellule = local_sheet.getCellRangeByName(local_tab(1)) Cat_Last_Lig = local_cellule.CellAddress.Row endif
So, in order to remain concise, the generated BASIC text will be:
Cat_Last_Lig = GiveLastLig(O_Cat_Sheet)

where the "GiveLastLig" Function gathers all the previons instructions

Posté par gerardmarquer à 12:22 - Commentaires [0] - Permalien [#]
Tags : , , , , , , ,

More informations are coming here soon

The texts are ready but I've not yet found time to add them here !!!

 

See you soon again !!

Posté par gerardmarquer à 12:24 - Commentaires [0] - Permalien [#]