如何使 vba 代码与 libre office 兼容

How do I make vba code compatible with libre office

提问by user3125707

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:

我最近从 Windows 迁移到 pclinuxos，似乎很喜欢它。我面临的唯一问题是 libreoffice，默认的电子表格包与 excel 宏不兼容。以下是我拥有的 vba 代码：

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...

有没有办法转换此代码以使其与 libreoffice 兼容，还是我必须学习一种全新的语言，例如 python？学习 python 不是问题，但不是我的问题的解决方案，因为我在 excel 中有许多与工作相关的文件，其中有很多 vba 代码，而且我不可能在工作中使用开放式办公室/libreoffice...

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?

我只想补充一点，SinceLastWash 函数在我使用它的某些单元格中给出了正确的值，而在其他单元格中给出了错误，#NAME？

Thanks

谢谢

采纳答案by Andrew

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.

您必须翻译操作文档的部分才能使用 UNO API。可悲的是，这可能会很棘手，具体取决于您的宏的作用。基本语句直接起作用。修改文档一般不会。

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"

像这样： ThisComponent.getCurrentController().getActiveSheet().getCellByPosition(nCol, nRow).getString() = "a"

I don't feel like figuring out what this does

我不想弄清楚这是做什么的

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

回答by RubberDuck

From LibreOffice's online help file:

来自LibreOffice 的在线帮助文​​件：

With a few exceptions, Microsoft Office and LibreOffice cannot run the same macro code. Microsoft Office uses VBA (Visual Basic for Applications) code, and LibreOffice uses Basic code based on the LibreOffice API (Application Program Interface) environment. Although the programming language is the same, the objects and methods are different.

The most recent versions of LibreOffice can run some Excel Visual Basic scripts if you enable this feature at LibreOffice - PreferencesTools - Options - Load/Save - VBA Properties.

除了少数例外，Microsoft Office 和 LibreOffice 不能运行相同的宏代码。Microsoft Office 使用 VBA（Visual Basic for Applications）代码，而 LibreOffice 使用基于 LibreOffice API（应用程序接口）环境的 Basic 代码。虽然编程语言相同，但对象和方法不同。

如果您在 LibreOffice - PreferencesTools - 选项 - 加载/保存 - VBA 属性中启用此功能，则最新版本的 LibreOffice 可以运行一些 Excel Visual Basic 脚本。

In reality, you would most likely need to sit down with the LibreOffice APIand rewrite the functionality.

实际上，您很可能需要坐下来使用LibreOffice API并重写功能。

回答by A. Harding

In LibreOffice 4.4, the first subroutine will not work at all (I suspect due to all the variables beginning with 'xl'. The other two work perfectly if you change ThisCell to ActiveCell.

在 LibreOffice 4.4 中，第一个子例程根本不起作用（我怀疑是由于所有变量都以“xl”开头。如果将 ThisCell 更改为 ActiveCell，其他两个子例程可以完美运行。

Rather than

而不是

Option VBASupport 

I am using

我在用

Option VBASupport 1
Option Compatible

回答by djikay

The only automatic tool I'm aware of is Business Spreadsheets(note that I have no personal or professional experience nor any affiliation with the site).

我知道的唯一自动工具是Business Spreadsheets（请注意，我没有个人或专业经验，也没有与该网站的任何从属关系）。

It seems specific to OpenOffice but I think it works with LibreOffice too.

它似乎特定于 OpenOffice，但我认为它也适用于 LibreOffice。

In general though, you're better off doing this yourself, as the tool is far from perfect...

但总的来说，你最好自己做这件事，因为该工具远非完美......

回答by coleman984

Selection.SpecialCells(xlBlanks).Delete shift:=xlToLeftdeletes blank cells if I'm not mistaken

Selection.SpecialCells(xlBlanks).Delete shift:=xlToLeft如果我没记错的话，删除空白单元格