Passing cell reference to basic function

Deever · December 20, 2021, 6:39pm

Hi folks!

When I pass the reference as a string like so
SampleUserFunction(“G19”)

this function works:
Function SampleUserFunction(refCell as Variant)
sheet = ThisComponent.Sheets.getByName(“someSheet”)
cell = sheet.getCellRangeByName(refCell)
SampleUserFunction = cell.getString()
End Function

But when I call this function like so:
SampleUserFunction(G19)

I’m getting an exception (without any useful message) on the line calling sheet.getCellRangeByName.
Obviously, I need it that way to have Calc automatically interpolate the formula when I draw down the selection…

Any hints?

EDIT: tried to format better, but unfortunately, this tool sucks terribly at it…

Villeroy · December 20, 2021, 7:35pm

You don’t need to resolve any reference. User defined functions take data and return data. No objects inolved. If a referenced cell contains a string, then you get a string, if it contains a number then you get a double precision floating point number. Cell ranges are passed as data arrays. Simply inspect your variables!

Deever · December 20, 2021, 7:53pm

I want to use the formula “=SampleUserFunction(G19)” in a cell, without the quotation marks around G19. How do I do that?

joshua4 · December 20, 2021, 8:44pm

As Villeroy said, it just works. If you have MYFUNC(Value As Variant) as your function signature and use MYFUNC(G19) in your spreadsheet then inside your User Defined Function (UDF) the variable Value will be the number or string value that is in cell G19. Calc will let you do most (but not everything, you cannot return true errors) with your UDF that you can do with an in-built function, like autofill, etc.

A near pass-through function that adds 1 to numbers or concatenates 1 to strings would look like this:

Function AddOne(Value As Variant)
   If IsNumeric(Value) Then
      AddOne = Value + 1
   Else
      AddOne = Value & "1"
   EndIf
End Function

So if G19 has 3, then the cell =AddOne(G19) is in will have 4. If G19 has Rat, then the cell =AddOne(G19) is in will have Rat1.

That’s all there is to it in normal usages. For production code you would need to check for out-of-bounds, do error handling, and make it array-formula aware, but those are all “different stories.”

Lupp · December 20, 2021, 9:31pm

Why do you think to need the cell as an object? If you only need the .String or the .Value property, it applies what @Villeroy told you. Otherwise your example is misleading.

Now assuming you actually need access to a cell as object:
LibreOffice doesn’t support the passing of references to a user function by native means. You therefore need to identify sheet and cell position with the help of numeric and/or string parameters and arguments as you already demonstrated.

The alternative is to use Basic with Option VBAsupport 1 set. You then can get passed a special kind of range reference (Service ooo.vba.excel.Range) for a simple reference as given as argument.
The reference doesn’t directly support com.sun.star.sheet.SheetCell if a single cell was referenced in the calling formula, but you can get get it like in

Option VBAsupport 1

Function myFunction(pVBArg)
LibOrg = pVBArg.CellRange
singleCell = libOrg.getCellByPosition(0, 0)

REM Follow statements / commands
End Function

Be carefuil. VBA related options may change the behaviour of “ordinary” functions and commands.

Deever · December 21, 2021, 12:50am

Ah, indeed, that was easy. But I went too far and got stuck!

Now I have the problem that VLOOKUP (called from the macro) always returns an empty result:

Function SingleDrawerContents(RefCell as Variant)
	SingleDrawerContents = GetPartByID(RefCell)
End Function

Function GetPartByID(Id as Integer)
	CellRange = ThisComponent.Sheets("parts").getCellRangeByName("A:L")
	GetPartByID = VLookUp(Id, CellRange, 5)
End Function

Function VLookUp(SearchValue as Variant, Range as Object, Index as Integer)
	Svc = CreateUnoService("com.sun.star.sheet.FunctionAccess")
	Args = Array(SearchValue, Range, Index, False)
	print "SearchValue: " & SearchValue
	print "Index: " & Index
	print "Result: |" & Svc.CallFunction("VLOOKUP", Args) & "|"
	rem VLookUp = Svc.CallFunction("VLOOKUP", Arg)
End Function

There is never something between the pipes. What’s the issue here?

joshua4 · December 21, 2021, 2:28am

I really don’t know why you are “wrapping” all of the Calc functions with your own code. It seems to me off hand that it would be more advised to create good Named Ranges and use a few nested built-in functions within the spreadsheet itself than to go about creating a set of UDF function names in BASIC. What I see here is, first, you had Arg, not Args on your Svc call, and second, you are trying to index ThisComponent.Sheets(), which I don’t think it likes. Use ThisComponent.Sheets.getByName(“parts”)…

Here is working code:

Function SingleDrawerContents(RefCell as Variant) As Variant
	SingleDrawerContents = GetPartByID(RefCell)
End Function

Function GetPartByID(Id as Integer) As Variant
	Dim CellRange As Object
	
	'CellRange = ThisComponent.Sheets("parts").getCellRangeByName("A:L")
	CellRange = ThisComponent.Sheets.getByName("parts").getCellRangeByName("A:L")
	'MRI CellRange
	GetPartByID = VLookUp2(Id, CellRange, 5)
End Function

Function VLookUp2(SearchValue as Variant, Range as Object, Index as Integer)
	Dim Svc
	Dim Args
	
	Svc = CreateUnoService("com.sun.star.sheet.FunctionAccess")
	Args = Array(SearchValue, Range, Index, False)
	VLookUp2 = Svc.CallFunction("VLOOKUP", Args)
End Function

I’d mention in passing, also, that you ought to be using Option Explicit (which would have caught the Arg(s) problem), and MRI 1.3.4 - someone might correct me, but I believe the link is https://github.com/hanya/MRI/releases.

Deever · December 21, 2021, 3:52am

Ah, the problem was there! Ok, got it fixed now.

Now, hopefully the last problem I have is that I’d like to intercept empty cells:

Function DrawerContents(RefCell as Variant)
	If RefCell = "" Then
		Exit Function
	End If
	' Does not work, this code is always executed
End Function

I’ve cleaned up my functions and I’m doing this in Basic, because it’d become too messy with the Calc functions…

Thanks for your help!

Lupp · December 21, 2021, 10:22am

The terms you use naming your variables look strange to me. A variable RefCell occurring in my own code would mean a cell (the referenced cell). You are using it in a comparison to the empty string. A cell never can be the empty string, though it may probably contain (have it as the value of its string property) it (RefCell.String = "").

Probably this is bad Excel-VBA customs. Don’t know.
Try to do it simpler. Use passed data (as @Villeroy told you in his first comment), not ranges wherever possible. It is possible in every ordinary case. If you want to use VLOOKUP(), do it by cell formulas. Don’t try to embed it in user code. There seem to be many bad “traditions” concerning Excel and VBA.

I never wrote VBA code personally, but I studied some examples years ago in detail. This lead me to the suspicion that VBA in Excel is mostly used in an obscuring and complicating way. Probably “sheet developers” try to valorize their marginal contributions by mystification - and by hiding the simple concepts this way?

Villeroy · December 21, 2021, 10:52am

… to impress the boss … and because generating some VBA snippet seems to be very easy. For some people generating (or copy-pasting) some VBA snippet is even easier than learning how to use a spreadsheet properly.

joshua4 · December 21, 2021, 4:14pm

So…if you want to test when a cell is blank, then you can wrap the ISBLANK() function the same way you wrapped the VLOOKUP() function. A cell with an empty string “” and a cell having no content are two different things–it’s sort of the difference between a null pointer and a pointer to an address with a zero-length string if you’ve done that sort of programming.

I see the idea you have of creating an entire ecosystem of functions specific to your tasks. But none of the tasks you’ve listed so far are anywhere near “too messy” to just do in the spreadsheet itself. Of course, I don’t know what you plans are…you haven’t uploaded examples…but I think everyone is trying to hint that your approach is swimming up stream.

If you want “self documentation” you can get it (in common languages) by naming functions and data structures. Of course you do both in a full-on program. But in a spreadsheet the idea is to use well-known functions (even if they are a little quirky at times) and name your data structures (all being tables or individual cells) with Named Ranges, so that anyone “auditing” the spreadsheet will see immediately what is happening. If you go about renaming VLOOKUP() for PartsInDrawer (directly or indirectly) then need VLOOKUP() for PartsPerOrderMinimum, then PartsCheckedOut then… you’ve just taken the generic VLOOKUP() that any spreadsheet user knows and turned it into a sea of nomenclature that has to be tracked back to your VLOOKUP wrapper function in your BASIC code. Relying on Named Ranges and standardized functions as much as possible is what I’d advise, and what the experts like Lupp and Villeroy seem to be saying, too.

I’m reminded of the story that a student once asked her French teacher how to say some English phrase in French. The teacher responded, “You wouldn’t.”