Excel VBA to OpenOffice Basic Converter  

Convert Excel VBA to OpenOffice Basic


Microsoft Excel Visual Basic for Applications (VBA) to OpenOffice Calc Basic converter
Excel Templates by Business Spreadsheets
Excel VBA to OpenOffice Basic Converter


Travel in New Zealand YOUR Way with NZYourWay.com
 
 

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.

Paste Excel VBA code:

I agree that, by using this converter, Business Spreadsheets gives no warranty of any kind, express or implied,
with regard to the accuracy, completeness or the outcomes of using any of the conversion results.


OpenOffice CalcBasic code:


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.

VBA Code Pattern:
OO Basic Format:
Instructions:
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 SubSub 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).ValueoSht = 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:=ActiveSheetDim oSheet as object
oSheet = ThisComponent.CurrentController.ActiveSheet
ThisComponent.Sheets.InsertNewByName( "$1", findSheetIndex(oSheet.Name) )
Application.ScreenUpdating = FalseThisComponent.LockControllers
Sub Worksheet_Activate().*?End SubGlobal 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 = FalseThisComponent.LockControllers
Application.ScreenUpdating = FalseThisComponent.LockControllers
Application.ScreenUpdating = TrueThisComponent.UnlockControllers
Worksheets.Add after:=ActiveSheetDim oSheet as object
oSheet = ThisComponent.CurrentController.ActiveSheet
ThisComponent.Sheets.InsertNewByName( "$1", findSheetIndex(oSheet.Name)$1)
Application.ScreenUpdating = TrueThisComponent.UnlockControllers
Application.ScreenUpdating = TrueThisComponent.UnlockControllers
Private Sub Worksheet_Activate()Sub Worksheet_Activate()
Range(.*?).Offset(.*?,.*?).ValueThisComponent.CurrentController.ActiveSheet.getCellRangeByName($1).getCellByPosition($2,$3).Value
Range(.*?).Cells(.*?,.*?).ValueThisComponent.CurrentController.ActiveSheet.getCellRangeByName($1).getCellByPosition($2,$3).Value
ActiveSheet.Charts(.*?).DeleteThisComponent.CurrentController.ActiveSheet.getCharts().removeByName($1)
.*?.RunAutoMacros xlAutoOpenDim 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.PathDim URLStr as String
Dim $1 as String
GlobalScope.BasicLibraries.LoadLibrary("Tools")
URLStr = ThisComponent.getURL()
$1 = DirectoryNameoutofPath(URLStr, ""/"")
Selection.end(.*?).SelectDim oSheet as Object
oSheet = ThisComponent.CurrentController.ActiveSheet
ThisComponent.CurrentController.select(MoveCursorToEnd(ThisComponent.getCurrentSelection,"$1"))
.*? = ActiveWorkbook.PathDim URLStr as String
URLStr = ThisComponent.getURL()
GlobalScope.BasicLibraries.LoadLibrary("Tools")
$1 = DirectoryNameoutofPath(URLStr, ""/"")
Application.PathSeparatorgetPathSeparator
.*? = ActiveWorkbook.NameDim URLStr as String
Dim $1 as String
GlobalScope.BasicLibraries.LoadLibrary("Tools")
URLStr = ThisComponent.getURL()
$1 = FileNameOutOfPath(URLStr)
.*? = Range(.*?).AddressDim oSheet as Object
oSheet = ThisComponent.CurrentController.ActiveSheet
$1 = CellRangeAddressString(oSheet.getCellRangeByName($2))
Worksheets(.*?).ActivateDim oSheet as Object
oSheet = ThisComponent.Sheets.getByName($1)
ThisComponent.CurrentController.setActiveSheet(oSheet)
If .*?.Value = True ThenDim oDlg as Object
Rem oDlg should be at module level
if oDlg.getControl("$1").getState() = True Then
Worksheets(.*?).DeleteThisComponent.Sheets.removeByName($1)
Range(.*?).SelectDim oSheet as Object
oSheet = ThisComponent.CurrentController.ActiveSheet
ThisComponent.CurrentController.select(oSheet.getCellRangeByName($1))
Range(.*?).ValueThisComponent.CurrentController.ActiveSheet.getCellRangeByName($1).Value
Range(.*?).CleargetCellRangeByName($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(.*?).TextThisComponent.CurrentController.ActiveSheet.getCellRangeByName($1).String
Worksheets(.*?)ThisComponent.Sheets.getByName($1)
ActiveWorksheetThisComponent.CurrentController.ActiveSheet
Worksheets.AddDim oSheets
oSheets = ThisComponent.Sheets
oSheets.insertNewByName("$1", oSheets.getCount())
Number to TextNumber to txt
ActiveWorkbookThisComponent
.*?.ListIndexDim oDlg as Object
Rem oDlg should be at module level - listindex should instead be the text of the combo box
oDlg.getControl($1).Text
ClearContentsclearContents(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)
ActiveSheetThisComponent.CurrentController.ActiveSheet
ActiveCellThisComponent.getCurrentSelection
.*?.Close$1.Close(False)
Range.*?Dim oSheet as Object
oSheet = ThisComponent.CurrentController.ActiveSheet
oSheet.getCellRangeByName($1)
.*?.ShowRem 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

Excel VBA to OpenOffice Basic Converter      
  © 2002-2007 Business Spreadsheets. All Rights Reserved. Legal