basiclibreoffice-calclibreoffice-basic

How to return a modified range without modify the spreadsheet


I want my VLOOKUP_RANGE() taking a ByVal VBA Range 'search' to return a modified Range object, to use like =SUMPRODUCT(VLOOKUP_RANGE(H2:I2;A2:B11;2);H3:I3) but the sheet is modified too (the keys are replace by the values).

My guess is that a Range contains only coords and a sheet key.

Is it possible to do use a function like my example, maybe with another object type ?

Option VBASupport 1
Option Compatible

Function VLOOKUP_RANGE(ByVal range_to_modify, ref_range, index As Integer, Optional sorted As Boolean = False)
    calc_built_in = getProcessServiceManager().createInstance("com.sun.star.sheet.FunctionAccess")
    sheet = ThisComponent.getCurrentController.ActiveSheet
    'VBA Range to LibreOffice
    lo_ref_range = sheet.getCellRangebyName(ref_range.Address)
    
    Dim sum
    For Each cell In range_to_modify
        If cell.Value <> 0 Then
            cell.Value = calc_built_in.callFunction("VLOOKUP", Array(cell.Value, lo_ref_range, index, sorted))
        End If
    Next
    
    VLOOKUP_RANGE = range_to_modify
End Function

Solution

  • In LibreOffice, such a function can be written, for example, like this:

    Option Explicit 
    
    Function VLOOKUP_RANGE(range_to_modify As Variant, ref_range As Variant, index As Integer) As Variant 
    Dim i As Long, j As Long, m As Long
    
        If index < LBound(ref_range,2) Or index > UBound(ref_range,2) Then Exit Function 
    
        For i = LBound(range_to_modify,1) To UBound(range_to_modify,1)
            For j = LBound(range_to_modify,2) To UBound(range_to_modify,2)
                For m = LBound(ref_range,1) To UBound(ref_range,1)
                    If range_to_modify(i, j) = ref_range(m, 1) Then
                        range_to_modify(i, j) = ref_range(m, index)
                        Exit For 
                    EndIf 
                Next m
            Next j
        Next i
        VLOOKUP_RANGE = range_to_modify
    End Function
    

    However, it is preferable to use formulas made up of Calc's built-in functions, as suggested in a comment.