STRJOIN macro suddenly broken in LibreOffice / Open Office

Question

This is my first post to Stackoverflow. I have gotten so much help and value from so many people on this site, so I want to first say thank you to everyone on this forum for their incredible knowledge and generous spirit. Much appreciated!

I've research this site extensively and can't find the answer to my problem: I've been very successful using the STRJOIN macro in LibreOffice (v4.2.4.2) , but after returning to work after a multi-week vacation, suddenly it's not working. My computer was shut down and left behind, so I'm positive nothing or no one changed anything. OSX updates have not occurred since I've returned.

When I run the macro (written by Adam Spiers, based on the work of Markus O'Reilly), I'm now getting the following errors:

1. In the macro editor, I get

BASIC runtime error. Argument is not optional

with this line in the macro:

If NOT IsMissing(range) Then

2. If I run the macro from Tools > Macro > Run Macro, I get:

A Scripting Framework error occurred while running the Basic script Standard.Module1.STRJOIN. Message: wrong number of parameters!

The same errors occurred in OpenOffice as well.

What happened / why is the macro now not functioning? Other macros are working fine, and I also created a new user profile thinking this might help, to no avail.

I'm particularly concerned about the function suddenly bombing out as I have a ginormous spreadsheet with STRJOIN occurring extensively throughout, and concatenate is not a sufficient substitute.

Here's Markus' and Adam's macro:

Function STRJOIN(range, Optional delimiter As String, _
                 Optional before As String, Optional after As String)
    Dim row, col As Integer
    Dim result, cell As String

    result = ""
    If IsMissing(delimiter) Then
        delimiter = ","
    End If
    If IsMissing(before) Then
        before = ""
    End If
    If IsMissing(after) Then
        after = ""
    End If

    If NOT IsMissing(range) Then
        If NOT IsArray(range) Then
            result = before & range & after
        Else
            For row = LBound(range, 1) To UBound(range, 1)
                For col = LBound(range, 2) To UBound(range, 2)
                    cell = range(row, col)
                    If cell <> 0 AND Len(Trim(cell)) <> 0 Then
                        If result <> "" Then
                            result = result & delimiter
                        End If
                        result = result & before & range(row, col) & after
                    End If
                Next
            Next
        End If
    End If

    STRJOIN = result 
End Function

Answer 1

First of all, STRJOIN is a function and was not intended to be run directly. Instead, use a subroutine to test it:

Sub Test_STRJOIN
    Dim strings(1 To 1, 1 To 3)
    strings(1,1) = "a"
    strings(1,2) = "b"
    strings(1,3) = "c"
    result = STRJOIN(strings, " / ")
    MsgBox(result)
End Sub

Call this subroutine either from the macro editor or Tools > Macro > Run Macro. It should display "a / b / c" in a message box.

Now, what is the problem when STRJOIN is used in a spreadsheet formula? Well, I think the macro just needs to be rerun somehow. You can verify that it is running by adding a line like MsgBox "STRJOIN() running" at the beginning of the function.

To really force it to run, manually enter another formula in a new cell, for example =STRJOIN(A1:B1).

Typically for me such problems go away when I make an arbitrary change in the Basic code, for example by adding a comment. This apparently makes Office realize that it needs to rerun the code. After doing this, close and reopen the document to update the formulas.