Referencing ranges in Visual Basic
This Microsoft Excel blog picks up from where we left off in an earlier blog that introduced the Visual Basic (VB) editor and how to run and save Macro script.
We will now follow on by learning how to reference a range of cells on the Excel worksheet in a VB workbook. We first of all begin be opening our saved VB script which has a .xlsm extension. When opening, we initially see only the worksheet itself. To get to the VB Workbook, simply go to the Developer ribbon and in Code panel click on Visual Basic; the saved book will appear (Excel may request that you enable Macros - agree to this).
If we would like to create a new VB workbook, simply follow the steps to open a book as in the previous blog (linked above).
In the image above we can see that we have opened the VB book and the previous script is still active. To disable this script, place an apostrophe ' in front of the line of code. This will turn it into a comment (coloured green). It is useful to to turn unwanted scripts into comments (to comment-out) so that a record of work can be maintained and possibly reused later on. In this case, we have also added an actual comment to let future workbook users know what's been going on.
We then come to the concept of cell ranges. Cell ranges are simply a set of one or more cells with the range determined by the coordinates of the top left and bottom right cell. For example, the range B3:D5 would include cells B3, B4, B5, D3, D4 and D5 as can be seen in the example here:
First of all, we need to write a VB script that directs the Workbook to a specific Sheet and then the Range. We can then use other commands such as Value to determine the contents of this range of cells. As we can see below the formula follows the notation: Thisworkbook.Sheets("Sheet1).Range("B3:D5").Value=100
Let's look more closely as this script.
ThisWorkbook - references the the workbook that we are writing in now and is followed by a period. Periods are used in the notation enable referencing of Objects.
Sheets - references the sheet we would like to work on. As such it is essentially and object within and object (and so on).
Range - as with Sheets, Range is a further object class (and the subject of this blog). The references of both Sheets and Range use the ("...") notation to tell Excel that this is not a variable.
Value - in this case is set as a simple integer variable and as such has no encasing notation.
When we hit the Run Sub button (green 'Play' button) we see that Excel has populated the defined range with the specified values. It has also over-written any previous values.
This is simply an introduction to some VB functionality. To see how your process can be improved with VB, contact our experts.
