Ghi Macro trong Excel - Bài báo trên TechTV

Đây là những ghi chú chương trình của tôi từ sự xuất hiện của tôi trên Tập 33 của Lời kêu gọi trợ giúp trên TechTV Canada.

VBA Macro Recorder

Mọi bản sao của Excel được bán từ năm 1995 đều bao gồm Visual Basic for Applications (VBA) cực kỳ mạnh mẽ ẩn sau các ô. Nếu bạn có Excel, bạn có VBA.

VBA cho phép bạn tự động hóa bất kỳ điều gì bạn có thể làm trong Excel, ngoài ra còn có thể thực hiện nhiều việc khác mà thông thường trong Excel không thể thực hiện được.

May mắn cho chúng tôi, Microsoft đã bao gồm một trình ghi macro với Excel. Điều này cho phép bạn viết mã VBA mà không cần phải là một lập trình viên. Thật không may, Macro Recorder có sai sót. Nó sẽ không tạo ra mã sẽ hoạt động mọi lúc. Trình ghi macro sẽ giúp bạn đóng lại, nhưng trong nhiều trường hợp, bạn cần sửa mã một chút để nó hoạt động ổn định. Trong các trường hợp khác, bạn cần biết khi nào sử dụng Ghi tương đối để macro hoạt động.

Tracy Syrstad và tôi đã viết VBA và Macro Microsoft Excel 2016 để giúp bạn hiểu những hạn chế của mã đã ghi và hướng dẫn bạn cách sửa mã đã ghi thành thứ gì đó sẽ hoạt động mọi lúc.

Trong chương trình, tôi đã trình bày quy trình ghi macro sẽ hoạt động hoàn hảo nếu bạn biết cách sử dụng nút Tham chiếu tương đối. Ai đó đã đưa cho tôi một trang tính Excel với hàng trăm tên và địa chỉ. Họ muốn có nhãn gửi thư. Nếu bạn đã từng sử dụng tính năng trộn thư của Microsoft Word, bạn biết rằng bạn cần mỗi trường trong một cột mới trong trang tính. Thay vào đó, trang tính cụ thể này có dữ liệu đi xuống cột A.

Quá trình sửa chữa một nhãn duy nhất là khá tẻ nhạt.

Bắt đầu với con trỏ ô trên tên trong cột A.
Nhấn vào mũi tên xuống để chuyển đến địa chỉ
Ctrl + x để cắt
Mũi tên lên, mũi tên phải để chuyển sang cột B bên cạnh tên
Ctrl + v để dán
Mũi tên xuống hai lần, mũi tên trái một lần để di chuyển đến thành phố
Ctrl + x để cắt
Mũi tên lên hai lần, dấu ba mũi tên phải
Ctrl + v để dán
Mũi tên trái hai lần, mũi tên xuống một lần để di chuyển đến hàng trống hiện tại.
Giữ phím shift trong khi bạn nhấn vào mũi tên xuống hai lần
Alt + edr để xóa các hàng trống
Mũi tên lên và mũi tên xuống để chỉ chọn lại tên.

Đây là hình ảnh động hiển thị các bước sau:

Thiết lập Excel để Cho phép Macro

Mặc dù mọi bản sao của Excel đều chứa VBA, theo mặc định, Microsoft sẽ tắt khả năng chạy macro. Bạn cần thực hiện điều chỉnh một lần để cho phép các macro chạy. Mở Excel. Từ menu, chọn Công cụ> Macro> Bảo mật. Nếu mức bảo mật macro của bạn hiện được đặt thành cao, hãy thay đổi nó thành Trung bình.

Chuẩn bị ghi Macro

Suy nghĩ về các bước cần thiết để đạt được nhiệm vụ. Bạn muốn đảm bảo rằng bạn bắt đầu bằng con trỏ ô trên một tên và kết thúc bằng nó ở tên tiếp theo. Điều này sẽ cho phép bạn chạy macro lặp lại nhiều lần. Khi bạn đã chuẩn bị xong các bước, hãy bật trình ghi macro. Từ menu, chọn Công cụ> Macro> Ghi Macro Mới.

Nếu bạn sẽ chạy macro này trong nhiều ngày, bạn nên đặt tên hữu ích cho macro. Nếu đó là macro một lần để tự động hóa tác vụ một lần, thì việc để tên Macro là Macro1 có lẽ là ổn. Trường quan trọng ở đây là trường phím tắt. Nếu bạn đang tạo macro để sử dụng một lần, hãy gán macro cho một phím tắt như Ctrl + a - khá dễ dàng để nhấn Ctrl + a liên tục vì các phím gần nhau. Nếu bạn định tạo một macro mà bạn sẽ sử dụng hàng ngày, thì bạn muốn gán macro cho một phím chưa phải là tổ hợp phím tắt quan trọng. Ctrl + j hoặc Ctrl + k là những lựa chọn tốt.

Các macro sử dụng một lần nên được lưu trữ trong ThisWorkbook. Nếu bạn cần sử dụng macro nhiều lần trên nhiều sổ làm việc khác nhau, thì bạn có thể lưu trữ macro trong Sổ làm việc Macro Cá nhân.

Bây giờ bạn được giới thiệu với thanh công cụ nhỏ nhất trong tất cả Excel; Thanh công cụ Dừng ghi. Nó chỉ có hai biểu tượng và trông như thế này:

Nếu thanh công cụ này cản trở bạn, đừng nhấn X để đóng nó. Thay vào đó, hãy lấy thanh màu xanh lam và kéo thanh công cụ đến vị trí mới. Hành động di chuyển thanh công cụ không được ghi lại trong macro. Nếu bạn vô tình đóng thanh công cụ, hãy khôi phục thanh công cụ đó bằng View> Toolbars> Stop Recording. Hành động này, tuy nhiên, sẽ được ghi lại.

Nút đầu tiên trên thanh công cụ là nút "Dừng ghi". Điều này là tự giải thích. Khi bạn ghi xong macro, hãy bấm thanh công cụ dừng ghi.

Nút quan trọng hơn (và hiếm khi được hiểu) là nút "Tham chiếu tương đối".

Trong ví dụ hiện tại của chúng tôi, bạn phải nhấn nút Tham chiếu tương đối trước khi bắt đầu. Nếu bạn ghi macro có bật Tham chiếu tương đối, Excel sẽ ghi lại các bước như sau:

Di chuyển một ô xuống
Cắt ô hiện tại
Di chuyển một ô lên trên
Di chuyển sang phải một ô
Dán
Vân vân.

Nếu bạn muốn ghi lại macro mà không có tham chiếu tương đối, thì macro sẽ ghi lại các bước sau:

Di chuyển đến ô A4
Cắt ô A4
Di chuyển đến ô A3
Di chuyển đến ô B3
Dán vào ô B3
Vân vân.

Điều này sẽ rất sai lầm - chúng ta cần macro hoạt động trên các ô cách điểm bắt đầu của macro là 1 và 2. Khi bạn chạy đi chạy lại macro, điểm bắt đầu sẽ là hàng 4, hàng 5, hàng 6, v.v. Ghi macro mà không bật tham chiếu tương đối sẽ có macro luôn chạy trên hàng 3 làm điểm bắt đầu.

Làm theo các bước sau:

Chọn Nút Tham chiếu Tương đối trên Thanh công cụ Dừng Ghi
Con trỏ ô phải có tên trong cột A.
Nhấn vào mũi tên xuống để chuyển đến địa chỉ
Ctrl + x để cắt
Mũi tên lên, mũi tên phải để chuyển sang cột B bên cạnh tên
Ctrl + v để dán
Mũi tên xuống hai lần, mũi tên trái một lần để di chuyển đến thành phố
Ctrl + x để cắt
Mũi tên lên hai lần, dấu ba mũi tên phải
Ctrl + v để dán
Mũi tên trái hai lần, mũi tên xuống một lần để di chuyển đến hàng trống hiện tại.
Giữ phím shift trong khi bạn nhấn vào mũi tên xuống hai lần
Alt + edr để xóa các hàng trống
Mũi tên lên và mũi tên xuống để chọn tên tiếp theo trong danh sách.
Nhấn vào thanh công cụ Dừng ghi
Lưu sổ làm việc
Kiểm tra macro bằng cách nhấn phím tắt được chỉ định ở trên. Nó sẽ sửa chữa hoàn hảo tên tiếp theo trong danh sách

Khi bạn thấy macro hoạt động như mong muốn, bạn có thể giữ Ctrl + a để sửa nhanh một vài tên mỗi giây. Toàn bộ danh sách 500 tên có thể được sửa trong một hoặc hai phút.

Mẹo thưởng - Không có trong chương trình

Bạn có thể dễ dàng quấn macro trong một vòng lặp đơn giản để nó sửa tất cả 500 tên mà không cần phải nhấn Ctrl + vài trăm lần.

Nhấn phím Alt + F11 để mở trình chỉnh sửa macro.

Nếu bạn không thấy ngăn Dự án - VBAProject, hãy nhấn Ctrl + r.

Nhấp chuột phải vào Module1 và chọn Mã Chế độ xem. Bạn sẽ thấy mã giống như sau:

Bây giờ, bạn không cần phải hiểu mã này làm gì, nhưng bạn biết rằng chúng tôi muốn chạy mọi thứ trong macro đó một lần cho mọi tên mà chúng tôi có. Nếu bạn biết rằng có 462 tên, thì bạn có thể thêm 2 dòng để chạy mã 462 lần. Ở đầu macro, hãy chèn một dòng mới có các từ " For i = 1 to 462". Ở cuối macro, hãy chèn một dòng có nội dung " Next i". Điều này yêu cầu VBA chạy mã bên trong 462 lần.

Phần kết luận

Sau macro đơn giản này, tôi đã đưa ra một ví dụ về một macro rất phức tạp. Macro này đã tạo các bảng và biểu đồ tổng hợp cho 46 phòng ban của một công ty. Báo cáo này từng mất 40 giờ mỗi tháng. Macro VBA hiện chạy và tạo tất cả 46 báo cáo trong vòng chưa đầy 2 phút.

Cuốn sách VBA và Macro Microsoft Excel 2016 sẽ hướng dẫn bạn lộ trình học tập để bạn có thể bắt đầu từ việc ghi lại các macro đơn giản như thế này đến việc chạy các báo cáo rất phức tạp có thể giúp bạn tiết kiệm hàng trăm giờ mỗi năm. Tất nhiên, nếu bạn không muốn học VBA, hãy thuê Chuyên gia tư vấn Excel để thiết kế báo cáo cho bạn.