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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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

Active Member
Joined
Jan 28, 2005
Messages
412
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,814
Messages
5,638,493
Members
417,029
Latest member
lingx86

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
Top