vba - 在vba excel中保存大量字符串

Question

我必须制作一个应用程序来在 excel vba 中加载图像，加密该图像并将其保存在单元格中。我的问题是单元格中的字符限制(32.767 个字符)，而我的加密字符串就像 800k 个字符。

我创建了一个函数来创建一个具有自定义字符串字符长度的字符串数组以保存在一行中，但是当我保存在单元格中时，我收到了这个错误:

Public Function SplitString(ByVal TheString As String, ByVal StringLen As Integer) As String()
Dim ArrCount As Integer  'as it is declared locally, it will automatically reset to 0 when this is called again
Dim I As Long  'we are going to use it.. so declare it (with local scope to avoid breaking other code)
Dim TempArray() As String
ReDim TempArray((Len(TheString) - 1) \ StringLen)

For I = 1 To Len(TheString) Step StringLen
    TempArray(ArrCount) = Mid$(TheString, I, StringLen)
    ArrCount = ArrCount + 1
Next

SplitString = TempArray   'actually return the value
End Function

Dim StringArray As Variant
        StringArray = SplitString(EncodeFile(.SelectedItems(1)), 30000)

        Dim ind As Integer
        ind = 2

        For index = 1 To UBound(StringArray)
            Sheet5.Cells(55, ind).value = StringArray(index)
            ind = ind + 1
        Next index

我通过在 for 循环中添加延迟来解决此问题，但这不是最佳解决方案

        For index = 1 To UBound(StringArray)
            Sheet5.Cells(55, ind).value = StringArray(index)
            ind = ind + 1
            Application.Wait (Now + TimeValue("00:00:01"))
        Next index

现在的问题是:我可以让这个更快或更好的方法来解决这个问题吗？

Answer 1

将数组转储到没有循环的一行内的单元格中。

Sheet5.Cells(55, "A").resize(1, ubound(StringArray) + 1) = StringArray