Trình ghi macro của Excel có rất nhiều sức mạnh, nhưng nó cũng có những hạn chế. Như đã đề cập trong một bài viết khác, trình ghi macro thường ghi lại mã không cần thiết và không thể ghi lại những thứ như logic hoặc tương tác với các chương trình khác. Nó cũng có thể khó sử dụng cho các macro dài hơn - bạn có thể thấy mình phải lên bảng phân cảnh trước cho các hành động của mình chỉ để tránh mắc phải những sai lầm tốn kém.
Bài viết này nhằm mục đích giúp bạn bắt đầu viết mã macro từ đầu trong VBA. Bạn sẽ tìm hiểu nơi Macro được lưu trữ, viết macro cơ bản và tìm hiểu kiến thức cơ bản về lập trình trong VBA bằng cách sử dụng các biến, logic và vòng lặp.
Bắt đầu
VBA và Visual Basic Editor
VBA, hoặc Visual Basic for Applications, là ngôn ngữ mà macro được viết. Tất cả macro được lưu trữ dưới dạng mã VBA, cho dù chúng được mã hóa thủ công hay được tạo bằng trình ghi macro.
Bạn có thể truy cập tất cả mã VBA trong sổ làm việc bằng Visual Basic Editor. Đây là một trình gỡ lỗi và chỉnh sửa văn bản đặc biệt được tích hợp sẵn cho tất cả các ứng dụng văn phòng, bao gồm cả Excel. Thông thường, bạn sẽ mở trình chỉnh sửa này với ALT + F11 phím tắt trong Excel, nhưng bạn cũng có thể truy cập nó từ Excel Nhà phát triển nếu bạn đã bật nó.
Trình khám phá dự án
Các Trình khám phá dự án là một cửa sổ bên trong VB Editor hiển thị cho bạn tất cả các mục có thể có mã VBA trong đó. Nếu bạn không thấy cửa sổ này, hãy nhấn F5 để làm cho nó xuất hiện hoặc chọn Trình khám phá dự án từ Quan điểm thực đơn.
Bấm đúp vào một mục trong Project Explorer sẽ hiển thị mã cho mục đó. Có một số loại mục có thể xuất hiện trong Project Explorer:
- Sách bài tập
- Trang tính
- UserForms
- Mô-đun lớp
- Mô-đun (macro được lưu trữ trong các mục này)
Mặc dù tất cả các loại mục này có thể bao gồm mã VBA, nhưng cách tốt nhất là viết mã macro trong Mô-đun.
Tạo Macro đầu tiên của bạn
Sử dụng Danh sách Macro
Danh sách Macro hiển thị cho bạn tất cả các macro trong sổ làm việc của bạn. Từ danh sách này, bạn có thể chỉnh sửa một macro hiện có hoặc tạo một macro mới.
Để tạo một macro mới bằng cách sử dụng danh sách Macro:
- Chọn tab Nhà phát triển và nhấp vào Macro (hoặc nhấn ALT + F8)
- Nhập tên mới cho macro của bạn, sau đó nhấp vào “Tạo”
Sau khi nhấp vào “Tạo”, VB Editor sẽ xuất hiện, hiển thị macro mới được tạo. Excel sẽ tạo một mô-đun mới cho macro nếu cần thiết.
Thủ công trong VB Editor
Bạn có thể thêm macro mới theo cách thủ công mà không cần danh sách Macro. Đây là tùy chọn tốt hơn nếu bạn muốn chỉ định mô-đun mà macro được lưu vào.
Để thêm macro theo cách thủ công:
- Mở VB Editor (ALT + F11)
- Một trong hai:
- Thêm một mô-đun mới bằng cách nhấp vào Chèn> Mô-đun trên menu (mô-đun sẽ tự động mở)
-
- HOẶC, bấm đúp vào một mô-đun hiện có trong Project Explorer để mở nó
- Trong mô-đun, hãy nhập mã cho macro mới của bạn
Sub MyMacro () End Sub
Hai dòng này cho biết phần đầu và phần cuối của macro có tên “MyMacro” (lưu ý dấu ngoặc đơn, bắt buộc phải có). Điều này sẽ hiển thị trong hộp thoại “Xem macro” trong Excel và có thể được gán cho một nút (ngay cả khi nó chưa thực hiện bất kỳ điều gì).
Thêm một số mã vào Macro
Bây giờ, hãy thêm một số mã giữa các dòng “Sub” và “End Sub” để làm cho macro này thực sự làm điều gì đó:
Phạm vi Sub MyMacro () (“A1”). Giá trị = “Hello World!” Kết thúc Sub
Cấu trúc mã cơ bản
Đối tượng Phạm vi
Excel VBA sử dụng Đối tượng Phạm vi để đại diện cho các ô trên trang tính. Trong ví dụ trên, một đối tượng Phạm vi được tạo bằng mã Phạm vi (“A1”) để truy cập giá trị của ô A1.
Đối tượng Phạm vi chủ yếu được sử dụng để đặt giá trị ô:
Phạm vi (“A1”). Giá trị = 1
Phạm vi (“A1”). Giá trị = "Ô đầu tiên"
Lưu ý rằng khi xác định giá trị ô dưới dạng số, bạn chỉ cần nhập số, nhưng khi nhập văn bản, bạn phải bao quanh văn bản bằng dấu ngoặc kép.
Dải ô cũng có thể được sử dụng để truy cập vào nhiều thuộc tính của ô như phông chữ, đường viền, công thức của chúng, v.v.
Ví dụ: bạn có thể đặt phông chữ của ô thành Bold như sau:
Phạm vi (“A1”). Font.Bold = True
Bạn cũng có thể đặt công thức của một ô:
Phạm vi (“A1”). Công thức = “= Tổng (A2: A10)”
Trong Excel, bạn có thể chọn một khối ô bằng con trỏ (giả sử từ A1 đến D10) và đặt tất cả chúng thành chữ đậm. Các đối tượng phạm vi có thể truy cập các khối ô như thế này:
Phạm vi (“A1: D10”). Font.Bold = True
Bạn cũng có thể tham khảo một số ô / khối cùng một lúc:
Phạm vi (“A1: D10, A12: D12, G1”). Font.Bold = True
Định dạng cho điều này giống với định dạng bạn sẽ sử dụng khi chọn ô cho công thức SUM () trong Excel. Mỗi khối được phân tách bằng dấu phẩy và các khối được biểu thị bằng các ô trên cùng bên trái và dưới cùng bên phải được phân tách bằng dấu hai chấm.
Cuối cùng, các đối tượng Range có các phương thức tích hợp để thực hiện các thao tác thông thường trên một trang tính. Ví dụ: bạn có thể muốn sao chép một số dữ liệu từ nơi này sang nơi khác. Đây là một ví dụ:
Phạm vi (“A1: D10”). Sao chép phạm vi (“F1”). PasteSpecial xlPasteValues Range (“F1”). PasteSpecial xlPasteFormats
Thao tác này sao chép các ô A1: D10 vào khay nhớ tạm và sau đó thực hiện PasteSpecial () bắt đầu từ ô C1 - giống như bạn làm thủ công trong Excel. Lưu ý rằng ví dụ này cho thấy cách sử dụng PasteSpecial () để chỉ dán Giá trị và Định dạng - có các tham số cho tất cả các tùy chọn mà bạn sẽ thấy trong hộp thoại Dán Đặc biệt.
Dưới đây là một ví dụ dán “Tất cả” vào một trang tính khác:
Phạm vi (“A1: D10”). Sao chép Trang tính (“Trang tính2”). Phạm vi (“A1”). PasteSpecial xlPasteAll
Câu lệnh If
Với một Câu lệnh if, bạn có thể làm cho một phần mã chỉ chạy “nếu” một câu lệnh nào đó là đúng.
Ví dụ: bạn có thể muốn tô đậm một ô và tô màu đỏ, nhưng chỉ “nếu” giá trị trong ô nhỏ hơn 100.
If Range (“A4”). Giá trị <100 Then Range (“A4”). Font.Bold = True Range (“A4”). Interior.Color = vbRed End If
Cấu trúc thích hợp của câu lệnh If như sau (dấu ngoặc vuông biểu thị các thành phần tùy chọn):
Nếu thì
[Khác Nếu sau đó]
[Khác]
Kết thúc nếu
Bạn có thể bao gồm nhiều ElseIf khối tùy thích để kiểm tra nhiều điều kiện. Bạn cũng có thể thêm một Khác khối chỉ chạy nếu không có điều kiện nào khác trong câu lệnh If được đáp ứng.
Dưới đây là một ví dụ khác dựa trên ví dụ trước, trong đó ô được định dạng theo nhiều cách khác nhau tùy thuộc vào giá trị:
If Range ("A4"). Giá trị <100 Then Range ("A4"). Font.Bold = True Range ("A4"). Interior.Color = vbRed ElseIf Range ("A4"). Giá trị <200 Then Range ( "A4"). Font.Bold = False Range ("A4"). Interior.Color = vbYellow Else Range ("A4"). Font.Bold = False Range ("A4"). Interior.Color = vbGreen End If
Trong ví dụ trên, ô không được in đậm trong các khối ElseIf mà giá trị không dưới 100. Bạn có thể tổ Câu lệnh if để tránh trùng lặp mã, như sau:
If Range ("A4"). Giá trị <100 Then Range ("A4"). Font.Bold = True Range ("A4"). Interior.Color = vbRed Else Range ("A4"). Font.Bold = False ' chỉ mở rộng phông chữ một lần If Range ("A4"). Giá trị <200 Then Range ("A4"). Interior.Color = vbYellow Else Range ("A4"). Interior.Color = vbGreen End If End If
Biến
MỘT Biến đổi là một phần bộ nhớ được sử dụng để lưu trữ thông tin tạm thời trong khi macro đang chạy. Chúng thường được sử dụng trong các vòng lặp làm trình lặp hoặc để giữ kết quả của một thao tác mà bạn muốn sử dụng nhiều lần trong một macro.
Dưới đây là một ví dụ về một biến và cách bạn có thể sử dụng nó:
Sub ExtractSerialNumber () Dim strSerial As String 'đây là khai báo biến' 'As String' có nghĩa là biến này dùng để giữ văn bản 'thiết lập một số sê-ri giả: Range ("A4"). Value = “serial # 804567-88 "" Phân tích cú pháp số sê-ri từ ô A4 và gán nó cho biến strSerial = Mid (Phạm vi ("A4"). Giá trị, 9) "bây giờ sử dụng biến hai lần, thay vì phải phân tích cú pháp số sê-ri hai lần Phạm vi (“ B4 ”). Giá trị = strSerial MsgBox strSerial End Sub
Trong ví dụ cơ bản này, biến ‘strSerial’ được sử dụng để trích xuất số sê-ri từ ô A4 bằng cách sử dụng hàm Mid (), sau đó được sử dụng ở hai nơi khác.
Cách tiêu chuẩn để tuyên bố một biến như sau:
Lờ mờ tên gì cũng được [Như kiểu]
- tên gì cũng được là tên bạn quyết định đặt cho biến của mình
- kiểu là kiểu dữ liệu của biến
“[Như kiểuCó thể bỏ qua phần] ”- nếu vậy, biến được khai báo là kiểu Biến thể, có thể chứa bất kỳ loại dữ liệu nào. Mặc dù hoàn toàn hợp lệ, nhưng nên tránh các loại Biến thể vì chúng có thể dẫn đến kết quả không mong muốn nếu bạn không cẩn thận.
Có quy tắc cho các tên biến. Chúng phải bắt đầu bằng một chữ cái hoặc một ký tự gạch dưới, không được có dấu cách, dấu chấm, dấu phẩy, dấu ngoặc kép hoặc các ký tự “! @ & $ # ”.
Dưới đây là một số ví dụ về khai báo biến:
Dim strFilename As String 'kiểu tên tốt - mô tả và sử dụng tiền tố Dim i As Long' kiểu tên xấu - chỉ chấp nhận được đối với một số trình lặp Dim SalePrice As Double 'kiểu tên ổn - mô tả, nhưng không sử dụng tiền tố Dim iCounter' tên được - không quá mô tả, sử dụng tiền tố, không có kiểu dữ liệu
Tất cả các ví dụ này sử dụng các cách đặt tên hơi khác nhau, nhưng tất cả đều hợp lệ. Không phải là một ý tưởng tồi nếu đặt tiền tố cho một tên biến có dạng ngắn của kiểu dữ liệu của nó (theo một số ví dụ sau), vì nó làm cho mã của bạn dễ đọc hơn trong nháy mắt.
VBA bao gồm rất nhiều Loại dữ liệu. Những cái phổ biến nhất bao gồm:
- Dây (được sử dụng để giữ dữ liệu văn bản)
- Dài (được sử dụng để chứa các số nguyên, tức là không có chữ số thập phân)
- Kép (được sử dụng để giữ số dấu phẩy động, tức là số thập phân)
Danh sách đầy đủ các kiểu dữ liệu nội tại của VBA có thể được tìm thấy tại đây: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary
Biến đối tượng phạm vi
Bạn cũng có thể tạo các biến tham chiếu Đối tượng phạm vi. Điều này hữu ích nếu bạn muốn tham chiếu đến một phạm vi cụ thể trong mã của mình ở một số nơi - theo cách đó nếu bạn cần thay đổi phạm vi, bạn chỉ cần thay đổi nó ở một nơi.
Khi bạn tạo một biến đối tượng Phạm vi, bạn phải “đặt” nó thành một thể hiện của Phạm vi. Ví dụ:
Dim rMyRange As Range Set rMyRange = Range (“A1: A10; D1: J10”)
Bỏ qua câu lệnh “Đặt” khi gán một biến Phạm vi sẽ dẫn đến lỗi.
Vòng lặp
Vòng lặp là các khối lặp lại mã bên trong chúng một số lần nhất định. Chúng hữu ích để giảm số lượng mã bạn phải viết và cho phép bạn viết một đoạn mã thực hiện các hành động giống nhau trên nhiều mục có liên quan khác nhau.
Cho tiếp theo
MỘT Cho tiếp theo khối là một vòng lặp lặp đi lặp lại một số lần nhất định. Nó sử dụng một biến làm người lặp lại để đếm số lần nó đã chạy và biến trình lặp này có thể được sử dụng bên trong vòng lặp. Điều này làm cho các vòng lặp For-Next rất hữu ích để lặp qua các ô hoặc mảng.
Dưới đây là một ví dụ lặp qua các ô từ hàng 1 đến hàng 100, cột 1 và đặt giá trị của chúng thành giá trị của biến trình lặp:
Dim i As Long For i = 1 to 100 Cells (i, 1) .Value = i Next i
Dòng “For i = 1 To 100” có nghĩa là vòng lặp bắt đầu từ 1 và kết thúc sau 100. Bạn có thể đặt bất kỳ số bắt đầu và số kết thúc nào bạn thích; bạn cũng có thể sử dụng các biến cho những số này.
Theo mặc định, các vòng lặp For-Next đếm bằng 1. Nếu bạn muốn đếm bằng một số khác, bạn có thể viết vòng lặp với một Bươc chân mệnh đề:
Đối với i = 5 đến 100 Bước 5
Vòng lặp này sẽ bắt đầu từ 5, sau đó thêm 5 vào ‘i’ mỗi khi lặp lại vòng lặp (vì vậy ‘i’ sẽ là 10 ở lần lặp thứ hai, 15 ở lần thứ ba, v.v.).
Sử dụng Bươc chân, bạn cũng có thể thực hiện đếm ngược vòng lặp:
Đối với i = 100 đến 1 bước -1
Bạn cũng có thể tổ Các vòng lặp For-Next. Mỗi khối yêu cầu biến riêng của nó để đếm, nhưng bạn có thể sử dụng các biến đó ở bất kỳ đâu tùy thích. Dưới đây là một ví dụ về cách hữu ích trong Excel VBA:
Dim i As Long, j As Long For i = 1 To 100 For j = 1 To 100 Cells (i, j) .Value = i * j Next j Next i
Điều này cho phép bạn lặp qua cả hàng và cột.
CẢNH BÁO: mặc dù được phép, bạn KHÔNG BAO GIỜ SỬA ĐỔI biến trình vòng lặp bên trong khối For-Next, vì nó sử dụng trình vòng lặp đó để theo dõi vòng lặp. Việc sửa đổi trình lặp có thể gây ra vòng lặp vô hạn và treo macro của bạn. Ví dụ:
Đối với i = 1 Đến 100 i = 1 Tiếp theo
Trong vòng lặp này, ‘I’ sẽ không bao giờ vượt quá 2 trước khi được đặt lại thành 1 và vòng lặp sẽ lặp lại mãi mãi.
Cho mỗi
Cho mỗi các khối rất giống với các khối For-Next, ngoại trừ chúng không sử dụng bộ đếm để chỉ định số lần chúng lặp lại. Thay vào đó, một khối For-Each lấy một "bộ sưu tập" các đối tượng (như một Phạm vi ô) và chạy nhiều lần nếu có các đối tượng trong bộ sưu tập đó.
Đây là một ví dụ:
Dim r As Range Cho Mỗi r Trong Phạm vi ("A15: J54") Nếu r.Value> 0 Thì r.Font.Bold = True End Nếu Tiếp theo r
Lưu ý việc sử dụng biến đối tượng Phạm vi ‘r’. Đây là biến lặp được sử dụng trong vòng lặp For-Each - mỗi khi qua vòng lặp, ‘r’ nhận được một tham chiếu đến ô tiếp theo trong Phạm vi.
Một lợi thế của việc sử dụng vòng lặp For-Each trong Excel VBA là bạn có thể lặp qua tất cả các ô trong một phạm vi mà không cần lồng các vòng lặp. Điều này có thể hữu ích nếu bạn cần lặp qua tất cả các ô trong một phạm vi phức tạp như Phạm vi (“A1: D12, J13, M1: Y12”).
Một nhược điểm của vòng lặp For-Each là bạn không có quyền kiểm soát thứ tự các ô được xử lý. Mặc dù trong thực tế Excel sẽ lặp qua các ô theo thứ tự, trên lý thuyết nó có thể xử lý các ô theo một thứ tự hoàn toàn ngẫu nhiên. Nếu bạn cần xử lý các ô theo một thứ tự cụ thể, bạn nên sử dụng vòng lặp For-Next.
Do-Loop
Trong khi các khối For-Next sử dụng bộ đếm để biết khi nào nên dừng, Do-Loop khối chạy cho đến khi một điều kiện được đáp ứng. Để làm điều này, bạn sử dụng một Cho đến khi mệnh đề trên đầu hoặc cuối của khối, kiểm tra điều kiện và làm cho vòng lặp dừng khi điều kiện đó được đáp ứng.
Thí dụ:
Dim str As String str = "Buffalo" Do Until str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo" str = str & "" & "Buffalo" Phạm vi vòng lặp ("A1"). Value = str
Trong vòng lặp này, “Buffalo” được nối với ‘str’ mỗi khi qua vòng lặp Cho đến khi nó khớp với câu mong đợi. Trong trường hợp này, kiểm tra được thực hiện khi bắt đầu vòng lặp - nếu 'str' đã là câu được mong đợi (đó không phải là vì chúng tôi không bắt đầu theo cách đó, nhưng nếu) thì vòng lặp thậm chí sẽ không chạy .
Bạn có thể làm cho vòng lặp chạy ít nhất một lần bằng cách chuyển mệnh đề Cho đến cuối, như sau:
Do str = str & "" & "Buffalo" Vòng lặp Cho đến khi str = "Trâu Trâu Trâu Trâu Trâu Trâu"
Bạn có thể sử dụng bất kỳ phiên bản nào có ý nghĩa trong macro của bạn.
CẢNH BÁO: bạn có thể gây ra một vòng lặp vô hạn với khối Do-Loop nếu điều kiện Cho đến khi không bao giờ được đáp ứng. Luôn viết mã của bạn để điều kiện Until chắc chắn sẽ được đáp ứng khi bạn sử dụng loại vòng lặp này.
Cái gì tiếp theo?
Khi bạn đã nắm được những kiến thức cơ bản, tại sao không thử học một số kỹ thuật nâng cao hơn? Hướng dẫn của chúng tôi tại https://easyexcel.net/excel/learn-vba-tutorial/ sẽ xây dựng dựa trên mọi thứ bạn đã học ở đây và mở rộng kỹ năng của bạn với Sự kiện, Biểu tượng người dùng, tối ưu hóa mã và hơn thế nữa!
