Thực đơn
trang chủẢnh và video

Bộ sưu tập các macro và chức năng excel. Tự động hóa các tác vụ bằng Trình ghi Macro - Excel. Giải quyết vấn đề bằng lập trình VBA

Macro để đánh dấu ô A1 trên mỗi trang tính trong sổ làm việc đang hoạt động. Điều này cũng khiến màn hình chuyển động.

Sub A1SelectionEachSheet() Dim i As Integer Application.ScreenUpdating = False For i = 1 To Sheets.Count Sheets(i).Chọn ActiveWindow.ScrollColumn = 1 ActiveWindow.ScrollRow = 1 Range("a1").Chọn Trang tiếp theo(1) .Select Application.ScreenUpdating = True End Sub

Macro để sao chép trang tính hiện tại một số lần nhất định. Hữu ích cho việc kiểm tra một số macro - đã thực hiện các chỉnh sửa, kiểm tra chúng trên bản sao dữ liệu. Chúng tôi đã hết bản sao—chạy lại macro

Sub SimpleCopy() Dim i As Integer, j As Integer i = Application.InputBox("Nhập số bản sao của trang tính hiện tại") Application.ScreenUpdating = False For j = 1 To i ActiveSheet.Copy after:=Sheets(Sheets .Count) ActiveSheet .Name = "Sao chép" & j Tiếp theo j Application.ScreenUpdating = True End Sub

Tạo trang tính với tiêu đề từ một phạm vi được chỉ định trên trang tính

Sub CreateFromList() Làm mờ ô làm phạm vi cho mỗi ô trong bảng lựa chọn.Thêm sau:=Sheets(Sheets.Count) ActiveSheet.Name = cell.Value Ô tiếp theo End Sub

Markros vì gửi thư chậm trễ. Macro được sửa đổi từ cuốn sách Lập trình VBA chuyên nghiệp của John Walkenbach

Sub SendLetter() Dim OutApp As Object Dim OutMail As Object Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon On Error GoTo cleanup Set OutMail = OutApp.CreateItem(0) On Error Tiếp tục lại với OutMail .To = " [email được bảo vệ]" .Subject = "Báo cáo bán hàng" .Attachments.Add "C:\Test.txt" .Body = "Văn bản email" .DeferredDeliveryTime = Thay thế(Ngày, ".", "/") & " 11:00:00 " .send ".Display để tạo một lá thư và mở nó End With On Error GoTo 0 Set OutMail = Không dọn dẹp: Set OutApp = Nothing End Sub

Macro mục lục được sửa đổi một chút từ Nikolai Pavlov.
Nếu trang “Mục lục” đã tồn tại trong sách thì macro sẽ nhắc bạn xóa nó. Nếu không, hãy tạo một trang “Mục lục” và chèn các liên kết có tên trang tính

Sub TableOfContent() Dim sheet As Worksheet Dim cell As Range Dim Answer As Integer Application.ScreenUpdating = False With ActiveWorkbook For Each Workbook Trong ActiveWorkbook.Worksheets Nếu Worksheet.Name = "Table of Contents" Then Answer = MsgBox("Sổ làm việc có một trang có tên Mục lục. Xóa nó?", vbYesNo) Nếu Trả lời = vbNo Sau đó Thoát Sub Nếu Trả lời = vbYes Then Application.DisplayAlerts = Sai Worksheet.Delete Application.DisplayAlerts = True End If End If Next End With Sheets(Array) (1)).Chọn Trang tính.Thêm Trang tính(1).Name = "Mục lục" Với ActiveWorkbook cho mỗi trang tính Trong ActiveWorkbook.Worksheets Nếu sheet.Name<>"Mục lục" Sau đó Đặt ô = Worksheets(1).Cells(sheet.Index, 1) .Worksheets(1).Hyperlinks.Add neo:=cell, Địa chỉ:="", SubAddress:=""" & sheet .Name & """ & "!A1" cell.Formula = sheet.Name End Nếu trang tiếp theo kết thúc bằng hàng("1:1").Delete Application.ScreenUpdating = True End Sub

Sắp xếp các trang tính từ Trình hướng dẫn VBA. Macro cũng sắp xếp tờ ẩn. Sẽ không hoạt động nếu cuốn sách có cấu trúc được bảo vệ

Sub SORT_ALL_SHEETS() Application.ScreenUpdating = False: Application.EnableEvents = False Dim iSht As Worksheet, oDict As Object, i%, j% Set oDict = CreateObject("Scripting.Dictionary") " ghi nhớ trạng thái hiển thị của từng trang tính và thực hiện mọi thứ hiển thị cho mỗi iSht trong ActiveWorkbook.Sheets oDict.Item(iSht.Name) = iSht.Visible: iSht.Visible = True Next With ActiveWorkbook " sắp xếp các trang hiển thị For i = 1 To .Sheets.Count - 1 For j = i + 1 Đến .Sheets.Count Nếu UCase(.Sheets(i).Name) > UCase(.Sheets(j).Name) Then .Sheets(j).Move Before:=.Sheets(i) Next j Next i End With " khôi phục trạng thái ban đầu khả năng hiển thị của từng trang tính Đối với mỗi iSht trong ActiveWorkbook.Sheets iSht.Visible = oDict.Item(iSht.Name) Ứng dụng tiếp theo.EnableEvents = True: Application.ScreenUpdating = True End Sub

Nhập cột "Field1" và "Field2" từ trang tính "Sheet1" tập tin Excel“C:\Manager.xls” thông qua kết nối ADODB và chèn nội dung bắt đầu từ ô A1 của trang tính hiện tại

Ngay cả khi bạn không biết gì về VBA và macro, bạn sẽ có cảm hứng để tìm hiểu thêm sau khi đọc một số thủ thuật và thủ thuật trong phần này. Nếu bạn mới bắt đầu làm việc với macro, bạn có thể tìm thấy thông tin hữu ích tại đây.

. .

Trong các bài viết trước, bạn đã biết Excel có thể quản lý người khác như thế nào Ứng dụng của Microsoft chẳng hạn như Word và Outlook. Nhưng đồng thời, các ứng dụng khác cũng có thể được sử dụng để Quản lý Excel. Ví dụ: bạn đã viết macro cho Word hoặc Access để tạo bảng Excel, điền dữ liệu vào bảng rồi lưu. Excel không nhất thiết phải […]

. .

Bạn có thể sử dụng công nghệ này để kiểm soát Microsoft Outlook và gửi email trực tiếp từ bảng tính của bạn hoặc sao chép các mục trong sổ ghi chép của bạn. Tất nhiên, các tính năng của Excel là như vậy e-mail bảng sẽ được gửi, nhưng phương pháp này chỉ cho phép bạn gửi một phần của bảng. Để mã này hoạt động, bạn cần phải có […]

. .

Phương pháp này có thể rất hữu ích, ví dụ nếu bạn có tài liệu chuẩn với các bảng chứa đầy macro dữ liệu từ bảng excel. Bạn có thể chạy macro và dữ liệu sẽ được chuyển đến các bảng trong Tài liệu văn bản. Gần đây tôi có nhiệm vụ viết chương trình điền báo cáo về SLA (Thỏa thuận cấp độ dịch vụ). Báo cáo […]

. .

Macro thường được sử dụng để tự động hóa hoạt động của ứng dụng. Bất kỳ macro nào cũng là một chuỗi hành động được ghi lại dưới một tên cụ thể. Nếu khi làm việc với Microsoft Excel cần phải thực hiện cùng một chuỗi thao tác nhiều lần (ví dụ: định dạng phức tạp của ô hiện tại hoặc thêm dòng mới bằng cách điền công thức vào một số ô của nó), sau đó bạn có thể viết ra những hành động này và […]

Làm việc với sách

Macro 1: Tạo sổ làm việc mới từ đầu
Macro 2. Lưu sổ làm việc khi thay đổi một ô/phạm vi cụ thể
Macro 3. Lưu sổ làm việc trước khi đóng
Macro 4. Bảo vệ bảng tính trong sổ làm việc trước khi đóng
Macro 5. Bỏ bảo vệ sheet khi mở file Excel
Macro 6. Mở sổ làm việc trên trang tính mong muốn
Macro 7. Mở một sổ làm việc cụ thể do người dùng xác định
Macro 8. Xác định xem sách có đang mở không
Macro 9. Xác định xem sách có tồn tại trong một thư mục hay không
Macro 10. Cập nhật tất cả kết nối trong sổ làm việc đang mở
Macro 11. Đóng tất cả sách cùng một lúc
Macro 12. Mở tất cả sổ làm việc trong một thư mục
Macro 13. In tất cả sách trong một thư mục
Macro 14. Không được phép đóng sách cho đến khi ô được lấp đầy
Macro 15. Tạo bản sao lưu cuốn sách hiện tại với ngày hôm nay

Làm việc với trang tính

Macro 16. Thêm một bảng tính mới và đặt tên
Macro 17. Xóa tất cả các trang trừ trang đang hoạt động
Macro 18. Ẩn mọi thứ trừ bảng tính đang hoạt động
Macro 19. Hiển thị tất cả các sheet của bảng tính
Macro 20. Di chuyển bảng tính
Macro 21. Sắp xếp các sheet theo tên
Macro 22. Nhóm các trang tính theo màu nhãn
Macro 23. Sao chép một trang tính vào sổ làm việc mới
Macro 24. Tạo mới sách bài tập cho mỗi tờ
Macro 25. In tờ
Macro 26. Bảo vệ tất cả các trang tính
Macro 27. Bỏ bảo vệ tất cả các trang tính
Macro 28. Tạo mục lục
Macro 29: Bấm đúp để phóng to bảng tính
Macro 30. Chọn cột hàng hiện hoạt

Lựa chọn và thay đổi phạm vi

Macro 31. Chọn và định dạng một phạm vi
Macro 32. Tạo và chọn phạm vi được đặt tên
Macro 33. Đếm số lượng ô
Macro 34. Lựa chọn và định dạng phạm vi
Macro 35. Chèn dòng trống trong phạm vi
Macro 36. Hiển thị tất cả dòng ẩn và cột
Macro 37. Xóa dòng trống
Macro 38. Xóa cột trống
Macro 39. Chọn và định dạng tất cả các công thức trong sổ làm việc
Macro 40: Tìm và chọn hàng hoặc cột trống đầu tiên
Macro 41. Áp dụng màu tô bổ sung
Macro 42. Sắp xếp phạm vi bằng cách nhấp đúp
Macro 43. Giới hạn phạm vi cuộn trong một khu vực cụ thể
Macro 44. Tự động thiết lập vùng in của sheet

Làm việc với dữ liệu

Macro 45. Sao chép và dán một phạm vi
Macro 46: Chuyển đổi tất cả các công thức trong một phạm vi thành giá trị
Macro 47. Chuyển đổi giá trị văn bản sang số
Macro 48. Chuyển dấu gạch ngang thành dấu trừ
Macro 49. Xóa khoảng trống thừa từ tất cả các ô trong phạm vi
Macro 50. Cắt 5 ký tự bên trái trong mỗi ô của dãy
Macro 51. Thêm số 0 còn thiếu vào ô
Macro 52. Thay thế các ô trống bằng 0
Macro 53. Thêm văn bản vào đầu hoặc cuối ô
Macro 54. Tạo macro chuyển đổi dữ liệu
Macro 55. Xóa dữ liệu (ký tự không in được)
Macro 56. Chọn trùng lặp trong vùng dữ liệu
Macro 57. Ẩn dòng lặp
Macro 58. Ẩn có chọn lọc các mũi tên của bộ lọc tự động
Macro 59. Sao chép các hàng đã lọc vào sổ làm việc mới
Macro 60. Tạo sheet mới cho từng phần tử trong AutoFilter
Macro 61. Hiển thị các cột được lọc trên thanh trạng thái

Làm việc với Bảng Pivot

Macro 62: Tạo khả năng tương thích ngược bảng tổng hợp
Macro 63. Cập nhật tất cả các bảng trụ trong một cuốn sách
Macro 64. Tạo một “kho” tất cả các bảng tổng hợp của cuốn sách
Macro 65. Tạo tất cả các bảng tổng hợp bằng cùng một bộ đệm dữ liệu
Macro 66. Ẩn tất cả các tổng phụ trong bảng tổng hợp
Macro 67. Thay đổi tên dữ liệu của tất cả các trường tóm tắt
Macro 68. Tổng hợp bắt buộc cho tất cả dữ liệu tóm tắt
Macro 69: Áp dụng định dạng số cho tất cả các mục dữ liệu
Macro 70. Sắp xếp các trường tóm tắt theo thứ tự bảng chữ cái
Macro 71. Áp dụng cách sắp xếp tùy chỉnh cho các mục dữ liệu
Macro 72: Đưa biện pháp bảo vệ lên bảng trụ
Macro 73: Áp dụng các ràng buộc trường Pivot
Vĩ mô 74. Tự động loại bỏ tờ với các chi tiết tóm tắt
Macro 75: In bảng tổng hợp cho từng thành phần bộ lọc
Macro 76. Tạo một file mới cho mỗi phần tử bộ lọc
Macro 77. Chuẩn bị vùng dữ liệu cho bảng trụ

Làm việc với biểu đồ và đồ thị

Macro 78. Thay đổi kích thước biểu đồ trên trang tính
Macro 79. Liên kết biểu đồ với một phạm vi cụ thể
Macro 80: Tạo tập hợp sơ đồ rời rạc
Macro 81: In tất cả biểu đồ trên bảng tính
Macro 82. Đánh dấu giá trị tốt nhất và xấu nhất trên
Macro 83. Màu giống nhau cho các giá trị trên các biểu đồ khác nhau
Macro 84. Khớp màu của biểu đồ với màu của phạm vi

Gửi email từ Excel

Macro 85. Gửi sách đang hoạt động qua thư (đính kèm)
Macro 86: Gửi một loạt giá trị dưới dạng tệp đính kèm
Macro 87. Gửi một tờ dưới dạng tệp đính kèm
Macro 88. Gửi email có liên kết tới tệp của chúng tôi
Macro 89: Gửi email thêm địa chỉ vào danh sách liên hệ của chúng tôi
Macro 90. Lưu tất cả tệp đính kèm vào một thư mục riêng
Macro 91. Lưu các tệp đính kèm cụ thể vào một thư mục

Khả năng tương tác với các ứng dụng Office khác

Macro 92. Chạy yêu cầu truy cập từ Excel



Macro 96. Nén cơ sở Tiếp cận thông tin từ Excel
Macro 97. Đang gửi Dữ liệu Excel vào một tài liệu Word
Macro 98. Thực hiện sáp nhập với tài liệu Word
Macro 99: Gửi dữ liệu Excel tới bản trình bày PowerPoint
Macro 100. Đang gửi Biểu đồ Excel trong bài thuyết trình PowerPoint
Macro 101: Chuyển đổi sổ làm việc thành bản trình bày PowerPoint

Khả năng tương tác với các ứng dụng Office khác
học cách làm việc với Word, Access và PowerPoint
Macro 92. Chạy yêu cầu truy cập từ Excel
Macro 93. Chạy macro Access từ Excel
Macro 94. Mở báo cáo Access từ Excel
Macro 95. Mở biểu mẫu truy cập từ Excel

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ác biến được khai báo như thế nào, các tham chiếu hoạt động như thế nào Ô Excel, 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ị cửa sổ thông báo nếu trên bảng tính hiện tại "Ô B1 được chọn Bảng tính phụ riêng tư_SelectionChange(ByVal Target As Range) "Kiểm tra xem ô B1 có được chọn không Nếu Target.Count = 1 Và Target.Row = 1 Và Target.Column = 2 Then " Nếu ô B1 được chọn, thực hiện hành động cần thiết 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 Workbook On Error GoTo ErrorHandling " Mở sổ làm việc có dữ liệu Set 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 đó đang tìm kiếm" ở 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

Chào buổi chiều

Tôi muốn dành bài viết này cho một phần lớn của MS Excel như macro, hay nói đúng hơn là hãy bắt đầu lại từ đầu và xem cách tạo macro trong Excel, nó dùng để làm gì và cách sử dụng nó trong công việc của bạn.

Như bạn đã biết từ trải nghiệm riêng, khi làm việc với macro, có rất nhiều “quy trình”, tức là các thao tác và hành động giống nhau được thực hiện cần thiết để thu được kết quả, việc này có thể là điền cùng loại bảng hoặc biểu mẫu, xử lý dữ liệu, tương tự như cặp song sinh báo cáo hàng tuần, hàng tháng, v.v. Nhưng việc sử dụng macro sẽ cho phép bạn thực hiện những hành động này một cách dễ dàng. chế độ tự động sử dụng Khả năng của Excelở mức tối đa, ném những thao tác thường ngày và đơn điệu này lên đôi vai mạnh mẽ của Excel. Ngoài ra, lý do sử dụng macro có thể được thêm vào những cơ hội cần thiết, điều chưa được triển khai ở tính năng tiêu chuẩn Excel (ví dụ: xuất, thu thập dữ liệu trên một trang tính, v.v.).

Nếu bạn chưa bao giờ nghe nói về macro thì định nghĩa chính xác nhất về nó sẽ là: đây là những hành động được lập trình để một trình tự nhất định và được viết trong môi trường lập trình bằng ngôn ngữ Ngôn ngữ lập trình dành cho ứng dụng (VBA). Việc chạy macro có thể được thực hiện nhiều lần và điều này sẽ buộc Excel thực hiện bất kỳ chuỗi hành động nào chúng ta cần mà chúng ta không thích hoặc không muốn thực hiện theo cách thủ công. Mặc dù có rất nhiều ngôn ngữ lập trình cho toàn bộ khu phức hợp Microsoft Office VBA là tiêu chuẩn và nó hoạt động trong mọi ứng dụng văn phòng.

Như vậy có 2 cách tạo macro trong Excel:

Tạo macro trong Excelsử dụng máy ghi macro

Trước tiên, hãy làm rõ máy ghi macro là gì và macro có liên quan gì đến nó.

Máy ghi macro là một chương trình nhỏ được tích hợp trong Excel để diễn giải mọi hành động của người dùng bằng mã ngôn ngữ lập trình VBA và ghi vào mô-đun phần mềm các lệnh nhận được trong quá trình làm việc. Nghĩa là, nếu chúng ta tạo báo cáo hàng ngày mà chúng ta cần khi bật trình ghi macro, trình ghi macro sẽ ghi lại mọi thứ trong lệnh của nó theo từng bước và do đó, tạo một macro sẽ tự động tạo báo cáo hàng ngày.

Phương pháp này rất hữu ích cho những ai chưa có kỹ năng và kiến ​​thức làm việc trong môi trường ngôn ngữ VBA. Nhưng việc dễ dàng thực thi và ghi lại macro như vậy cũng có những nhược điểm cũng như những ưu điểm của nó:

  • Trình ghi macro chỉ có thể ghi lại những gì nó có thể chạm vào, có nghĩa là nó chỉ có thể ghi lại các hành động khi các nút, biểu tượng, lệnh menu và mọi thứ tương tự không có sẵn cho nó;
  • Nếu xảy ra lỗi trong quá trình ghi, lỗi đó cũng sẽ được ghi lại. Nhưng bạn có thể sử dụng nút hủy hành động cuối cùng, xóa lệnh cuối cùng mà bạn viết sai trong VBA;
  • Việc ghi vào trình ghi macro chỉ được thực hiện trong phạm vi cửa sổ MS Excel và nếu bạn đóng chương trình hoặc bật chương trình khác, quá trình ghi sẽ bị dừng và không còn được thực hiện nữa.

Để bật trình ghi macro để ghi, bạn phải thực hiện các bước sau:


Bước tiếp theo khi làm việc với trình ghi macro sẽ là định cấu hình các tham số của nó để ghi thêm macro; việc này có thể được thực hiện trong cửa sổ; "Ghi macro", Ở đâu:

  • Trường tên macro- bạn có thể viết tên mà bạn hiểu bằng bất kỳ ngôn ngữ nào, nhưng tên đó phải bắt đầu bằng một chữ cái và không chứa dấu chấm câu hoặc dấu cách;
  • Trường phím tắt- sẽ được bạn sử dụng trong tương lai cho bắt đầu nhanh vĩ mô của bạn. Trong trường hợp bạn cần đăng ký mới, tùy chọn này sẽ có sẵn trong menu “Công cụ” - “Macro” - “Macro” - “Chạy” hoặc trên tab "Nhà phát triển" nhấn một nút "Macro";
  • trường "Lưu vào..."— bạn có thể chỉ định vị trí lưu văn bản macro (nhưng không được gửi) và có 3 tùy chọn:
    • "Cuốn sách này"— macro sẽ được ghi vào mô-đun của sổ làm việc hiện tại và chỉ có thể được thực thi nếu đã cho Sổ làm việc Excel sẽ được mở;
    • "Một cuốn sách mới"- macro sẽ được lưu trong mẫu trên cơ sở mẫu trống được tạo trong Excel Một quyển sách mới, điều đó có nghĩa là macro sẽ có sẵn trong tất cả các sổ làm việc sẽ được tạo trên máy tính này kể từ bây giờ;
    • "Sách Macro Cá Nhân"- là một sổ làm việc macro Excel đặc biệt được gọi là "Cá nhân.xls" và được sử dụng như một thư viện lưu trữ macro đặc biệt. Khi khởi động, macro từ sổ làm việc “Personal.xls” được tải vào bộ nhớ và có thể khởi chạy trong bất kỳ sổ làm việc nào vào bất kỳ lúc nào.
  • Trường "Mô tả"- ở đây bạn có thể mô tả macro nên làm gì và như thế nào, tại sao nó được tạo và nó có những chức năng gì, đây là một trường thông tin thuần túy, được gọi là bộ nhớ.

Khi bạn đã chạy và ghi lại macro của mình, hãy hoàn thành tất cả hành động cần thiết, có thể dừng ghi âm bằng lệnh "Dừng ghi âm" và macro của bạn sẽ được tạo bằng trình ghi macro.

Tạo macro trong Exceltrong trình soạn thảo Visual Basic.

Trong phương pháp này, chúng ta sẽ xem cách tạo macro trong Excel thông qua trình soạn thảo chương trình VBA, như tôi đã nói ở trên, được tích hợp vào bất kỳ phiên bản Excel nào.

Việc khởi chạy trình soạn thảo chương trình VBA diễn ra khác nhau, tùy thuộc vào phiên bản chương trình Excel của bạn:

  • trong các phiên bản Excel 2003 trở lên, chúng ta cần trong menu "Dịch vụ", chọn mục "Vĩ mô" và hãy nhấn "Trình soạn thảo Visual Basic";
  • trong các phiên bản Excel 2007 trở lên, chúng tôi cần trên tab "Nhà phát triển" Nhấn nút "Trình soạn thảo Visual Basic". Nếu bạn không tìm thấy tab này, bạn cần kích hoạt nó bằng cách chọn mục menu “Tệp” - “Tùy chọn” - “Tùy chỉnh Ribbon” và trong hộp thoại, hãy sử dụng hộp kiểm để kích hoạt tab "Nhà phát triển".

Trong cửa sổ mở ra, bạn có thể thấy giao diện của trình soạn thảo VBA, mọi thứ ở đây đều được viết bằng tiếng Anh và không có bản địa hóa, bạn không cần phải tìm kiếm mà chỉ cần chấp nhận và làm việc, đặc biệt là vì nó chỉ khó hiểu lúc đầu, sau đó mọi thứ sẽ quen thuộc.

Vậy, cách làm việc trong trình soạn thảo VBA, những gì được lưu trữ ở đâu và cách tạo macro trong Excel. Những câu hỏi thuộc loại này nảy sinh ngay lập tức khi bạn gặp người biên tập và bây giờ chúng ta sẽ xem xét tất cả chúng.

Tất cả các macro của chúng tôi sẽ được lưu trữ trong cái gọi là module phần mềm. Trong bất kỳ cuốn sách nào Các phiên bản Excel chúng ta có thể tạo ra bất kỳ module phần mềm với số lượng bất kỳ và đặt tất cả các macro mà chúng tôi tạo vào đó. Một mô-đun có thể chứa bất kỳ số lượng macro nào bạn cần hoặc tạo. Các mô-đun có sẵn trong cửa sổ "Thám hiểm dự án" và nằm ở góc trên bên trái của trình soạn thảo macro (bạn cũng có thể gọi nó bằng tổ hợp phím CTRL+R).

Các mô-đun chương trình trong trình soạn thảo VBA tồn tại dưới nhiều loại và được sử dụng để tùy chọn khác nhau và tình huống:


Trên thực tế, bản thân macro khi làm việc trong một mô-đun tiêu chuẩn sẽ trông như thế này:

Hãy xem một ví dụ về macro đang hoạt động:

  • Tất cả các macro trong bắt buộc sẽ bắt đầu với toán tử phụ, theo sau là tên macro của bạn và danh sách các đối số trong ngoặc đơn. Trong trường hợp không có đối số thì để trống dấu ngoặc đơn;
  • Tất cả các macro phải kết thúc bằng toán tử Kết thúc phụ;
  • Dữ liệu giữa các nhà khai thác phụ Và Kết thúc phụ, là phần thân của macro sẽ chạy khi macro được chạy. Trong ví dụ này, macro sẽ kiểm tra và khi nhập dữ liệu, nó sẽ tìm thấy nó trong danh sách cơ sở dữ liệu và hiển thị giá trị được chỉ định theo tiêu chí.

Như bạn có thể thấy, phương pháp thứ hai khó sử dụng và khó hiểu hơn nếu bạn không có kinh nghiệm về lập trình nói chung hoặc VBA nói riêng. Rất khó để hiểu và tìm ra những lệnh nào được nhập và cách nhập, những đối số nào được sử dụng để macro bắt đầu thực hiện công việc của nó một cách tự động. Nhưng người đi sẽ làm chủ con đường, như các nhà hiền triết xưa đã nói, và do đó, người ta không nên bỏ cuộc mà hãy làm theo lời dặn của ông nội Lênin...

Tạo nút chạy macro trên thanh công cụ

Như tôi đã nói trước đó bạn có thể gọi một thủ tục macro sự kết hợp nóng bỏng các phím, nhưng thật tẻ nhạt khi nhớ tổ hợp nào được gán cho ai, vì vậy cách tốt nhất của bạn là tạo một nút để chạy macro. Các nút có thể được tạo thành nhiều loại, cụ thể là:


Phương pháp này có sẵn cho mọi phiên bản MS Excel và bao gồm thực tế là chúng tôi sẽ đặt nút trực tiếp trên bảng tính của mình dưới dạng đối tượng đồ họa. Để làm điều này bạn cần:

  • Trong MS Excel 2003 trở lênđi tới thực đơn "Xem", chọn "Thanh công cụ" và nhấn nút "Các hình thức".
  • Trong MS Excel 2007 trở lên bạn cần trên tab "Nhà phát triển" mở menu thả xuống "Chèn" và chọn một đối tượng "Cái nút".

Sau tất cả những điều này, bạn phải vẽ một nút trên trang tính của mình trong khi giữ nút chuột trái. Sau khi quá trình vẽ hoàn tất, một cửa sổ sẽ tự động mở nơi bạn cần chọn macro cần thực thi khi nhấp vào nút của mình.

Cách tạo hàm tùy chỉnh trong VBA

Về nguyên tắc, việc tạo cái gọi là hàm do người dùng xác định không khác lắm so với việc tạo macro thông thường trong mô-đun chương trình tiêu chuẩn. Sự khác biệt giữa các khái niệm này là macro sẽ thực hiện các hành động có trong nó với các đối tượng sách hoặc trang tính (đây là các công thức, ô, v.v.), nhưng hàm người dùng chỉ hoạt động với các giá trị mà nó nhận được từ chúng ta và đây là những đối số dữ liệu ban đầu để tính toán.

Ví dụ, để tạo chức năng tùy chỉnh thuế giá trị gia tăng, hay còn gọi là VAT, chúng ta cần mở trình soạn thảo VBA và thêm mô-đun mới, chọn từ trình đơn "Chèn"đoạn văn "Mô-đun" và nhập văn bản cho chức năng của chúng tôi ở đó: Cần lưu ý rằng sự khác biệt chính giữa hàm và macro là tiêu đề Chức năng thay thế phụ và có một danh sách đầy đủ các đối số, trong ví dụ của chúng tôi đây là tổng hợp. Khi mã của chúng tôi đã được nhập, nó sẽ có sẵn trong cửa sổ tiêu chuẩn Trình hướng dẫn chức năng, nằm trong menu "Công thức", đoạn văn "Chèn chức năng".
Và chọn một danh mục « Đã xác định người dùng» trong đó chức năng viết của chúng tôi sẽ được hiển thị "NDS".
Sau khi chọn hàm của chúng tôi, bạn có thể đặt con trỏ lên ô có đối số, đối số này sẽ chứa số tiền mà chúng tôi tính VAT, mọi thứ diễn ra như với một hàm thông thường.
Và đó là tất cả đối với tôi! Tôi thực sự hy vọng rằng bài viết hướng dẫn tạo macro trong Excel rõ ràng và hữu ích cho bạn. Tôi sẽ rất biết ơn những nhận xét của bạn, vì đây là dấu hiệu cho thấy khả năng đọc và truyền cảm hứng cho tôi viết bài mới! Chia sẻ những gì bạn đọc với bạn bè và thích nó!