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
