ExcelVBA寫陣列沒有For回圈

我有一個包含 30,000 行和 28 列資料的電子表格。我正在尋找以特定方式對資料進行編碼。資料由字串和數字的混合組成。對于每一行（第 1 到 28 列），我需要將每個單元格中的每個字符轉換為一個數字。我有一本字典可以進行轉換。其中字符是鍵，值是編碼。

我的代碼有效，但是有點慢。完成任務需要 30 多分鐘。鑒于我們正在查看的資料量，這是可以理解的。30, 000 行 x 28 列 x N 個字符。這是很多。

下面代碼的快速描述：

1. 回圈遍歷 Range 中的每個單元格（30,000 行，28 列）
2. 對于每一行，將所有值連接成一個字串
3. 將大字串逐個字符傳遞到字典中，檢索編碼值（數字）。
4. 將編碼的字串寫入作業表。每個值都有自己的單元格。

我猜瓶頸是當我在回圈中將編碼寫入作業表時。我想知道他們是否有更快的方法來做到這一點？

Sub main()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

'initialize dictionary for encoding data on another module
Globals.initialize_globals

'loop through each record
Dim wkbook As Workbook: Set wkbook = Workbooks.Application.ActiveWorkbook
Dim wksheet As Worksheet: Set wksheet = wkbook.Worksheets("Raw Data")
Dim lastRow As Integer: lastRow = wksheet.Cells(Rows.Count, 1).End(xlUp).Row
Dim stringbuilder As String
Dim encoding() As Integer
Dim char_count As Integer
Dim i, ii, e As Integer

'loop through rows, columns and encode string data
For i = 2 To lastRow
    'loop through columns
    For ii = 1 To 27
        'concatenate each cell value as a large string
        stringbuilder = stringbuilder & wksheet.Range(Cells(i, ii), Cells(i, ii)).Value
    Next
    encoding = EncodeString(stringbuilder)
    stringbuilder = ""
    For e = 1 To UBound(encoding)
        'write encoding onto sheet
        wksheet.Range(Cells(i, 33   e), Cells(i, 33   e)) = encoding(e)
    Next
Next

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Function EncodeString(str As String) As Integer()
Dim encoded() As Integer
ReDim encoded(1 To Len(str))

For i = 1 To Len(str)
    ' build an encoded string by passing in each character as a key, and retrieve encoded value
    encoded(i) = Parameters.Item(Mid(str, i, 1))
Next
EncodeString = encoded
End Function

uj5u.com熱心網友回復：

請嘗試下一個更新的代碼：

Sub main()
'initialize dictionary for encoding data on another module
Globals.initialize_globals

Dim wkbook As Workbook: Set wkbook = Workbooks.Application.ActiveWorkbook
Dim wksheet As Worksheet: Set wksheet = wkbook.Worksheets("Raw Data")
Dim lastRow As Integer: lastRow = wksheet.cells(rows.count, 1).End(xlUp).row
Dim stringbuilder As String, encoding() As Integer, char_count As Integer
Dim i As Long, ii As Long, e As Long
Dim arr, arrFin, N As Long, maxCol As Long 'new variables

N = 1000 'the estimated maximum number of characters on a row
arr = wksheet.Range("A2:AA" & lastRow).Value
ReDim arrFin(1 To UBound(arr), 1 To N)
'loop through array rows, columns and encode string data
For i = 2 To UBound(arr)
    For ii = 1 To UBound(arr, 2)
        'concatenate each cell value as a large string
        stringbuilder = stringbuilder & arr(i, ii) 
    Next ii
    encoding = EncodeString(stringbuilder)
    stringbuilder = ""
    If UBound(encoding) > UBound(arrFin, 2) Then
        maxCol = UBound(encoding)
        ReDim Preserve arrFin(1 To UBound(arr), 1 To maxCol)
    End If
    For e = 1 To UBound(encoding)
        arrFin(i, e) = encoding(e)
    Next e
Next i
if maxCol = 0 Then maxCol =ubound(arrFin, 2)
'drop the array content at once:
wksheet.Range("AG2").Resize(UBound(arrFin), maxCol).Value = arrFin
End Sub

請使用您現有的功能進行編碼...

當然沒有經過測驗，但這應該是使代碼更快的想法。事實上，我嘗試將我在評論中提出的建議放入代碼中。

uj5u.com熱心網友回復：

VBA for Excel 的瓶頸是從 VBA 訪問 Excel，因此您可以通過將范圍內的資料讀取到二維陣列中并使用陣列中的資料執行操作來顯著減少運行時間。

同樣，您可以將輸出資料收集到一個陣列中并立即將其寫回。在您的特殊情況下，逐行寫入資料可能更容易（對于每一行，您可能有不同長度的資料），但至少您可以一次性轉儲一行的資料。

VBA 中的字串處理速度相當快，您不必擔心。

這應該相當快：

data = wksheet.Range(wksheet.Cells(2, 1), wksheet.Cells(lastRow, 27))
Dim row As Long, col As Long

For row = LBound(data, 1) To UBound(data, 1)
    Dim stringbuilder As String
    stringbuilder = ""
    For col = LBound(data, 2) To UBound(data, 2)
        'concatenate each cell value as a large string
        stringbuilder = stringbuilder & data(row, col)
    Next

    If stringbuilder <> "" Then
        Dim encoding() As Long
        encoding = EncodeString(stringbuilder)
    End If
    wksheet.Cells(row   1, 33).Resize(1, UBound(encoding)) = encoding
Next

請注意，您始終應該使用Long而不是Integer在 VBA 中。它避免了溢位并且無論如何都更快（并且它不使用更多記憶體）

更新 做了一個基準測驗（普通電腦，Excel 2016）。具有混合（隨機）資料的 30k 行 * 26 列（使用將每個字符轉換為其 ascii 值的虛擬編碼函式）。花了大約。7s 執行。
我還使用大型二維輸出陣列進行了更改（就像FaneDuru在他的回答中所做的嘗試），但這需要更長的時間（13 秒）。

標籤：擅长 vba 表现

上一篇：用于回圈性能的Javascript因回圈設計而改變

下一篇：python中的裸體檢測