Use of loop code in VBA

Recently, I am studying VBA by myself. With the previous Python foundation, I feel that learning VBA is easier. After all, VBA and Python are object-oriented programming languages. Through self-study, I have basically mastered the VBA cycle method. The following are my simple notes to share with you.

1. Fill the cells in a loop

1. Use a for loop and Cells() to fill the cells of rows 1 to 10 of columns A to 1, 2, 3...10. The code is as follows:

Sub Fill cells()
Dim i As Integer
For i = 1 To 10
    Cells(i,1)= i
Next i
End Sub

2. Fill in numbers through for loop and Range()

Sub Fill cells2()
Dim i As Integer
For i = 1 To 10
    Range("A" & i)= i
Next i
End Sub

3. Alternative Solution

This method combines the above two methods by defining a Range object and then traversing the elements in the object.

Sub Fill cells3()
Dim col as integer
Set col = Range("A1:A10")
For Each cell In col
     = ()
Next cell
End Sub

2. Looping through the worksheet

1. Perform a for loop by getting the total number of worksheets. The following code implements the for loop to fill the first cell of each worksheet with the table name of the worksheet.

Sub Loop worksheet()
Dim i As Integer
For i = 1 To  'Get the number of worksheets
    Worksheets(i).Range("A1") = Worksheets(i).Name
Next i
End Sub

2. Use for each ... in to loop through the worksheet

The core of this method is to define the worksheet object sht and then traverse the worksheet collection object.

Sub Loop worksheet2()
Dim sht As Worksheet
For Each sht In Worksheets
    ("A1") = 
Next sht
End Sub

3. Looping through the workbook

That is, iterate through Excel files, assuming that all files are already open. There are two methods provided here: the first is the object method, the code is as follows:

Sub Loop workbook()
For Each wb In Workbooks
    For Each sht In 
        ("A1") = 
    Next sht
Next wb
End Sub

The second is the slice method, and the function implemented is to fill in the table name of the current worksheet in cell A1 of each worksheet.

Sub Loop workbook()
Dim i, m As Integer
For i = 1 To 
    For m = 1 To 
        Worksheets(m).Range("A1") = Worksheets(m).Nam
    Next m
Next i
End Sub

4. Things to note

The for loop is paired, be sure to add next X, otherwise the program will report an error.

There are roughly two types of VBA loops, one is the object method and the other is the slice method. It is recommended to use the object method without considering the number of objects.

To identify cells, you can use Range and Cells. Each has its advantages and disadvantages and can be used selectively.

Unlike Python programming, VBA programming requires the object type to be defined in advance, so its code execution speed will be faster than Python.

This is the end of this article about the use of loop code in VBA. For more related VBA loop content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!