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.

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)

In VBA you can write the three following instructions:
    Cells(ilig, icol) = 3.1416
    Cells(ilig, icol) = "Pi Value"
    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
myCell.String = "Pi Value"
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