☰

用于查找 LibreOffice 宏中最后填充的列的函数

tag-icon • tag-icon macros openoffice libreoffice
用于查找 LibreOffice 宏中最后填充的列的函数

我正在尝试查找特定行的最后一列的列索引，出于同样的目的，我正在尝试在 LibreOffice 中编写一个函数。我必须首先说明，我是宏编写方面的新手，尤其是 LibreOffice，因为我有 Excel 背景。虽然我曾尝试在论坛的帮助下编写一个函数，但我无法完全纠正列函数，以下是用于查找最后一行的原始函数的链接：

https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=10817

如果有人能帮助我找到函数中的错误，我将不胜感激，问题似乎仅出在列索引部分。

Function LastColumnIndex (InformedRow, Optional InformedSheet) as long
'this function returns the index of the last column with data in a row
'it returns -1 if the whole row is empty
   Dim oSheet As Object, C as Long
   Dim oColumn As Object, oFinder As Object, oResult as object
   Dim PartsOfTheName   

   '------- Sheet -------
   If IsMissing(InformedSheet) then
      oSheet = ThisComponent.CurrentController.ActiveSheet
   ElseIf IsNumeric(InformedSheet) then
      oSheet = ThisComponent.Sheets(InformedSheet)
   Else
      oSheet = ThisComponent.Sheets.GetByName(InformedSheet)
   End If

   '------- Row -------
   If Not IsNumeric(InformedRow) then
      Dim AllRowNames (0 to 1048575)      
      AllRowNames = oSheet.Rows.ElementNames
      For i = 0 to 1048575
         If AllRowNames(i) = UCase(InformedRow) then
            C = i
         End If
      Next
   Else
      C = InformedRow
   End If

   '------- Search -------
   oRow = oSheet.Rows(C)
   oFinder = oRow.createSearchDescriptor
   oFinder.searchRegularExpression = true
   oFinder.SearchString = "."
   oResult = oRow.FindAll(oFinder)

   '------- Column Index -------
   If Not IsNull(oResult) then
      ResultName$ = oResult.AbsoluteName
      PartsOfTheName = Split(ResultName,"$")
      LastColumnIndex = Val(PartsOfTheName(ubound(PartsOfTheName))) - 1
   Else
      LastColumnIndex = - 1
   End If
End Function

答案1

很抱歉，但代码有很多问题。看起来你对代码应该关于行还是列感到困惑，哪些部分应该是数字而不是字母命名，以及如何使用 UNO 对象。你试过自省工具吗？X射线？

这是一个可以起作用的函数：

Function LastColumnIndex (InformedRow As Long, Optional InformedSheet) As Long
    'this function returns the index of the last column with data in a row
    'it returns -1 if the whole row is empty
    Dim oSheet As Object
    Dim oColumn As Object, oFinder As Object, oResult as object
    Dim PartsOfTheName
    Dim LastColumnName As String

    '------- Sheet -------
    If IsMissing(InformedSheet) then
        oSheet = ThisComponent.CurrentController.ActiveSheet
    ElseIf IsNumeric(InformedSheet) then
        oSheet = ThisComponent.Sheets(InformedSheet)
    Else
        oSheet = ThisComponent.Sheets.GetByName(InformedSheet)
    End If

   '------- Search -------
   oRow = oSheet.Rows(InformedRow)
   oFinder = oRow.createSearchDescriptor
   oFinder.searchRegularExpression = true
   oFinder.SearchString = "."
   oResult = oRow.FindAll(oFinder)

   '------- Column Index -------
   LastColumnIndex = -1
   If Not IsNull(oResult) then
      ResultName$ = oResult.AbsoluteName
      PartsOfTheName = Split(ResultName,"$")
      LastColumnName = PartsOfTheName(UBound(PartsOfTheName) - 1)
      oColumns = oSheet.getColumns()
      If oColumns.hasByName(LastColumnName) Then
          oColumn = oColumns.getByName(LastColumnName)
          LastColumnIndex = oColumn.getRangeAddress().StartColumn
      End If
   End If
End Function

我用这个程序测试了它：

Sub LastColumnIndexExample
    MsgBox LastColumnIndex(3, 0)  'row 4 of the first sheet
End Sub

相关内容