How can I use match in a function

[thenok]

Please excuse me, I have no experience in vbs.

I am trying to make things easier to understand in a very elaborate exel file. How can I use MATCH inside a function?

Update:
After reading with the help of Lupp here is the end result(ps don’t know why but text does not work in vbs but does outside):

Function customMatch(var1,var2)

Dim oSheet, oRange,oFuncAcc
oSheet = ThisComponent.Sheets.getByName("Sheet1")
oRange = oSheet.getCellRangeByName( "B1:E5" )
oFuncAcc = createUnoService( "com.sun.star.sheet.FunctionAccess" )

'search with static range
'customMatch=  oFuncAcc.callFunction("VLookup", array("text to find", oRange, 3, 0))

'search with range from variable
customMatch=  oFuncAcc.callFunction("VLookup", array(var1, var2, 3, 0))

 End Function

[Lupp]

@thenok: “don’t know why but text does not work in vbs but does outside”
I am not sure about how to understand this. “text does not”: Is this about the type of value to match / elements of the vector to serach in? How do you go “in vbs”? Is “vbs” meant to mean “Visual Basic Script”? Dit you use the “Option VBAsupport 1”? Or were there some typos?

[thenok]

@Lupp I mean =vlookup(3,b1:e5,3,0) works, =vlookup(“zxs”,b1:e5,3,0) works, =customMatch(3,b1:e5) works but =customMatch(“zxs”,b1:e5) does not (also does not work if I call the commented line with “zxs”, also with ‘zxs’)

[Lupp]

Quoting @thenok: “… but =customMatch(“zxs”,b1:e5) …”
I supposed so. Did not test myself yet.
But what about the “vbs” thing? I simply don’t understand. Did you use “vbs” in the sense of “user code” or “script” for some cryptic reason? “bs” reading “Basic Script” might do. The “v” might be an inadvertent hit left of the “b” key … The only predefined meaning I could find in the context was “VisualBasicScript” … No good idea again.

[Lupp]

acustomMatch= oFuncAcc.callFunction("VLookup", array(var1, var2, 3, 0))
cannot work because var2 NOT is a CellRange object.

[Lupp]

To call a Calc function from user code you need to create a FunctionAccess object and use its CallFunction method.
You should study the famous texts by Andrew Pitonyak to learn more about the usage of the API. See this page and subchapter 6.28 of the “Useful Macro Information” e.g. concerning this subject. It’s about ‘OpenOffice’, but there is no difference in this respect.

(Editing with respect to the comments posted meanwhile:)
Just found the time to test a bit. Reporting the result:
The erroneous code will neither work with numbers nor with texts (strings). Rectified code will work in both cases. Example:

 REM  *****  BASIC  *****
 Option Explicit
 Function customLookup(pSearchFor,pVlookupArray, Optional pZ)
 ' If pVlookupArray (which in fact is a text containing an address) includes the
 ' SheetName this name MUST NOT contain enclosing apostrophes. Simply use reasonable
 '  SheetName. As an alternative you may use the pZ parameter which has to give the
 '  number of the sheet in the way the SHEET function is counting: starting with 1.
 ' Therefore the pZ-1 below. If pZ and a SheetName contained in pVlookupArray are both
 ' given, they must be consistent.
 ' This user function as given here is surely of no use.
 ' Call VLOOKUP from a cell directly. Take it as a "Proof Of Concept".
 customLookup = "fail"
 On Error GoTo errorExit
 Dim theSheetName As String, theSheet As Object, theVlookupArray As Object, funcAcc As Object
 If Left(pVlookupArray, 1) ="$" Then
 	pVlookupArray = Mid(pVlookupArray, 2, 65535)
 EndIf
 If	IsMissing(pZ) Then
 	theSheetName  = Split(pVlookupArray, ".")(0)
 	theSheet      = ThisComponent.Sheets.GetByName(theSheetName)
 Else
 	theSheet      = ThisComponent.Sheets(pZ-1)
 EndIf
 theVlookupArray   = theSheet.GetCellRangeByName(pVlookupArray)
 funcAcc           = CreateUnoService( "com.sun.star.sheet.FunctionAccess" )
 customLookup = funcAcc.CallFunction("VLOOKUP", Array(pSearchFor, theVlookupArray,     theVlookupArray.Columns.Count, 0))
 errorExit:
 End Function  

(Being back. This demo contains the code.)

[thenok]

Thank you so much!