Vòng lặp VBA trong Excel - Cho từng vòng, Đối với tiếp theo, Thực hiện trong khi, Lồng nhau và hơn thế nữa

Để làm việc hiệu quả trong VBA, bạn phải hiểu về Vòng lặp.

Các vòng lặp cho phép bạn lặp lại một khối mã một số lần hoặc lặp lại một khối mã trên mỗi đối tượng trong một nhóm đối tượng.

Đầu tiên, chúng tôi sẽ chỉ cho bạn một vài ví dụ để cho bạn thấy khả năng của các vòng lặp. Sau đó, chúng tôi sẽ dạy bạn mọi thứ về vòng lặp.

Ví dụ nhanh về vòng lặp VBA

Đối với mỗi vòng lặp

Đối với Mỗi vòng lặp lặp qua mọi đối tượng trong một tập hợp, chẳng hạn như mọi trang tính trong sổ làm việc hoặc mọi ô trong một phạm vi.

Lặp qua tất cả các bảng tính trong Workbook

Mã này sẽ lặp qua tất cả các trang tính trong sổ làm việc, hiện mỗi trang tính:

12345678

Sub LoopThroughSheets ()Dim ws As WorksheetĐối với mỗi ws trong Worksheetsws.Vible = TrueKế tiếpKết thúc Sub

Vòng qua tất cả các ô trong phạm vi

Mã này sẽ lặp qua một loạt ô, kiểm tra xem giá trị ô là âm, dương hay 0:

1234567891011121314

Sub If_Loop ()Làm mờ ô dưới dạng phạm viĐối với mỗi ô trong phạm vi ("A2: A6")Nếu Cell.Value> 0 ThìCell.Offset (0, 1) .Value = "Tích cực"ElseIf Cell.Value <0 Sau đóCell.Offset (0, 1) .Value = "Phủ định"KhácCell.Offset (0, 1) .Value = "Zero"Kết thúc nếuÔ tiếp theoKết thúc Sub

Đối với các vòng tiếp theo

Một loại Vòng lặp “Cho” là Vòng lặp Tiếp theo. Vòng lặp For Next cho phép bạn lặp qua các số nguyên.

Mã này sẽ lặp qua các số nguyên từ 1 đến 10, hiển thị mỗi số với một hộp thông báo:

123456

Sub ForLoop ()Dim i As IntegerĐối với i = 1 đến 10MsgBox iTiếp theo tôiKết thúc Sub

Thực hiện vòng lặp trong khi

Vòng lặp Do While sẽ lặp lại khi một điều kiện được đáp ứng. Mã này cũng sẽ lặp qua các số nguyên từ 1 đến 10, hiển thị mỗi số một hộp thông báo.

12345678

Sub DoWhileLoop ()Dim n As Integern = 1Làm trong khi n <11MsgBox nn = n + 1VòngKết thúc Sub

Do Until Loops

Ngược lại, các vòng lặp Do Until sẽ lặp lại cho đến khi một điều kiện được đáp ứng. Đoạn mã này thực hiện tương tự như hai ví dụ trước.

12345678

Sub DoUntilLoop ()Dim n As Integern = 1Làm cho đến khi n> = 10MsgBox nn = n + 1VòngKết thúc Sub

Chúng ta sẽ thảo luận vấn đề này bên dưới, nhưng bạn cần phải cực kỳ cẩn thận khi tạo các vòng lặp Do While hoặc Do Until để không tạo ra một vòng lặp không bao giờ kết thúc.

Trình tạo vòng lặp VBA

Đây là ảnh chụp màn hình của "Trình tạo vòng lặp" từ Bổ trợ VBA cao cấp của chúng tôi: AutoMacro. Trình tạo vòng lặp cho phép bạn nhanh chóng và dễ dàng xây dựng các vòng lặp để lặp qua các đối tượng hoặc số khác nhau. Bạn có thể thực hiện các hành động trên từng đối tượng và / hoặc chỉ chọn các đối tượng đáp ứng các tiêu chí nhất định.

Phần bổ trợ này cũng chứa nhiều trình tạo mã khác, thư viện mã VBA mở rộng và nhiều loại công cụ mã hóa. Đó là điều bắt buộc đối với bất kỳ nhà phát triển VBA nào.

Bây giờ chúng ta sẽ trình bày sâu hơn về các loại vòng lặp khác nhau.

VBA cho vòng tiếp theo

Đối với cú pháp vòng lặp

Vòng lặp Tiếp theo cho phép bạn lặp lại một khối mã một số lần nhất định. Cú pháp là:

12345

[Dim Counter as Integer]Đối với bộ đếm = Bắt đầu đến kết thúc [Giá trị bước][Làm việc gì đó][Bộ đếm] tiếp theo

Trong đó các mục trong ngoặc là tùy chọn.

• [Dim Counter as Long] - Khai báo biến đếm. Bắt buộc nếu khai báo Option Explicit ở đầu mô-đun của bạn.
• Quầy tính tiền - Một biến số nguyên được sử dụng để đếm
• Bắt đầu - Giá trị bắt đầu (Ví dụ: 1)
• Kết thúc - Giá trị cuối cùng (Ví dụ: 10)
• [Giá trị bước] - Cho phép bạn đếm mọi n số nguyên thay vì mọi 1 số nguyên. Bạn cũng có thể làm ngược lại với một giá trị âm (ví dụ: bước -1)
• [Làm việc gì đó] - Mã sẽ lặp lại
• [Bộ đếm] tiếp theo - Câu lệnh đóng cho Vòng lặp Tiếp theo. Bạn có thể bao gồm Quầy hoặc không. Tuy nhiên, tôi thực sự khuyên bạn nên bao gồm bộ đếm vì nó giúp mã của bạn dễ đọc hơn.

Nếu điều đó khó hiểu, đừng lo lắng. Chúng tôi sẽ xem xét một số ví dụ:

Đếm đến 10

Mã này sẽ được tính đến 10 bằng cách sử dụng Vòng lặp Tiếp theo:

12345678

Sub ForEach_CountTo10 ()Dim n As IntegerĐối với n = 1 đến 10MsgBox nTiếp theo nKết thúc Sub

Đối với bước vòng lặp

Đếm đến 10 - Chỉ số chẵn

Mã này sẽ đếm đến 10 chỉ đếm các số chẵn:

12345678

Sub ForEach_CountTo10_Even ()Dim n As IntegerĐối với n = 2 đến 10 Bước 2MsgBox nTiếp theo nKết thúc Sub

Lưu ý rằng chúng tôi đã thêm “Bước 2”. Điều này cho biết Vòng lặp For “bước” qua bộ đếm bằng 2. Chúng ta cũng có thể sử dụng giá trị bước âm để bước ngược lại:

Đối với bước lặp - Nghịch đảo

Đếm ngược từ 10

Mã này sẽ đếm ngược từ 10:

123456789

Sub ForEach_Countdown_Inverse ()Dim n As IntegerĐối với n = 10 đến 1 bước -1MsgBox nTiếp theo nMsgBox "Tắt"Kết thúc Sub

Xóa hàng nếu ô trống

Tôi đã sử dụng thường xuyên nhất bước For-Loop phủ định để lặp qua các phạm vi ô, xóa các hàng đáp ứng các tiêu chí nhất định. Nếu bạn lặp lại từ các hàng trên cùng đến các hàng dưới cùng, khi xóa các hàng, bạn sẽ làm rối bộ đếm của mình.

Ví dụ này sẽ xóa các hàng có ô trống (bắt đầu từ hàng dưới cùng):

12345678910

Sub ForEach_DeleteRows_BlankCells ()Dim n As IntegerĐối với n = 10 đến 1 bước -1Nếu Phạm vi ("a" & n) .Value = "" ThìPhạm vi ("a" & n) .EntireRow.DeleteKết thúc nếuTiếp theo nKết thúc Sub

Lồng nhau cho vòng lặp

Bạn có thể “lồng” một Vòng lặp For vào trong Vòng lặp Đối với một Vòng lặp khác. Chúng tôi sẽ sử dụng các vòng lặp cho lồng nhau để tạo một bảng cửu chương:

1234567891011

Sub Nested_ForEach_MultiplicationTable ()Làm mờ hàng dưới dạng số nguyên, cột dưới dạng số nguyênĐối với hàng = 1 đến 9Đối với col = 1 đến 9Các ô (row + 1, col + 1) .Value = row * colCol tiếp theoHàng tiếp theoKết thúc Sub

Thoát cho

Câu lệnh Exit For cho phép bạn thoát khỏi vòng lặp For Next ngay lập tức.

Bạn thường sử dụng Exit For cùng với Câu lệnh If, thoát khỏi Vòng lặp Tiếp theo nếu một điều kiện nhất định được đáp ứng.

Ví dụ: bạn có thể sử dụng Vòng lặp For để tìm một ô. Sau khi tìm thấy ô đó, bạn có thể thoát khỏi vòng lặp để tăng tốc mã của mình.

Mã này sẽ lặp qua các hàng từ 1 đến 1000, tìm kiếm "lỗi" trong cột A. Nếu được tìm thấy, mã sẽ chọn ô, thông báo cho bạn về lỗi đã tìm thấy và thoát khỏi vòng lặp:

12345678910111213

Sub ExitFor_Loop ()Dim i As IntegerĐối với i = 1 đến 1000Nếu Phạm vi ("A" & i) .Value = "lỗi" ThìPhạm vi ("A" & i). ChọnMsgBox "Đã tìm thấy lỗi"Thoát choKết thúc nếuTiếp theo tôiKết thúc Sub

Quan trọng: Trong trường hợp Vòng lặp Cho lồng nhau, Thoát Cho chỉ thoát khỏi Vòng lặp Cho hiện tại, không phải tất cả các Vòng lặp đang hoạt động.

Tiếp tục Đối với

VBA không có lệnh "Tiếp tục" được tìm thấy trong Visual Basic. Thay vào đó, bạn sẽ cần sử dụng “Exit”.

VBA cho mỗi vòng lặp

VBA For Each Loop sẽ lặp qua tất cả các đối tượng trong một bộ sưu tập:

• Tất cả các ô trong một phạm vi
• Tất cả các trang tính trong một sổ làm việc
• Tất cả các hình dạng trong một trang tính
• Tất cả các sổ làm việc đang mở

Bạn cũng có thể sử dụng lồng nhau cho mỗi vòng lặp để:

• Tất cả các ô trong một phạm vi trên tất cả các trang tính
• Tất cả các hình dạng trên tất cả các trang tính
• Tất cả các trang tính trong tất cả các sổ làm việc đang mở
• và như thế…

Cú pháp là:

123	Đối với từng đối tượng trong bộ sưu tập[Làm việc gì đó]Tiếp theo [Đối tượng]

Ở đâu:

• Sự vật - Biến đại diện cho Phạm vi, Trang tính, Sổ làm việc, Hình dạng, v.v. (ví dụ: rng)
• thu thập - Bộ sưu tập các đối tượng (ví dụ: Phạm vi (“a1: a10”)
• [Làm việc gì đó] - Khối mã để chạy trên từng đối tượng
• Tiếp theo [Đối tượng] - Tuyên bố đóng cửa. [Đối tượng] là tùy chọn, tuy nhiên được khuyến nghị.

Đối với mỗi ô trong phạm vi

Mã này sẽ lặp qua từng ô trong một phạm vi:

123456789

Sub ForEachCell_inRange ()Làm mờ ô dưới dạng phạm viĐối với mỗi ô trong phạm vi ("a1: a10")cell.Value = cell.Offset (0,1) .ValueÔ tiếp theoKết thúc Sub

Đối với mỗi trang tính trong sổ làm việc

Mã này sẽ lặp qua tất cả các trang tính trong sổ làm việc, bỏ bảo vệ từng trang tính:

123456789

Sub ForEachSheet_inWorkbook ()Dim ws As WorksheetĐối với mỗi ws trong Worksheetsws.Unprotect "mật khẩu"Tuần tiếp theoKết thúc Sub

Đối với mỗi sổ làm việc đang mở

Mã này sẽ lưu và đóng tất cả các sổ làm việc đang mở:

123456789

Sub ForEachWB_inWorkbooks ()Dim wb As WorkbookĐối với mỗi wb trong sổ làm việcwb.Close SaveChanges: = TrueTuần tiếp theoKết thúc Sub

Đối với mỗi hình dạng trong trang tính

Mã này sẽ xóa tất cả các hình dạng trong trang tính đang hoạt động.

123456789

Sub ForEachShape ()Dim shp như hình dạngĐối với mỗi shp Trong ActiveSheet.Shapesshp.DeleteShp tiếp theoKết thúc Sub

Đối với mỗi hình dạng trong mỗi trang tính trong sổ làm việc

Bạn cũng có thể lồng cho Mỗi vòng lặp. Ở đây, chúng tôi sẽ lặp qua tất cả các hình dạng trong tất cả các trang tính trong sổ làm việc đang hoạt động:

1234567891011

Sub ForEachShape_inAllWorksheets ()Dim shp As Shape, ws As WorksheetĐối với mỗi ws trong WorksheetsĐối với mỗi shp Trong ws.shp.DeleteShp tiếp theoTuần tiếp theoKết thúc Sub

Đối với mỗi - Vòng lặp IF

Như chúng tôi đã đề cập trước đây, bạn có thể sử dụng câu lệnh If trong vòng lặp, chỉ thực hiện các hành động khi đáp ứng một số tiêu chí nhất định.

Mã này sẽ ẩn tất cả các hàng trống trong một phạm vi:

12345678910

Sub ForEachCell_inRange ()Làm mờ ô dưới dạng phạm viĐối với mỗi ô trong phạm vi ("a1: a10")If cell.Value = "" Then _cell.EntireRow.Hidden = TrueÔ tiếp theoKết thúc Sub

VBA Do While Loop

VBA Do While và Do Until (xem phần tiếp theo) rất giống nhau. Họ sẽ lặp lại một vòng lặp trong khi (hoặc cho đến khi) một điều kiện được đáp ứng.

Vòng lặp Do While sẽ lặp lại một vòng lặp khi một điều kiện được đáp ứng.

Đây là cú pháp Do While:

123	Thực hiện trong khi điều kiện[Làm việc gì đó]Vòng

Ở đâu:

• Tình trạng - Điều kiện để kiểm tra
• [Làm việc gì đó] - Khối mã lặp lại

Bạn cũng có thể thiết lập vòng lặp Do While với Điều kiện ở cuối vòng lặp:

123	Làm[Làm việc gì đó]Vòng lặp trong khi điều kiện

Chúng tôi sẽ demo từng cái và cho biết chúng khác nhau như thế nào:

Làm trong khi

Đây là ví dụ về vòng lặp Do While mà chúng tôi đã trình bày trước đây:

12345678

Sub DoWhileLoop ()Dim n As Integern = 1Làm trong khi n <11MsgBox nn = n + 1VòngKết thúc Sub

Lặp lại trong khi

Bây giờ chúng ta hãy chạy cùng một quy trình, ngoại trừ chúng ta sẽ di chuyển điều kiện đến cuối vòng lặp:

12345678

Sub DoLoopWhile ()Dim n As Integern = 1LàmMsgBox nn = n + 1Vòng lặp Trong khi n <11Kết thúc Sub

VBA Do Until Loop

Vòng lặp Do Until sẽ lặp lại một vòng lặp cho đến khi một điều kiện nhất định được đáp ứng. Cú pháp về cơ bản giống như các vòng lặp Do While:

123	Làm cho đến khi có điều kiện[Làm việc gì đó]Vòng

và tương tự, điều kiện có thể xảy ra ở đầu hoặc cuối của vòng lặp:

123	Làm[Làm việc gì đó]Loop Until Condition

Làm cho đến khi

Vòng lặp do Until này sẽ đếm đến 10, giống như các ví dụ trước của chúng tôi

12345678

Sub DoUntilLoop ()Dim n As Integern = 1Làm cho đến khi n> 10MsgBox nn = n + 1VòngKết thúc Sub

Vòng lặp cho đến khi

Vòng lặp Loop Until này sẽ được tính đến 10:

12345678

Sub DoLoopUntil ()Dim n As Integern = 1LàmMsgBox nn = n + 1Vòng lặp Cho đến khi n> 10Kết thúc Sub

Thoát Do Loop

Tương tự như sử dụng Exit For để thoát khỏi Vòng lặp For, bạn sử dụng lệnh Exit Do để thoát khỏi Vòng lặp Do ngay lập tức

1

Thoát Do

Đây là một ví dụ về Exit Do:

123456789101112131415

Sub ExitDo_Loop ()Dim i As Integeri = 1Làm cho đến khi tôi> 1000Nếu Phạm vi ("A" & i) .Value = "lỗi" ThìPhạm vi ("A" & i). ChọnMsgBox "Đã tìm thấy lỗi"Thoát DoKết thúc nếui = i + 1VòngKết thúc Sub

Kết thúc hoặc ngắt vòng lặp

Như chúng tôi đã đề cập ở trên, bạn có thể sử dụng Exit For hoặc Exit Do để thoát khỏi các vòng lặp:

1

Thoát cho

1

Thoát Do

Tuy nhiên, các lệnh này phải được thêm vào mã của bạn trước khi bạn chạy vòng lặp của mình.

Nếu bạn đang cố gắng "phá vỡ" một vòng lặp hiện đang chạy, bạn có thể thử nhấn thoát hoặc ĐIỀU KHIỂN + Nghỉ tạm dừng trên bàn phím. Tuy nhiên, điều này có thể không hoạt động. Nếu nó không hoạt động, bạn sẽ cần đợi cho đến khi vòng lặp của bạn kết thúc hoặc trong trường hợp vòng lặp vô tận, hãy sử dụng ĐIỀU KHIỂN + ALT + Xóa bỏ để buộc đóng Excel.

Đây là lý do tại sao tôi cố gắng tránh các vòng lặp Do, rất dễ vô tình tạo ra một vòng lặp vô tận buộc bạn phải khởi động lại Excel, có thể làm mất công việc của bạn.

Các ví dụ về vòng lặp khác

Vòng qua các hàng

Điều này sẽ lặp qua tất cả các hàng trong một cột:

123456789

Public Sub LoopThroughRows ()Làm mờ ô dưới dạng phạm viĐối với mỗi ô trong phạm vi ("A: A")Ff cell.value "" rồi đến MsgBox cell.address & ":" & cell.valueÔ tiếp theoKết thúc Sub

Vòng qua các cột

Điều này sẽ lặp qua tất cả các cột trong một hàng:

123456789

Public Sub LoopThroughColumns ()Làm mờ ô dưới dạng phạm viĐối với mỗi ô trong phạm vi ("1: 1")If cell.Value "" Then MsgBox cell.Address & ":" & cell.ValueÔ tiếp theoKết thúc Sub

Lặp qua các tệp trong một thư mục

Mã này sẽ lặp qua tất cả các tệp trong một thư mục, tạo ra một danh sách:

12345678910111213141516171819

Sub LoopThroughFiles ()Làm mờ oFSO dưới dạng đối tượngDim oFolder As ObjectDim oFile As ObjectDim i As IntegerĐặt oFSO = CreateObject ("Scripting.FileSystemObject")Đặt oFolder = oFSO.GetFolder ("C: \ Demo)i = 2Đối với mỗi oFile Trong oFolder.FilesPhạm vi ("A" & i) .value = oFile.Namei = i + 1OFile tiếp theoKết thúc Sub

Vòng qua mảng

Mã này sẽ lặp qua mảng ‘arrList’:

123	Đối với i = LBound (arrList) Đến UBound (arrList)MsgBox arrList (i)Tiếp theo tôi

Hàm LBound nhận "giới hạn dưới" của mảng và UBound nhận "giới hạn trên".

Các vòng lặp trong Access VBA

Hầu hết các ví dụ trên cũng sẽ hoạt động trong Access VBA. Tuy nhiên, trong Access, chúng tôi lặp qua Đối tượng Tập ghi chứ không phải Đối tượng Phạm vi.

123456789101112131415161718

Sub LoopThroughRecords ()Khi có lỗi Tiếp tục tiếp theoLàm mờ dbs dưới dạng cơ sở dữ liệuDim đầu tiên As RecordsetĐặt dbs = CurrentDbĐặt rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)Với đầu tiên.MoveLast.MoveFirstLàm cho đến khi .EOF = TrueMsgBox (rst.Fields ("ClientName")).MoveNextVòngKết thúc vớiđầu tiên. ĐóngĐặt đầu tiên = Không có gìĐặt dbs = Không có gìKết thúc Sub