Convert Excel VBA to OpenOffice Basic
Microsoft Excel Visual Basic for Applications (VBA) to OpenOffice Calc Basic converter
|
 |
Business Spreadsheets has developed a service to assist in the conversion of Excel VBA code to the equivalent OpenOffice Calc Basic script. The VBA to OO Basic converter does not completely convert Excel VBA macros to OO Basic macros for Calc nor seamlessly migrate Excel VBA projects to OpenOffice environments.
While much of the underlying logic behind OpenOffice Basic compares to that of Visual Basic for Applications, there exists fundamental differences to the way the code is constructed. This Excel VBA to OpenOffice Basic converter offers a starting point for migrating VBA projects in Excel to the OpenOffice or StarOffice Calc application. The converter may also serve as a learning mechanism to better understand the similarities and differences between the VBA and OO Basic coding specifications.
Reference should be made to the material for Porting Excel/VBA to Calc/StarBasic at OpenOffice.org when undertaking migrations from Microsoft Excel to OpenOffice or StarOffice Calc.
The Excel VBA to OO Calc Basic Converter uses code snippet pairs of VBA and OO Basic with place holders so that replacements can be made using regular expressions. Users of the converter are encouraged to submit such snippets here in a community effort to strengthen the results of the VBA to OO Basic conversion process. A full list of current Excel VBA to OO Basic code snippet pairs can be viewed here.
To find assistance, make suggestions or share experiences about using this converter, please see the Help Forum. Business Spreadsheets also offers the free Bond Yield to Maturity Calculator in both Excel VBA and OpenOffice Basic with open source code as an example of an Excel VBA project migrated to OpenOffice Calc.
Submit VBA to OpenOffice Basic Code Conversion Snippets
You are encouraged to submit your own code snippets to add to the database
of VBA to OpenOffice code pairs used for the conversion process. The
conversion process executes regular expressions of the VBA code with
placeholders so that references to objects can be retained and placed
in the appropriate place of the OpenOffice Basic equivalent. To utilize
this, please
read the instructions below before submitting. All submissions will
be reviewed before inclusion.
Code snippets should be at the lowest level of granularity possible meaning that VBA code should be split into the smallest components possible and still have an OpenOffice Basic equivalent.
In order to use regular expressions effectively it is best to illustrate with a existing code pair as an example.
VBA Code Pattern: Range(.*?).Offset(.*?,.*?).Value
OO Basic Format: ThisComponent.CurrentController.ActiveSheet.getCellRangeByName($1).getCellByPosition($2,$3).Value
Note that the $ signs followed by the numbers in the OO Basic Format represent the order of the .*? content in the VBA code - i.e. $1 is will be replaced by the content of the first instance .*? in the VBA Code Pattern and so on. More detailed information on applying more complex regular expression conversions can be found here.
Current VBA to OO Basic Code Snippet Pairs:Show >>
| VBA Code Pattern | OpenOffice Basic Format |
| Sub Pulsante41_Clic() Dim balue ActiveSheet.PrintOut ActiveSheet.PrintOut balue = Worksheets("DATI").Cells(2, 14) Worksheets("DATI").Cells(2, 14) = Worksheets("DATI").Cells(2, 14) + 1 Worksheets("FATTURA").Cells(2, 5) = Date ActiveWorkbook.SaveCopyAs "C:\FATTURE\FAT_" + Mid(Date, 7, 10) + Mid(Date, 4, 2) + Mid(Date, 1, 2) + "_" + CStr(balue) + ".XLS" Worksheets("FATTURA").Range("E2").Formula = "=Today()" End Sub | Sub Pulsante41_Clic() Dim balue as integer rem ThisComponent.CurrentController.ActiveSheet.PrintOut rem ThisComponent.CurrentController.ActiveSheet.PrintOut balue = ThisComponent.Sheets.getByName()("DATI").Cells(2, 14) ThisComponent.Sheets.getByName()("DATI").Cells(2, 14) = ThisComponent.Sheets.getByName()("DATI").Cells(2, 14) + 1 ThisComponent.Sheets.getByName()("FATTURA").Cells(2, 5) = Date ThisComponent.SaveCopyAs "C:\FATTURE\FAT_" + Mid(Date, 7, 10) + Mid(Date, 4, 2) + Mid(Date, 1, 2) + "_" + CStr(balue) + ".XLS" ThisComponent.Sheets.getByName()("FATTURA").Dim oSheet as Object oSheet = ThisComponent.CurrentController.ActiveSheet oSheet.getCellRangeByName($1)("E2").Formula = "=Today()" End Sub |
| Sub WR_Nxt() WR_TxtBx.Value = WR_TxtBx.Value + 1 End Sub | Sub WR_Nxt() WR_TxtBx.Text = Int(WR_TxtBx.Text) + 1 End Sub |
| Application.Wait (Now() + TimeValue("00:00:0.*?")) | wait |1|100 |
| Workbooks.Open (ActiveWorkbook.Path & "\.*?") | Dim DirectoryName as String Dim NoArgs() NewWorkbook = StarDesktop.loadComponentFromURL ("file:///" & DirectoryName & ""/$1"", ""_blank"",0 ,NoArgs() ) |
| .*? = WorksheetFunctions.Average(Range.*?) | FuncService = createunoservice("com.sun.star.sheet.FunctionAccess") oSheet = ThisComponent.CurrentController.ActiveSheet $1 = FuncService.callFunction(""AVERAGE"",array(oSheet.getCellRangeByName(""$2""))) |
| Worksheets.Add before:= Worksheets(.*?) | Dim oSheet as object oSheets.InsertNewByName( "?1?", findSheetIndex($1)) |
| val = Activesheet.Cells(row,col).Value | oSht = ThisComponent.CurrentController.ActiveSheet val = oSht.getCellByPosition(col,row).formula |
| Worksheets.Add after:= Worksheets(.*?) | Dim oSheet as object oSheets.InsertNewByName( "?1?", findSheetIndex($1) + 1) |
| Worksheets.Add before:=ActiveSheet | Dim oSheet as object oSheet = ThisComponent.CurrentController.ActiveSheet ThisComponent.Sheets.InsertNewByName( "$1", findSheetIndex(oSheet.Name) ) |
| Application.ScreenUpdating = False | ThisComponent.LockControllers |
| Sub Worksheet_Activate().*?End Sub | Global oActiveSheetListener as Object Global CurrentWorksheetName as String Sub WorksheetActivationListenerOn CurrentWorksheetName = "" oActiveSheetListener = createUnoListener(""ACTIVESHEET_"", ""com.sun.star.beans.XPropertyChangeListener"") ThisComponent.CurrentController.addPropertyChangeListener(""ActiveSheet"", oActiveSheetListener)$1 End Sub Sub WorksheetActivationListenerOff ThisComponent.CurrentController. removePropertyChangeListener(""ActiveSheet"", oActiveSheetListener) End Sub |
| Application.ScreenUpdating = False | ThisComponent.LockControllers |
| Application.ScreenUpdating = False | ThisComponent.LockControllers |
| Application.ScreenUpdating = True | ThisComponent.UnlockControllers |
| Worksheets.Add after:=ActiveSheet | Dim oSheet as object oSheet = ThisComponent.CurrentController.ActiveSheet ThisComponent.Sheets.InsertNewByName( "$1", findSheetIndex(oSheet.Name)$1) |
| Application.ScreenUpdating = True | ThisComponent.UnlockControllers |
| Application.ScreenUpdating = True | ThisComponent.UnlockControllers |
| Private Sub Worksheet_Activate() | Sub Worksheet_Activate() |
| Range(.*?).Offset(.*?,.*?).Value | ThisComponent.CurrentController.ActiveSheet.getCellRangeByName($1).getCellByPosition($2,$3).Value |
| Range(.*?).Cells(.*?,.*?).Value | ThisComponent.CurrentController.ActiveSheet.getCellRangeByName($1).getCellByPosition($2,$3).Value |
| ActiveSheet.Charts(.*?).Delete | ThisComponent.CurrentController.ActiveSheet.getCharts().removeByName($1) |
| .*?.RunAutoMacros xlAutoOpen | Dim DirectoryName as String Dim NewWorkbook as Object Dim Args(0) as new com.sun.star.beans.PropertyValue Args(0).Name = "MacroExecutionMode" Args(0).Value = com.sun.star.document.MacroExecMode.ALWAYS_EXECUTE NewWorkbook = StarDesktop.loadComponentFromURL (""file:///"" & DirectoryName & ""/$1"", ""_blank"",0 ,Args() ) |
| .*? = ActiveWorkbook.Path | Dim URLStr as String Dim $1 as String GlobalScope.BasicLibraries.LoadLibrary("Tools") URLStr = ThisComponent.getURL() $1 = DirectoryNameoutofPath(URLStr, ""/"") |
| Selection.end(.*?).Select | Dim oSheet as Object oSheet = ThisComponent.CurrentController.ActiveSheet ThisComponent.CurrentController.select(MoveCursorToEnd(ThisComponent.getCurrentSelection,"$1")) |
| .*? = ActiveWorkbook.Path | Dim URLStr as String URLStr = ThisComponent.getURL() GlobalScope.BasicLibraries.LoadLibrary("Tools") $1 = DirectoryNameoutofPath(URLStr, ""/"") |
| Application.PathSeparator | getPathSeparator |
| .*? = ActiveWorkbook.Name | Dim URLStr as String Dim $1 as String GlobalScope.BasicLibraries.LoadLibrary("Tools") URLStr = ThisComponent.getURL() $1 = FileNameOutOfPath(URLStr) |
| .*? = Range(.*?).Address | Dim oSheet as Object oSheet = ThisComponent.CurrentController.ActiveSheet $1 = CellRangeAddressString(oSheet.getCellRangeByName($2)) |
| Worksheets(.*?).Activate | Dim oSheet as Object oSheet = ThisComponent.Sheets.getByName($1) ThisComponent.CurrentController.setActiveSheet(oSheet) |
| If .*?.Value = True Then | Dim oDlg as Object Rem oDlg should be at module level if oDlg.getControl("$1").getState() = True Then |
| Worksheets(.*?).Delete | ThisComponent.Sheets.removeByName($1) |
| Range(.*?).Select | Dim oSheet as Object oSheet = ThisComponent.CurrentController.ActiveSheet ThisComponent.CurrentController.select(oSheet.getCellRangeByName($1)) |
| Range(.*?).Value | ThisComponent.CurrentController.ActiveSheet.getCellRangeByName($1).Value |
| Range(.*?).Clear | getCellRangeByName($1).clearContents(com.sun.star.sheet.CellFlags.VALUE + com.sun.star.sheet.CellFlags.STRING + com.sun.star.sheet.CellFlags.DATETIME + com.sun.star.sheet.CellFlags.ANNOTATION + com.sun.star.sheet.CellFlags.FORMULA + com.sun.star.sheet.CellFlags.HARDATTR + com.sun.star.sheet.CellFlags.STYLES + com.sun.star.sheet.CellFlags.OBJECTS + com.sun.star.sheet.CellFlags.EDITATTR) |
| Range(.*?).Text | ThisComponent.CurrentController.ActiveSheet.getCellRangeByName($1).String |
| Worksheets(.*?) | ThisComponent.Sheets.getByName($1) |
| ActiveWorksheet | ThisComponent.CurrentController.ActiveSheet |
| Worksheets.Add | Dim oSheets oSheets = ThisComponent.Sheets oSheets.insertNewByName("$1", oSheets.getCount()) |
| Number to Text | Number to txt |
| ActiveWorkbook | ThisComponent |
| .*?.ListIndex | Dim oDlg as Object Rem oDlg should be at module level - listindex should instead be the text of the combo box oDlg.getControl($1).Text |
| ClearContents | clearContents(com.sun.star.sheet.CellFlags.VALUE + com.sun.star.sheet.CellFlags.STRING + com.sun.star.sheet.CellFlags.DATETIME) |
| .addItem .*? | Rem change 0 to array order 0 to count |1|.addItem($1,0) |
| ActiveSheet | ThisComponent.CurrentController.ActiveSheet |
| ActiveCell | ThisComponent.getCurrentSelection |
| .*?.Close | $1.Close(False) |
| Range.*? | Dim oSheet as Object oSheet = ThisComponent.CurrentController.ActiveSheet oSheet.getCellRangeByName($1) |
| .*?.Show | Rem oDlg should be visible at the module level Dim oDlg As Object DialogLibraries.LoadLibrary("Standard") oDlg = CreateUnoDialog(DialogLibraries.Standard.$1) oDlg.execute() |
| Find Excel Templates & Excel Add-ins in the Excel Add-ins & Templates Directory |
| Download the PDF Whitepaper: Empowering Business Decision Making with Business Spreadsheets A Practical User Guide |
