WRITING TO CELLS IN EXCEL FROM VBA
Our primary goal is to build financial models that allow the user to input preferences in Excel that are then used by VBA with results
outputted back into Excel. Therefore, we need to understand how VBA communicates with Excel. The following code demonstrates
several ways we can output results from VBA and into a single cell in the Excel spreadsheet.
' You can write comments by using the apostrophe
' Notice that comments are written in the color green
Cells(1, 1) = "Welcome MSQF Students!"
Cells(2, 1) = "Good Luck!"
Range("A3") = "and work hard!"
ActiveCell.Value = 1
ActiveCell.Offset(0, 1).Value = 2
ActiveCell.Offset(1, 0) = 3
ActiveCell.Offset(1, 1) = 4
Cells(i,j) refers to the row i and column j of the active worksheet in the active workbook.
Alternatively, we can use the more commonly used letter/number referencing system to indicate the desired cell using the
Range function as demonstrated in line (7). I tend to find this a less desirable approach since I often want VBA to change the
cell reference. We will see examples of dynamically changing cell references later.
We can use the OFFSET(i,j) function to instruct VBA to move from the active cell, B5 in this case, i cells in the downward
and j cells to the right.
For example, line (10) references the active cell B5 and then moves one cell down and zero cell to the
right. As a result, the value ‘3’ is place in cell B6.