Excel Visual Basic (VBA) Tutorial - Part 4

Navigating Through Spreadsheets

Now that we know more about Visual Basic programming, we can return once more to the specifically Excel side of things. So far our sample macros have referenced ranges of cells, individual cell properties, active sheets and so on. The question is, what is the underlying Visual Basic model of a workbook, and how do we navigate our way through one in code?

The object at the highest level is the Application, which in this case is Excel itself. At this level the Application object contains the current Excel settings and options, the built-in worksheet functions and methods which return objects such as ActiveSheet, ActiveCell and so on. Very often there's no need to use the word 'Application' when you want to access some of these objects - ActiveSheet.Name and Application.ActiveSheet.Name will both return the name of the active worksheet.

The workbook object is at the next level down in the hierarchy, and this is used to represent a complete Excel workbook. Each workbook is part of the Workbooks collection, as was mentioned previously. To access a particular workbook you can either use the Workbooks collection and use the name or the index of the workbook. For example Workbooks(1).Name will give the name of the first workbook in the collection, or Workbooks("Sales.xls").Activate will activate the workbook named Sales.xls. Note that the Workbooks collection only contains those workbooks which are open, so your code will need to open any workbooks to operate on them. To add a new workbook you can use the Workbooks.Add method to create a new workbook and to add it to the workbooks collection. ActiveWorkbook will always reference the currently active workbook.

The workbook object contains many other objects in turn, including WorkSheet, DialogSheet, Chart and so on. The Worksheet contains may objects, such as List, PivotTable, Drawing, Label, Range and so on. As we have seen previously in our sample macros, ActiveSheet references the active worksheet. To activate a particular worksheet in a workbook you can use the Worksheets collection as follows: Worksheets("Sheet1").Activate or else you can refer to the index into the collection - though referencing by name makes your code easier to understand and maintain.

To add a new worksheet to a workbook you can use the Add method, and the Delete method can be used to delete a sheet. Hiding and unhiding sheets can also be achieved in code, in this case by setting the Visible property of the sheet to True or False.

Having worked our way down the hierarchy from the Application down to the worksheet, we are ready to access individual cells and groups of cells in a spreadsheet. A range can consist of a single cell, a group of cells, a row, a column or even a block of non-contiguous cells. The simplest method of accessing a cell is to reference it directly (here we place the value of 3.14 into cell A1):

	ActiveSheet.Range("A1").Value=3.14

We can also assign workbook functions to cells by using the Formula property. For example to assign the SUM(B1:B10) function to cell A2, we can execute the following in a macro:

	ActiveSheet.Range("A2").Formula= "=sum(B1:B10)"

If you only wish to address a single cell then you can use the Cells method instead of the Range. This uses a row and column index to point to individual cells, so that cell A1 is referenced as 1,1 and B1 as 1,2. To insert the value 23 into cell C4, we would execute the following:

	ActiveSheet.Cells(4,3).Value=24

In many cases the Cells method is more convenient to use than Range because it means you can easily set up counters and loops to cycle through blocks of cells. For example, if we wished to go through all the cells in the range A1:D10 and to set those which are negative to zero, we could code a macro using a nested For.. Next loop to count through the rows and columns:

Sub MakeZero()
Dim Row As Integer, Col As Integer
    
    For Col = 1 To 4
        For Row = 1 To 10
            If ActiveSheet.Cells(Row, Col) < 0 Then ActiveSheet.Cells(Row, Col) = 0
        Next Row
    Next Col
    
End Sub

The Excel object model is obviously a lot more complex than the sub-set covered here. The on-line help file is actually pretty good at displaying and navigating through the object hierarchy, displaying the methods and properties of the different objects and collections that make up Excel. And when that fails very often the quickest solution is to simply record a macro and then to use the editor to find out what object, methods and properties have been used.

Conclusion

As will be appreciated, programming macros in Excel can be as complicated or as simple as your requirements. In this series of articles we've only scratched the surface of tools and techniques which are available. Major topics such as error handling, interfacing to other Office applications and the use of forms are outside of the scope of the series, but are worthy of further examination at some later point.

The Visual Basic environment is sufficiently rich in functionality that even quite complex applications can be coded in it. Hopefully, this brief over-view of the language and the environment has been enough to help you get started on coding or given you some pointers in having to support your users' macro programming efforts.

Return to home page