VBA JOIN XL97 + thread expanded Table-It & XL97

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, gurus,

JOIN is not available in XL97
a workaround is to loop
or is there something better ?
Code:
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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
 
Upvote 0
Erik

This is what I wrote for 97 user before
Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,167
Messages
6,170,470
Members
452,330
Latest member
AFK_Matrix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top