Excel 宏有时会挂起

Question

我在 excel 中编写了一个宏，有时宏会按应有的方式工作，但大约 40% 的时间它会挂起完整的 excel 并且没有任何反应。我试图逐步通过，大多数时候我发现宏观手在 3 个特定的陈述。有人可以告诉我我做错了什么，或者如何更好地使宏更加健壮和稳定。

以下是宏中的代码：

Sub fastcloudextractor()
'
' fastcloud extractor Macro
'
'    defenitions
Dim data_arr() As Variant, temp_arr() As Variant
Dim i As Long, j As Long, k As Long, curent_item As Long
Dim pctCompl As Integer, err As Integer, total_items As Integer
Application.ScreenUpdating = False
err = 2
'
'    get data row count and load data into array
'
Sheets("Original").Select
data_count = Range("A1").End(xlDown).Row
data_count = data_count + 1
Cells(data_count, 1) = 1
Cells(data_count, 5) = 1
data_arr = Range(Cells(2, 5), Cells(data_count, 14))
'    without Below 2 Lines the program gives a error
'
Sheets("sheet4").Select
temp_arr = Range(Cells(1, 1), Cells(data_count, 10))

' ----- Begin new code -----
k = 1
current_item = data_arr(1, 1)
'    Debug.Print current_item
For j = LBound(data_arr) To UBound(data_arr)
If data_arr(j, 1) = current_item Then
do some thing 
Else
Do some thing else
End If
k = k + 1
Next j
Erase temp_arr
Erase data_arr
Sheets("Original").Select
Range("A2:N2").Select
Sheets("Unique").Select
Range("A2").Select
Sheets("Selected").Select
Range("A1").Select
Sheets("Compiled").Select
Range("A2").Select
Sheets("Extracted").Select
Range("A1").Select
Sheets("Magmi").Select
Application.ScreenUpdating = True
Application.StatusBar = False
Beep
MsgBox "Data Conversion Completed" & vbCr & "Total no of products is .." & total_items
End Sub

宏正常挂起

1. data_arr = Range(Cells(2, 5), Cells(data_count, 14))

或

2. temp_arr = Range(Cells(1, 1), Cells(data_count, 10))

有人可以帮助我确定我做错了什么以及如何纠正它。

我仍然是新手，所以如果提到更正，请提供代码示例。

Answer 1

我觉得data_count = Range("A1").End(xlDown).Row非常可疑。如果只有一行数据，则data_count将等于 1048576，然后用data_arr = Range(Cells(2, 5), Cells(data_count, 14))填充此数组 10,485,760 个值。这是很多。最好改用data_count = Range("A" & Rows.Count).End(xlUp).Row。