Ask Your Question
0

How can I use match in a function

asked 2017-04-26 15:07:36 +0100

thenok gravatar image

updated 2017-04-26 23:13:05 +0100

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
edit retag flag offensive close merge delete

Comments

@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?

Lupp gravatar imageLupp ( 2017-04-26 23:29:16 +0100 )edit

@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')

thenok gravatar imagethenok ( 2017-04-27 08:29:56 +0100 )edit

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 gravatar imageLupp ( 2017-04-27 10:12:27 +0100 )edit

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

Lupp gravatar imageLupp ( 2017-04-27 11:21:19 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2017-04-26 15:23:27 +0100

Lupp gravatar image

updated 2018-01-25 19:22:48 +0100

Jim K gravatar image

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.)

edit flag offensive delete link more

Comments

Thank you so much!

thenok gravatar imagethenok ( 2017-04-26 22:33:39 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-04-26 15:07:36 +0100

Seen: 798 times

Last updated: Jan 25 '18