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 EndIf If isMissing(delim) Then delim = " " EndIf 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 EndIf if cnt <= n Then if cnt = n Then delim = "" ' no more delimiter needed at the end EndIf out_str = out_str & arr(i) & delim EndIf EndIf Next 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:
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 –> OpenOffice.org 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.