Programming in Excel VBA Compared To LibreOffice (OpenOffice) Calc

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.

LibreOffice Calc

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.

Advertisements

3 thoughts on “Programming in Excel VBA Compared To LibreOffice (OpenOffice) Calc

  1. Pingback: Statistics of this Blog: 2.600 Views, 78 Countries, 20 Comments | Elmar Klausmeier's Weblog

  2. Buon Giorno mi chiamo Maurizio .
    Avrei una Damanda da farvi ed e questa : Io e da Sempre che programmo di tutto e di più le cose più Svariate usando Excel 2007 della Microsoft .
    Ora peò e da un pò di tempo che stò cercando di Programmare anche sfruttando OpenOffice Calc , ma attualmente non ci capisco nulla .
    Per qui volevo sapere da voi : E possibile Condovodere i Miei programmi scritti in VBA con OpenOffice Calc e se cosi Fosse Come si fà per accedere hai suddetti Moduli ; Creare delle macro ecc…!
    In più come mai i miei programmi sempre scritti con Microsoft offece 2007 (Excel) se tento di Aprirli con OpenOffice calc ; Si li apre ma non mi funziona nulla sè tento di premere un tasto del mio programma Ecc….!
    Come posso Oviare a questo Inconvegnente ; Grazie .
    Sinceri saluti da A.Maurizio
    la mia Email è : “a.maury1704@gmail.com”

    Like

    • Dear Maurizio,

      I am very sorry, my Italian is very bad, and even after running your inquiry through translate.google.com I did not really understand what your inquiry is about. Could you possibly translate your comment in english?

      Thank you.

      Elmar.

      Like

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s