You are invited to Log in or Register a free Frihost Account!

How to write your own OpenOffice Calc Functions

Today I have been looking for a function in OpenOffice which I would call ( and know from many programming languages ) ‘substring' and which would allow me to split a string into chunks using a pre-defined delimiter and then return one or more words from this collection of chunks.

Did I explain this well ? If not, here is an example: from a string like ‘+ 6.84%' I just need the numeric part, the 6.84 so to speak -> to then be able to convert that to a number.

Unfortunately such a function is not offered with OpenOffice. Or should I say … fortunately ? Because I actually started to figure out how to write my own functions in OpenOffice using Visual Basic For Applications, aka VBA. And at the end of the day I know how to do this and probably write many more functions in the future.

Here is my substring function in VBA:

Function substring(str As String, start As Integer, optional n As Integer, optional delim As String) As String
  If isMissing(n) Then
     n = 1
  If isMissing(delim) Then
     delim = " "
  out_str = ""
  cnt = 0
  Dim arr() as String
  arr = Split(str,delim)
  For i = 0 to UBound(arr)
     If (i+1) >= start Then
        If arr(i) <> "" Then
                cnt = cnt + 1         ' Ignore empty values
        if cnt <= n Then
           if cnt = n Then
              delim = ""            ' no more delimiter needed at the end
           out_str = out_str & arr(i) & delim
  substring = out_str
End Function

As you probably can see it takes a string and a starting position as parameter 1 and 2, the number of words to return and the delimiter to use as optional parameter 3 and 4. Parameter 3 will be 1 as default, parameter 4 a white space, if not specified.

Here is an example how I now can use this function to do my data tweaking together with two of the built-in functions SUBSTITUTE and VALUE to finally achieve what I described above:

=VALUE(SUBSTITUTE(SUBSTRING(SUBSTRING(A1;2;1;” “);1;1;”%”);”.”;”,”))

would get me 6,84 in numeric format if cell A1 contains ‘+ 6.84%’.

So far, so good. How to get such a function into OpenOffice Calc ?

It starts with navigating to the Macro Organizer: Tools –> Macros –> Organize Macros –> Basic

From here I select “My Macros” –> “Standard” –> “Module1” and then click the Edit button. An IDE for VBA opens allowing me to paste in the code I have shown above. Done.

That IDE is quiet useful since it also allows me to do debugging, like setting breakpoints and inspecting the content of variables.

2 blog comments below

Nice programming, amagard
standready on Thu Feb 20, 2014 10:00 pm
You've inspired me! I decided to see if we can use python as a scripting language in libreoffice. I found this page:

which I found to work on my linuxmint (Ubuntu variant) system. There is a note that it does not work in 'Fedora, however if the needed package is available for fedora, it should be possible to use python there also.

For reference, and in case the link above quits working, here is the Ubuntu command that enables python scripting:

sudo apt-get install libreoffice-script-provider-python

In the article, the package python-uno is also installed. The only purpose of that is to replace the python 3 version of the package, so that it will use python 2 scripts. I would much rather use python 3. Also, a python script is moved to /usr/lib/libreoffice/share/Scripts/python in the article. I found that file, and two others, already there. You could put other macro scripts there if you wanted to, but a better place is probably ~/.config/libreoffice/4/user/Scripts/python, which I read about on another site. I verified I can run a script from there, but not a valid macro.
SonLight on Tue Jul 19, 2016 7:51 am

© 2005-2011 Frihost, forums powered by phpBB.