Các macro vba excel làm sẵn thú vị. Làm việc hiệu quả trong MS Office. Tạo Macro trong Excel bằng Trình ghi Macro

Kế tiếp ví dụ đơn giản Macro Excel minh họa một số tính năng và kỹ thuật được mô tả trong hướng dẫn Excel VBA.

Macro Excel: Ví dụ 1

Ban đầu thủ tục này phụđược đưa ra làm ví dụ về việc sử dụng nhận xét trong mã VBA. Tuy nhiên, tại đây bạn cũng có thể xem cách khai báo các biến, cách hoạt động của tham chiếu ô trong Excel và cách sử dụng vòng lặp. Vì, điều hành có điều kiện Nếu như và hiển thị một cửa sổ tin nhắn.

"Thủ tục Sub tìm kiếm một ô chứa chuỗi được chỉ định "trong phạm vi ô A1:A100 của trang tính hiện hoạt Sub Find_String(sFindText As String) Dim i As Integer "Một số nguyên kiểu Integer, được sử dụng trong vòng lặp For Dim iRowNumber As Integer "Một số nguyên kiểu Integer để lưu kết quả iRowNumber = 0 "Xem qua từng ô A1:A100 cho đến khi tìm thấy chuỗi sFindText For i = 1 To 100 If Cells(i, 1).Value = sFindText Then " Nếu tìm thấy kết quả khớp với chuỗi đã chỉ định " hãy lưu số dòng hiện tại và chúng tôi rời đi từ Vòng lặp For iRowNumber = i Thoát để kết thúc nếu tiếp theo i "Chúng tôi thông báo cho người dùng trong cửa sổ bật lên xem hàng được yêu cầu có được tìm thấy hay không" Nếu chuỗi đã chođã tìm thấy, cho biết kết quả khớp được tìm thấy ở ô nào Nếu iRowNumber = 0 Then MsgBox "Không tìm thấy hàng " & sFindText & "" Khác MsgBox "Đã tìm thấy hàng " & sFindText & " trong ô A" & iRowNumber End If End Sub

Macro Excel: Ví dụ 2

Thủ tục tiếp theo phụ– ví dụ về việc sử dụng vòng lặp Làm trong khi. Bạn cũng có thể xem cách khai báo các biến, làm việc với các tham chiếu ô Excel và sử dụng câu lệnh có điều kiện. Nếu như.

"Thủ tục Sub xuất ra các số Fibonacci không vượt quá 1000 Sub Fibonacci() Dim i As Integer "Bộ đếm để chỉ vị trí của một phần tử trong chuỗi Dim iFib As Integer "Lưu trữ giá trị hiện tại của chuỗi Dim iFib_Next As Integer "Lưu trữ giá trị hiện tại của chuỗi Dim iFib_Next As Integer giá trị tiếp theo của chuỗi Dim iStep As Integer "Lưu trữ kích thước của số gia tiếp theo" Khởi tạo các biến i và iFib_Next i = 1 iFib_Next = 0 "Vòng lặp Do While sẽ được thực thi cho đến khi giá trị của" số Fibonacci hiện tại vượt quá 1000 Do While iFib_Tiếp theo< 1000 If i = 1 Then "Một trường hợp đặc biệtđối với phần tử đầu tiên của chuỗi iStep = 1 iFib = 0 Khác "Lưu kích thước của bước tăng tiếp theo trước khi ghi đè" giá trị hiện tại của chuỗi iStep = iFib iFib = iFib_Next End Nếu "Xuất số Fibonacci hiện tại vào cột A của bảng tính hoạt động "trong hàng có chỉ mục i Cells(i, 1).Value = iFib "Tính số Fibonacci tiếp theo và tăng chỉ số vị trí của phần tử lên 1 iFib_Next = iFib + iStep i = i + 1 Loop End Sub

Macro Excel: Ví dụ 3

Thủ tục này phụ quét các ô của một cột MỘT trang tính đang hoạt động cho đến khi gặp một ô trống. Các giá trị được ghi vào một mảng. Macro Excel đơn giản này cho thấy cách làm việc với mảng động và cũng sử dụng một vòng lặp Làm cho đến khi. TRONG trong ví dụ này chúng ta sẽ không thực hiện bất kỳ hành động nào với mảng, mặc dù trong thực tế lập trình thực tế, sau khi dữ liệu được ghi vào mảng, những hành động như vậy thường được thực hiện trên chúng.

"Thủ tục Sub lưu trữ các giá trị ô của cột A của trang tính đang hoạt động trong mảng Sub GetCellValues() Dim iRow As Integer "Lưu trữ số của hàng hiện tại Dim dCellValues() As Double "Một mảng để lưu trữ các giá trị ô ​​iRow = 1 ReDim dCellValues(1 To 10) "Thực hiện Vòng lặp cho đến khi lặp tuần tự qua các ô của cột A của trang tính đang hoạt động" và trích xuất các giá trị của chúng thành một mảng cho đến khi gặp một ô trống Thực hiện cho đến khi IsEmpty(Cells( iRow, 1)) "Kiểm tra xem mảng dCellValues ​​​​có đủ kích thước không" Nếu không, hãy tăng kích thước mảng lên 10 bằng cách sử dụng ReDim If UBound(dCellValues)< iRow Then ReDim Preserve dCellValues(1 To iRow + 9) End If "Сохраняем значение текущей ячейки в массиве dCellValues dCellValues(iRow) = Cells(iRow, 1).Value iRow = iRow + 1 Loop End Sub

Macro Excel: Ví dụ 4

Trong ví dụ này, thủ tục phụđọc giá trị từ một cột MỘT bảng tính Trang 2 và làm điều đó với họ các phép tính toán học. Kết quả được nhập vào các ô cột MỘT trên bảng tính đang hoạt động. Macro này thể hiện việc sử dụng các đối tượng Excel. Đặc biệt, việc kháng cáo được thực hiện theo thủ tục phụđến đối tượng Cột và hiển thị cách đối tượng này được truy cập thông qua đối tượng Bảng tính. Nó cũng cho thấy rằng khi truy cập một ô hoặc một phạm vi ô trên trang tính đang hoạt động, không cần thiết phải chỉ định tên của trang tính này khi viết liên kết.

"Thủ tục Sub, sử dụng vòng lặp, đọc các giá trị trong cột A của bảng tính Sheet2," thực hiện các phép tính số học với từng giá trị và ghi kết quả vào "cột A của bảng tính đang hoạt động (Trang tính 1) Sub Transfer_ColA() Dim i As Integer Dim Col As Range Dim dVal As Double "Gán cho biến Col cột A của bảng tính Sheet 2 Đặt Col = Sheets("Sheet2").Columns("A") i = 1 "Sử dụng vòng lặp, chúng ta đọc các giá trị ​​của các ô của cột Col cho đến khi "cho đến khi gặp ô trống Thực hiện Until IsEmpty(Col.Cells(i)) "Thực hiện các phép tính số học trên giá trị của ô hiện tại dVal = Col.Cells(i).Value * 3 - 1" Lệnh tiếp theo ghi kết quả vào cột A của bảng tính đang hoạt động "Không cần chỉ ra tên trang tính trong liên kết, vì đây là trang tính đang hoạt động. Cells(i, 1) = dVal i = i + 1 Loop End Sub

Macro Excel: Ví dụ 5

Macro này hiển thị một ví dụ về mã VBA theo dõi sự kiện Excel. Sự kiện mà macro được đính kèm xảy ra mỗi khi một ô hoặc phạm vi ô được chọn trên trang tính. Trong trường hợp của chúng tôi, khi chọn một ô B1, trên màn hình xuất hiện cửa sổ thông báo.

"Mã này hiển thị hộp thông báo nếu ô B1 được chọn trên bảng tính hiện tại. Bảng tính phụ riêng tư_SelectionChange(ByVal Target As Range) "Kiểm tra xem ô B1 có được chọn hay không Nếu Target.Count = 1 Và Target.Row = 1 Và Target.Column = 2 Sau đó "Nếu ô B1 được chọn, hãy thực hiện hành động được yêu cầu MsgBox "Bạn đã chọn ô B1" End If End Sub

Macro Excel: Ví dụ 6

Thủ tục này minh họa việc sử dụng các toán tử Có lỗi Và Bản tóm tắtđể xử lý lỗi. Mã này cũng hiển thị một ví dụ về mở và đọc dữ liệu từ một tệp.

"Thủ tục Sub gán các đối số Val1 và Val2 các giá trị của ô A1 và B1" từ sổ làm việc Data.xlsx nằm trong thư mục C:\Documents and Setting Sub Set_Values(Val1 As Double, Val2 As Double) Dim DataWorkbook As Sổ làm việc có lỗi GoTo ErrorHandling " Đang mở sách bài tập với dữ liệu Đặt DataWorkbook = Workbooks.Open("C:\Documents and Setting\Data") "Gán các biến giá trị Val1 và Val2 ​​từ sổ làm việc đã cho Val1 = Sheets("Sheet1").Cells(1, 1) Val2 = Sheets( "Sheet1").Cells(1, 2) DataWorkbook.Close Exit Sub ErrorHandling: "Nếu không tìm thấy tệp, người dùng sẽ được nhắc đặt tệp họ đang tìm" vào thư mục mong muốn và sau đó tiếp tục thực thi macro MsgBox "Không tìm thấy tệp Data.xlsx!" & _ "Vui lòng thêm sổ làm việc vào thư mục C:\Documents and Cài đặt và nhấp vào OK" Tiếp tục End Sub

Hướng dẫn sử dụng chứa macro về các chủ đề sau:
Chạy macro để tìm kiếm ô.
Chạy macro khi mở sổ làm việc.
Chạy macro khi bạn nhập “2” vào ô.
Chạy macro khi bạn nhấn Enter.
Thêm tab “Phần bổ trợ” (Định dạng ô) của riêng bạn vào bảng điều khiển.
Làm việc với các tệp (tức là trao đổi dữ liệu với TXT, RTF, XLS, v.v.).
Kiểm tra sự hiện diện của tệp tại đường dẫn đã chỉ định.
Tìm kiếm tập tin cần thiết.
Tự động xóa tập tin.
Văn bản miễn phí trên thanh trạng thái.
Khôi phục thanh trạng thái.
Dòng leo trên thanh trạng thái.

Nhanh chóng thay đổi tiêu đề cửa sổ.
Thay đổi tiêu đề cửa sổ (ẩn tên file).
Trở lại tiêu đề ban đầu.
Những gì đang mở vào lúc này.
Làm việc với các tập tin văn bản.
Viết và đọc tập tin văn bản.
Xử lý nhiều tập tin văn bản.
Xác định cuối dòng trong tệp văn bản.
Sao chép từ tệp văn bản sang Excel.

Sao chép nội dung vào một tập tin văn bản.
Xuất dữ liệu sang txt.
Xuất dữ liệu sang html.
Nhập dữ liệu yêu cầu nhiều hơn 256 cột.
Sự sáng tạo bản sao lưu các tập tin có giá trị.
Đếm số lần một file được mở.
Xuất đường dẫn tệp đến ô hiện hoạt.
Sao chép nội dung tập tin RTF trong Excel.
Sao chép dữ liệu từ một sổ làm việc đã đóng.
Trích xuất dữ liệu từ một tập tin đã đóng.
Tìm kiếm một từ trong tập tin.
Tạo một tệp văn bản và nhập văn bản vào tệp.
Tạo một tệp văn bản và nhập văn bản (kết thúc phát hiện tệp).
Sự sáng tạo Tài liệu Word dựa trên bảng excel.
Các lệnh tạo và xóa thư mục.
Lấy thư mục hiện tại.
Thay đổi thư mục.

Xem tất cả các tập tin trong một thư mục.
Xem tất cả các tập tin trong một thư mục.
Đang làm việc Khu vực Microsoft Excel.
Sách bài tập.
Số lượng tên sổ làm việc.
Bảo vệ sổ làm việc.
Cấm in sách.
Mở một cuốn sách (hoặc tập tin văn bản).
Mở sách và thêm văn bản vào ô A1.
Có bao nhiêu cuốn sách được mở?
Đóng tất cả các cuốn sách.
Chỉ đóng một sổ làm việc khi một điều kiện được đáp ứng.
Lưu sổ làm việc với tên đại diện cho ngày hiện tại.
Sổ làm việc có được lưu không?
Tạo một sổ làm việc với một trang tính.
Tạo một cuốn sách.
Loại bỏ những tên không cần thiết.
Sao chép nhanh một sổ làm việc.
Sắp xếp tờ.
Tìm giá trị lớn nhất trên tất cả các trang tính của sổ làm việc.
Bảng tính.
Kiểm tra xem bảng tính có được bảo vệ hay không
Danh sách các tờ được sắp xếp.
Tạo nên lá mới.
Tạo một trang tính mới.
Loại bỏ các tờ dựa trên ngày.
Sao chép một tờ trong một cuốn sách.
Sao chép một trang tính vào sách mới(tạo).
Di chuyển một tờ trong một cuốn sách.
Di chuyển nhiều trang tính sang một sổ làm việc mới.
Thay thế một tập tin hiện có.
"Lật" cuốn sách.
Chèn đầu trang và chân trang với tên của sổ làm việc, trang tính và ngày hiện tại.
Trang tính có tồn tại không?
Trang tính có tồn tại không?
Hiển thị số lượng trang tính trong sổ làm việc đang hoạt động.
Hiển thị số trang tính trong sổ làm việc đang hoạt động dưới dạng siêu liên kết.
Hiển thị lần lượt tên các sheet đang hoạt động.
Hiển thị tên và số trang của cuốn sách hiện tại.
Làm cho tờ giấy trở nên vô hình.
Có bao nhiêu trang trên tất cả các tờ?
Ô và phạm vi (cột và hàng).
Sao chép hàng sang trang tính khác.
Sao chép các cột sang một trang tính khác.
Đếm số ô chứa các giá trị được chỉ định.
Đếm số ô trong một phạm vi chứa các giá trị được chỉ định.
Đếm số lượng ô hiển thị trong một phạm vi.
Xác định số lượng ô trong một phạm vi và tổng giá trị của chúng.
Đếm số lượng tế bào.
Tự động tính toán lại dữ liệu bảng khi giá trị của nó thay đổi.
Nhập dữ liệu vào ô.
Nhập dữ liệu bằng công thức.
Đầu vào dữ liệu nối tiếp.
Nhập dữ liệu văn bản vào ô.
Hiển thị tên sách, sheet và số sheet trong ô.
Loại bỏ các dòng trống.
Loại bỏ các dòng trống.
Loại bỏ các dòng trống.
Xóa một hàng theo điều kiện.
Gỡ bỏ hàng ẩn.
Xóa các hàng bị ẩn hoặc có chiều cao bằng 0 đã sử dụng.
Loại bỏ các bản sao bằng cách sử dụng mặt nạ.

Chọn một phạm vi phía trên ô hiện tại.
Chọn một ô và đặt một số ở đó.
Làm nổi bật các giá trị âm.
Chọn một phạm vi và sử dụng địa chỉ tuyệt đối.

Chọn các ô theo khoảng thời gian.
Lựa chọn nhiều phạm vi.
Di chuyển qua các tế bào.
Tìm ô trống gần nhất trong một cột.
Tìm giá trị lớn nhất.
Tìm kiếm và thay thế theo mẫu.
Tìm kiếm một giá trị và hiển thị kết quả trong một cửa sổ riêng.

Tìm kiếm với việc đánh dấu dữ liệu tìm thấy.
Tìm kiếm theo điều kiện trong một phạm vi.
Tìm ô không trống cuối cùng trong một phạm vi.
Tìm ô không trống cuối cùng trong một cột.
Tìm ô không trống cuối cùng trong một hàng.
Tìm kiếm ô có màu xanh trong phạm vi.
Tìm giá trị âm trong một phạm vi và đánh dấu nó bằng màu xanh lam.
Tìm sự hiện diện của một giá trị trong một cột.
Tìm kết quả phù hợp trong một phạm vi.
Tìm kiếm một ô trong một phạm vi.
Tìm kiếm một ô trong một phạm vi.
Tìm giá trị gần đúng trong một phạm vi.
Tìm phần đầu và phần cuối của một phạm vi chứa dữ liệu.
Tìm phần đầu của dữ liệu.
Tự động thay thế các giá trị
Điền nhanh một phạm vi (mảng).
Điền thông qua khoảng (mảng).
Điền vào phạm vi được chỉ định (mảng).
Điền vào phạm vi (mảng).
Tính tổng các giá trị đầu tiên của phạm vi.
Vị trí trong một tế bào của một chiếc đồng hồ điện tử.
"Báo thức".
Thiết kế mặt trên và giới hạn dưới phạm vi.
Địa chỉ ô đang hoạt động.
Tọa độ của ô đang hoạt động.
Công thức tế bào hoạt động
Lấy công thức từ một ô.
Kiểu dữ liệu di động.
Xuất ra địa chỉ của phần cuối của phạm vi.
Lấy thông tin về phạm vi đã chọn.
Lấy từ có ký tự thứ 13 trong ô.
Tạo một danh sách có thể chỉnh sửa (bảng).
Kiểm tra giá trị trống.
Giao điểm ô.
Nhân phạm vi đã chọn với.
Đồng thời nhân tất cả dữ liệu trong một phạm vi.
Chia phạm vi thành.
Bình phương từng ô trong phạm vi.
Chỉ tóm tắt dữ liệu từ các ô hiển thị.
Tổng các ô có giá trị số.
Khi tính tổng, con trỏ nằm trong phạm vi.

Lãi suất tích lũy tùy theo số tiền.
Lãi suất tích lũy tùy theo số tiền.
Ví dụ tóm tắt về tính toán hoa hồng.
Chuyển động dọc theo phạm vi.
Bù trừ từ ô đã chọn.
Lặp lại qua các ô dọc theo cột.
Tạo một phạm vi điền.
Chọn một tham số ô.
Phân chia phạm vi.
Hợp nhất dữ liệu phạm vi.
Hợp nhất dữ liệu phạm vi.
Tìm ra cột hoặc hàng tối đa.
Giới hạn các giá trị phạm vi có thể.
Kiểm tra tốc độ của phạm vi đọc và viết.
Mở MsgBox khi một ô được chọn.
Ẩn một hàng.
Ẩn nhiều hàng.
Ẩn một cột.
Ẩn nhiều cột
Ẩn một hàng theo tên ô.
Ẩn nhiều hàng theo địa chỉ ô.
Ẩn một cột theo tên ô.
Ẩn nhiều cột theo địa chỉ ô.
Tế bào nhấp nháy.
Làm việc với ghi chú.
Hiển thị tất cả các ghi chú trong bảng tính.
Chức năng trích xuất bình luận.
Danh sách ghi chú cho các tờ được bảo vệ.

Danh sách ghi chú trong một danh sách riêng biệt.
Danh sách ghi chú trong một danh sách riêng biệt.

Đếm số nốt nhạc.
Đếm ghi chú.
Chọn các ô có nhận xét.
Hiển thị tất cả các ghi chú.
Thay đổi màu của ghi chú.
Thêm ghi chú.
Thêm ghi chú vào một phạm vi dựa trên điều kiện.
Di chuyển nhận xét đến một ô và quay lại.

Chuyển giá trị từ một ô sang nhận xét.
Các tab tùy chỉnh trên ribbon.
Bổ sung thanh công cụ.
Thêm một nút vào thanh công cụ.
Bảng điều khiển một nút.
Bảng điều khiển có hai nút.
Tạo một bảng điều khiển ở bên phải.

Sự sáng tạo thực đơn tùy chỉnh(lựa chọn 1).
Tạo menu tùy chỉnh (tùy chọn 2).
Tạo menu tùy chỉnh (tùy chọn 3).
Tạo menu tùy chỉnh (tùy chọn 4).
Tạo menu tùy chỉnh (tùy chọn 5).
Tạo menu tùy chỉnh (tùy chọn 6).
Tạo danh sách các mục menu chính của Excel.
Tạo danh sách các mục menu ngữ cảnh.
Hiển thị thanh công cụ theo một điều kiện nhất định.
Ẩn và hiển thị thanh công cụ.
Tạo chú giải công cụ cho các nút của tôi.
Tạo một menu dựa trên dữ liệu bảng tính.
Tạo một menu ngữ cảnh.
Chặn menu ngữ cảnh.
Thêm lệnh vào menu Công cụ.
Thêm lệnh vào menu Xem.
Tạo bảng danh sách.
Một phim hoạt hình có sự tham gia của một trợ lý.
Thêm văn bản, tiêu đề, nút và biểu tượng vào trợ lý.
Tùy chọn trợ lý mới.
Sử dụng trợ lý để chọn màu tô.
CỬA SỔ ĐỐI THOẠI.
Chức năng INPUTBOX (thông qua giá trị đầu vào).
Xem trước cuộc gọi.
Thiết lập mục nhập dữ liệu trong hộp thoại.

Mở hộp thoại (Mở tệp).
Mở hộp thoại (In).
Các hộp thoại khác.
Gọi trình duyệt từ Excel.
Hộp thoại nhập dữ liệu.
Hộp thoại cài đặt phông chữ.
Giá trị mặc định.
Định dạng văn bản. Những cái bàn. BIÊN GIỚI VÀ ĐIỀN.
Hiển thị danh sách các phông chữ có sẵn.
Chọn tất cả các số từ văn bản.
Chỉ viết hoa ở đầu văn bản.
Đếm số lần lặp lại của văn bản tìm kiếm.
Chọn một phần tử tùy ý từ văn bản.
Hiển thị văn bản ngược.
Văn bản tiếng Anh được viết hoa.
Khởi chạy bảng ký hiệu từ Excel.
thông tin về người dùng, máy tính, máy in, v.v.
Nhận tên người dùng.
Giám sát đầu ra độ phân giải.
Nhận thông tin về máy in bạn đang sử dụng.
Xem thông tin về ổ đĩa máy tính của bạn.
NGƯỜI DÙNG.
SƠ ĐỒ.
Xây dựng biểu đồ bằng macro.
Lưu sơ đồ vào một tập tin riêng biệt.
Xây dựng và xóa biểu đồ chỉ bằng một nút bấm.
Hiển thị danh sách sơ đồ trong một cửa sổ riêng.
Áp dụng bảng màu ngẫu nhiên.
Hiệu ứng trong suốt của biểu đồ.
Xây dựng biểu đồ dựa trên dữ liệu từ nhiều bảng tính.
Tạo chú thích cho dữ liệu biểu đồ.
CHƯƠNG TRÌNH KHÁC NHAU.
Chương trình soạn câu đố ô chữ.
Tạo bìa DVD.
Trò chơi "Bãi mìn".
Trò chơi "Đoán con vật".
Tính toán dựa trên các ô có màu nhất định.
CHỨC NĂNG VÀ MACRO KHÁC.
Gọi các phím chức năng.
Tính toán trung bình số học.
Biến số thành “tiền”.
Tìm kiếm thứ Hai gần nhất.
Đếm số năm trọn vẹn.
Tính toán bình quân gia quyền.
Chuyển đổi số tháng thành tên của nó.
Sử dụng liên kết tương đối
Chuyển đổi bảng Excel sang định dạng HTML.
Trình tạo số ngẫu nhiên.
Số ngẫu nhiên - dựa trên phạm vi.
Áp dụng một hàm mà không cần nhập nó vào ô.
Đếm các đối tượng được đặt tên
Bật bộ lọc tự động bằng macro.
Tạo một đường leo.
Tạo một hình ảnh đang chạy.
Xoay tự động định hình.
Gọi bảng màu.
Tạo một máy tính.
Sự suy giảm của họ, tên và bảo trợ.
NGÀY VÀ GIỜ.
Ngày và giờ đầu ra.
Ngày và giờ đầu ra.
Lấy ngày hệ thống.
Truy xuất ngày và giờ.
Chức năng DateFull. Phiên bản MS Office được sử dụng không được chỉ định.

VBA được coi là ngôn ngữ kịch bản tiêu chuẩn cho các ứng dụng của Microsoft và hiện được đưa vào tất cả Ứng dụng văn phòng và thậm chí cả các ứng dụng từ các công ty khác. Vì vậy, khi đã thành thạo VBA cho Excel, bạn có thể chuyển ngay sang tạo macro cho phần mềm khác. Sản phẩm của Microsoft. Hơn nữa, bạn sẽ có thể tạo đầy đủ sản phẩm phần mềm, đồng thời sử dụng các chức năng của nhiều ứng dụng khác nhau.

Cách bật macro trong Excel

Theo mặc định, tab chịu trách nhiệm quản lý và điều hướng macro trong Excel bị ẩn. Để kích hoạt tùy chọn nàyđi tới tab Tài liệuđến nhóm Tùy chọn. Trong hộp thoại xuất hiện Tùy chọnExcelđi tới tab Tùy chỉnh ruy băng, trong hộp tổ hợp bên phải, đặt điểm đánh dấu đối diện với tab Nhà phát triển. Những hành động này có liên quan đến Các phiên bản Excel 2010 trở lên.

Xuất hiện trên băng phần mới Nhà phát triển với các điều khiển tự động hóa Excel.

Viết macro trong Excel

Trong tab Nhà phát triển trong nhóm Mã số, nhấn vào nút Ghi lại một macro. Một hộp thoại sẽ xuất hiện Ghi macro, yêu cầu một số thông tin về mã được viết trong tương lai. Nếu đây là lần đầu tiên bạn tạo macro, bạn chỉ cần nhấp vào nút ĐƯỢC RỒI. VỚI tại thời điểm này Excel sẽ ghi lại mọi hành động của người dùng trong mô-đun VBA, có thể là nhập dữ liệu, định dạng hoặc tạo biểu đồ. Để dừng ghi macro, hãy bấm vào nút Dừng ghiđó là trong cùng một nhóm Mã số.

Bạn cũng có thể tận dụng tùy chọn thay thế để ghi macro bằng cách sử dụng nút Ghi macro, nằm ở góc dưới bên trái của sổ làm việc Excel (bên phải trạng thái Sẵn sàng).

Bây giờ bạn có thể xem danh sách tất cả các macro đã tạo bằng cách nhấp vào nút vĩ mô, nằm trong nhóm Mã số. Trong hộp thoại xuất hiện, bạn có thể đặt thêm tên mô tả cho mã của mình hoặc đặt phím tắt để chạy một macro cụ thể. Lựa chọn thay thếđể khởi chạy cửa sổ này là nhấn Phím Alt+ F8.

Chỉnh sửa macro

Chúc mừng! Bạn đã viết macro đầu tiên của mình. Sẽ là hợp lý nếu bây giờ kiểm tra xem Excel đã tạo mã nào cho chúng ta. Mã được tạo được viết bằng ngôn ngữ VBA (Ngôn ngữ lập trình cho Ứng dụng). Bạn phải mở nó ra để xem nó. Biên tập viênVB(VBE),được khởi chạy bằng cách nhấn Alt + F11 hoặc nút Thị giácNền tảng trên tab Nhà phát triển.

Để tránh nhầm lẫn trong trình chỉnh sửa, bạn chỉ có thể làm việc với một tab trong sổ làm việc, trang tính hoặc mô-đun. Đây là diện mạo của biên tập viên ngoài đời thực.

tôi đề nghị trên ở giai đoạn này Tìm hiểu thêm về các cửa sổ và menu khác nhau của trình soạn thảo VBA. Điều này sẽ giúp bạn tiết kiệm được rất nhiều thời gian sau này.

Để xem mã, hãy nhấp vào chủ đề Mô-đun trong cửa sổ dự án và nhấp đúp vào nhánh xuất hiện mô-đun1 . Trình chỉnh sửa sẽ mở một cửa sổ có mã, như trong hình.

Tại đây bạn có thể chỉnh sửa mã được tạo được viết khi làm việc trong Excel. Ví dụ: bạn cần điền vào một cột nhất định với các giá trị từ 1 đến 10. Bạn đã có ba bước đầu tiên, đó là nhập các giá trị 1, 2 và 3 vào ba ô đầu tiên của cột A. Chúng ta cần hoàn thành bảy bước còn lại.

Nếu bạn nhìn vào đoạn mã trên, bạn sẽ thấy macro theo một cách nào đó có cấu trúc. Trước tiên, ứng dụng sẽ di chuyển con trỏ đến ô bằng lệnh Range("A1").Select, sau đó chỉnh sửa nội dung của nó bằng ActiveCell.FormulaR1C1 = "1". Vì vậy đối với các bước còn lại chúng ta có thể lặp lại các bước này, thay đổi địa chỉ ô và giá trị bạn muốn ghi vào ô đó. Ví dụ: để đặt ô A4 thành 4, bạn sẽ viết:

Và lặp lại các bước tương tự cho các giá trị còn lại.

Sau khi chỉnh sửa xong, hãy lưu sách của bạn. Bạn có thể chạy macro bằng cách nhấn nút F5 hoặc bằng cách quay lại chế độ làm việc Sổ làm việc Excel, đi tới tab Nhà phát triểnđến nhóm Mã số -> Macro và chọn từ danh sách macro mà bạn quan tâm.

Hãy dành vài phút để nghiên cứu kỹ mã mà Excel đã tạo. Nếu bạn là người mới bắt đầu, việc đầu tư một vài phút vào việc học mã sẽ mang lại kết quả đáng kinh ngạc trong việc làm quen với các đối tượng VBA sau này. Xin lưu ý rằng ví dụ chúng tôi thảo luận chỉ là một minh họa. Có nhanh hơn và cách hiệu quảđạt được kết quả tương tự mà chúng ta sẽ thảo luận sau.

Tăng tốc độ thực thi macro Excel

Càng xa càng tốt. Hãy xem xét một số thủ thuật sẽ giúp tăng tốc độ thực thi macro. Hãy lấy đoạn mã trên làm ví dụ. Máy tính hiện đại sẽ xử lý mã được đề cập nhanh đến mức bạn thậm chí không nhận thấy nó. Nhưng nếu bạn cần thực hiện thao tác 50.000 lần thì sao? Điều này sẽ mất một thời gian. Nếu macro bạn viết dài hàng trăm dòng, bạn có thể tăng tốc độ thực thi mã bằng cách cắt bớt phần quy trình không được sử dụng trong quá trình thực thi macro.

Sử dụng lệnh Application.ScreenUpdating

Mẹo đầu tiên là tránh cập nhật màn hình trong khi macro đang chạy. Điều này sẽ cho phép Excel lưu khả năng tính toán máy tính và chỉ cập nhật màn hình với các giá trị mới nhất sau khi tất cả mã đã được thực thi. Để thực hiện việc này, bạn cần thêm lệnh tắt làm mới màn hình ở đầu macro và lệnh bật làm mới màn hình ở cuối macro.

Lệnh Application.ScreenUpdating yêu cầu Excel ngừng hiển thị dữ liệu được tính toán lại trên màn hình và trả về các giá trị đã hoàn thành khi kết thúc quá trình thực thi mã.

Sử dụng lệnh Ứng dụng. Phép tính

Thủ thuật thứ hai là tắt tính toán tự động. Hãy để tôi giải thích. Mỗi khi người dùng hoặc quy trình cập nhật một ô, Excel sẽ cố gắng tính toán lại tất cả các ô phụ thuộc vào nó. Vì vậy, giả sử nếu ô mà macro đang cố cập nhật ảnh hưởng đến 10.000 ô khác, Excel sẽ cố gắng tính toán lại tất cả chúng trước khi mã thực thi xong. Theo đó, nếu có toàn bộ dòngảnh hưởng đến các ô, việc tính toán lại có thể làm chậm đáng kể việc thực thi mã. Để ngăn điều này xảy ra, bạn có thể cài đặt lệnh Ứng dụng. Tính toán ở đầu mã, sẽ chuyển việc tính toán lại các công thức sang chế độ thủ công rồi trả về phép tính tự động ở cuối macro.

Hãy cẩn thận, đừng quên chuyển lại tùy chọn này thành chế độ tự độngở cuối macro. Nếu không, bạn sẽ cần thực hiện việc này trong Excel bằng cách nhấp vào tab Công thứcđến nhóm Phép tính và lựa chọn Các tùy chọn tính toán -> Tự động.

Tránh chọn ô và phạm vi

Đang ở chế độ ghi âm tự động macro, bạn có thể nhận thấy rằng Excel rất thường xuyên sử dụng lệnh chọn ô, ví dụ: Range("A1").Select. Trong ví dụ của chúng tôi, chúng tôi đã sử dụng lệnh này nhiều lần để chọn một ô và thay đổi giá trị của nó. Bạn có thể tránh điều này bằng cách chỉ định địa chỉ ô và đặt cho nó giá trị được yêu cầu (Macro đã ghi lại chuyển động của con trỏ từ ô này sang ô khác, do đó chèn các bước này. Tuy nhiên, chúng không cần thiết). Vì vậy, một mã hiệu quả hơn sẽ trông như thế này.

TRONG trong trường hợp này chúng tôi chỉ cần tham chiếu ô và cung cấp cho nó giá trị cần thiết mà không cần chọn nó. Phương pháp này nhanh hơn phương pháp trước.

Ví dụ về macro Excel

Dưới đây là một số mã VBA mẫu sẽ giúp bạn tự động hóa các tác vụ phổ biến nhất.

Nếu bạn không mạnh về lập trình nhưng đồng thời cũng cần phải thực hiện một sốmột nhiệm vụ vượt xa tính năng tiêu chuẩn và các lệnh MS Excel, bạn có thể tìm kiếm giải pháp trên Internet. Giải pháp rất có thể sẽ ở dạng mã VBA mà bạn cần sao chép và dán vào sổ làm việc của mình, sau đó bằng cách nào đó làm cho mã này phù hợp với bạn, trong bài viết này tôi sẽ cho bạn biết cách thực hiện điều này.

Chúng ta hãy xem hai ví dụ:

1. Tìm và sử dụng lệnh

Ví dụ: chúng ta cần một lệnh chèn số “1” vào các ô đã chọn. Chúng tôi khởi chạy công cụ tìm kiếm, nhập cụm từ tìm kiếm, nhận kết quả, bắt đầu duyệt, tìm mã giống như thế này:

Sub Insertion1() Dim q As Object On Error Resume Next Set q = Cells For Each q In Selection q = 1 Next q End Sub

Chúng tôi đánh dấu mã này(không đánh số dòng, bắt đầu bằng từ phụ) và hãy nhấn Ctrl+C. Đi tới sổ làm việc MS Excel của bạn và nhấn phím tắt Alt+F11, cửa sổ soạn thảo VBA sẽ mở ra:

Trong cửa sổ bên trái "Project - VBA Project", chúng tôi chọn (nhấp chuột) sổ làm việc mà chúng tôi cần chèn macro vào, ví dụ: "VBAProject (Book2)":

Trong mục menu "Chèn", chọn "Mô-đun":

Trong cửa sổ bên trái "Dự án - Dự án VBA" bạn sẽ thấy thư mục mới"Mô-đun" và trong đó một đối tượng mới "Mô-đun1":

Di chuyển con trỏ sang trường nhập “Lớn” bên phải và nhấp vào Ctrl+V, macro đã sao chép sẽ được dán vào mô-đun:

Macro trong MS Excel có thể được chèn vào những vị trí sau:

• Trong Mô-đun, họ thường chèn mã macro, mã này sẽ được khởi chạy khi người dùng nhấn nút (ví dụ như trong trường hợp của chúng tôi) hoặc mã chức năng (công thức);
• Trong Trang tính, mã macro thường được chèn vào, việc khởi chạy mã này sẽ tự động diễn ra tùy thuộc vào hành động của người dùng hoặc thay đổi dữ liệu trong trang tính (dữ liệu đã thay đổi, macro được thực thi);
• Mã macro thường được chèn vào Sổ làm việc, mã này sẽ được khởi chạy tự động tùy thuộc vào các hành động được thực hiện trên sổ làm việc (tệp). Ví dụ: macro chạy khi sổ làm việc được mở hoặc đóng hoặc khi sổ làm việc được lưu;
• Macro cũng có thể là một phần của biểu mẫu người dùng.

Thông thường, người đăng mã sẽ chỉ định vị trí cần chèn mã vào mô-đun, bảng tính hoặc sổ làm việc.

Để chèn mã vào Trang tính, hãy chọn trang tính thích hợp trong cửa sổ bên trái của trình soạn thảo VBA, nhấp đúp vào trang tính đó bằng nút chuột trái, di chuyển con trỏ đến trường nhập bên phải và dán mã.

Để chèn mã vào Sách, chọn "Sách này":

Hãy cùng luyện tập. Dán mã được đăng bên dưới vào "Sheet1".

Bảng tính phụ riêng tư_Change(Mục tiêu ByVal dưới dạng phạm vi) Khi có lỗi Chuyển đến a Nếu mục tiêu = 2 Sau đó VBA.MsgBox("Ô " & Target.Address & " = 2") Kết thúc Nếu a: Thoát Sub End Sub

Macro này hiển thị thông báo thông tin nếu bạn nhập “2” vào bất kỳ ô trang tính nào.

Quay lại bảng tính, vào “Sheet1” và nhập số “2” vào ô “A1” và nhấp vào Đi vào, sau đó bạn sẽ thấy thông báo sau:

Nếu bạn thấy thông báo này thì bạn đã làm mọi thứ đúng. Nếu không được thì bạn đã chèn sai mã ở đâu đó, hãy thử lại.

Khi dán mã, bạn cần cẩn thận nơi dán mã. Điều này có thể được thực hiện bằng cách xem những gì được viết trong tiêu đề của cửa sổ soạn thảo VBA:

2. Tìm và sử dụng hàm

Chúng tôi tìm thấy trên Internet mã VBA của một hàm đếm, ví dụ: số từ trong một ô:

Hàm công cộng Số từ trong ô(Ô dưới dạng phạm vi) Dim q As Variant Application.Volatile q = VBA.Split(Application.WorksheetFunction.Trim(Cell.Value), " ") Số từ trong ô = UBound(q) + 1 End Chức năng

Sao chép mã và nhấn tổ hợp phím Alt+F11, trình soạn thảo VBA sẽ mở:

Thêm vào mô-đun mới Dán mã đã sao chép vào sách của bạn và vào mô-đun này:

Đóng trình soạn thảo VBA và đi tới sổ làm việc của bạn. Tạo một trang tính mới (tùy chọn) trong một ô A1 nhập dòng chữ “mẹ đã rửa khung”. Chúng ta đi đến ô mà chúng ta muốn nhận kết quả (số từ), trong menu “Công thức”, nhấp vào nút “Chèn hàm”:

Trong cửa sổ "Chèn hàm" mở ra, trong trường "Danh mục", chọn "Người dùng xác định"

Trên danh sách chức năng có sẵn chọn "Số từ trong ô", nhấp vào "OK":

Nhập các đối số cần thiết và nhấp vào "OK":

Chúng tôi nhận được kết quả:

Quan trọng:

Nếu bạn không lưu sổ làm việc mà bạn đã chèn macro dưới dạng “Sổ làm việc hỗ trợ macro”, tất cả các mô-đun có macro sẽ bị xóa và bạn sẽ phải thực hiện lại tất cả công việc này.

Nếu khi chèn macro vào mô-đun, bạn thấy các dấu hiệu màu đỏ thay vì một số văn bản " ???????? "

Điều này có nghĩa là bạn gặp sự cố về mã hóa, xảy ra khi sao chép văn bản Cyrillic từ một số trình duyệt. Để khắc phục sự cố này, hãy thử dán mã đã sao chép vào tờ giấy trắng MS Excel dưới dạng "Văn bản trong mã hóa Unicode". Để thực hiện việc này, hãy chuyển đến sổ làm việc MS Excel, chọn hoặc tạo một trang tính trống, đi đến ô "A1" và nhấn phím tắt Ctrl+Alt+V. Một menu sẽ xuất hiện " Chèn đặc biệt", chọn "Văn bản Unicode" và nhấp vào "OK".

Mã phải được dán vào bảng tính không có dấu chấm hỏi:

Sau đó, sao chép lại mã đã dán vào trang tính và dán vào mô-đun.

Nếu bạn không thấy cửa sổ "Project - VBA Project" trong trình soạn thảo VBA, hãy chuyển đến tab menu "Xem" và chọn "Project Explorer" từ danh sách hoặc nhấn phím tắt Ctrl+R:

Bảng Excel là một trong những cách phổ biến nhất để lưu trữ và sắp xếp thông tin. Nếu được triển khai ở Macro Excel, thì khả năng của chương trình có thể được mở rộng gần như gấp đôi. Ví dụ: macro giúp tự động hóa quy trình thường lệ và đẩy nhanh công việc đơn điệu. Chúng thường được sử dụng bởi các chuyên gia dịch báo cáo từ 1C sang Excel. Với sự trợ giúp của mã nhúng, chúng có thể dễ dàng được rút gọn thành một mẫu tiêu chuẩn duy nhất.

Macro Excel là gì?

Macro không chỉ được sử dụng trong bảng mà còn trong mọi thứ Microsoft Office. Chúng là mã được tạo bằng Visual Basic for Application. Nếu bạn đã quen một phần với lập trình web, thì bạn có thể rút ra sự tương tự với Javascript. Các macro Excel thực hiện gần giống như Javascript thực hiện với dữ liệu HTML.

Một phần nhỏ những gì macro có thể làm trong các ứng dụng văn phòng:

Căn chỉnh tài liệu theo phong cách và định dạng nó;
. thực hiện các thao tác với dữ liệu số và văn bản;
. tìm kiếm sự giúp đỡ tập tin bên ngoài(văn bản, cơ sở dữ liệu và những thứ khác);
. tạo tài liệu mới từ đầu;
. kết hợp một số hành động và thực hiện chúng một cách tự động.

Để làm việc với bảng, bạn có thể tự tạo hoặc tải xuống tùy chọn làm sẵn từ trên mạng. Macro chỉ nên được tải xuống từ các trang web đáng tin cậy, nếu không bạn có thể đưa vi-rút vào Excel.

Macro trong Excel có thể nguy hiểm như thế nào?

Làm hỏng các tập tin trên PC của bạn;
. ăn cắp dữ liệu bí mật.

Virus được đưa vào trong quá trình hoạt động của một trong những Ứng dụng văn phòng- Word hoặc Excel. Sau khi bạn ngừng làm việc, họ sẽ bắt đầu hoạt động tự chủ virus và lây nhiễm vào toàn bộ hệ thống.

Một cách khác mà macro độc hại hoạt động là làm trung gian đưa các mối đe dọa vào hệ thống. Trong trường hợp này, nó là một loại cửa ngõ để giới thiệu và tạo ra phần mềm Trojan. Nó sẽ không còn được kiểm soát bởi macro đã sửa đổi nữa mà bởi hệ điều hành, và do đó mối đe dọa trở nên lớn hơn. Không phải lúc nào cũng có thể chữa khỏi những loại virus như vậy, đôi khi bạn phải thay đổi hoàn toàn hệ điều hành Windows hoặc ổ cứng PC (tùy theo nhiệm vụ mà hacker đặt ra khi viết mã).

Bạn có thể chọn bất kỳ phương pháp bảo vệ nào chống lại các macro như vậy. Tùy chọn phổ biến nhất là tăng mức độ bảo mật của Excel, nhưng trong trường hợp này chương trình có thể không còn hỗ trợ bản quyền của bạn nữa, macro hữu ích. Bạn không nên tin cậy các tập tin từ không biết nguồn gốc, vì họ có thể bị nhiễm bệnh. Ngay cả macro nhận được từ PC của đồng nghiệp của bạn cũng có thể nguy hiểm. Nếu một người bạn đưa cho bạn một tệp để làm việc hỗ trợ việc sử dụng macro, thì trước khi cho phép hệ thống sử dụng nó, hãy làm rõ cách anh ta nhận được macro này.

Tùy chọn tốt nhất là tự mình tìm hiểu nghệ thuật tạo macro cho nhu cầu của bạn. Trong trường hợp này, bạn hoàn toàn có thể đảm bảo về chất lượng và độ an toàn của chúng.

Cách ghi macro trong Excel

Cách dễ nhất để tạo macro là lập trình trực tiếp trong Excel. Để làm điều này, bạn sẽ cần một bảng chứa dữ liệu đã nhập và một chút kiến ​​​​thức để có thể ghi mã đó vào chương trình.

Quá trình tạo bắt đầu bằng menu Xem. Trong đó, bạn cần chọn vị trí “Macro” và lệnh “Ghi macro”. Tiếp theo, bạn cần chỉ định tên không có khoảng trắng, ví dụ: “Định dạng” (nếu mã sẽ chịu trách nhiệm thay đổi định dạng của văn bản hoặc ô).

Sau đó mọi hành động của bạn sẽ được tự động ghi lại và chuyển thành mã macro. Một nút có hình vuông nhỏ (“Dừng”) sẽ xuất hiện ở cuối tài liệu, bằng cách nhấp vào đó bạn có thể dừng ghi. Bằng cách này, bạn có thể tự động ghi lại các macro trong Excel, các ví dụ mà bạn có thể phân tích sâu hơn trong quá trình học.

Cách bật macro trong Excel

Để kích hoạt macro đã ghi trong các tài liệu khác, bạn phải lưu nó sau khi xóa bảng. Định dạng tài liệu phải được đặt là XLTM (đây là định dạng hỗ trợ macro).

Sau này, khi bạn truy cập tài liệu của mình để bật macro trong Excel, trước tiên bạn phải bật chúng trong cửa sổ bật lên dòng trên cùng. Sau đó, bạn cần nhập dữ liệu sẽ được macro chỉnh sửa từ bảng CSV thông thường. Sau khi quá trình nhập hoàn tất, bạn cần quay lại menu “Chế độ xem”, chọn “Macro” ở đó và tìm tên mã bạn đã tạo trong danh sách. Cuối cùng, nhấp vào nút “Chạy”. Sau đó, bạn sẽ thấy chương trình, theo thuật toán bạn đã chỉ định trước đó, thực hiện các phép tính và định dạng tài liệu như thế nào.

Làm cách nào để xem mã macro?

Tất cả các macro tạo trong Excel đều được viết tự động, không cần sự can thiệp của con người. Chương trình tự soạn Mã chương trình, có thể được mở từ bảng bằng cách gọi bằng menu.

Để làm điều này, bạn cần chạy các lệnh tuần tự:

Mở rộng tab “Xem”;
. mở "Macro";
. chọn tên của macro cần thiết;
. Gọi một cửa sổ có mã của nó bằng nút “Thay đổi”.

Trong hộp thoại mới, bạn có thể chỉnh sửa và thay đổi macro. Bạn sẽ thấy ở đó mã mà chương trình đã ghi lại tự động và bạn có thể thêm bất kỳ hành động nào theo cách thủ công. Sau khi lưu thông tin, macro sẽ hoạt động khác khi nhập dữ liệu mới.

Cách bắt đầu macro bằng cách nhấp vào một ô

Giả sử bạn muốn macro chạy khi ô A1 được chọn. Để chọn vào ô Excel chạy macro, bạn cần thêm mã chương trình vào mô-đun đầu tiên:

Bảng tính phụ riêng tư_SelectionChange(ByVal Target As Range)
Nếu Target.Address = "$A$1" thì: Gọi module1
Kết thúc phụ

Cách tốt nhất để học macro là hướng dẫn excel. Có nhiều hơn một ví dụ tương tự, và đối với những người mới làm quen với tất cả các khả năng của bảng thì đây là Cách tốt nhất học cách sử dụng đầy đủ chức năng của nó.

Bạn có thể học cách làm việc với macro ở đâu?

Có một số cách để tìm hiểu cách tạo các macro phức tạp hơn. Chúng phải được phân biệt bằng logic và một chuỗi hành động được xác định rõ ràng. Các macro như vậy chỉ có thể được tạo bằng cách viết mã VBA theo cách thủ công. Để tạo các macro phức tạp, bạn sẽ cần dành chút thời gian để tìm hiểu sự phức tạp của ngôn ngữ Lập trình trực quan Cơ bản.

Bây giờ cùng khám phá nhé mọi người Khả năng của Excel Nhiều khóa học đặc biệt đã được tạo ra kéo dài trong vài tuần, nhưng để tham dự chúng, bạn sẽ phải đến trung tâm đào tạo hoặc tham dự hội thảo trên web (điều này không phải lúc nào cũng thuận tiện do chênh lệch múi giờ). Nhưng cách hiệu quả và đơn giản hơn để học cách tạo macro trong Excel là hướng dẫn kèm theo các ví dụ. Bạn có thể học nó bất cứ lúc nào thuận tiện cho bạn và sách giáo khoa từng bước sẽ giúp bạn phát triển các biến thể khác nhau: từ nhiều nhất mã đơn giảnđến phức tạp nhất. Microsoft Excel là một trình soạn thảo bảng tính phổ quát có thể được sử dụng để giải quyết nhiều vấn đề phức tạp.

Ví dụ

Có những mã cho phép bạn đạt được tự động hóa Các nhiệm vụ khác nhau. Vì vậy, để làm ví dụ, hãy xem macro chèn hàng. Nó trông như thế này:

Macro phụ1()