Can run macro in callFunction ("VLOOKUP")

[eugenefoxx]

Hello! Please, prompt me, can run this Calc functions from a macro

Code: Select all

Private Sub Macro_VLOOKUP()
     Dim oDoc, oDoc2 as Object
     Dim oRange1, oRange2, oRange3, oRange4, oRange5 as Object
     Dim oTextField1, oTextField2, oTextField3 as Object
     Dim oSheet, oSheet2  as Object
     Dim oFile, oFile2 as String
     Dim oURL, oURL2 as String
     Dim Text1, Text2, Text3 As Object
     Dim Sel1 As New com.sun.star.awt.Selection
     'Dim a, b, c, iLastrow As Long, i As Long, ii As Long
     Dim CellRange, CellRange2 as Object
     Dim SearchValue as Object
     Dim Column as Variant
     Dim Mode as Variant
     Dim svc
     Dim arg
     Dim Value
     Dim i, r as Integer
        oFile = "C:\Users\User\Desktop\Reserved_template.ods"
   	oURL = convertToUrl  (oFile)
        oDoc = starDeskTop.loadComponentFromURL (oURL, "_blank", 0, _
  	                                                                                    Array() )
        oSheet = oDoc.Sheets (0)
        oFile2 = "C:\Users\Евгений\Desktop\MB52.xlsx"
		oURL2 = convertToUrl  (oFile2)
		oDoc2 = starDeskTop.loadComponentFromURL (oURL2, "_blank", 0, _
		                                                     Array() )
  	                                                     
		oSheet2 = oDoc2.Sheets (0)
		CellRange = oDoc2.Sheets (0)
		
		oSheet = oDoc.getSheets().getByIndex(0)
		
		CellRange = oDoc2.getSheets().getByIndex(0).getCellRangeByName("A1:B10000")
		For i = 1 to 10000
		SearchValue = oDoc.getSheets().getByIndex(0).getCellByPosition (9,i) ("I & i+1 & ")
		Next i
		Column = 2
		Mode = 0
		svc = createUnoService( "com.sun.star.sheet.FunctionAccess" )
		arg = Array(SearchValue, CellRange, Column, Mode)

		Value = svc.callFunction("VLOOKUP",arg) 'here appear error
		For r = 1 to 10000
		CellRange2 = oDoc.getSheets.getByIndex(0).getCellByPosition (8,r) ("H & r+1 &")
		Next r
		CellRange2.setDate(svc.callFunction) 'here I wish set the resulting value

[Zizi64]

Tre 'Range' parameter of the called function VLOOKUP() must be data ARRAY type - if I remember exactly...

[Villeroy]

Code: Select all

Sub Macro_VLOOKUP()
      oDoc = ThisComponent
      oSheet = oDoc.Sheets (0)
      CellRange = oSheet.getCellRangeByName("A1:B10000")
      SearchValue = 13
      Column = 2
      Mode = 0
      svc = createUnoService( "com.sun.star.sheet.FunctionAccess" )
      arg = Array(SearchValue, CellRange, Column, Mode)
      Value = svc.callFunction("VLOOKUP",arg)
      print typename(value), value
End Sub

https://en.wikipedia.org/wiki/Cargo_cult_programming

[eugenefoxx]

I so it understand the problem with

Code: Select all

CellRange2.setDate(svc.callFunction) 'here I wish set the resulting value

on this show me programm by message of error "Type: com.sun.star.lang.IllegalArgumentException
Message: arguments len differ!."

[Zizi64]

Can you upload your example file(s) with the macro code?

[eugenefoxx]

OK. You are welcome.

[Villeroy]

Dates do not exist in spreadsheets. As a spreadsheet programmer you should know this.

Code: Select all

CellRange.setValue(value)

[Tutorial] Ten concepts that every Calc user should know long before hacking it.
[UNO, Calc] How to fill spreadsheets cells programmatically

[eugenefoxx]

Villeroy, in this case

Code: Select all

CellRange.setValue(value)

appear the message of error "BASIC runtime error. Property or method not found: setValue."

[Villeroy]

Indeed. One cell has a value. A range has many values which can be set in one go by setDataArray(). See documentation.

All in all, this code is useless anyway. You can do this easily with ordinary cell functions.

[eugenefoxx]

May be have a method making macro a-la VLOOKUP without callFunction ("VLOOKUP"). I know this method in VBA.

[Villeroy]

May be have a method making macro a-la VLOOKUP without callFunction ("VLOOKUP"). I know this method in VBA.

Then do it in VBA and stick with MS Excel forever.

[eugenefoxx]

Villeroy, I need create and used this function in LibreOffice exactly. My organization used open source, especially LibreOffice. I'm very interesting, and I try to learn OO basic.

[Villeroy]

There are far too many "VBA experts" who never learned how to program. May be you are the wrong guy for the job?

Code: Select all

someCell.setValue(srv.callFunction("VLOOKUP",Array(range,2,0))

but all this is useless bullshit anyway because you program things that don't need to be programmed.
Next time when you switch to another spreadsheet product you'll fight the same idiotic problems.

[eugenefoxx]

I do not practice directly in programming. I optimizes its work through macro programming. I try to succeed in this.

[eugenefoxx]

Thank you for the promt about idiotic problems. I have the decision about this question with the help of cell functions, written in macro code.

[moriturimax]

It's funny how people insult other people who use VBA and don't need fancy programming skills for most solutions.. whereas in Openoffice you have to be a nuclear engineer to figure out how to even go about doing a simple table lookup in the provided BASIC (emphasis on BASIC) language... and don't bat an eye when you have to search the Internet for a solution that VBA does as a matter of course with a couple simple modifications. One would think OO by now would have simplified this task.

The irony, it burns. Course, I am trying to learn OOBasic now too since nobody seems to want to pay to use Excel. Which is fine. But the insults could be dropped and that would be fine too.

[Zizi64]

whereas in Openoffice you have to be a nuclear engineer to figure out how to even go about doing a simple table lookup in the provided BASIC (emphasis on BASIC) language...

The StarBasic (the OpenOffice/LibreOffice Basic) is a very-very-very simple programming language. Everybody can learn it in five/ten minutes.

But you need study the API functions and procedures. You can call the API functions from all of the supported programming languages and environments - because the AOO/LO are multiplatform softwares. The StarBasic is only one of them.
Yes, that is not such a simple thing... There are hundreds, thousands of different functions for controlling the most of features of the office suite programatically.
API: Application Programming Interface.

[moriturimax]

Good to know, hopefully I'll learn as I go.. been using VBA for 20+ years for small things...

[Lupp]

The 'Range' parameter of the called function VLOOKUP() must be data ARRAY type - if I remember exactly...

In this special case you either don't remember quite correctly - or the behaviour of AOO / LibO changed since you learned about this question.

Lookup functions (and also the .SetDataArray() method of CellRange accept "ordinary" 2D-arrays. The Lbound for columns and for rows as well is arbitrary in addition. Only the number of rows and columns is relevant. (As usual for dynamic arrays in some programming languages.)

See the silly example below which worked "as expected" regarding my above remark, and answered "B".

Code: Select all

Sub Test()
Dim common(2 To 5, 3 To 7) As Variant
common(2,3) = 3   : common(3,3) = 17  : common(4,3) = -8  : common(5,3) = 7
common(2,5) = "A" : common(3,5) = "B" : common(4,5) = "C" : common(5,5) = "D"
fa = CreateUnoService("com.sun.star.sheet.FunctionAccess")
find = 17 : col = 3 : mode = 0
r = fa.CallFunction("VLOOKUP",Array(find, common, col, mode)
MsgBox(r)
End Sub

@moriturimax, @eugenefoxx:

Code: Select all

res = functionAccessObject.CallFunction("THISFUNCTIONNAME",myArray) 

does not allow omission of the array patrameter. If no parameters are to pass simply use Array(), the array with 0 elements.

[moriturimax]

The 'Range' parameter of the called function VLOOKUP() must be data ARRAY type - if I remember exactly...

In this special case you either don't remember quite correctly - or the behaviour of AOO / LibO changed since you learned about this question.

Lookup functions (and also the .SetDataArray() method of CellRange accept "ordinary" 2D-arrays. The Lbound for columns and for rows as well is arbitrary in addition. Only the number of rows and columns is relevant. (As usual for dynamic arrays in some programming languages.)

See the silly example below which worked "as expected" regarding my above remark, and answered "B".

Code: Select all

Sub Test()
Dim common(2 To 5, 3 To 7) As Variant
common(2,3) = 3   : common(3,3) = 17  : common(4,3) = -8  : common(5,3) = 7
common(2,5) = "A" : common(3,5) = "B" : common(4,5) = "C" : common(5,5) = "D"
fa = CreateUnoService("com.sun.star.sheet.FunctionAccess")
find = 17 : col = 3 : mode = 0
r = fa.CallFunction("VLOOKUP",Array(find, common, col, mode)
MsgBox(r)
End Sub

@moriturimax, @eugenefoxx:

Code: Select all

res = functionAccessObject.CallFunction("THISFUNCTIONNAME",myArray) 

does not allow omission of the array patrameter. If no parameters are to pass simply use Array(), the array with 0 elements.

VLook works. It just had a hiccup with a series of numbers until I removed the TRUE/FALSE parameter in the function. I never omitted the array parameter.