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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: