VBA JOIN XL97 + thread expanded Table-It & XL97

[erik.van.geit]

Hi, gurus,

JOIN is not available in XL97
a workaround is to loop
or is there something better ?

Option Explicit

Sub test()

Dim arr(10) As Variant
Dim i As Long
Dim txt As String

    For i = 1 To 10
    arr(i) = i
    Next i
    
On Error Resume Next
MsgBox "...." & Join(arr, vbLf) & "....", 64, "EXCEL 2000 +"

    For i = 1 To 10
    txt = txt & vbLf & arr(i)
    Next i
    
MsgBox "...." & txt & "....", 64, "EXCEL 97"

End Sub

I didn't find much documentation on this.
Can you point me to some links or do you know a better workaround ?

kind regards,
Erik

 

[Andrew Poulsom]

Hello Erik

A UDF but still a loop:

http://www.dicks-blog.com/archives/2004/05/20/join97/

 

[erik.van.geit]

Thank you Andrew,

The UDF is more flexible, that's a good thing.

I'll replace my JOIN-lines in "Table-It". Some user posted a comment about this.

greatings
Erik

 

[picklefactory]

Thanks Erik.
I'm at home now, I'll check it out tomorrow.
Cheers

 

[erik.van.geit]

Thanks Erik.
I'm at home now, I'll check it out tomorrow.
Cheers

You're welcome !

Table-it is uploaded with the changes

 

[picklefactory]

Sorry Erik, now I'm getting a 'Run time error 13' Type Mismatch. It won't let me into debug, so I don't know what line is hanging on, all I can do is end the run sequence. It works fine on cells with no formulas, it only hangs on cells with them. Does that give you any clues?
Thanks

 

[jindon]

Erik

This is what I wrote for 97 user before

Function Split(Expression, Optional delimeter, Optional limit As Long = -1 _
                    Compare As VBCompareMethod = vbTextCompare)
Dim x(), i As Long, n AS Long, y As Long
Redim x(0)
Expression = CStr(Expression)
If limit = 1 Then x(0) = Expression : Split = x : Exit Function
If IsMissing(delimeter) Then delimeter = Chr(32)
y = Len(delimeter)
i = 1
Do
       If StrComp(Mid(Expression, i, y), delimeter, Compare) = 0 Then
           Redim Preserve x(n)
           x(n) = Left(Expression, i - 1)
           Expression = Right(Expression, Len(Expression) - i - y + 1)
           n = n + 1
           If limit <> -1 Then _
           If n > limit - 1 Then Exit Do
           i = 0
       End If
       i = i + 1
Loop Until i > Len(Expression)
If Len(Expression) Then
       ReDim Preserve x(n)
       x(n) = Expression
End If
Split = x
End Function

Function Join(SourceArray, Optional delimeter) As String
Dim e As Variant
On Error GoTo Last
If UBound(SourceArray) = -1 Then Exit Function
If IsMissing(delimeter) Then delimeter = Chr(32)
For Each e In SourceArray
       Join = Join & e & delimeter
Next
Join = Left(Join, Len(Join) - Len(delimeter))
Last:
End Function

 

[erik.van.geit]

Sorry Erik, now I'm getting a 'Run time error 13' Type Mismatch. It won't let me into debug, so I don't know what line is hanging on, all I can do is end the run sequence. It works fine on cells with no formulas, it only hangs on cells with them. Does that give you any clues?
Thanks

To be clear for everybody: this is about Table-It using XL97
that's a pitty
but at the same time you make me glad when helping to find out what the problem might be

first let's check if this is XL97-related
1. use the menu "create Table-It / only formulas"
now you will go into debug if there is a problem
2. please try out with very simple formulas (=A1)
3. if this does bug, it will probably be an XL97-problem

when you get into debug mode try to find out as much information as possible (hovering with your mouse over the items (in VBEditor) to see the values) and report
at least we should know on what line it bugs

best regards,
Erik

 

[erik.van.geit]

jindon,
I didn't see your reply before submitting my message (which was long time in on my screen before sending, since I was checking out some things)

This is what I wrote for 97 user before

thank you for this information
the problem picklefactorymentioned can be anything ...
he is talking about the entire Table-It add-in

 

[jindon]

Hi, Erik

I didn't know he is talking about Table-It add-in...