Re: [tdf-discuss] Calc: let things such as formulas do the rest of the work
On 06/11/2014 05:52 AM, david_lynch wrote:
I sent the message below to the users list. The moderator replied that
I should raise the issues on this list.
Side note David... Do not forget that you can easily write your own Calc
functions and then call them from the calc document. For example, it
would be simple to write exactly the function that you desire.
In a recent "Macro's in Libre Calc" thread, Andrew Douglas Pitonyak,
an expert on macros, wrote:
"Be certain to only do what you really need to do using a macro, and
then let things such as formulas do the rest of the work."
Good advice, which I would like to take further. It is frustrating, as
a user, not to be able to use formulas to do things that a macro could
do. I'd like to propose as a (long-term) design aim:
"Enrich the functionality of Calc to enable formulas and similar to do
what currently needs a macro. Use experience gained to influence the
As a simple example, take REPLACE
REPLACE("Text"; Position; Length; "NewText")
My spreadsheets would be greatly simplified if Text and NewText could
be regular expressions. Currently, either I have to use macros, or use
SEARCH, which does support regular expressions, and code the NewText
regular expression myself. It's frustrating as the code to do what I
want is in the Edit -> Find & Replace... dialog. Note that the
enhanced REPLACE would be OpenFormula compliant.
Another example is character editing within a cell: I realise that
this is more demanding technically.
I seek feedback, advice and, I hope, support. In particular:
1. Are there design aims, or similar, in LibreOffice? If so, who
owns them: does the Engineering Steering Committee have a role here?
2. Should we progress the strategic aspects first? Or should I
propose tactical enhancements such as the one to REPLACE above?
3. How should I push this forward, whether tactically or
In calc, I wanted a function that would take a decimal number and
convert it to the closest fraction, so, I simply wrote my own Macro
function to do that, and then I call it from my Calc sheet. I wanted a
function that would give me an internal rate of return based on randomly
timed additions and subtractions, so, I wrote my own function to do
exactly that. I am unsure if I did this calculation correctly, but it
certainly returns believable values based on the intent of the function.
My Macro Document: http://www.pitonyak.org/AndrewMacro.odt
To unsubscribe e-mail to: firstname.lastname@example.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.documentfoundation.org/www/discuss/
All messages sent to this list will be publicly archived and cannot be deleted
Impressum (Legal Info)
: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (MPLv2
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our trademark policy