[Solved] How to use Calc formulas in Basic?

Creating a macro - Writing a Script - Using the API

[Solved] How to use Calc formulas in Basic?

Postby Wraith367 » Thu Jul 21, 2011 8:44 pm

I have some experience with programming macros in VBA and recently switched to OO, but one thing is really bugging me.Is there an equivelant to the Application.[Function] Command? I haven't been able to find one in the documentation. For example, if I want to use vlookup in my VBA macro it would be Application.VLookup(inputs), how would I do this in OO?
Last edited by Wraith367 on Thu Jul 21, 2011 9:41 pm, edited 1 time in total.
Open Office 3.2.1 on Window 7 x64
Wraith367
 
Posts: 1
Joined: Thu Jul 21, 2011 8:37 pm

Re: How to use Calc formulas in Basic?

Postby Charlie Young » Thu Jul 21, 2011 9:09 pm

Wraith367 wrote:I have some experience with programming macros in VBA and recently switched to OO, but one thing is really bugging me.Is there an equivelant to the Application.[Function] Command? I haven't been able to find one in the documentation. For example, if I want to use vlookup in my VBA macro it would be Application.VLookup(inputs), how would I do this in OO?


It's called FunctionAccess.

Here is an example calling RANDBETWEEN (The spreadsheet random number functions are better than the Basic RND):

Code: Select all   Expand viewCollapse view
Function Sheet_RndBetween(l As Long, h As Long) As Long
   Dim svc As Object
   svc = createUnoService("com.sun.star.sheet.FunctionAccess") 
   Sheet_RndBetween = svc.callFunction("RANDBETWEEN",Array(l, h))
End Function

Most important is to know how to use Array() to pass the arguments to the function (this can be tricky in some cases).  You also need to pass an empty Array() in the case of a function that takes no arguments, such as NOW() (the spreadsheet time functions give finer resolution than the Basic versions).



Here is one for VLOOKUP:

Code: Select all   Expand viewCollapse view
Function vlook(item, InRange As Object, FromCol As Integer)
   Dim svc As Object
   
   svc = createUnoService("com.sun.star.sheet.FunctionAccess")
   vlook = svc.callFunction("VLOOKUP",Array(item, InRange, FromCol, True))
End Function

Apache OpenOffice 4.1.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: [Solved] How to use Calc formulas in Basic?

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

Here is one for VLOOKUP:

Code: Select all   Expand viewCollapse view
Function vlook(item, InRange As Object, FromCol As Integer)
   Dim svc As Object
   
   svc = createUnoService("com.sun.star.sheet.FunctionAccess")
   vlook = svc.callFunction("VLOOKUP",Array(item, InRange, FromCol, True))
End Function



Tried the VLookup code, couple questions if anyone can clarify... 1. Does there need to be a passed variable for the fourth element of VLookup? It has item, InRange, FromCol, but nothing in the function header for the True/False.. does OpenOffice just assume it is valid if its passed, ie optional parameters don't need the actual reference? 2. I tried it with a simple 2 column table, 0/1 5/2 10/3 (basically from 0 to 95 in increments of 5, with a value in the second column counting from 10 to 29.. if I pass a value that matches one of the values it works fine, if I pass, say 6, the function returns a blank... it doesn't seem to work with the values in between the multiples of 5.
COL1 COL2 (AnClassIndex)
0 10
5 11
10 12
15 13
20 14
25 15
30 16
35 17
40 18
45 19
50 20
55 21
60 22
65 23
70 24
75 25
80 26
85 27
90 28
95 29

=VLOOK(55;AnClassIndex;2;0) works fine, returns 21
=VLOOK(56;AnClassIndex;2;0) doesn't work, returns blank
OpenOffice 4.1.5
Windows 10 Pro 64-bit
moriturimax
 
Posts: 14
Joined: Sat Sep 15, 2018 9:05 pm

Re: [Solved] How to use Calc formulas in Basic?

Postby Villeroy » Sun Sep 16, 2018 8:15 pm

Same VLOOKUP since 30 years. Either you switch the mode or you use LOOKUP instead.
[Tutorial] VLOOKUP questions and answers
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: [Solved] How to use Calc formulas in Basic?

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

Villeroy wrote:Same VLOOKUP since 30 years. Either you switch the mode or you use LOOKUP instead.
[Tutorial] VLOOKUP questions and answers


Are you talking about the macro vlook I am talking about, or the spreadsheet version, which works fine, but isn't what I am -cough- looking for.
OpenOffice 4.1.5
Windows 10 Pro 64-bit
moriturimax
 
Posts: 14
Joined: Sat Sep 15, 2018 9:05 pm

Re: [Solved] How to use Calc formulas in Basic?

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

Please read the description odf the function VLOOKUP(). (About "what the fourth parameter means":

https://wiki.openoffice.org/wiki/Docume ... P_function
If the parameter is TRUE then the list must be sorted and (AND!!!) only the exact matches will be found.
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: [Solved] How to use Calc formulas in Basic?

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

It does the blank thing REGARDLESS of whether its TRUE or FALSE. I did try that first. Unless the value I pass to it matches one of the table numbers it displays blank, if it matches I get the 2nd column value returned as normal.
OpenOffice 4.1.5
Windows 10 Pro 64-bit
moriturimax
 
Posts: 14
Joined: Sat Sep 15, 2018 9:05 pm

Re: [Solved] How to use Calc formulas in Basic?

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

Please upload your ODF type sample file here (or rather at your original post) and the code of your macro routine.
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: [Solved] How to use Calc formulas in Basic?

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

Kind of interesting, I tried two different versions of the Vlook function, when I leave in the TRUE or FALSE parameter in the function, I get a blank result of the value I am looking for isn't exactly equal to one of the numeric entries in the 1st table. If I remove it completely from the Vlook function it works as it should and identifies results even if the value is interpolated on the 1st table.

UPDATE: I tried a second table of unsorted letters and that seems to work with either function. Go figure.

Also, as I was working on this spreadsheet, I did SOMETHING (don't know what) and ALL the result cells turned into #REF! values... nothing I tried would fix it, it was the same function calls, same values, same results, but every single cell that called the functions had those error codes... big mystery so I did a new one from scratch and all works so far. It might have been some weird bug because I created and named the second table.
Attachments
TestVLOOK2.ods
(13.92 KiB) Downloaded 6 times
OpenOffice 4.1.5
Windows 10 Pro 64-bit
moriturimax
 
Posts: 14
Joined: Sat Sep 15, 2018 9:05 pm

Re: [Solved] How to use Calc formulas in Basic?

Postby Villeroy » Mon Sep 17, 2018 11:49 am

moriturimax wrote:
Villeroy wrote:Same VLOOKUP since 30 years. Either you switch the mode or you use LOOKUP instead.
[Tutorial] VLOOKUP questions and answers


Are you talking about the macro vlook I am talking about, or the spreadsheet version, which works fine, but isn't what I am -cough- looking for.

Looks like a bug to me.
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: [Solved] How to use Calc formulas in Basic?

Postby Villeroy » Mon Sep 17, 2018 1:37 pm

Works properly when you replace Basic True with 1.
True and 1 are the same in Calc but not in Basic. Using Basic types in spreadsheet functions may yield unexpected results. Integers, doubles and strings are OK. Dates, times, booleans not.
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


Return to Macros and UNO API

Who is online

Users browsing this forum: Google [Bot] and 6 guests