CODEWITHSUNDEEP
macroslibreofficebasiclibreoffice-calc
arielnmz
arielnmz

Reputation: 9145

How to end a Basic function properly

I'm trying to write a function in Basic for LibreOffice Calc to get the first letter of each word of the selected cell using the following code:

Function GetFirstLetters(rng) As String
    Dim arr
    Dim I As Long
    arr = Split(rng, " ")
    If IsArray(arr) Then
        For I = LBound(arr) To UBound(arr)
            GetFirstLetters = GetFirstLetters & Left(arr(I), 1)
        Next I
    Else
        GetFirstLetters = Left(arr, 1)
    End If
End Function

And it works correctly, unless I try to execute it again, then it seems that the new result gets appended to that of any previous execution and it will return both strings together, example:

first example

It also doesn't matter if I delete some or even all cells, or if I call it using an empty cell or even in another page, it will always append the result to the previous one:

second example

Why does this happen? How can I fix this behaviour?

I don't know anything about Basic, so please don't bash on me if this is something very simple.

The original function is this:

Function GetFirstLetters(rng As Range) As String
'Update 20140325
    Dim arr
    Dim I As Long
    arr = VBA.Split(rng, " ")
    If IsArray(arr) Then
        For I = LBound(arr) To UBound(arr)
            GetFirstLetters = GetFirstLetters & Left(arr(I), 1)
        Next I
    Else
        GetFirstLetters = Left(arr, 1)
    End If
End Function

And I got it from here: http://www.extendoffice.com/documents/excel/1580-excel-extract-first-letter-of-each-word.html.

Upvotes: 0

Views: 192

Answers (1)

Axel Richter
Axel Richter

Reputation: 61915

The code you have found is VBA for Excel. Openoffice or Libreoffice uses StarBasic, not VBA. This is similar but not equal. So you can't simply use the same code as in Excel.

First difference is, there is no Range object. This you have noticed and have used rng as an Variant.

But another difference is, function names are like variable names in the global scope. And they will not be reseted if the function is called again. So in StarBasic we better do:

Function GetFirstLetters(sCellValue as String) As String
    Dim arr As Variant
    Dim I As Long
    Dim sResult As String
    arr = Split(sCellValue, " ")
    If IsArray(arr) Then
        For I = LBound(arr) To UBound(arr)
            sResult = sResult & Left(arr(I), 1)
        Next I
    Else
        sResult = Left(arr, 1)
    End If
    GetFirstLetters = sResult
End Function

sResult is reseted (new Dimed) every time the function is called. So even the function's return value.

Upvotes: 1

Related Questions