1. Spreadsheet Components coded in VB

Learning Outcomes

On completion of this chapter you will be familiar with how to manipulate the most commonly used components of a spreadsheet using VB code.

Download pdf version

Introduction

All of the code you have done so far could be run inside Access, Word and PowerPoint. This is because we have concentrated on programming code and techniques alone without reference to the application inside which the code would be run. From now on you are going to be using VB code in order to manipulate spreadsheet components that up to now you have manipulated manually. These manipulations can be as simple as moving from one worksheet to another by clicking on it or as complex as adding a number of new worksheets to the workbook and naming them. It can also involve adding or deleting columns or rows, entering data into ranges and formatting ranges. As we have discovered in Chapter 13 any manipulation you can do manually, can also be done using code.

Workbook, ThisWorkbook

Go to top

The Workbook object represents an actual Workbook. You cannot work with the Workbook itself, you have to create a variable that points to it and work with that variable.

Once you create such a variable it can be used to point to any workbook that you have opened. It cannot point to a workbook that is stored on the disk. Normally, however, it is used to point to the workbook that contains the code itself. This workbook has a special name – ThisWorkbook. In all of our examples here we shall be dealing only with the workbook which contains the code and thus we shall be using ThisWorkbook in almost all of our code.

When dealing with Forms and the controls that could be placed on them we discovered that the forms and their controls had properties and that we could manipulate those controls by either reading or altering the values of those properties.

Similarly all of our worksheet components also have properties. Like the form controls they have more properties than we will ever use and so we shall be looking at a very small number of those properties. Some of the properties of a Workbook include Name, FullName and Path.

As well as properties, a Workbook also has Collections. A Collection is an object in its own right and has its own properties. As its name implies a Collection is an object that can deal with a number of items of the same type. In a non-programming environment a school could be seen as an object. This School object would have the following collections: Teachers, Students, Classrooms, Buildings etc. A Vehicle object would have a Wheels collection.

Of the collections that we will be using in this course we shall be using two of their properties: Count and Add. If a school had 50 teachers working in it then we could have a line of code that would be:

Kambubu.Teachers.Count = 50

If we wanted to select the first teacher we would use:

Kambubu.Teachers(1)=”Mr Wagi”

Finally if we wanted to add a new teacher we would use:

Kambubu.Teachers.Add (“Joe Bloggs”)

Listing 14‑1 below is a short example of how to manipulate a workbook and its properties.

Listing 14‑1

1	Sub workBookExperiment()
2	Dim wb As Workbook
3	Dim intcounter As Integer
4	Set wb = ThisWorkbook
5	Debug.Print "The name of the workbook is ", wb.Name
6	Debug.Print "The number of worksheets is ", wb.Worksheets.Count
7	Debug.Print "The names of the worksheets are"
8	For intcounter = 1 To wb.Worksheets.Count
9	Debug.Print wb.Worksheets(intcounter).Name
10	Next
11	End Sub

In lines 2 and 3 we declare our varibles: our Workbook variable in line 2 and a loop counter in Line 3.

In line 4 we specify that the variable wb is to point to the workbook that we are working with.

Line 5 prints out the string constant “The name of the workbook is “ followed by the Name property of the current workbook.

Line 6, as well as printing a string constant, also prints the Count property of the Worksheets collection of the current workbook.

Finally lines 8, 9 and 10 is a For loop that prints out the name of each Worksheet object in the Worksheets collection.

A sample output of the programme is shown below.

The name of the workbook is Sample Sheet for VB training.xlsm

The number of worksheets is 7

The names of the worksheets are

Employees

Payroll

Timesheets

Macro Recording examples

Workers List

Stock

Sales

Listing 14‑2

1	Sub addManySheets()
2	Dim wb As Workbook
3	Set wb = ThisWorkbook
4	wb.Worksheets.Add Count:=5
5	End Sub

The code above uses the Add method in order to add new worksheets to the workbook. The Add method on its own will simply add one worksheet but in this case we also have the argument Count:=5. This tells it to add five worksheets instead of one.

Listing 14‑3

1	Sub addSheetToWorkbook()
2	Dim wb As Workbook
3	Set wb = ThisWorkbook
4	wb.Worksheets.Add
5	wb.ActiveSheet.Name = "New Sheet"
6	End Sub

Listing 14‑3 above adds only a single sheet at line 4. Once a new sheet is added to the workbook that sheet automatically becomes the active sheet. Because of this line 5 can change the Name property of the new sheet to “New Sheet”

Listing 14‑4

1	Sub showNames()
2	Dim wb As Workbook
3	Set wb = ThisWorkbook
4	Debug.Print wb.Name
5	Debug.Print wb.FullName
6	Debug.Print wb.Path
7	End Sub

Listing 14‑4 above simply prints out the Name, FullName and Path properties of the current workbook. The output is shown below. By examining this we can see that the property FullName is simply the Path property with the Name property added on to it.

Sample Sheet for VB training.xlsm

D:\VB for apps\Sample Sheet for VB training.xlsm

D:\VB for apps

Activesheet, Cells, Selection, Range, Rows, Columns

Go to top

Most of the work done in a spreadsheet is on the worksheets themselves. It is here that we add, modify, read and remove data. Even though a workbook may have up to 255 worksheets we can only work on one sheet at a time, in exactly the same way that we can only read a book one page at a time. The sheet that we are working on in a workbook is referred to as the active sheet. In VB for Applications the active sheet is pointed to by ActiveSheet. An example of how to use it is shown below in Listing 14‑5.

Listing 14‑5

1	Sub activeSheetDemo1()
2	Dim wb As Workbook
3	Set wb = ThisWorkbook
4	wb.Worksheets("New Sheet").Activate
5	With ActiveSheet
6	.Cells(1, 1) = "Hello everyone"
7	.Cells(1, 2) = "This is the new sheet that I have created"
8	End With
9	End Sub

The code in lines 2 and 3 should be familiar by now so we begin with line 4. This line could be translated into English as search the worksheets in the current workbook and make the one named “New Sheet” the active one.

Lines 5 – 8 is a With..End With construct. We have not met this construct before and thus we need to spend some time explaining what it is all about. Earlier we have mentioned that all spreadsheet objects such as Worksheet, Workbook etc have properties. Many of those properties have their own properties, and those properties can have their own properties! Thus you could easily end up with a piece of code that would look as follows:

Listing 14‑6

1	Application.Workbooks(1).Worksheets(2).Cells(2,3) = 12.
2	Application.Workbooks(1).Worksheets(2).Cells(2,4) = 14.
3	Application.Workbooks(1).Worksheets(2).Cells(2,5) = 16.

The first line of this code inserts the value 12 into the cell C2 of the second worksheet, of the first workbook that is opened in Excel. The second and third lines insert the values 14 and 16 into the cells D2 and E2 of the same worksheet.

These are very long lines of code to be writing, especially if there is a lot of data to be inserted. Within Visual Basic we can avoid this tedious and messy construction using the With construct. This would alter our code to the following:

Listing 14‑7

1	With Application.Workbooks(1).Worksheets(2)
2	.Cells(2,3) = 12
3	.Cells(2,4) = 14
4	.Cells(2,5) = 16
5	End With

Here we only need to write the long line Application.Workbooks(1).Worksheets(2)only once after the keyword With. Between this line and the line End With, if we meet a line that begins with a period then VB presumes that the rest of that line should be appended to the piece of code that follows the keyword With. Thus as far as Visual Basic is concerned Listing 14‑6 is identical to Listing 14‑7.

In the example below we look at the Range and Selection objects as well as at the properties Rows and Columns properties

Listing 14‑8

1	Sub activeSheetDemo2()
2	Dim wb As Workbook
3	Dim intRow As Integer
4	Dim intCol As Integer
5	Set wb = ThisWorkbook
6	wb.Worksheets("New Sheet").Activate
7	With ActiveSheet
8	.Range(Cells(3, 4), Cells(12, 15)).Select
9	For intRow = 1 To Selection.Rows.Count
10	For intCol = 1 To Selection.Columns.Count
11	*Selection.Cells(intRow, intCol) = intRow intCol**
12	Next
13	Next
14	End With
15	End Sub

This application creates a multiplication table in the worksheet named “New Sheet”. The first seven lines of the code should be familiar to you by now, so we begin with line 8.

This uses the Range property of the active sheet. This property is similar to the Range that we refer to when doing normal spreadsheeting. Both versions of the word simply mean a rectangular group of cells. In normal spreadsheeting we refer to a range as D3:O12 – in other words we specify the cell at one corner of the range and the cell at the diagonally opposite corner. In VB for Applications we can refer to a range in the same way, but this limits us to always using the same range. On the other hand if we refer to it as we do in line 8 then we can extend or alter our range as we wish.

In line 8 the Range has two parameters: Cells(3,4) and Cells(12,15). Cells(3, 4) means the cell where row 3 meets column 4 – in other words cell D3. Similarly Cells(12, 15) means the cell where row 12 meets column 15 – i.e. cell O12. Thus line 8 is equivalent to saying select the range D3:O12 in the active sheet. In the worksheet it has the same effect as holding the mouse button down over D3 and dragging as far as O12.

Line 9 is the start of a For loop. The lowest range for the loop is 1 and the upper range is determined by Selection.Rows.Count. The selection referred to here is the selected range D3:O12. As the selection spans rows 3 to 12 then the number of rows in it is 10.

Line 10 is the start of a nested For loop inside the first one. The lowest range for this is 1 and the upper range is determined by Selection.Columns.Count. This is like the previous one except that this time it is the number of columns in the selection that it gives us. Since the selection spans column 4 to 15 inclusive the value of Count in this case is 12.

Line 11 is where the processing of the loops occur. The values of intRow and intCol are multiplied and inserted into the cell of the selection determined by the values of the two counters. As an example, the first time through both loops both intRow and intCol will have a value of 1 each. Thus the value to be entered into the cell will be 1 multiplied by 1, which is one. The cell it will be entered into will be the cell in the first row and first column of the selection which is cell D3. The inside loop will not increment intCol to 2 while intRow stays at 1. Thus the value 2 will be inserted into the cell of the selection where the first row intersects the second column, i.e. cell E3. Figure 14‑1 shows the result of running the programme.

Figure 14‑1

Practice

Go to top

1. Copy Listing 14‑1 into your programme area and run it. Check that the correct name of the workbook, the correct number of worksheets and the correct names of all of the sheets are printed out.
Add a few extra sheets and rename a few more. Now run the programme again and check that the new output reflects the change you have made to the workbook

2. Copy Listing 14‑2 into the programme area and run it. Check that it has added five extra worksheets to the workbook.

3. Modify Listing 14‑3 so that it adds four new worksheets in turn and names them “Cash at Bank”, “Accounts Receivable”, “Accounts Payable”, “Stock”

4. Copy listing Listing 14‑5 into your programme area and run it. Check that it inserts the two text items into the first two cells of row 1.
Once it is performing correctly, modify it so that different text or numeric values are entered into other cells. You can be as inventive as you wish.

5. Modify Listing 13‑1 so that instead of using the two steps of selecting a cell and then using the FormulaR1C1 to enter data into the cells it uses the ActiveSheet.Cells(row, col) method.