VBA function not returning long strings to excel

[climbingalien]

Hello

I have a function supplied by a member of this message board which works very well. But when the strings it works with are over a certain length it stops working.

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
    Dim y As Variant
    If TypeOf a Is Range Then
        For Each y In a.Cells
            aconcat = aconcat & y.Value & sep
        Next y
    ElseIf IsArray(a) Then
        For Each y In a
            aconcat = aconcat & y & sep
        Next y
    Else
        aconcat = aconcat & a & sep
    End If
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

im quite a newbi and have no idea how to get this to work with longer text strings. Any pointers?

Thanks in advance.

 

[CharlesChuckieCharles]

There is a maximum visible length of a string in a CELL

I think it's a 255 in fact ... from MS themselves

In Microsoft Excel 7.0 or earlier, this behavior occurs because the character limit per cell is 255 characters. As a result, strings greater than 255 characters in length that are passed from a Visual Basic procedure to any Microsoft Excel function or object are truncated to 255 characters.

In Excel, you can use more than 255 characters in a cell; however, when you pass strings that are greater than 255 characters in length from a Visual Basic procedure, Excel uses the same design that earlier versions use.

This limit applies to all strings that you pass from a Visual Basic procedure to an Excel sheet; it is not exclusive to information you pass to cells. For example, if you pass a text string that is longer than 255 characters to a text box on a worksheet or a dialog sheet, Excel truncates the text even though a text box can hold up to 10,240 characters.

 

[climbingalien]

Hi.

Does this mean there is no way i can get the function to work? I found the microsoft page giving details of workaround but I have no idea how I would implement them here. http://support.microsoft.com/kb/105416

Any guidance at all?

 

[CharlesChuckieCharles]

Sorry the Maximum VISIBLE Length of a CELL is 1024, if you view the contents in the formula Bar (which you can drag down to widen) you'll see up to 32,000 chars

If this thing is very wordy can you not use WORD?


but try this

Dim tester As String
tester = aconcat()
Range("A1").Select
    While Len(Selection.Text) < Len(tester)
        If Len(Selection.Text) < 1 Then
            Selection.Value = Left(tester, 255)
 
        Else
        Selection.Value = Selection.Text & Mid$(tester, Len(Selection.Text) + 1, 255)
End If
    Wend
 
End Sub

 

[climbingalien]

Sorry Im very new to this. How would I use this code?

 

[CharlesChuckieCharles]

Hi

I can use aconcat directly on a spreadsheet concatenating a range(A1:Z1) with separator = ""

and it produces a 3000+ char long concatented string

so what is the problem you are having?