Useful Excel Visual Basic Macros for Programmers

Excel VB Macro is exactly useful because it can do things in your workbooks for you, like manipulating cells and worksheets. Excel Visual Basic (VB) gives us a number of methods to interact with worksheets and cells, and I will cover some of the more intuitive methods here.
One of my favorite ways to interact with cells in a worksheet is rather direct. I like it because it is easy to double-check and conceptualize.
Let's look at the basic statement that is the second line in the following very short Excel macro.
Sub put_value_in_Cell
Worksheets("Sheet2").Cells(3, 7).Value = 1
End Sub

This statement assigns the Cell located at (3,7) in Sheet2 the value 1. That literally means that is you go to that sheet1 in your active Excel Workbook, you'll see a 1 in the cell located at (3,7).
What is this (3,7)? We are using index numbers for the column instead of the column-letters you might be accustomed to. Note: Index for cells begins at 1, not zero. E.g. there isn'tt a row zero. I'll re-type the last macro, but this time using variables that could make it more clear.


Sub put_value_in_Cell ()
my_row = 3 '
my_column = 7
my_workSheet = "Sheet2"
Worksheets(my_workSheet).Cells(my_row, my_column).Value = 1
End Sub

Using these index numbers inside loops can really be useful to get stuff done. For example, to go down the first column of our Excel worksheet and put a zero into the first 10 cells, you could do something like this:

Sub an_example()
For row_counter = 1 to 10
Worksheets("Sheet1").Cells(row_counter, 1).Value = 0
Next row_counter
End Sub

or generally...

Sub an_example()
For row_counter = 1 to 10
'Whatever you want your macro to do...Check or assign values, etc.
Next row_counter
End Sub

To compare the values of two cells, you can do this:

Sub an_example2()
For row_counter = 1 to 10
If Worksheets("Sheet1").Cells(1, 1).Value = _
Worksheets("Sheet1").Cells(1, 2).Value Then
'Whatever you want done if the cells are equal in value.
'Note the _ in the If statement is there because it allows
'a statement to span multiple lines so we can see it all once
without off the page like this line.
End if
Next row_counter
End Sub

To compare the values of two cells, you can do this:

It does not stop with the values of cells either. You may have noticed earlier that we used .Value after specifying a cell. However there are other properties and methods of cells that Excel VB provides that are really useful. For example the following statement that could be in a macro checks whether the cell contains a formula:

If Worksheets("Sheet1").Cells(theRow, theCol).HasFormula Then

There really are quite a number of things that may be accomplished with these techniques.