Why does my string snap?

[Kelvin Stott]

Hello,

I have written the following function in VBA, which converts a 1-dimensional data array into a text string by concatenating successive data points separated by a comma. The string is defined as variable length with no fixed limit, and I had understood this should allow for about 2 billion characters, but the function generates an error ("wrong data type") as the string exceeds about 64,000 characters. Is there any way to extend/avoid this limit?

Function DSTORE(DArray)
'Converts a 1-dimensional data array into a text string, with successive data points separated by a comma

    Dim d As Long
    Dim DText As String    'Variable length string with no fixed limit
    
    For d = LBound(DArray) To UBound(DArray)
        DText = DText & DArray(d) & ","
    Next d

    DSTORE = DText

End Function

Thanks for any help with this!

Best regards,
Kelvin

 

[Andrew Poulsom]

Does this work for you?

Function DSTORE(DArray)
'Converts a 1-dimensional data array into a text string, with successive data points separated by a comma
    DSTORE = Join(DArray, ",")
End Function

 

[Kelvin Stott]

Brilliant, thank you!!!

 

[Kelvin Stott]

In fact, it seems I don't even need to create this new function, as it already exists as Join()!

 

[Kelvin Stott]

Ouch, it seems that the limit is actually driven by the capacity of an individual cell, when I try to use the formula in a worksheet.

What is the maximum capacity (no. of characters) of a cell? Does this vary in different versions of Excel?

 

[pgc01]

Hi Kelvin

It's 32K and has been that number for a while.

 

[pgc01]

Hi Kelvin

It's 32K and has been that number for a while.

... at least since excel 2000

 

[Kelvin Stott]

Thanks once again!