VBA-Lesson 13.3. Using Arrays (Exercises)

To practice using arrays, we'll create our own version of the macro we used to demonstrate the speed advantage of arrays, step by step...

The following is the starting point for this exercise (you'll see that the dataset has been reduced to 1000 rows):

Here you can download an Excel file with an example: arrays_exercise.xls

Purpose of the exercise: the procedure should process the data in the dataset using a loop and count the number of YES or NO responses for each year and for each customer number (either YES or NO, depending on the user's choice) and enter this amount in the indicated cell of the worksheet.

Complete the following macro to save the data from the "DS" worksheet to an array:

Sub actualize()
      Dim last_row As Integer
     
      'The last line of the data set
      '...
        
      "Saving a set of data in a dynamic array
      Dim array_db()
      '...
        
End Sub

Here is one example of a solution:

Sub actualize()
      Dim last_row As Integer
     
      'The last line of the database
      last_row = Sheets("DS").Range("A1").End(xlDown).Row

      "Saving a set of data in a dynamic array
      Dim array_db()
      ReDim array_db(last_row - 2, 2)
     
      For row_number = 2 To last_row
          array_db(row_number - 2, 0) = Sheets("DS").Range("A" & row_number)
          array_db(row_number - 2, 1) = Sheets("DS").Range("B" & row_number)
          array_db(row_number - 2, 2) = Sheets("DS").Range("C" & row_number)
      Next
End Sub

This basically repeats what we did in the previous lesson...

But now we need to modify our macro by adding the following actions:

Define user choice ("YES" or "NO");

Count the number of answers "YES" or "NO" in the data set, in order to determine the size of the array (Redim);

We save only those rows from the data set that contain the answers "YES" or "NO" in the array (this means that we do not need to save data from the third column).

Here is one example of a solution:

Sub actualize()
      Dim last_row As Integer, search_value As String, insert_row As Integer, value_yes_no As String, rows_number As Integer
     
      'The last line of the database
      last_row = Sheets("DS").Range("A1").End(xlDown).Row

      'Search for value (YES or NO)
      If Sheets("RES").OptionButton_yes.Value = True Then
          search_value = "YES"
      Else
          search_value = "NO"
      End If
     
      The number of YES or NO answers
      rows_number = WorksheetFunction.CountIf(Sheets("DS").Range("C2:C" & last_row), search_value)
     
      'Saving a set of data into an array
      Dim array_db()
      ReDim array_db(rows_number - 1, 1)

      insert_row = 0
     
      For row_number = 2 To last_row
          value_yes_no = Sheets("DS").Range("C" & row_number)
          If value_yes_no = search_value Then
              array_db(insert_row, 0) = Sheets("DS").Range("A" & row_number)
              array_db(insert_row, 1) = Sheets("DS").Range("B" & row_number)
              insert_row = insert_row + 1
          End If
      Next
End Sub

The user's choice of search is determined at the beginning of the procedure by the following code:

'Search for value (YES or NO)
If Sheets("RES").OptionButton_yes.Value = True Then
      search_value = "YES"
Else
      search_value = "NO"
End If

We will use the CountIF function to determine the number of "YES" or "NO" answers:

The number of YES or NO answers
rows_number = WorksheetFunction.CountIf(Sheets("DS").Range("C2:C" & last_row), search_value)

The array has been resized to fit the number of YES or NO responses and reduced to two columns:

ReDim array_db(rows_number - 1, 1)

This data will now be stored in the array when its third column matches the user's selection:

'Inserting a number into an array
insert_row = 0

'Data set processing
For row_number = 2 To last_row
      'The value of column C (YES or NO)
      value_yes_no = Sheets("DS").Range("C" & row_number)
      'If the value matches the user's choice, the string is stored in the array
      If value_yes_no = search_value Then
          'Saving the value to column A
          array_db(insert_row, 0) = Sheets("DS").Range("A" & row_number)
          'Saving value to column B
          array_db(insert_row, 1) = Sheets("DS").Range("B" & row_number)
          'One row has been saved => the insertion number in the array is increased by 1
          insert_row = insert_row + 1
      End If
Next

Our array contains only the data we are interested in.

All that remains to be done is:

Process each element of the table in the "RES" worksheet using 2 loops (same idea as on the right with the checkerboard);

Insert the total number of occurrences on this sheet for each year by each customer number for each cell.

Here is one example of a solution:

'Number of "YES"/"NO" responses
For no_years = 2011 To 2026
      For no_client = 1 To 30
          counter = 0
          For i = 0 To UBound(array_db)
              If Year(array_db(i, 0)) = no_years And array_db(i, 1) = no_client Then
                  counter = counter + 1
              End If
          Next
          Cells(no_years - 2009, no_client + 1) = counter
      Next
Next

Solved problem with comments explaining it in detail:

'Loop for each row
For no_years = 2011 To 2026
      'Loop for each column
      For no_client = 1 To 30
          'Counter reset
          counter = 0
          'Array processing
          For i = 0 To UBound(array_db)
              Checking that the row in the table corresponds to the year and the number of the client
              If Year(array_db(i, 0)) = no_years And array_db(i, 1) = no_client Then
                  If the year and customer number match, the counter increases by 1
                  counter = counter + 1
              End If
          Next
          After processing the array, the result is entered in the corresponding cell
          Cells(no_years - 2009, no_client + 1) = counter
      Next
Next

And finally, the code for our entire macro:

Sub actualize()
      Dim last_row As Integer, search_value As String, insert_row As Integer, value_yes_no As String, rows_number As Integer, counter As Integer
     
      'Remove content
      Range("B2:AE17").ClearContents
     
      'The last row in the data set
      last_row = Sheets("DS").Range("A1").End(xlDown).Row

      'Search for value (YES or NO)
      If Sheets("RES").OptionButton_yes.Value = True Then
          search_value = "YES"
      Else
          search_value = "NO"
      End If
     
      The number of YES or NO answers
      rows_number = WorksheetFunction.CountIf(Sheets("DS").Range("C2:C" & last_row), search_value)
     
      "Saving values in an array."
      Dim array_db()
      ReDim array_db(rows_number - 1, 1)

      insert_row = 0
     
      For row_number = 2 To last_row
          value_yes_no = Sheets("DS").Range("C" & row_number)
          If value_yes_no = search_value Then
              array_db(insert_row, 0) = Sheets("DS").Range("A" & row_number)
              array_db(insert_row, 1) = Sheets("DS").Range("B" & row_number)
              insert_row = insert_row + 1
          End If
      Next
     
      Counting YES or NO answers
      For no_years = 2011 To 2026
          For no_client = 1 To 30
              counter = 0
              For i = 0 To UBound(array_db)
                  If Year(array_db(i, 0)) = no_years And array_db(i, 1) = no_client Then
                      counter = counter + 1
                  End If
              Next
              Cells(no_years - 2009, no_client + 1) = counter
          Next
      Next
End Sub

Here you can download an Excel file with an example: arrays_exercise_completed.xls

Articles on the topic: