Search and replace macro as a function in calc

[mchernen]

Hi folks,

I’m trying to write a simple calculator in a music theory context which will calculate the distance between a note and a key center and then represent that distance in musical language, to be used in further calculations. The first part is okay, I just subtract the cell of the key center from the cell of the note in the MOD(12) function, so for F (5) in the key of G (7) I get =MOD([5]-[7],12), or 10, which is the interval of a minor 7 (m7).

I now want to represent that in musical language i.e. search and replace 0, 1, 2, 3, etc. with P1, m2, M2, m3, etc. respectively and I am under the impression that a macro as a function is the best way to do that. I only want it to affect the output cell, not the entire document, as there are other numbers being used that needn’t be converted. I have looked into Andrew Pitonyak’s work but I’m having trouble piecing together all the parts I need (multiple searches and replaces, use macro as function, confine to specific cells).

Thanks

[Lupp]

Sorry. I don’t understand the example.

Anyway it may be helpful for you to note that there are standard functions with related capabilities.
A very basic one is SUBSTITUTE(), and a much more powerful and sophisticated function in the field is REGEX(), available since V6.2 of LibreOffice.

If you write a “macro” for Find-and-Replace you will need these functions callable with the help of a FunctionAccess service. (To use F&R via macro is possible, but only applicable to objects exporting the interface XSearchable, and strrings you want to work on aren’t objects at all, not to speak of objects supporting complex services.)

A possible advantage of resorting to user code in this case is (as often) that you can serialize the task easily in cases where you would need lots of helper columns in Calc.

Unfortunately the serialization on the level of elements of find/replace lists is error-prone: A short string just inserted, or parts of it togehter with characters that already were in place, may create an unwanted finding for another pass during consecutiv replacements.

=== Edit1 Attaching a file containing a solution and application examples. ===

Here is the attachment.

[Lupp]

Hmmm. I considered this question once more. It is interesting, and a soulution may be applicable in many cases.
As soon as I find the time I will polish a roughly designed but already working solution a bit and publish it then.