数组值超过255个字符时出现VBA代码错误 [英] VBA code error when array value exceeds 255 characters

问题描述

我从我正在开发的项目中提取了以下代码段：

I have extracted the following code snippet from a project I am working on :

Sub testData()

Dim dataRange As Range
Set dataRange = Range("B2").Offset(1, 0).Resize(, 3)

Dim data As Variant
data = dataRange.Value2

Dim i As Integer
For i = 1 To UBound(data)
    Dim datarow As Variant
    datarow = WorksheetFunction.Index(data, i, 0)

    For j = 1 To dataRange.Count
        MsgBox "The data is " & datarow(j)
    Next j
Next i

End Sub

此示例中单元格B3，C3和D3中的值可能是文本，日期，数字。

Values in Cells B3, C3 and D3 in this example might be text, date, number.

只要文本内容指定范围内的每个单元格不超过255个字符。如果更大的话，代码将会在错误的一行出现：

This code executes fine as long as the text contents of each cell in the range specified does not exceed 255 characters. If greater, the code will throw an error at the line :

datarow = WorksheetFunction.Index(data, i, 0)

我已经阅读了有关各种解决方法的Excel中的各种字符串限制（使用2010）。然而，由于使用了数据类型Variant，我不确定如何检查超出这些限制的文本值的存在。

I have read about various String limits in Excel (using 2010) for which there are various workarounds. However given the use of datatype Variant, I am uncertain how one can check for the existence of a text value exceeding these limits.

有人能够建议如何调整允许更大的文本长度超过255个字符的代码？

Is anyone able to suggest how one might adjust the code to allow for greater text length than 255 characters?

推荐答案

根据Tim的建议调整代码，直接访问数组比使用Worksheet.Index。更新代码如下：

Adjusted my code as per Tim's suggestion to access the array directly rather than using the Worksheet.Index. Updated code as follows :

For i = 1 To UBound(data)
   For j = 1 To dataRange.Count
      MsgBox "The data is " & data(i, j)
   Next j
Next i

发出255个字符的限制。

This avoids the issue of the 255 character limitation.

这篇关于数组值超过255个字符时出现VBA代码错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！