LibreOffice Macro始终显示#NULL！重新打开文件后 [英] LibreOffice Macro always show #NULL! after reopening the file

问题描述

我在LibreOffice Calc中编写了一个宏，它能够正确运行.但是，如果我关闭文件并重新打开，它将始终显示#NULL!而不是正确的值.我在这里想念什么?

I wrote a macro in LibreOffice Calc and it is able to run correctly. But if I close the file and reopen, it always show #NULL! instead of the correct value. What am I missing here?

我的宏代码

Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1
Function Calculate(CalType As String) As Double
'
' Calculate Macro
'

Dim i As Integer
Calc = 0

i = 1

Do While Not IsEmpty(Cells(i, 2))
    If (Cells(i, 3).Value = CalType And (Cells(i,2) = "A" Or Cells(i,2) = "B")) Then
        Calculate = Calculate + Cells(i, 4).Value
    ElseIf (Cells(i, 3).Value = CalType And Cells(i,2) = "C") Then
        Calculate = Calculate - Cells(i, 4).Value
    End If
    i = i + 1
Loop

'
End Function

调用函数将类似于=Calculate(J6)

文件另存为.ods格式.

推荐答案

Cells调用对我根本不起作用.它来自VBA，而不是LO Basic.但是我不认为这是主要问题.

The Cells call did not work at all for me. It is from VBA, not LO Basic. However I do not think that is the main problem.

LibreOffice希望用户定义的函数将很简单，仅访问包含公式的单元格.由于调用该函数时，电子表格尚未完全加载，因此无法读取其他单元格.

LibreOffice expects that user-defined functions will be simple, only accessing the cell that contains the formula. Since the spreadsheet has not been fully loaded yet when the function is called, it is not possible to read other cells.

解决方法是忽略错误，并等到文档完全加载后再运行该功能.以以下代码为例:

The workaround is to ignore errors and wait until the document is fully loaded before running the function. Take the following code as an example:

Function ReadOtherCell(row, col)
    On Error GoTo ErrorHandler
    oSheet = ThisComponent.CurrentController.ActiveSheet()
    oCell = oSheet.getCellByPosition(row, col)
    ReadOtherCell = "value is '" & oCell.getString() & "'"
    Exit Function
    ErrorHandler:
        Reset
End Function

Sub RecalculateAll
    ' This is for user-defined functions that need to read the spreadsheet.
    ' Assign it to the "View created" event,
    ' because before that, the spreadsheet is not fully loaded.
    ThisComponent.calculateAll
End Sub

在A1中输入foo，在A2中输入=ReadOtherCell(0,0).到目前为止，这仍然存在相同的问题-首次打开文档时，它将失败.

Enter foo in A1, and =ReadOtherCell(0,0) in A2. So far, this has the same problem -- It will fail when the document is first opened.

现在，转到Tools -> Customize.在事件"选项卡中，突出显示View created.按Macro...并找到RecalculateAll功能.然后按确定.

Now, go to Tools -> Customize. In the Events tab, highlight View created. Press Macro... and find the RecalculateAll function. Then press OK.

现在，当关闭并重​​新打开文档时，单元格A2应该显示结果value is 'foo'.

Now when the document is closed and reopened, cell A2 should show the result value is 'foo'.

这是从B. Marcelly在 http://ooo-forums.apache.org/en/forum/viewtopic.php?f=20&t=73090&sid=f92a89d676058ab597b4b4494833b2a0 .

This is derived from B. Marcelly's answer at http://ooo-forums.apache.org/en/forum/viewtopic.php?f=20&t=73090&sid=f92a89d676058ab597b4b4494833b2a0.

这篇关于LibreOffice Macro始终显示#NULL！重新打开文件后的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！