LibreOffice Calc中StarBasic自定义VLOOKUP函数调用报错求助

问题根源分析 #

1. 初始版本报错原因：DEMO函数传递的是字符串格式的区域"$Sheet2.A:G"，但MyVlook的DataArray参数声明为Object类型（要求单元格区域对象），类型不匹配导致Object Variable not set错误。
2. 修改后异常原因：CalcNight调用MyVlook时传入的是字符串"false"，但函数参数SortedRangeLookup声明为Byte类型，类型不匹配触发com.sun.star.lang.IllegalArgumentException；另外硬编码单元格范围$Sheet2.A1:G10000不够灵活，且重复创建FunctionAccess对象会降低效率。

修复方案 #

1. 增强函数兼容性，同时支持字符串区域和对象区域 #

修改MyVlook，自动将字符串格式的区域转换为单元格对象，同时修正参数类型问题，添加可选参数默认值：

Function MyVlook(Lookup As Variant, DataArray As Variant, Index As Integer, Optional SortedRangeLookup As Boolean = False) As Variant
    Dim oSheetRange As Object
    Dim oService As Object
    Dim vArgs As Variant
    
    ' 处理DataArray：如果是字符串，转换为单元格区域对象；如果是直接传入的区域对象，直接使用
    If VarType(DataArray) = vbString Then
        oSheetRange = ThisComponent.getSheets().getCellRangesByName(DataArray)(0)
    ElseIf VarType(DataArray) >= vbObject Then
        oSheetRange = DataArray
    Else
        ' 无效参数返回空值
        MyVlook = Empty
        Exit Function
    End If
    
    ' 调用原生VLOOKUP并处理返回值
    Set oService = CreateUnoService("com.sun.star.sheet.FunctionAccess")
    vArgs = Array(Lookup, oSheetRange, Index, SortedRangeLookup)
    
    On Error Resume Next
    MyVlook = oService.callFunction("VLOOKUP", vArgs)
    If Err.Number <> 0 Then
        MyVlook = Empty
    End If
    On Error GoTo 0
End Function

2. 修正调用方的参数传递问题 #

• DEMO函数修正：确保参数传递完整，SortedRangeLookup传入布尔值或省略（使用默认值）：

Function DEMO(lookup As Variant) As Variant
   Dim DataArray As String
   DataArray = "$Sheet2.A:G"
   ' 传入布尔值False，或省略该参数使用默认值
   DEMO = MyVlook(lookup, DataArray, 6, False)
END Function

• CalcNight函数修正：将字符串"false"改为布尔值False，匹配参数类型：

Function CalcNight(icaoDep As String, icaoArr As String, t1 As Date, t2 As Date) As Variant
    Dim lat1 As Variant, lon1 As Variant
    Dim lat2 As Variant, lon2 As Variant
    Dim sDataRange As String
    
    sDataRange = "$Sheet2.A:G" ' 统一定义区域，避免硬编码
    
    'Try and find the coordinates in sheet 2
    lat1 = MyVlook(icaoDep, sDataRange, 6, False)

    'If the AIRPORT can not be found
    If IsEmpty(lat1) Then
        CalcNight = icaoDep & " Airport coordinates unknown"
        Exit Function
    End If
    lon1 = MyVlook(icaoDep, sDataRange, 7, False)

    lat2 = MyVlook(icaoArr, sDataRange, 6, False)

    'If the AIRPORT can not be found
    If IsEmpty(lat2) Then
        CalcNight = icaoArr & " Airport coordinates unknown"
        Exit Function
    End If
    lon2 = MyVlook(icaoArr, sDataRange, 7, False)

    'Rest of script that continues calculations with LAT/LON
    '...
End Function

关键优化点 #

• 参数类型兼容：通过VarType判断输入的DataArray类型，自动转换字符串区域为对象，同时支持直接传入单元格区域（保持单元格调用的兼容性）。
• 错误处理：添加On Error捕获调用原生VLOOKUP时的异常，返回空值避免函数抛出错误。
• 参数规范化：将SortedRangeLookup改为Boolean类型（更符合语义），并设置默认值，简化调用。
• 代码复用：在CalcNight中统一定义数据区域字符串，减少重复硬编码。

内容的提问来源于stack exchange，提问作者Jacco