Tip:
Highlight text to annotate it
X
In this lesson, we will discuss how you can use loops to interact with the Excel workbook environment.
One of VBA’s greatest strengths is that it is capable of interacting with the Excel workbook environment.
You can use loops to obtain data from many cells in a spreadsheet or output data to many cells in an spreadsheet.
For example, here is a spreadsheet that contains students’ test scores.
We will create a VBA macro that obtains the test scores, calculates the mean of those scores, and then outputs the mean two rows
below the last test score.
There are eight test scores in this worksheet.
Although we could create a macro that would calculate the mean of just these eight scores, it would be better if we designed the macro to be
general for any number of test scores that happen to be in the worksheet.
Our macro should work if there are 8, 80, or even 800 test scores in column B.
This task can be accomplished with the help of the Selection.End( ).Select statement.
This statement moves the active cell to the first cell or last cell in the current row or current column of a data region depending on what text
is placed inside the parentheses.
If xldown is placed inside the parentheses, the active cell will move to the last cell in the current column of the data region.
This statement acts like the Ctrl + down arrow shortcut in the Excel workbook environment.
If xlToRight is placed inside the parentheses, the active cell will move to the last cell in the current row of the data region.
This statement acts like the Ctrl + right arrow shortcut in the Excel workbook environment.
If xlup is placed inside the parentheses, the active cell will move to the first cell in the current column of the data region.
This statement acts like the Ctrl + up arrow shortcut in the Excel workbook environment.
If xlToLeft is placed inside the parentheses, the active cell will move to the first cell in the current row of the data region.
This statement acts like the Ctrl + left arrow shortcut in the Excel workbook environment.
Two other useful statements are: ActiveCell.Row and ActiveCell.Column
ActiveCell.Row determines the row number of the active cell.
ActiveCell.Column determines the column number of the active cell.
The number 1 is associated with column A. The number 2 is associated with column B. And so on.
Here is a macro that will calculate the mean test score regardless of the number of test scores in column B.
When this macro is run, the mean of all the test scores, which is 82, is placed in the cell that is two rows below the last score.
Let’s discuss how the macro works.
Five variables are declared.
The variable i is a counter variable used for the For loop.
The variable firstrow stores the row number where the first test score appears.
The variable lastrow stores the row number where the last test score appears.
The variable numscores stores the total number of test scores in the spreadsheet in column B.
The variable mean stores the value of the mean test score.
We first want to determine how many test scores are in column B of Sheet1.
In order to do this, the active cell is moved to where the first test score appears, which is cell B2 on Sheet1.
ActiveCell.Row obtains the row number of the active cell, which is 2, and assigns this number to the variable firstrow.
Next, we want to determine the row where the last test score appears.
Selection.End(xlDown).Select moves the active cell downward to the last test score in the column.
ActiveCell.Row obtains the row number of this cell, which is 9 in this case since there are eight scores in the column, and assigns this number
to the variable lastrow.
The difference between lastrow and firstrow is 7 in this case, but the total number of test scores is 8.
So the quantity lastrow – firstrow + 1 gives the total number of test scores in the column.
This value is assigned to numscores.
Now that we know how many test scores are in the column, we can calculate the mean of all these scores.
The active cell is moved back to B2 and the variable mean is assigned the value 0.
Since we know that we will need to add a specific number of scores to calculate the mean, we use a For loop.
The counter variable i starts at 1 and increases by 1 after each iteration of the loop since the default step size is +1.
Inside the loop, the value of the current active cell is added to mean, then the active cell is moved down one row using the
ActiveCell.Offset().Select statement.
When i exceeds numscores, the loop is terminated.
This means the two statements inside the loop will be executed numscores amount of times.
In this particular case, the loop will be executed eight times.
When the loop is terminated the active cell will end up one row below the last test score.
At this point, the variable mean stores the sum of all the test scores.
The mean value of the test scores is calculated by dividing the variable mean by numscores.
The active cell is moved down one row using the ActiveCell.Offset().Select statement and the mean is assigned to the new active cell, which
is two rows below the last test score.
This macro we created will work for any number of test scores.
Let’s add two more scores and rerun the macro.
We have added two additional scores, 70 and 74.
When we rerun the macro, we will get a new mean score that is two rows below 74.