[Solved] How to use Calc formulas in Basic?

[Wraith367]

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?

[Charlie Young]

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

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

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

[moriturimax]

Here is one for VLOOKUP:

Code: Select all

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

[Villeroy]

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

[moriturimax]

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.

[Zizi64]

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.

[moriturimax]

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.

[Zizi64]

Please upload your ODF type sample file here (or rather at your original post) and the code of your macro routine.

[moriturimax]

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.

[Villeroy]

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.

[Villeroy]

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.