Could TYPEIN() compliment the FORMULA() function? ["TYPEIN()" was "TRANSFER()" in the original question.]

You know a lot more about functions in LibreOffice than I do. So, I’ll ask whether it might be worthwhile to add another function to the more than 500 that we already have. Could we add a function, TYPEIN(), to compliment the action of FORMULA()?

What is a complimentary function? It may be defined as one which reverses the action of another function. The compliment of addition is subtraction and division of multiplication. People use this type of operator to return to a previous state. Remember the 80286 processor protected mode to real-mode switching problems?

We recognize the plethora of functions in current spreadsheets. Could our Office Suite use one more complimentary function?

This question was raised in “[Solved] Reverse “FORMULA” function” at Apache OpenOffice Community Forum - [Solved] Reverse "FORMULA" function - (View topic).

Some people, including me, wish to have a complimentary function to FORMULA(). I would like, from a Donor Document, to update and run a formula in a Receiving Document without calling a macro and without user intervention (ex. “Copy-Paste as unformatted text”) on the Receiving Spreadsheet. Please see DocDonor.ods and DocRecipient_Variable_A_.ods.

Let us say we wanted to add such a function. What would we ask a programmer to do for us?

Form: TYPEIN(Donor_cell, Recipient_cell) to effect TYPEIN(Value_in_one_cell, Formula_in_another_cell)

Donor_cell and Recipient_cell need to be addressed either by Name or by coordinates (A1 notation).

TYPEIN() should follow the OpenFormula standard.

TYPEIN() should support nesting.

TYPEIN() should support standard Error Codes.

TYPEIN() should support the Function Wizard, including the Array checkbox.

TYPEIN() should be a Volatile function.

TYPEIN() should empty Recipient_cell before entering data to it.

DocDonor.ods (32.0 KB)
DocRecipient_Variable_A.ods (44.4 KB)

Accounting on sheets is a highly unprofessional sin.

Every database program has a feature named “TRANSACTION”.

Professional accounting always makes use of some database engine.

1 Like

The OriginalQuestioner edited the question and changed the name of the proposed function.
I wouldn’t adapt all my posts every time somebody makes such a mistake.

  1. A Formula returns its result to a cell or (in case of an array-result) To a cell range. Your function can’t comply with this rule.
  2. If the rule is relaxed specifically for you, the (working!) formula in the Recipient_cell would be there only as a part of the result of the formula having executed the TRANSFER() function.Shall it be removed/changed or shall it persist as soon as the mentioned formula gives a different result or sends it to a different Recipient_cell due to changes in its arguments - or simply is removed itself?
  3. You can experiment with a function similar to what you described if you code it as a UserDefinedFunction, accept the persistence of the second action mostly, and restrict the second part of its output (Recipient_cell) to a different sheet. Report your experioences.
  4. You then surely can also give a proposed specification concerning the question what shall happen to the previous content of a cell your TRANFER() pushes a result to.

Villeroy, I agree, database programs have the feature “TRANSACTION,” and it is useful. Your graphic illustrates your point in a most grace filled way. Thank you. Your GeeksforGeeks reference was unknown to me. Thank you for alerting me to it. I am interested in SQL and have joined the site. I have downloaded material and plan to continue using this site. Again, I appreciate your help.
Lupp, Thank you for your response. A Formula does return its result to a cell (or range, if array). This proposed function does push data onto a cell in the document. Thank you for helping me to better understand the implications and restrictions of our spreadsheet functions. I expect no rule to be relaxed for me. You are helping me work within the rules. Thank you.

This new function should take, for example, the value in A1 and enter the characters of the value into A2 just as though a user had read the string appearing in A1 and typed those characters into A2. Maybe we should name the function TYPEIN() to emphasize that we are pushing data into a cell and to help imply that the data can go into any cell, since a user can type a string of characters into any given cell. In our example in DocRecipient_Variable_A_.ods we would effectively type what we see in cell E76 into E81.

As far as I know, the data a user enters by the keyboard persists in the cell until it is replaced, for example, by re-invoking TYPEIN() for the same Recipient_cell. If the Recipient_cell argument were changed to a Different_Cell, then TYPEIN() would enter the data into the Different_Cell, and the data in the First_Recipient_Cell would remain unchanged. It would operate just like a user entered one set of data into a cell (First_Recipient_Cell) and then entered the same or different data into a second cell (Different_Cell).

We agree that, at the moment, our only option is to code it, with the currently available LibreOffice functions, as a UserDefinedFunction and accept the limitation that the output be placed on a different sheet. How do we get TYPEIN() to avoid these limitations? If I knew how to program in C++, I might be able to program TYPEIN(), but I don’t. So, what can we do to specify this function for a C++ programmer? Maybe we should empty Recipient_cell before entering the characters from Donor_cell into it.

You say, “A Formula returns its result to a cell or (in case of an array-result) To a cell range.”

How may a programmer set the result of a formula to a variable?

If you can’t find out how to do this, you’ll never write any macro.

So I said.

On the surface a formula is something starting with an equal sign. Somebody enters it into a spreadsheet cell (not refusing formula recognition). As soon as this was done the formula got parsed and tokenized in the background to allow for efficient recalculation (roughly) everytime a precedent changed. You see: Working formulas only exist in spreadsheet cells. And many references only have a definite meaning (what’s referenced) based on the position of the formula in a specific sheet and a specific cells. This vital environment is missing in a program, say a Basic macro. Therefore a formula can only exist as a dead string there. Constructs written where calculations shall be performed in programs mostly are called “expressions”. They aren’t recalculated like formulas due to changes in precedents, but when it is their turn during the sequential execution of the program.

In short: Since there is no formula, there is no result, and you can’t assign it to a variable.
What I showed you using the FormulaParser service may be useful in rare cases. You obviously used it. There is no FormulaEvaluator service in the API to Calc - and there are many reasons for the fact.

If there is a living formula in a cell, and you know the cell, and you are sure it isn’t “dirty” (not yet recalculated though precedents changed) , and … you can access that cell and ask it for the resulttype and for the .Value or the .String (probably better read from te .DataArray), and assign what you get to your program variable.
For actual macro programming: Forget it.

By any standard any spreadsheet function must not write anything anywhere.

I don’t intend to use the “solution” contained in the attached example file myself. There are only six lines of working Basic code, but also commemts.
I made the example for purely theoretical interest remembering a recent case of using the FormulaParser service. No assurances of any kind!
relocateFormula.ods (15.3 KB)

1 Like

Thank you, Lupp. I did not think this was possible using such a short and generalized macro. Very insightful solution. Of course, we are dependent on the FormulaParser service. Aren’t we all dependent on something. Today, for example, I depend on my house to shield my wife and I from hurricane Ian.

I appreciate well commented code. Also, I enjoyed the disclaimer. Your solution is one I never would have found. Grateful am I for your gracious help. Bruce

Hope at least your lives are safe. I was terrified by the hurricane news.
And I’m terrified again and again by “political” news which actually are about criminal tragedies.
Humans should add less to overall horror. How to achieve that?

Dear Wolfgang,
Did my enthusiasm run away with me? I fear it did. I apologize to you.

Only after I wrote my post did I realize, did I realize that I had not looked carefully enough to understand everything you so kindly wrote. So, here is a graphic showing what I do not yet understand.

Am I to understand that the formulae with the magenta font color in column AA correspond to “…DocRecipient_Variable_A_.ods’#$DonorSheet.E81?”
The cells corresponding to “…DocRecipient_Variable_A_.ods’#$DonorSheet.E76?” are not obvious to me. What am I missing?

I am distressed more than I can say to have to write this. You have been so very gracious to be kind enough to help me understand this problem. Right now I do not know whether I am looking at the solution and just do not understand what I should see or whether we are so close to solving this, but not quite there yet.

In either case, I feel I have failed as your assistant in this effort. You have been wonderful. Thank you so much.

Warmest regards,

I won’t comment on sheets I don’t know (have at hand). Please don’t attach images, but always the “real thing” (an .odf file giving the example) If not there is an issue specifically concerning a view.

In this case I completed my example with some explanations:
relocateFormulaPlusEx.ods (25.6 KB)

Lupp, here are the Documents I did not send earlier. I also include relocateFormulaPlusEx.ods with comments.
relocateFormula_modified.ods (31.2 KB)
relocateFormulaPlusEx_annotated.ods (30.1 KB)
DocRecipient_Variable_A_.ods (43.4 KB)