CODEWITHSUNDEEP
basiclibreoffice-calclibreoffice-basic
Vincent
Vincent

Reputation: 45

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

Upvotes: 0

Views: 73

Answers (1)

JohnSUN
JohnSUN

Reputation: 2539

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.

Upvotes: 1

Related Questions