TOP
# VBA-Lesson 8.2. Loops

### Articles on the topic:

VBA-Lesson 8.1. Loops
VBA-Lesson 9. Procedures and functions

To practice what we've just learned, we'll walk through the process of creating a macro to paint a 10x10 checkerboard (red and black) starting from the currently active cell. Let's take a look:

Next is the first step of the exercise:

Sub loops_exercise() Const NB_CELLS As Integer = 10 'The number of cells we want to paint '... End Sub

Let's start by adding a **FOR** loop to black out column A (Constant ** NB_CELLS ** equals 10). See lower:

Try to make this cycle yourself.

Now you can check with the following code:

Sub loops_exercise() Const NB_CELLS As Integer = 10 'The number of cells we want to paint For r = 1 To NB_CELLS 'r => row number Cells(r, 1).Interior.Color = RGB(0, 0, 0) 'Black Next End Sub

The next step colors each subsequent cell red, using an **IF** statement (based on whether the row number is even or odd). See lower:

The code for solving this subtask is as follows:

Sub loops_exercise() Const NB_CELLS As Integer = 10 'The number of cells we want to paint For r = 1 To NB_CELLS 'r => row number If r Mod 2 = 0 Then 'Mod => is the remainder from division Cells(r, 1).Interior.Color = RGB(200, 0, 0) 'Red Else Cells(r, 1).Interior.Color = RGB(0, 0, 0) 'Black End If Next End Sub

The condition **If r Mod 2 = 0** means: if the remainder of dividing **r** by 2 is 0...

Only the row number that is even has a remainder of 0 when divided by 2:

How to make another loop that would execute the loop we have already written for 10 columns. See lower:

The code for solving this subtask is as follows:

Sub loops_exercise() Const NB_CELLS As Integer = 10 '10x10 checkerboard of cells For r = 1 To NB_CELLS 'r => Row number For c = 1 To NB_CELLS 'c => Column number If r Mod 2 = 0 Then Cells(r, c).Interior.Color = RGB(200, 0, 0) 'Red Else Cells(r, c).Interior.Color = RGB(0, 0, 0) 'Black End If Next Next End Sub

Now the second cycle is inserted into the first.

To achieve the following result...

Replace:

If r Mod 2 = 0 Then

At:

If (r + c) Mod 2 = 0 Then

All that remains to be done is to change the code so that the checkerboard is created starting from the selected cell (not A1). See below:

For this, we will write the following code:Sub loops_exercise() Const NB_CELLS As Integer = 10 '10x10 checkerboard of cells Dim offset_row As Integer, offset_col As Integer ' => add 2 variables 'Offset (rows) starting from the first cell = row number of the current active cell - 1 offset_row = ActiveCell.Row - 1 'Offset (columns) starting from the first cell = column number of the current active cell - 1 offset_col = ActiveCell.Column - 1 For r = 1 To NB_CELLS 'Row number For c = 1 To NB_CELLS 'Column number If (r + c) Mod 2 = 0 Then 'Cell(row number + additional row offset, column number + 'additional column offset) Cells(r + offset_row, c + offset_col).Interior.Color = RGB(200, 0, 0) 'Red Else Cells(r + offset_row, c + offset_col).Interior.Color = RGB(0, 0, 0) 'Black End If Next Next End Sub

- 1. What is VBA, basic concepts.
- 2. Debugger (Debugger)
- 3. Work with sheets (Sheets)
- 4. Working with cells (Ranges)
- 5. Properties (Properties)
- 6.1. Data types (Variables)
- 6.2. Data Types (Continued)
- 7.1. Conditions (Conditions)
- 7.2. Terms (Continued)
- 8.1. Loops (Loops)
- 8.2. Cycles (Continued)
- 9. Procedures and functions
- 10. Dialog windows
- 11.1. Workbook events
- 11.2. Worksheet events
- 12.1. Custom forms
- 12.2. Controls (Controls)
- 12.3. Control elements (Continued)
- 12.4. Control elements (Exercises)
- 13.1. Arrays (Arrays)
- 13.2. Arrays (Continued)
- 13.3. Arrays (Exercises)
- 14.1. Using Excel functions
- 14.2. Creating a custom function

- 1. SQL language, basic concepts.
- 2. Data selection (SELECT)
- 3. Data sorting (ORDER BY)
- 4. Data filtering (WHERE)
- 5. Use of metacharacters (LIKE)
- 6. Calculation (calculation) fields
- 7. Data processing functions
- 8. Data grouping (GROUP BY)
- 9. Sub-requests
- 10. Combination of tables (INNER JOIN)
- 11. OUTER JOIN
- 12. Combined requests (UNION)
- 13. Adding data (INSERT INTO)
- 14. Creating tables (CREATE TABLE)

- Import of currency rates from the NBU website
- Sum written in English
- Unpivottables (Unpivot)
- Function Google Translate
- Camel Case Function
- Snake Case Function
- Kebab Case Function
- Break Case Function
- Sentence Case Function
- Title Case Function
- Fuzzy Lookup Function
- Function GETSUBSTR
- YouLibreCalc extension for LO Calc