iLearn Excel VBA: Cells and Ranges

Cells and Ranges . . . I’m lost already!

If you open up Excel you will see countless boxes. These boxes are called Cells. You can store many different kinds of variables within these cells including numbers, strings, and even dates. If you highlight a bunch of cells (click on a cell and while holding the left click down, drag your mouse to another cell), you just selected a Range of cells. The row numbers are on the left and the column numbers are denoted as letters across the top row. Now that definitions are out of the way, we can move on to cell manipulation.

Cell Manipulation

Let’s say we want to change the value in Cell A1 which is the top left most cell in the workbook to the value 42. Let’s look at the code:

Sub CellManipulation()
    Cells(1, 1) = 42
End Sub

Yup it’s that easy. The syntax is Cells(row number, column number) Now let’s try to make Cell A2 equal to 20 and then add the two cells together.

Sub CellManipulation()
    Cells(1, 1) = 42
    Cells(2, 1) = 20
    Cells(3, 1) = Cells(1, 1) + Cells(2, 1)
End Sub

Wowee, I think we almost did something noteworthy right there. Let’s not limit cell manipulation to just changing values, we can also change the font for a particular cell. Let’s make Cell A1 highlighted yellow, Cell A2 Times New Roman font, and Cell A3 size 20:

Sub CellManipulation()
    Cells(1, 1) = 42
    Cells(1, 1).Interior.ColorIndex = 6 'Yellow
    Cells(2, 1) = 20
    Cells(2, 1).Font.Name = "Times New Roman"
    Cells(3, 1) = Cells(1, 1) + Cells(2, 1)
    Cells(3, 1).Font.Size = 20
End Sub

But now everything is all messy and gross, let’s bring it back to normal by clearing everything:

Sub ClearEverything()
    Cells.Clear
End Sub

Now as you can see, we can do a lot with cell manipulation. Let’s see what we can do with ranges.

Range Manipulation

Now let’s assume that we have cells A1 through A10 filled with pseudo-random numbers between 1 and 100 and we want to see who passed, who failed, what the class average was, and what the highest score was. So we will change the font color to red for those who failed and green for those who passed. Try it on your own then have a look at the code:

Sub RangeExample()
    'For loop puts a random number (1-100) in the first 10 cells
    For i = 1 To 10
        Cells(i, 1) = Int((100 - 1 + 1) * Rnd + 1)
    Next i
    Cells(1, 2) = "Average"
    Cells(1, 3) = Application.Average(Range(Cells(1, 1), Cells(10, 1)))
    Cells(2, 2) = "Maximum"
    Cells(2, 3) = Application.Max(Range(Cells(1, 1), Cells(10, 1)))
    For j = 1 To 10
        If (Cells(j, 1) >= 60) Then
            Cells(j, 1).Font.ColorIndex = 4 'Green
        Else
            Cells(j, 1).Font.ColorIndex = 3 'Red
        End If
    Next j
End Sub

Lines 2-5 is just a fancy way to generate random numbers and you can ignore it for now. Line 7 finds the average of a range. The syntax for a range selection is Range(Cells(row number, column number), Cells(row number, column number) where the first cell is the top left most cell in your range and the second cell is the bottom right most cell in your range. This does not need to be just a row or column range, you can select multiple rows and columns. This is great for making charts.

With just this basic knowledge of cell manipulation and range manipulation, we can do almost anything involving calculations or custom formula solvers. It doesn’t look like people are that interested in Excel VBA, so if the people want more examples/tutorials I will make them, but for now I might be moving to something more interesting like Actionscript 3.0. ~Signing Off iAtoria.