Why user defined function do not work when using labels instead of cell names

English
, , ,
#1

I use one column of the spreadsheet as labes an the next columnt to enter or compute values:

I can use ‘a’ in formula and it works fine - see line labeled result.
But when I try to pass ‘a’, ‘b’ or ‘c’ as arguments to my basic function vol(), it fails.

    labels           values
|----------------|-------------------|
|      a         |          1        |     entered number 1
|      b         |          3        |     entered number 3
|      c         |          5        |     entered number 5
|   result       |         15        |     entered formula ='a'*'b'*'c'  - works OK
|    test        |                   |    enterd formula =VOL('a';'b';'c')  - got error
|----------------|-------------------|

I get basic runtime error “Object variable not set”
In debuger I can see, that argument a is variant(1 to 1,1 to 1021). What does it mean ?

function vol is simple example from User-Defined Functions - LibreOffice Help

#2

As a workaround I used 

=vol(‘a’ *1; ‘b’ *1;‘c’ *1)
but this is not nice.

#3

The workaroud seems clever as it causes already the formula tokenizer to apply the intersection mode, and the user function only gets the singhle value. Cf, my answer below.

#4

I never use the “automatic labels” because the feature neither is of much use nor was reliable at any time [Edit t=2010-01-27 about 09:45 UTC]one or another time I tried to use it. (The problem was the automatic updating of the rangesize under certain conditions.)[/Edit]
Concerning your example I don’t understand for what reason the entered formula and the workaround do as you tell… except your formulas actually were placed in the colmun titled “Values”. In this case the “intersection mode” of the evaluation of array references in a sheet would apply.

Generally an automatic label should silently define a named range (not shown in the manager). In case of a row it should reach as far to the right as no blank cell interrupts the sequence. In your case this means a range of one element in one row. The automatic range ‘b’ passed to the respective parameter, say p2 of your UF will result in p2 getting an array of type (1 To 1, 1 To 1) As Variant with p2(1, 1) = 3, and that’s ok, but you cannot access the value as simply pA. There is no "automatic conversion of one-.element-arrays (sometimes called “singleton”) to a single value. And the body of the function doesn’t know about the specific situation in the sheet and cannot apply the intersection mode.

Best abandon relying on automatic labels as I do.

#5

Hi, @Matvej, I agree with the @Lupp comment, it will be more reliable to name the cells that have the data. For example: cel1, cel2 and cel3 the formula will be: =cel1*cel2*cel3 a the function: =VOL(cel1;cel2;cel3)

#6

See also How can I write a scalar User Defined Function for Calc that takes range? and related tdf#66717.

Best abandon relying on automatic labels as I do

Well - the automatic labels might be useful; just keep in mind that they define ranges (as @Lupp told you: so your ‘a’ is not a single cell next to it like D2, but the rest of the row, e.g. D2:AMJ2), and that user-defined functions always take ranges as array of values, not as a single value taken by intersection (as opposed to built-in and add-in functions (using UNO API) and operators (like * or +), where it’s known how to interpret each argument - e.g., passing a range to SUMPRODUCT results in processing the range; passing that range to CELL result in intersection mode).

#7

From the (long ago) time I also sometimes used automatic labes I remember the feature behaving (though often erratical) as I told:
The range being assigned automatically was terminated before a blank cell occurred.
This was also the case when I tested today with LibO V6.4.0,2RC, but I had to use the “workaround” {=TRANSPOSE(TRANSPOSE('label1'))} to get the range returned. With {='label1} entered for matrix evaluation I got applied the intersection mode nonetheless, and also having preselected the output range the first element was repeated.
Automatic labels for rows did not work at all.
And no. I will not report a bug about this gimmick ;-).
@Lupp, aka “Wolfgang Jäger”

#8

@Lupp: it was 1-cell range for me, too - but still that was a range (variant(1 to 1, 1 to 1)). And so it doesn’t matter which size the range is: it is passed to user-defined functions as range, not as scalar value.

#9

Yes. This was what I experienced yesterday when automatic row labels worked in an example. I hade made from the “typed screenshot”. I just stumled over the AMJ2 in your comment above.
On my last try now autoamtic row labels worked for me in V6.4.0.2 too.

#10

My AMJ2 was from OP’s variant(1 to 1,1 to 1021), which could be because of some content to the right of discussed range. :slight_smile:

#11

hello @Matvej,

i agree with all proposals for ‘better coding’,

one add. comment, pls. check if hard recalc (strg.-shift-F9) will get results, there are rumors that ‘macro functions’ are excluded from autocalculate if they can’t find their dependencies once thoose are not ‘direct’ references but masked in strings (or behind labels?),

the ‘rumors’:

you may find add. info in bugs.libreoffice.org at #40203, #43003, #54858, #55774, #79588, #81757?, #82610, #129896,

esp. https://bugs.documentfoundation.org/show_bug.cgi?id=79588#c26 made things clear(er) to me,

imho plenty users would like improvements, but it would be a big job in coding?

in the meantime i’d suggest to correct help and manual to give info about this weakness …

#12

This is completely unrelated to the topic. The question is about passing arguments to user-defined functions; your references (written without using the convenient tdf#NNNNNN syntax, that would automatically become hyperlinks, which you could already known by now, having spent enough time on the site) are about how Calc knows which cells depend on which. The latter is just a matter of Calc knowing something is a reference or not; and strings are of course not, so passing a string to any function, Calc does not automatically mark that cell as dependent on anything that the string could possibly mean. Some built-in functions (like INDIRECT) can establish a dependency to cells referenced by text, but that’s only possible to low-level functions. But - again, this question is not about passing strings; passing named ranges does establish the dependency in Calc.

#13

hello @Mike2,

you’re right, it’s another problem - in the same area -

(minimum common: ‘macro functions don’t work as expected’)

with the reference to the ‘vol’ function now given (which was not given at the time of my guessing) i could test, funny results,

  1. ‘passing named ranges does establish the dependency’, yes, manually set cell labels (named ranges) work correctly, but the OP doesn’t do that,

  2. using named ranges in formulas expect them given without quoting,

  3. ‘automagically found’ labels do not work,

  4. they do work for ‘simple’ formulas,

  5. they do not work for ‘macro-functions’, at least not in ths case,

  6. the formula is aware of and does adopt the automatic ‘label → value’ relation to the extent of changing the reference when the label is changed, but

  7. on evaluation: error trying to save another opened sheet and ‚basic runtime error, object variable not set‘

imho that qualifies for filing a bug.

ver: 6.4.0.2

#14

This qualifies for learning things (as usual).

  1. Calc can reference ranges using different names: A1 syntax; R1C1 syntax (dependent on a setting); named ranges; automatic row/column labels (a special kind of named range). Automatic row/column labels are enclosed in the single quotes (the quotes are added automatically when you enter a*b*c). So - “but the OP doesn’t do that” is the first false statement (OP does that, using the labels as range names).
  2. “using named ranges in formulas expect them given without quoting” only relates to named ranges managed through Sheet▸Named Ranges And Expressions.
  3. ‘automagically found’ labels do work as expected, given that they refer to ranges. Trying to use =VOL(A1:A1;B1:B1;C1:C1) will result in the same result, although this does not use any “automagic” names.
#15

4 : that built-in (and add-in) functions “work” is just because for such functions, their argument type (scalar/range) is known to Calc, and so Calc knows when to use intersection to provide the expected scalar argument. For user functions, Calc doesn’t know the type (see tdf#66717).

5 : they do work for ‘macro-functions’: just programmer needs to account for the mentioned property of user functions. Again - a matter of learning.

7 : what is that about? the question is not about saving other sheets. Trying to multiply arrays of arrays in Basic is an error, so naturally an error on evaluation results.

#16

@Mike2:

  1. the OP didn’t use manually set named ranges, they would have worked, with the restrictions given under 3.

  2. yes, that’s what i was talking about, manually set named ranges, that they are working ok with formula “abc” (opposite to automatically found labels, not working ok, with formula “‘a’‘b’‘c’”, äh, sorry, when used in a macro function),

  3. a, b, c, didn’t work for the OP, your sample A1:A1 uses direct adressing, that doesn’t work either, what worked was applying ‘named ranges’ as labels to single cells,

#17

sorry, numbering in previous comment automatically screwed,

4 : can that be improved by better ‘defining’ of the user functions? or by providing an interface which gives the users / macro programmers the ability to define that process?

5 : please be more specific, what could the OP have done better to get the automatically assigned labels to work correctly? providing the arguments with ‘1*’ or ‘0+’? why isn’t calc capable to do such silly things by it’s own while finding assumed labels even on other sheets?

6 : you agree?

7 : on my system calc showed a dialog box: 'error saving file xxxxxxx.yyy, basic runtime error, object variable not set‘ when triggering the macro, on new tests the part reg. file is gone,

8 : tdf#66717 is a nice pice of irritating users by use of ‘randbetween’, i suspected my whole system buggy :wink: - will play with and try to learn from it in near future …

#18

OMG.

1, 2: Please try to understand the simple thing: the problem is not the “kind” or range you use: be it a named range, or A1-like address, or automatic row/column label. All these are normal Calc ranges, and are treated identically in any place of Calc: it’s not the difference between “named ranges vs automatic labels”, it’s the difference between a single cell like “A1” and a range like “A1:A1”. These are different entities, the former being a cell object, the latter being a range (not a cell object). If you define your named range as A1:A1, it will behave just like automatic label. Stop trying to repeat that “named ranges work; automatic labels don’t” mantra. Simply learn the difference. Where you can fine-control ranges, like in “named ranges”, you can choose between “A1” and “A1:A1”. Where it’s automatic, you need to accept that it uniformly makes anything ranges, be it A1:B1 or A1:A1.

#19

4: did you even try to understand the bug I mentioned (and you referred to in #8)? Or are you discussing without even trying to see what you are being told?

5: OP needed to check types of passed arguments, and do one thing when it’s an array of array of only one element; another if array of multiple elements is passed; and yet other if scalars are passed.

6: agree with “the formula is aware of and does adopt the automatic ‘label → value’ relation to the extent of changing the reference when the label is changed” part? it’s obvious, and as I told, that’s a normal range reference. I don’t understand why should I need to repeat the obvious.

7: “on your system” is something unrelated to the question. Please start another, or file a bug. Enough off-topic from your side without that piece.

8: “randbetween”??? another cryptic part…

#20

@Mike2,

just call me b., god is reserved for others,

1 : yes, you’re right, the ‘label finding automatic’ applies the labels to a range! of cells instead of one single cell … i was a little slow to realize this because the example has only one column for values :frowning: thanks for your patience to clarify, hope it will help others as well …

2 : i’m still short how / why the ‘trick’ of applying a calculation (‘0+’ or ‘1*’) to the cell / value is working. is it by doing the selection which cell of the labeled range is to be taken in the formula into the sheet rather than into the called function?

4 :, 8 : yes, i tried, ‘intersection’ made somthing clearer, ‘randbetween’ - used in your! sample there - was quite fun

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.