Jeff có một sổ làm việc Excel phức tạp sử dụng VBA để tạo nhiều báo cáo ở định dạng PDF. Nó chạy tuyệt vời trên Excel 2013 32-bit trên nhiều PC. Ngược lại, trên nhiều PC khác đang chạy Office 365, Jeff nhận được lỗi “Hết bộ nhớ” giữa chừng.

Do đó, các macro hoạt động hoàn hảo trên phiên bản Excel cũ hơn, nhưng không hoạt động trên phiên bản mới nhất. (Một lần nữa, Jeff đã thử nghiệm điều này trên nhiều PC để kết quả có thể lặp lại.) Jeff tự hỏi liệu có sự cố rò rỉ bộ nhớ hoặc sự khác biệt macro trong Office 365 có thể gây ra sự cố này hay không.

Nói chung, việc sử dụng bộ nhớ liên quan đến một vấn đề chính — các biến và cách chúng được sử dụng trong macro của bạn. Nó đặc biệt có tác dụng với các mảng biến. Luôn luôn là một ý kiến ​​hay để đảm bảo rằng bạn khai báo tất cả các biến của mình (sử dụng chỉ thị Option Explicit để trợ giúp về vấn đề này) và kiểm tra việc xác định kích thước mảng của bạn để đảm bảo rằng bạn không cố gắng khai báo một mảng quá lớn.

Các biến đối tượng cũng có thể sử dụng nhiều bộ nhớ. Nếu bạn sử dụng từ khóa Đặt để gán một đối tượng cho một biến, hãy đảm bảo rằng bạn đặt biến đối tượng thành Không có gì khi bạn hoàn tất. Điều này đặc biệt quan trọng bên trong vòng lặp — nếu mỗi lần đi qua vòng lặp dẫn đến việc khai báo một biến đối tượng khác, nếu bạn không xóa biến đó trong mỗi lần lặp lại vòng lặp, bạn có thể sử dụng bộ nhớ rất nhanh.

Cũng có một điểm cần thực hiện để tắt cập nhật màn hình trong khi macro của bạn đang chuyển động quay vòng. Nếu macro của bạn cố gắng cập nhật liên tục màn hình, điều đó sẽ sử dụng tài nguyên và làm chậm macro của bạn.

Tuy nhiên, người ta sẽ nghĩ rằng trong tình huống của Jeff, việc sử dụng bộ nhớ không phải là một vấn đề lớn. Rốt cuộc, anh ấy đang chuyển từ phiên bản Excel 32 bit sang (nhiều khả năng là) phiên bản 64 bit. Điều này cho phép Excel sử dụng không gian bộ nhớ lớn hơn bao giờ hết. Do đó, người ta sẽ nghĩ rằng lỗi “Hết bộ nhớ” sẽ ít xảy ra hơn là nhiều khả năng.

Thật không may, có một cờ lê để ném vào hỗn hợp ở đây. Khi bạn gặp lỗi “Hết bộ nhớ”, có thể không phải bộ nhớ là thủ phạm.

Trong nhiều năm, Microsoft đã ghi nhận rằng bộ nhớ đặc biệt này có bản chất chung và nó có thể được gây ra bởi bất cứ điều gì. (Trong cuốn sách của tôi, điều đó làm cho thông báo lỗi trở nên vô dụng, nhưng tôi là ai để đặt câu hỏi cho Microsoft. Thật bực bội!)

Điều khác mà tôi sẽ cân nhắc làm (chưa xem mã của Jeff) là để đảm bảo rằng bạn không lập trình tuyến tính. Nói cách khác, hãy chia mã của bạn thành các chương trình con riêng lẻ để tạo sẵn các tác vụ nhỏ, rời rạc. Sau đó, bạn có thể gọi từng chương trình con từ một quy trình điều khiển chính. Lợi ích của việc này là quản lý bộ nhớ do phạm vi.

Các biến chỉ có phạm vi trong quy trình mà chúng được sử dụng.

(Chà, điều này đúng trừ khi bạn khai báo chúng là có phạm vi toàn cục, nhưng đó là một nồi cá khác.) Điều này có nghĩa là các biến được sử dụng trong một thủ tục bị phá hủy và bộ nhớ của chúng tự động giải phóng khi thủ tục được thoát.

Tuy nhiên, nếu macro của bạn được viết tuyến tính để không có chương trình con, thì tất cả các biến được duy trì trong hệ thống của bạn trong toàn bộ thời gian macro đang chạy. Tốt hơn hết, từ quan điểm quản lý bộ nhớ, hãy chia nhỏ mã của bạn thành các thủ tục để bạn có thể giảm thiểu việc sử dụng bộ nhớ.

Ưu điểm khác của việc modularizing mã của bạn theo cách này là nếu bạn gặp lỗi — thậm chí là “Hết bộ nhớ” nói chung là vô giá trị

lỗi — nó có thể xảy ra trong một quy trình cụ thể và do đó bạn có thể có cơ hội tốt hơn để xác định vị trí lỗi thực sự đang xảy ra. Theo suy nghĩ của tôi, khả năng theo dõi hiệu quả hơn điểm tiềm ẩn của sự thất bại là một lợi ích lớn.

Nếu điều này vẫn không giải quyết được vấn đề, tôi đã thấy một số báo cáo rằng khi bạn lưu sổ làm việc của mình, nó buộc VBA thực hiện thu dọn rác trên không gian bộ nhớ của bạn. Vì lý do này, một số người khuyên bạn nên để macro của bạn lưu sổ làm việc thường xuyên, thay vì chỉ ở cuối macro. Tôi không biết báo cáo này chính xác đến mức nào, nhưng nếu bạn đang sử dụng macro của mình để lưu sổ làm việc, thì việc lưu nó thường xuyên hơn là ít hơn cũng không gây hại gì.

Có một điều cuối cùng cần được đề cập. Nếu bạn hiện đang sử dụng Office 365 Business Premium, nó bao gồm SharePoint Online và Excel Web App. Do đó, các phiên bản này giới hạn kích thước của tệp Excel là 10 MB. Nếu tệp của bạn khá lớn, nó có thể hoạt động tốt trong Excel 2013, nhưng không thành công trong Office 365 Business Premium do giới hạn kích thước tệp này.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (13619) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.