StarBasic (巨集轉換)

OpenOffice represented a major cost saving opportunity for the business world, especially for large corporations who spent millions of dollars on Microsoft Office licenses. However, the adoption of OpenOffice has been slow, mainly due to a technical challenge: the lack of macro (VBA) portability.

Since its introduction, spreadsheets, notably Lotus 1-2-3 and Microsoft Excel, have become the most vital tool for any accounting and finance professionals. In the last decade or two, user-friendly programming languages were bundled with spreadsheet applications, and the most popular one is Visual Basic for Application (VBA), developed by Microsoft. Introducing VBA in Excel has resulted in a proliferation of user-developed programs, which significantly boast productivity.

These user-developed programs were never managed centrally, and scattered in millions of spreadsheet files managed by users themselves. When OpenOffice was introduced, its spreadsheet application was called Calc, and came with Calc a language named StarBasic (rooted in the original name of OpenOffice: StarOffice). OpenOffice was initially developed by Sun Microsystems, hence its architecture was inevitably based on Java components. Fortunately, with the huge number of VBA programs developed, Sun had made some effort to bridge the two languages.

Most of my consulting colleagues know that I am obsessed with VBA programming. I developed VBA for games like Sudoku, and even a whole suite of applications that could almost replace ERWIN. Recently I have finally started to learn StarBasic, and converted an old Excel/VBA module to Calc/StarBasic. The reference I found highly useful was: http://documentation.openoffice.org/HOW_TO/various_topics/VbaStarBasicXref.pdf, and let me give you some examples here:

Visual Basic	StarBasic
ActiveWorkbook	ThisComponent
Dim ws as Worksheet set ws = ActiveWorksheet	Dim oSheet as Object oSheet = ThisComponent.CurrentController.ActiveSheet
Worksheets("Sheet1").Activate	Dim oSheet as Object oSheet = ThisComponent.Sheets.getByName("Sheet1") ThisComponent.CurrentController.setActiveSheet(oSheet)
MyNumber = Range("A1").Value	MyNumber = ThisComponent.CurrentController.ActiveSheet. getCellRangeByName("A1").Value (or oSheet.getCellRangeByName("A1").Value, if oSheet has been defined)

Most of the differences are about the referencing of the objects, and the rest of the programming syntax (like IF...THEN...ELSE, FOR...NEXT, etc.) remains pretty much the same. You should be able to look up all major differences in the above documentation. There are few other things you will need to know:

• You will need to put "Option VBASupport 1" at the top of your module.
• You will need to explicitly put "Global" or "Static" for variables which values you need to retain across executions.
• You will need to differentiate whether the value of a cell is a number (use oSheet.getCellRangeByName("A1").Value) or a text (use oSheet.getCellRangeByName("A1").String), meaning there is no implicit variable type casting.

Lastly, do not waste time looking for Immediate Window or inserting Debug.Print, as they are not supported by the Integrated Development Environment (IDE) of Calc yet.

Alright, now you must be wondering why I am so free. I was actually developing a macro that could generate a consistently winning strategy in Blackjack, so that when I am fired (very soon), I can make a living in Sands or Venetian...