LỖI: Việc xóa tên vùng dữ liệu bằng VBA trong Excel 2010 chậm hơn nhiều so với phiên bản 2007.

Tôi có một bảng tính với hàng nghìn vùng dữ liệu được đặt tên trên nhiều trang tính khác nhau để vẽ biểu đồ cho lượng lớn dữ liệu theo nhiều cách khác nhau. Các vùng dữ liệu mong muốn được điều khiển bằng cách sửa đổi các vùng dữ liệu được đặt tên trong mã VBA. Vì dữ liệu có thể thay đổi, nên để đảm bảo chọn đúng dữ liệu, tên của các vùng dữ liệu hiện có sẽ bị xóa trước khi chúng được định nghĩa lại. Dưới đây là bản sao của mã:

Sub DeleteCompNames ()
Dim NamedRange As Name
'Lặp qua tất cả các phạm vi được đặt tên
Đối với mỗi NamedRange trong ActiveWorkbook.Names
'Cập nhật thanh trạng thái'
Application.StatusBar = "Đang xem xét phạm vi được đặt tên " & NamedRange.Index & " trong số " & ActiveWorkbook.Names.Count
'Xóa các phạm vi so sánh hiện có'
Nếu Right(LCase(NamedRange.Name), 11) = "_comp_range" thì
NamedRange.Delete
ElseIf Right(LCase(NamedRange.Name), 12) = "_comp2_range" Then
NamedRange.Delete
Kết thúc nếu
NextNamedRange
End Sub

Đoạn mã này chạy cực nhanh trong Excel 2007 nhưng lại chạy rất chậm trong Excel 2010. Trước khi hàm con này được gọi, ScreenUpdating được đặt thành False, Calculation được đặt thành xlCalculationManual, EnableEvents được đặt thành False và AutoRecover.Enabled được đặt thành False nên đó không phải là nguyên nhân gây ra vấn đề về hiệu suất. Phần chậm của đoạn mã này trong Excel 2010 là việc thực thi lệnh NamedRange.Delete . Với Excel 2007, hàm con DeleteCompNames chỉ mất vài giây để hoàn thành, nhưng giờ đây lại mất vài phút với Excel 2010. Điều gì đã thay đổi? Có cách nào để khắc phục vấn đề này không?

Cảm ơn sự giúp đỡ của bạn.

Matt

Trả lời:
Matt đã viết:

Phần chậm nhất của đoạn mã này trong Excel 2010 là việc thực thi lệnh NamedRange.Delete . Với Excel 2007, chương trình con DeleteCompNames chỉ mất vài giây để hoàn thành, nhưng giờ đây với Excel 2010, nó mất vài phút. Điều gì đã thay đổi?

Tôi không biết. Và nhận định của bạn có thể đúng. Nhưng thông thường, tôi thấy rằng những tuyên bố như vậy là do sự khác biệt về dữ liệu, cấu hình và các yếu tố khác.

Ngoài lề một chút, hoàn toàn không liên quan… Sau khi thiết lập Application.StatusBar, nên gọi DoEvents để Excel có cơ hội hiển thị sự thay đổi. Điều này có thể rõ ràng hơn trên máy tính lõi đơn cổ lỗ sĩ của tôi so với các máy tính đa lõi hiện đại hơn.

Suy nghĩ thêm... Hiệu năng sẽ như thế nào nếu bạn bỏ chú thích dòng Application.StatusBar?



Trả lời:

Suy nghĩ thêm... Hiệu năng sẽ như thế nào nếu bạn bỏ chú thích dòng Application.StatusBar?

Tôi cũng đã nghĩ đến điều đó nhưng dòng trạng thái KHÔNG ảnh hưởng đến hiệu năng.

Trả lời:
Matt đã viết:

Tôi cũng đã nghĩ đến điều đó nhưng dòng trạng thái KHÔNG ảnh hưởng đến hiệu năng.

Chẳng có gì đáng ngạc nhiên. Chỉ là một phỏng đoán vu vơ thôi. Có vô vàn lời giải thích khả thi, từ cao siêu đến lố bịch; và có quá nhiều lời bóng gió thiếu hiểu biết. Tôi không muốn góp phần vào chuyện đó.

Tôi có cài đặt XL2007 và XL2010 trên máy tính. Tôi có thể thực hiện một so sánh tương đương. Tôi không thể hứa chắc chắn điều gì. Nhưng nếu bạn muốn, bạn có thể gửi tệp Excel và hướng dẫn cách tái tạo lại quan sát của bạn cho tôi theo địa chỉ joeu2004 "at" hotmail.com.

(Hoặc tải tệp Excel và hướng dẫn lên một trang web chia sẻ tệp và đăng URL ở đây.)



Trả lời:

Bạn đã giải quyết được vấn đề này chưa? Chúng tôi đang chuyển từ Office 2003 sang 2010, và về cơ bản tôi cũng gặp vấn đề tương tự, mặc dù tôi có thể khái quát hóa nó một chút: bất kỳ thao tác xóa hoặc thay đổi phạm vi nào của các vùng được đặt tên đều trở nên cực kỳ chậm khi có một vùng được đặt tên bao gồm rất nhiều dữ liệu.

Về nguyên nhân, hiện tượng chậm chạp nghiêm trọng xảy ra khi có các vùng dữ liệu được đặt tên bao gồm rất nhiều dữ liệu. Ví dụ, tôi sử dụng một vùng dữ liệu được đặt tên tương đối để tham chiếu đến hàng trước đó trên một trang tính có nhiều mục nhập văn bản, vì vậy nó bao trùm toàn bộ trang tính. Nếu tôi xóa hầu hết các giá trị và công thức khỏi trang tính, vấn đề chậm chạp sẽ biến mất. Hiện tượng chậm chạp này chưa từng xảy ra với Excel 2003.

Về các tác động, không chỉ riêng lệnh Name.Delete. Các đoạn mã của tôi không xóa các vùng được đặt tên, mà chỉ mở rộng chúng bằng cách thêm lại chúng với một vùng mới lớn hơn khi thêm các hàng dữ liệu. Trong Excel 2010, việc mở rộng lại vùng này trở nên cực kỳ chậm (xử lý việc mở rộng lại một tên duy nhất mất vài phút, trong khi với Excel 2003, nó có thể xử lý hàng chục tên chỉ trong một phần nhỏ của giây).

Tôi đã thử một giải pháp tạm thời là xóa phạm vi bao trùm toàn bộ trang tính gây ra lỗi. Sau khi xóa phạm vi đó, việc sắp xếp lại phạm vi lại diễn ra nhanh chóng, nhưng thao tác xóa lại mất thời gian tương đương với thao tác sắp xếp lại phạm vi thông thường mà không cần xóa trước đó.

Trả lời:
Giải pháp khả thi duy nhất mà tôi tìm thấy là quay lại định dạng .xls. Lỗi này chỉ xuất hiện ở định dạng xlsm chứ không phải xls.


Trả lời:

Chào mọi người và xin lỗi vì lại mở chủ đề này lên nhưng tôi đã vật lộn với vấn đề này khá lâu rồi vì việc quay lại sử dụng định dạng .xls thậm chí không phải là một giải pháp tạm thời đối với tôi (sẽ mất đi chức năng và định dạng ở phía máy chủ).

Vậy có cách nào khác để khắc phục tình trạng chậm khi sử dụng lệnh xóa trên các vùng dữ liệu được đặt tên trong Excel 2010 không?