, 2 min read

Programming in Excel VBA Compared To LibreOffice (OpenOffice) Calc

Photo

Programming in Excel VBA is quite easy. Below are some simple basic concepts to access and change the spreadsheet data.

  1. Worksheets(i) references the i-th sheet, usually they are called Sheet1, Sheet2, Sheet3
  2. Cells(i,j) references i-th row, j-th column, e.g., Cells(2,3) is C2
  3. Cells(i,j).Value is the content of the cell
  4. `Cells(i,j).AddComment("my comment")` adds a comment to the cell
  5. Cells(i,j).Interior.Color sets the background color of the cell
  6. MsgBox(string) for displaying a short text in a message box
  7. IsEmpty(cell) checks whether cell is empty
  8. Worksheets(i).Cells.Interior.ColorIndex=xlNone to clear background color in all cells in a worksheet
  9. Worksheets(i).Cells.ClearComments to clear all comments in all cells in a worksheet
  10. Worksheets(i).Cells.Clear to wipe out all content in a worksheet
  11. Worksheets(i).Activate makes i-th worksheet active (bring to foreground)
  12. Application.Dialogs(xlDialogSaveAs).Show "filename.csv", xlCSV open file-save-as dialog
  13. UCase(...) return upper case of argument

The usual BASIC-style statements are used, e.g.,

  1. for i=1 to n ... next
  2. if ... then ... else ... end if
  3. select case variable case value ...end select
  4. Dim x as String, Dim i as Integer

That's basically all it needs to program in Excel VBA.

Doing the same in LibreOffice (OpenOffice) is somewhat similar but usually much more arcane and more dogmatic. Information on various BASIC functionality seems to be spread across various internet forums.

In case one wants a push-button in the spreadsheet: The connection between clicking on a push button and its association with a subroutine is not by naming convention but rather by explicit association with Events.

Now the above Excel functionality in LibreOffice.

  1. ThisComponent.getSheets().getByIndex(0) references the first sheet, usually called Sheet1
  2. ThisComponent.getSheets().getByIndex(0).getCellByPosition(i,j).getString() fetching character string from cell at row (i+1), and column (j+1)
  3. ...getValue() for the numeric value
  4. ThisComponent.getSheets().getByIndex(0).Annotations.insertNew( ThisComponent.getSheets().getByIndex(0).getCellByPosition(0,0).getCellAddress, "New comment" ) for adding a comment
  5. ThisComponent.getSheets().getByIndex(0).getCellByPosition(0,0).CellBackcolor = 48000 sets the background color
  6. MsgBox(string) as above
  7. ThisComponent.getSheets().getByIndex(0).getCellByPosition(0,0).Type = 2 to check if cell is empty
  8. ThisComponent.getSheets().getByIndex(1).ClearContents( 1023 ) to delete everything in that sheet
  9. See ClearContents() above
  10. See ClearContents() above
  11. ThisComponent.getcurrentController.setActiveSheet( ThisComponent.getSheets().getByIndex(2) ) to activate sheet #3
  12. Somehow the lines with ThisComponent.storeAsUrl("file://tmp/Test.csv", args2() ), but didn't dwell into that any further
  13. ...setString( UCase(...getString())) to uppercase a cell

See LibreOffice Calc Guide and wiki.openoffice.org.