Can run macro in callFunction ("VLOOKUP")

Creating a macro - Writing a Script - Using the API

Can run macro in callFunction ("VLOOKUP")

Postby eugenefoxx » Sun Aug 16, 2015 1:22 am

Hello! Please, prompt me, can run this Calc functions from a macro
Code: Select all   Expand viewCollapse view
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
LibreOffice 4.4.0.3 on Windows 8.1
eugenefoxx
 
Posts: 30
Joined: Thu Jun 04, 2015 11:59 pm

Re: Can run macro in callFunction ("VLOOKUP")

Postby Zizi64 » Sun Aug 16, 2015 7:55 am

Tre 'Range' parameter of the called function VLOOKUP() must be data ARRAY type - if I remember exactly...
Tibor Kovacs, Hungary; LO4.4.7, LO5.4.7 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.1.0 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7162
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Can run macro in callFunction ("VLOOKUP")

Postby Villeroy » Sun Aug 16, 2015 2:01 pm

Code: Select all   Expand viewCollapse view
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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25703
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can run macro in callFunction ("VLOOKUP")

Postby eugenefoxx » Sun Aug 16, 2015 8:03 pm

I so it understand the problem with
Code: Select all   Expand viewCollapse view
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!."
LibreOffice 4.4.0.3 on Windows 8.1
eugenefoxx
 
Posts: 30
Joined: Thu Jun 04, 2015 11:59 pm

Re: Can run macro in callFunction ("VLOOKUP")

Postby Zizi64 » Sun Aug 16, 2015 8:14 pm

Can you upload your example file(s) with the macro code?
Tibor Kovacs, Hungary; LO4.4.7, LO5.4.7 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.1.0 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7162
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Can run macro in callFunction ("VLOOKUP")

Postby eugenefoxx » Sun Aug 16, 2015 8:47 pm

OK. You are welcome.
Attachments
MB52.ods
(120.98 KiB) Downloaded 415 times
Reserved_template.ods
(33.41 KiB) Downloaded 79 times
LibreOffice 4.4.0.3 on Windows 8.1
eugenefoxx
 
Posts: 30
Joined: Thu Jun 04, 2015 11:59 pm

Re: Can run macro in callFunction ("VLOOKUP")

Postby Villeroy » Sun Aug 16, 2015 8:57 pm

Dates do not exist in spreadsheets. As a spreadsheet programmer you should know this.
Code: Select all   Expand viewCollapse view
CellRange.setValue(value)


[Tutorial] Ten concepts that every Calc user should know long before hacking it.
[UNO, Calc] How to fill spreadsheets cells programmatically
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25703
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can run macro in callFunction ("VLOOKUP")

Postby eugenefoxx » Mon Aug 17, 2015 7:58 pm

Villeroy, in this case
Code: Select all   Expand viewCollapse view
CellRange.setValue(value)


appear the message of error "BASIC runtime error. Property or method not found: setValue."
LibreOffice 4.4.0.3 on Windows 8.1
eugenefoxx
 
Posts: 30
Joined: Thu Jun 04, 2015 11:59 pm

Re: Can run macro in callFunction ("VLOOKUP")

Postby Villeroy » Mon Aug 17, 2015 8:06 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25703
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can run macro in callFunction ("VLOOKUP")

Postby eugenefoxx » Mon Aug 17, 2015 8:30 pm

May be have a method making macro a-la VLOOKUP without callFunction ("VLOOKUP"). I know this method in VBA.
LibreOffice 4.4.0.3 on Windows 8.1
eugenefoxx
 
Posts: 30
Joined: Thu Jun 04, 2015 11:59 pm

Re: Can run macro in callFunction ("VLOOKUP")

Postby Villeroy » Mon Aug 17, 2015 9:36 pm

eugenefoxx wrote: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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25703
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can run macro in callFunction ("VLOOKUP")

Postby eugenefoxx » Mon Aug 17, 2015 9:49 pm

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.
LibreOffice 4.4.0.3 on Windows 8.1
eugenefoxx
 
Posts: 30
Joined: Thu Jun 04, 2015 11:59 pm

Re: Can run macro in callFunction ("VLOOKUP")

Postby Villeroy » Mon Aug 17, 2015 9:55 pm

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   Expand viewCollapse view
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25703
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can run macro in callFunction ("VLOOKUP")

Postby eugenefoxx » Mon Aug 17, 2015 10:02 pm

I do not practice directly in programming. I optimizes its work through macro programming. I try to succeed in this.
LibreOffice 4.4.0.3 on Windows 8.1
eugenefoxx
 
Posts: 30
Joined: Thu Jun 04, 2015 11:59 pm

Re: Can run macro in callFunction ("VLOOKUP")

Postby eugenefoxx » Tue Aug 18, 2015 1:06 am

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.
LibreOffice 4.4.0.3 on Windows 8.1
eugenefoxx
 
Posts: 30
Joined: Thu Jun 04, 2015 11:59 pm

Re: Can run macro in callFunction ("VLOOKUP")

Postby moriturimax » Sun Sep 16, 2018 8:20 pm

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.
OpenOffice 4.1.5
Windows 10 Pro 64-bit
moriturimax
 
Posts: 14
Joined: Sat Sep 15, 2018 9:05 pm

Re: Can run macro in callFunction ("VLOOKUP")

Postby Zizi64 » Sun Sep 16, 2018 9:08 pm

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.
Last edited by Zizi64 on Sun Sep 16, 2018 9:20 pm, edited 2 times in total.
Tibor Kovacs, Hungary; LO4.4.7, LO5.4.7 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.1.0 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7162
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Can run macro in callFunction ("VLOOKUP")

Postby moriturimax » Sun Sep 16, 2018 9:15 pm

Good to know, hopefully I'll learn as I go.. been using VBA for 20+ years for small things...
OpenOffice 4.1.5
Windows 10 Pro 64-bit
moriturimax
 
Posts: 14
Joined: Sat Sep 15, 2018 9:05 pm

Re: Can run macro in callFunction ("VLOOKUP")

Postby Lupp » Sun Sep 16, 2018 10:13 pm

Zizi54 wrote: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   Expand viewCollapse view
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   Expand viewCollapse view
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.
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 1981
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Can run macro in callFunction ("VLOOKUP")

Postby moriturimax » Sun Sep 16, 2018 10:53 pm

Lupp wrote:
Zizi54 wrote: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   Expand viewCollapse view
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   Expand viewCollapse view
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.
OpenOffice 4.1.5
Windows 10 Pro 64-bit
moriturimax
 
Posts: 14
Joined: Sat Sep 15, 2018 9:05 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 7 guests