Ghi sửa đổi Chạy Excel Macro - Mẹo Excel

Đây là mẹo Excel thứ 19 hàng tuần tại.com. Nhiều thủ thuật Excel liên quan đến một số loại thủ thuật macro. Tuần này, đối với những người dùng Excel chưa bao giờ viết macro, tôi cung cấp một tài liệu sơ lược về cách ghi và sau đó tùy chỉnh macro Excel hữu ích.

Dữ liệu địa chỉ mẫu

Giả sử bạn có 400 hàng dữ liệu địa chỉ như được hiển thị trong hình trên cùng bên trái. Trường tên nằm trong cột A, địa chỉ đường phố ở cột B và thành phố ở cột C.

Mục tiêu của bạn là chuyển đổi dữ liệu thành một cột giống như trong hình thứ hai.

Bài toán đơn giản này sẽ được sử dụng để minh họa cách ghi, sửa đổi và sau đó chạy một macro đơn giản.

Đối với người dùng Excel 95: Sau khi ghi macro, Excel sẽ đặt macro của bạn trên một trang tính có tên là Module1 trong sổ làm việc của bạn. Bạn chỉ cần nhấp vào trang tính để truy cập macro.

Mặc dù có 400 bản ghi trong trang tính này, tôi muốn ghi lại một chút macro chỉ quan tâm đến địa chỉ đầu tiên. Macro sẽ giả định rằng điểm kiểm tra ô nằm trên tên đầu tiên. Nó sẽ chèn ba hàng trống. Nó sẽ sao chép ô ở bên phải của ô gốc sang ô bên dưới ô gốc. Nó sẽ sao chép ô thành phố vào ô 2 hàng dưới ô gốc. Sau đó, nó sẽ di chuyển con trỏ ô xuống để nó nằm trên tên tiếp theo.

Điều quan trọng là phải nghĩ ra quá trình này trước khi bạn ghi lại. Bạn không muốn mắc nhiều lỗi khi ghi macro.

Vì vậy, hãy đặt con trỏ ô của bạn vào ô A1. Chuyển đến menu và chọn Công cụ> Macro> Ghi Macro mới. Hộp thoại Record Macro gợi ý tên của Macro1. Điều này là tốt, vì vậy hãy nhấn OK.

Trình ghi macro Excel có một cài đặt mặc định rất ngu ngốc mà bạn hoàn toàn phải thay đổi để macro này hoạt động. Trong Excel 95, đi tới Công cụ> Macro> Sử dụng Tham chiếu Tương đối Trong Excel 97-2003, bấm vào biểu tượng thứ hai trên Thanh công cụ Dừng Ghi. Biểu tượng trông giống như một trang tính nhỏ. Một ô màu đỏ ở C3 chỉ đến ô màu đỏ khác ở A3. Biểu tượng được gọi là Tham chiếu tương đối. Khi biểu tượng này 'bật', có một số màu xung quanh biểu tượng. Biểu tượng ghi nhớ cài đặt cuối cùng từ phiên Excel hiện tại, vì vậy bạn có thể phải nhấp vào biểu tượng đó một vài lần để tìm ra phương pháp nào được bật hay không. Trong Excel 2007, sử dụng Dạng xem - Macro - Sử dụng Tham chiếu Tương đối.

OK, chúng tôi đã sẵn sàng để đi. Làm theo các bước sau:

Nhấn mũi tên xuống một lần để di chuyển đến ô B1.
Giữ phím shift và nhấn mũi tên xuống hai lần để chọn các hàng 2, 3 và 4
Từ menu, chọn Chèn, sau đó chọn Hàng để chèn ba hàng trống.
Nhấn vào mũi tên lên rồi đến mũi tên phải để di chuyển đến ô B2.
Nhấn Ctrl X để cắt ô B2.
Nhấn vào mũi tên xuống, mũi tên trái, sau đó nhấn Ctrl V để dán vào ô A2.
Nhấn vào mũi tên lên, mũi tên phải, mũi tên phải, Ctrl X, mũi tên trái, mũi tên trái, mũi tên xuống, mũi tên xuống, Ctrl V để di chuyển C1 sang A3.
Nhấn vào mũi tên xuống hai lần để con trỏ ô hiện ở tên tiếp theo trong Hàng A5.
Nhấp vào biểu tượng "Dừng ghi" trên thanh công cụ để dừng ghi macro.

Vâng, bạn đã ghi lại macro đầu tiên của mình. Chúng ta hãy xem xét. Đi tới Công cụ> Macro> Macro. Từ danh sách, đánh dấu Macro1 và nhấn nút Chỉnh sửa. Bạn sẽ thấy một cái gì đó trông giống như thế này.

Sub Macro1() ' ' Macro1 Macro ' Macro recorded 4/18/99 by Reader ' ' ActiveCell.Offset(1, 0).Range("A1:A3").Select Selection.EntireRow.Insert ActiveCell.Offset(-1, 1).Range("A1").Select Selection.Cut ActiveCell.Offset(1, -1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 2).Range("A1").Select Selection.Cut ActiveCell.Offset(2, -2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(2, 0).Range("A1").Select End Sub

Này, nếu bạn không phải là một lập trình viên, điều đó có thể trông khá đáng sợ. Đừng để nó như vậy. Nếu có điều gì đó bạn không hiểu, sẽ có sự trợ giúp tuyệt vời. Nhấp vào con trỏ của bạn ở đâu đó trong từ khóa Offset và nhấn F1. Với điều kiện bạn đã cài đặt tệp trợ giúp VBA, bạn sẽ thấy chủ đề trợ giúp cho từ khóa Offset. Trợ giúp cho bạn biết cú pháp của câu lệnh. Nó nói rằng nó là Offset (RowOffset, ColumnOffset). Vẫn chưa rõ ràng lắm? Tìm từ "ví dụ" được gạch chân màu xanh lá cây ở gần đầu trợ giúp. Các ví dụ VBA của Excel sẽ cho phép bạn tìm hiểu những gì đang xảy ra. Trong ví dụ về Offset, nó cho biết để kích hoạt ô hai hàng bên dưới và ba hàng bên phải ô hiện tại, bạn sẽ sử dụng:

ActiveCell.Offset(3, 2).Activate

OK, đó là một manh mối. Hàm offset là một cách di chuyển xung quanh bảng tính Excel. Với một chút thông tin này, bạn có thể thấy macro đang làm gì. Phần bù đầu tiên (1, 0) là nơi chúng tôi đã di chuyển điểm di động xuống A2. Khoảng cách tiếp theo là nơi chúng tôi di chuyển lên một hàng (-1 hàng) và hơn 1 cột. Bạn có thể không hiểu bất kỳ điều gì khác trong macro, nhưng nó vẫn hữu ích.

Quay lại trang tính Excel. Đặt con trỏ ô của bạn vào ô A5. Chọn Công cụ> Macro> Macro> Macro1> Chạy. Macro chạy và địa chỉ thứ hai của bạn được định dạng.

Bạn có thể nói rằng việc chọn toàn bộ chuỗi lệnh lớn dài này khó hơn là chỉ định dạng bằng tay. OK, sau đó thực hiện Công cụ> Macro> Macro> Tùy chọn. Trong hộp phím tắt, giả sử Ctrl + w là phím tắt cho macro này. Bấm OK, sau đó loại bỏ hộp thoại Macro với Hủy. Bây giờ, khi bạn nhấn Ctrl w, macro sẽ chạy. Bạn có thể định dạng một địa chỉ bằng một lần gõ phím.

Bạn đã sẵn sàng cho thời gian lớn? Bạn còn lại bao nhiêu địa chỉ? Tôi nhấn Ctrl wa vài lần, vì vậy tôi còn lại 395. Quay lại macro của bạn. Chúng tôi sẽ đặt toàn bộ mã macro trong một vòng lặp. Chèn một dòng mới có nội dung "Làm cho đến khi activecell.value =" "" trước dòng đầu tiên của mã macro. Chèn một dòng có nội dung "Vòng lặp" trước dòng Kết thúc phụ. Vòng lặp Do sẽ thực thi mọi thứ giữa Do và dòng Loop cho đến khi nó chạy vào một dòng trống. Macro bây giờ trông như thế này:

Sub Macro1() ' ' Macro1 Macro ' Macro recorded 4/18/99 by Reader ' ' Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Range("A1:A3").Select Selection.EntireRow.Insert ActiveCell.Offset(-1, 1).Range("A1").Select Selection.Cut ActiveCell.Offset(1, -1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 2).Range("A1").Select Selection.Cut ActiveCell.Offset(2, -2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(2, 0).Range("A1").Select Loop End Sub

Quay lại trang tính Excel của bạn. Đặt con trỏ ô vào tên tiếp theo. Nhấn Ctrl w và macro sẽ định dạng tất cả các bản ghi của bạn trong vài giây.

Các tác giả của sách Excel nói rằng bạn không thể làm bất cứ điều gì hữu ích bằng cách ghi macro. Không đúng! Đối với những người sẽ phải cắt và dán 800 lần, macro này rất hữu ích. Phải mất vài phút để ghi lại và tùy chỉnh. Đúng vậy, các lập trình viên chuyên nghiệp sẽ chỉ ra rằng mã kém hiệu quả một cách khủng khiếp. Excel đặt cả đống thứ vào đó mà nó không cần phải đưa vào đó. Có, nếu bạn biết mình đang làm gì, bạn có thể hoàn thành nhiệm vụ tương tự với một nửa số dòng sẽ chạy trong 1,2 giây thay vì 3 giây. VẬY THÌ SAO? 3 giây nhanh hơn nhiều so với 30 phút mà nhiệm vụ sẽ thực hiện.

Một số mẹo khác để bắt đầu ghi macro:

Dấu nháy đơn được sử dụng để chỉ ra một nhận xét. Mọi thứ sau dấu nháy đơn đều bị VBA bỏ qua
Đây là lập trình hướng đối tượng. Cú pháp cơ bản là object.action. Nếu một trình biên dịch hướng đối tượng đang chơi bóng đá, nó sẽ nói "ball.kick" để đá bóng. Vì vậy, "Selection.Cut" cho biết thực hiện "chỉnh sửa> cắt" trên lựa chọn hiện tại.
Trong ví dụ trên, các công cụ sửa đổi Phạm vi có liên quan đến ô hiện hoạt. Nếu ô hiện hoạt nằm trong B2 và bạn nói "ActiveCell.Range (" A1: C3 "). Chọn", thì bạn chọn khu vực 3 hàng x 3 cột bắt đầu từ ô B2. Nói cách khác, bạn chọn B2: D4. Nói "ActiveCell.Range (" A1 ")" nói để chọn phạm vi ô 1 x 1 bắt đầu bằng ô hiện hoạt. Điều này là vô cùng thừa. Nó tương đương với việc nói "ActiveCell.Select".
Lưu sổ làm việc của bạn trước khi chạy macro lần đầu tiên. Bằng cách này, nếu nó có lỗi và xảy ra điều gì đó không mong muốn, bạn có thể đóng mà không cần lưu và hoàn nguyên về phiên bản đã lưu.

Hy vọng rằng ví dụ đơn giản này sẽ cung cấp cho bạn những người mới làm quen với trình ghi macro can đảm để ghi lại một macro đơn giản vào lần tiếp theo bạn có nhiệm vụ định kỳ cần thực hiện trong Excel.