Làm việc với Excel VBA Macro
Macro trong Excel được lưu trữ dưới dạng mã VBA và đôi khi bạn muốn chỉnh sửa mã này trực tiếp. Hướng dẫn này sẽ trình bày cách xem và chỉnh sửa macro, mô tả một số kỹ thuật gỡ lỗi macro và đưa ra một số ví dụ chỉnh sửa phổ biến.
Xem Macro
Danh sách các macro có thể được hiển thị trong hộp thoại Macro. Để xem hộp thoại này, hãy chọn tab Nhà phát triển trên ruy-băng và nhấp vào nút Macro.
Nếu nhiều sổ làm việc đang mở, macro từ tất cả các sổ làm việc sẽ được hiển thị trong danh sách. Macro trong sổ làm việc đang hoạt động sẽ xuất hiện theo tên riêng, trong khi macro trong các sổ làm việc khác sẽ có tiền tố là tên sổ làm việc và dấu chấm than (tức là “Book2! OtherMacro”).
Mở Macro để chỉnh sửa
Bạn có thể sử dụng hộp thoại Macro để mở mã cho macro bằng cách chọn tên của macro và nhấp vào nút Chỉnh sửa. Thao tác này sẽ mở macro trong VB Editor.
Ngoài ra, bạn có thể mở VB Editor trực tiếp bằng cách nhấp vào nút Visual Basic trên tab Nhà phát triển hoặc bằng cách nhấn phím tắt ALT + F11.
Sử dụng phương pháp này, bạn sẽ cần điều hướng đến macro mong muốn của mình (còn được gọi là ‘thủ tục’). Chúng ta sẽ xem xét bố cục VBA Editor:
Tổng quan về Trình soạn thảo VB
VB Editor có một số cửa sổ; trong hướng dẫn này, chúng tôi sẽ bao gồm Cửa sổ Dự án, Cửa sổ Thuộc tính và Cửa sổ Mã.
Cửa sổ dự án
Cửa sổ Dự án hiển thị mỗi tệp Excel dưới dạng dự án của riêng nó, với tất cả các đối tượng trong dự án đó được phân loại theo loại. Các macro đã ghi sẽ xuất hiện trong danh mục "Mô-đun", thông thường trong đối tượng “Module1”. (Nếu dự án của bạn có nhiều Mô-đun và bạn không chắc chắn nơi lưu trữ macro của mình, chỉ cần mở nó từ hộp thoại Macro đã nói ở trên.)
Cửa sổ thuộc tính
Cửa sổ Thuộc tính hiển thị các thuộc tính và giá trị liên quan của một đối tượng - ví dụ: bấm vào một đối tượng trang tính trong Cửa sổ Dự án sẽ hiển thị danh sách các thuộc tính cho trang tính. Tên thuộc tính ở bên trái và giá trị thuộc tính ở bên phải.
Chọn một mô-đun trong cửa sổ Dự án sẽ cho thấy nó chỉ có một thuộc tính, “(Tên)”. Bạn có thể thay đổi tên của mô-đun bằng cách nhấp đúp vào giá trị của thuộc tính, nhập tên mới và nhấn Enter. Thay đổi tên của mô-đun sẽ đổi tên nó trong Cửa sổ Dự án, điều này rất hữu ích nếu bạn có nhiều mô-đun.
Cửa sổ mã
Cửa sổ mã là trình chỉnh sửa văn bản đặc biệt, trong đó bạn có thể chỉnh sửa mã VBA của macro của mình. Nếu bạn muốn xem mã cho macro nằm trong Module1, bạn sẽ bấm đúp vào ‘Module1’ trong Cửa sổ Dự án.
Chạy Macro trong VB Editor
Macro có thể được chạy trực tiếp từ VB Editor, rất hữu ích để kiểm tra và gỡ lỗi.
Chạy Macro
- Trong Cửa sổ Dự án, bấm đúp vào mô-đun chứa macro bạn muốn kiểm tra (để mở cửa sổ Mã của nó)
- Trong cửa sổ Mã, đặt con trỏ vào bất kỳ vị trí nào trên mã của macro giữa “Phụ” và “Kết thúc phụ”
- Nhấn vào Chạy trên thanh công cụ hoặc nhấn phím tắt F5
“Bước qua” Macro
Thay vì chạy macro cùng một lúc, bạn có thể chạy macro từng dòng một, sử dụng phím tắt để “xem qua” mã. Macro sẽ tạm dừng trên mỗi dòng, cho phép bạn đảm bảo rằng mỗi dòng mã thực hiện những gì bạn mong đợi trong Excel. Bạn cũng có thể ngăn macro tiếp tục bất kỳ lúc nào bằng cách sử dụng phương pháp này.
Để "bước qua" một macro:
- Trong Cửa sổ Dự án, bấm đúp vào mô-đun chứa macro bạn muốn kiểm tra (để mở cửa sổ Mã của nó)
- Trong cửa sổ Mã, đặt con trỏ vào bất kỳ vị trí nào trên mã của macro
- Nhấn phím tắt F8 để bắt đầu quá trình “từng bước”
- Nhấn F8 nhiều lần để thực thi mã trước, được biểu thị bằng điểm đánh dấu màu vàng trong cửa sổ Mã
- Để ngăn macro tiếp tục, hãy nhấn Cài lại cái nút
Tại sao phải chỉnh sửa Macro VBA?
Trình ghi macro - trong khi hiệu quả - cũng rất hạn chế. Trong một số trường hợp, nó tạo ra các macro chậm, ghi lại các hành động bạn không định lặp lại hoặc ghi lại những điều bạn không nghĩ là mình đang làm. Học cách chỉnh sửa macro của bạn sẽ giúp chúng chạy nhanh hơn, hiệu quả hơn và dễ đoán hơn.
Bên cạnh việc khắc phục những vấn đề đó, bạn cũng sẽ đạt được sự gia tăng đáng kể về năng suất khi bạn khai thác toàn bộ sức mạnh của macro. Macro không chỉ phải là bản ghi các tác vụ - macro có thể bao gồm logic để chúng chỉ thực hiện các tác vụ trong một số điều kiện nhất định. Chỉ trong vài phút, bạn có thể viết mã các vòng lặp lặp lại một nhiệm vụ hàng trăm hoặc hàng nghìn lần trong một lần!
Dưới đây, bạn sẽ tìm thấy một số mẹo hữu ích để giúp tối ưu hóa mã macro của mình, cũng như các công cụ để làm cho macro của bạn hoạt động tốt hơn và thông minh hơn.
Các ví dụ về chỉnh sửa macro phổ biến
Tăng tốc Macro
Nếu bạn có một macro cần nhiều thời gian để chạy, có thể có một số lý do khiến nó chạy chậm.
Đối với một: khi một macro chạy, Excel sẽ hiển thị mọi thứ như nó xảy ra trong thời gian thực - trong khi nó có thể nhìn nhanh chóng với bạn, thực sựhiển thị công việc là một thành công hiệu suất đáng kể. Một cách để làm cho Excel chạy nhanh hơn đáng kể là yêu cầu nó ngừng cập nhật màn hình:
'Tắt ứng dụng cập nhật màn hình.ScreenUpdating = False' Bật ứng dụng cập nhật màn hình.ScreenUpdating = True
Dòng “Application.ScreenUpdating = False” có nghĩa là bạn sẽ không thấy macro hoạt động, nhưng nó sẽ chạy nhanh hơn nhiều. Lưu ý rằng bạn phải luôn đặt Cập nhật màn hình thành Đúng ở cuối macro của mình, nếu không Excel có thể không hoạt động theo cách bạn mong đợi sau này!
Một cách khác để tăng tốc macro:tắt tính toán tự động trong macro. Nếu bạn đã làm việc với các bảng tính phức tạp, bạn sẽ biết rằng những thay đổi nhỏ có thể kích hoạt hàng nghìn phép tính cần thời gian để hoàn thành, đó là lý do tại sao nhiều người tắt tính năng tự động trong các tùy chọn của Excel. Bạn cũng có thể chuyển đổi điều này bằng mã VBA, vì vậy macro của bạn sẽ vẫn hoạt động nhanh chóng trên các máy tính khác. Điều này hữu ích trong trường hợp bạn đang sao chép dán nhiều ô công thức hoặc khiến nhiều phép tính kích hoạt khi bạn dán dữ liệu vào một dải ô:
'Tắt ứng dụng tự động tính toán.Calculation = xlCalculationManual'
Thêm vòng lặp và logic (câu lệnh If)
Trình ghi macro lưu tất cả các hành động của bạn dưới dạng mã bằng ngôn ngữ gọi là VBA. VBA không chỉ là một cách ghi lại các hành động trong Excel - nó là một ngôn ngữ lập trình, có nghĩa là nó có thể chứa mã để đưa ra quyết định về những hành động cần thực hiện hoặc lặp lại các hành động cho đến khi một điều kiện được đáp ứng.
Vòng lặp
Giả sử bạn muốn tạo một macro chuẩn bị báo cáo và là một phần của macro đó, bạn phải thêm mười chín trang tính vào sổ làm việc, tổng cộng là hai mươi. Bạn có thể ghi lại chính mình khi nhấp vào nút (+) lặp đi lặp lại hoặc bạn có thể viết một vòng lặp lặp lại hành động cho bạn, như sau:
Sub ReportPrep () Dim i As Long For i = 1 to 19 Sheets.Add Next i End Sub
Trong ví dụ này, chúng tôi sử dụng Vòng lặp for, là một loại vòng lặp lặp đi lặp lại qua một loạt các mục. Ở đây, phạm vi của chúng tôi là các số từ 1 đến 19, sử dụng một biến có tên là ‘i’ để vòng lặp có thể theo dõi. Trong vòng lặp của chúng tôi, chỉ có một hành động được lặp lại giữa vì vàKế tiếp dòng (thêm trang tính), nhưng bạn có thể thêm nhiều mã vào bên trong vòng lặp như bạn muốn để thực hiện những việc như định dạng trang tính hoặc sao chép và dán dữ liệu vào mỗi trang tính - bất cứ điều gì bạn muốn lặp lại.
Câu lệnh If
Một Câu lệnh if được sử dụng để quyết định xem một số mã có chạy hay không, sử dụng một bài kiểm tra logic để đưa ra quyết định. Đây là một ví dụ đơn giản:
Sub ClearIfSmall () If Selection.Value <100 Then Selection.Clear End If End Sub
Ví dụ đơn giản này cho thấy cách hoạt động của câu lệnh If - bạn kiểm tra một số điều kiện là Đúng hoặc Sai (Giá trị của ô được chọn có nhỏ hơn 100 không?), và nếu kiểm tra trả về True, thì mã bên trong sẽ chạy.
Một thiếu sót của mã này là nó chỉ kiểm tra một ô tại một thời điểm (và sẽ không thành công nếu bạn chọn nhiều ô). Điều này sẽ hữu ích hơn nếu bạn có thể… lặp qua mọi ô đã chọn và kiểm tra từng ô…
Sub ClearIfSmall () Dim c Theo phạm vi cho mỗi c Trong lựa chọn. Giá trị Nếu c. Giá trị <100 Sau đó c. Kết thúc rõ ràng nếu Tiếp theo c Kết thúc Sub
Trong ví dụ này, có một vòng lặp For hơi khác - vòng lặp này không lặp qua một dải số mà thay vào đó vòng qua tất cả các ô trong vùng chọn, sử dụng một biến có tên là ‘c’ để theo dõi. Bên trong vòng lặp, giá trị của ‘c’ được sử dụng để xác định xem ô có nên được xóa hay không.
Các câu lệnh Loops và If có thể được kết hợp theo bất kỳ cách nào bạn muốn - bạn có thể đặt các vòng lặp bên trong các vòng lặp, hoặc một If bên trong một vòng lặp khác, hoặc sử dụng If để quyết định xem một vòng lặp có nên chạy hay không.
<<>>
Xóa hiệu ứng cuộn
Một lý do phổ biến để chỉnh sửa mã macro là loại bỏ thao tác cuộn màn hình. Khi ghi macro, bạn có thể phải đến các vùng khác của trang tính bằng cách cuộn, nhưng macro không cần cuộn để truy cập dữ liệu.
Việc cuộn có thể làm lộn xộn mã của bạn với hàng trăm hoặc thậm chí hàng nghìn dòng mã không cần thiết. Dưới đây là một ví dụ về mã được ghi lại khi bạn nhấp và kéo trên thanh cuộn:
Loại mã này hoàn toàn không cần thiết và có thể bị xóa mà không ảnh hưởng đến bất kỳ chức năng nào khác. Ngay cả khi bạn muốn giữ lại cuộn, mã này vẫn có thể được cô đọng thành một vòng lặp.
Loại bỏ code dư thừa
Các macro đã ghi có xu hướng thêm nhiều mã thừa không nhất thiết phản ánh những gì bạn muốn macro thực hiện. Lấy ví dụ, đoạn mã được ghi lại sau đây ghi lại việc thay đổi tên phông chữ trên một ô:
Mặc dù chỉ thay đổi tên phông chữ, mười một (11) thay đổi phông chữ đã được ghi lại như kích thước phông chữ, hiệu ứng văn bản, v.v. Nếu mục đích của macro là chỉ thay đổi tên phông chữ (trong khi để nguyên tất cả các thuộc tính khác) thì macro đã ghi sẽ không hoạt động!
Có thể thay đổi macro này để nó chỉ thay đổi tên phông chữ:
Macro này không chỉ sẽ hoạt động như dự kiến bây giờ mà còn dễ đọc hơn nhiều.
Loại bỏ các chuyển động con trỏ
Một thứ khác được ghi lại trong macro là các lựa chọn trang tính và ô. Đây là một vấn đề vì người dùng có thể dễ dàng mất dấu những gì họ vừa làm nếu con trỏ di chuyển đến một vị trí khác sau khi macro chạy.
Như với việc cuộn, bạn có thể cần phải di chuyển con trỏ và chọn các ô khác nhau để thực hiện một tác vụ, nhưng các macro không nhất thiết phải sử dụng con trỏ để truy cập dữ liệu. Hãy xem xét đoạn mã sau, mã này sao chép một dải ô và sau đó dán nó vào ba trang tính khác:
Có một số vấn đề với mã này:
- Người dùng sẽ mất vị trí trước đó của họ trong sổ làm việc
- Macro không chỉ định chúng tôi đang sao chép trang tính nàotừ - đây có thể là sự cố nếu macro được chạy trên trang tính sai
Ngoài ra, mã rất khó đọc và lãng phí. Những vấn đề này có thể được giải quyết đủ dễ dàng:
Trong mã này, có thể thấy rõ rằng chúng tôi đang sao chép từ Trang tính1 và cả trang tính đang hoạt động cũng như phạm vi đã chọn đều không cần thay đổi để dán dữ liệu. (Một thay đổi đáng kể là việc sử dụng “PasteSpecial” thay vì “Paste” - Các đối tượng trong phạm vi, như “Phạm vi (“ C4 ″) ”, chỉ có quyền truy cập vào lệnh PasteSpecial.)
Bất cứ khi nào mã có đầy đủ các tham chiếu đến “.Select” và “Selection”, thì đó là manh mối cho thấy có chỗ để tối ưu hóa mã đó và làm cho nó hiệu quả hơn.
