vba - VBA 代码中出现内存不足错误

Question

下午好，

在我的代码中收到内存不足错误，有任何更改代码的建议吗？

A 列的输入被传输到 2 个不同的单元格。

输出 1:将 A 列中的所有数据连接起来，并在每个单元格值之间插入逗号输出 2:将 A 列中的所有数据与插入的逗号和引号连接起来 - 对于每个单元格值

谢谢

Sub Inserting_Commas()
' Macro to insert commas at the end of end cell value & to convert values from rows to single column

Range("A2").Select

Dim lastRow As Long
Dim outputStr As String
Dim outputStr2 As String
Dim rownumber As Long

Sheets("Sheet1").Select
lastRow = Range("A" & Rows.Count).End(xlUp).Row

' Seperate the column A by Commas
outputStr = Range("A2")

For rownumber = 3 To lastRow
    outputStr = outputStr & "," & Range("A" & rownumber)
Next

Range("D2") = outputStr

' Seperate the Column with Quotes and Commas
Range("A2").Select

For rownumber = 2 To lastRow
    Range("B" & rownumber).Value = "''" & Range("A" & rownumber) & "'"
Next

' --------------------------------------

outputStr2 = "'" & Range("B2")

For rownumber = 3 To lastRow
    outputStr2 = outputStr2 & "," & Range("B" & rownumber)
Next

Range("D20") = outputStr2

End Sub

Answer 1

根据您在 @George's answer 下的评论那...

this code is only a partial step. I am using the output data and extract information from a Dashboard (internal system similar to SQL). The dashboard works similar to search engines. If I copy and paste the output cell into dashboard - I can segregate the required dataset.

...没有理由尝试将完整的输出存储在 Excel 内的单元格中。 Excel单元格只能存储32,767 characters ，因此无论构建字符串是否耗尽内存，您的输出都将被截断。

由于您似乎不需要 Excel 中的结果，因此只需将其写入文本文件即可:

Sub Inserting_Commas()
    Dim fso As Object, outFile As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set outFile = fso.CreateTextFile("C:\Foo\bar.txt", True) 'Replace with your own path.

    With Sheets("Sheet1")
        Dim lastRow As Long, rownumber As Long
        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        For rownumber = 2 To lastRow
            outFile.Write "'" & .Cells(rownumber, 1).Value & "'"
            If rownumber <> lastRow Then outFile.Write ","
        Next
    End With
End Sub