How do I make vba code compatible with libre office

excel, libreoffice, openoffice.org, vba

Solution

You must translate the portions that manipulate the document to use the UNO API. Sadly, this can be tricky depending on what your macro does. Basic statements work directly. Modifying a document generally does not.

Range(Cells(CurrentRow, i), Cells(CurrentRow, i)).Value = "a"

The Cells command returns a specific cell based on a row and column. So, you need the current row. Here is some craziness to get the active cell:

Sub RetrieveTheActiveCell()
  Dim oOldSelection 'The original selection of cell ranges
  Dim oRanges       'A blank range created by the document
  Dim oActiveCell   'The current active cell
  Dim oConv         'The cell address conversion service
  Dim oDoc
  oDoc = ThisComponent

  REM store the current selection
  oOldSelection = oDoc.CurrentSelection

  REM Create an empty SheetCellRanges service and then select it.
  REM This leaves ONLY the active cell selected.
  oRanges = oDoc.createInstance("com.sun.star.sheet.SheetCellRanges")
  oDoc.CurrentController.Select(oRanges)

  REM Get the active cell!
  oActiveCell = oDoc.CurrentSelection

  oConv = oDoc.createInstance("com.sun.star.table.CellAddressConversion")
  oConv.Address = oActiveCell.getCellAddress
  Print oConv.UserInterfaceRepresentation
  print oConv.PersistentRepresentation

  REM Restore the old selection, but lose the previously active cell
  oDoc.CurrentController.Select(oOldSelection)
End Sub

When you have the active cell, you get the cell address, and from that, you have the row. You do not need to use the range at all, since you only care about a single cell, so, you get the active sheet and then get a particular cell from the sheet.

Something like this: ThisComponent.getCurrentController().getActiveSheet().getCellByPosition(nCol, nRow).getString() = "a"

I don't feel like figuring out what this does

Selection.SpecialCells(xlBlanks).Delete shift:=xlToLeft

Problem

I have recently migrated to pclinuxos from windows and seem to like it. The only problem I am facing is that libreoffice, the default spreadsheet package is not compatible with excel macros. Below is the vba code I have: ``` Option VBASupport Sub DeleteToLeft() Selection.SpecialCells(xlBlanks).Delete shift:=xlToLeft End Sub Function SinceLastWash() Application.Volatile WashCount = 0 WearCount = 0 CurrentRow = Application.ThisCell.Row For i = 3 To 35 If Range(Cells(CurrentRow, i), Cells(CurrentRow, i)).Value = "a" Then WearCount = WearCount + 1 End If If Range(Cells(CurrentRow, i), Cells(CurrentRow, i)).Value = "q" Then WashCount = WashCount + 1 WearCount = 0 End If Next i SinceLastWash = WearCount End Function Function testhis() testhis = Application.ThisCell.Row End Function ``` Is there a way to convert this code to make it compatible with libreoffice or do I have to learn an altogether new language like python? Learning python would not be a problem but is not a solution to my problem as I have many work related files in excel which have a lot of vba code and it is not possible for me to use open office/libreoffice at work... I just want to add that the function SinceLastWash gives the correct value in some cells where I use it and in others gives an error, #NAME? Thanks

Original source