CODEWITHSUNDEEP
excelvba
user3341082
user3341082

Reputation: 113

VBA 255 Maximum Characters in 1 Cell

I have a simple macro which adds the contents of each row in an excel sheet to a text file, with delimiters in between each cell value. This is done by running a for loop which iterates through each row and at the end of each iteration the values are added to the end of a String variable.

Each ROW can have a lot of characters in it - I have not noticed any issues with that. However, when 1 single cell contains more than 255 characters, the concatenation fails. I am not sure if it is because of String limitations (I don't think that is the case), or if it is the Trim, Join, or Index functions that contains this limitation, or if it something else. Any help in getting some more insight would be appreciated.

The line in question ('R' refers to the row/iteration number):

stringVariable = stringVariable & vbNewLine & Application.Trim(Join(Application.Index(Cells(R, "A").Resize(, 25).Value, 1, 0), "|"))

The error is:

Run-time error '13': Type mismatch

Upvotes: 0

Views: 1210

Answers (2)

user3341082
user3341082

Reputation: 113

After experimenting using different combinations of the already present functions, I found that the macro finishes without issues when Index is not used.

In the end I decided to add the value of each cell to the end of the string, one at a time. Solution:

For i = 1 To numberOfColumns
    If i < numberOfColumns Then
        stringVariable = stringVariable & Trim(Cells(R, i).Value) & "|"
    Else
        stringVariable = stringVariable & Trim(Cells(R, i).Value)
    End If
Next i
stringVariable = stringVariable & vbNewLine

Upvotes: 0

Axel Richter
Axel Richter

Reputation: 61975

The problem is with the Application.Index. How to debug?

Let's have the active sheet with any values in row 1, all with less than 255 chars. But in one of this cells in row 1, for example in C1, should be the formula:

=REPT("c",255)

Now split the code into parts:

Sub test()

 r = 1

 v2DArray = Cells(r, "A").Resize(, 25).Value
 index1DArray = Application.Index(v2DArray, 1, 0)
 joinString = Join(index1DArray, "|")
 stringVariable = Application.Trim(joinString)

 MsgBox stringVariable

End Sub

This will work until you change the formula to =REPT("c",256). Now it will fail at Application.Index.

Instead of the Application.Index you could do the following:

Sub test2()

 r = 1

 v2DArray = Cells(r, "A").Resize(, 25).Value
 ReDim v1DArray(LBound(v2DArray, 2) To UBound(v2DArray, 2)) As String
 For i = LBound(v2DArray, 2) To UBound(v2DArray, 2)
  v1DArray(i) = v2DArray(1, i)
 Next
 joinString = Join(v1DArray, "|")
 stringVariable = Application.Trim(joinString)

 MsgBox stringVariable

End Sub

Upvotes: 1

Related Questions