Excel 2010 VBA Lesson 20: The Workbook Object

 [Lesson 19]<<[Table of Contents]>>[Lesson 21]

In this lesson, we shall learn about the Workbook object. The Workbook object lies at the top of the hierarchy of the Excel 2010 VBA  objects. We will deal with properties and methods associated the Workbook object.

20.1 The Workbook Properties.

When we write Excel 2010 VBA  code involving the Workbook object, we use Workbooks.

The reason is that we are dealing with a collection of workbooks most of the time, so using Workbooks enables us to manipulate multiple workbooks at the same time.When will deal with multiple workbooks, we can use indices to denote different workbooks that are open, using the syntax Workbooks (i), where i is an index. For example, Workbooks (1) denotes Workbook1, Workbooks (2) denotes Workbook2 and more.




Workbooks have a number of properties. Some of the common properties are Name, Path and FullName Let’s look at the following example:

Example 20.1

Private Sub CommandButton1_Click()
 MsgBox Workbooks(1).Name
End Sub

The program will cause a message dialog box to pop up and displays the first workbook name, i.e. Book1 as shown in Figure 20.1 below:

vba2010_figure20.1

Figure 20.1

If we have only one open workbook, we can also use the syntax ThisWorkbook in place of Workbook (1), as follows:

Private Sub CommandButton1_Click ()
 MsgBox ThisWorkbook.Name
End Sub

Example 20.2

Private Sub CommandButton1_Click ()
 MsgBox ThisWorkbook.Path
End Sub

Or you can use the following code:

Private Sub CommandButton1Click ()
 MsgBox Workbooks (“Book1”).Path
End Sub




The Output is shown in Figure 20.2

vba2010_figure20.2

Figure 20.2

Example 20.3

This example will display the path and name of the opened workbook. The code is:

Private Sub CommandButton1_Click ()
 MsgBox ThisWorkbook.FullName
End Sub

Or

Private Sub CommandButton1Click()
 MsgBox Workbooks(“Book1”).Fullname
End Sub

The output is shown in Figure 20.3.

vba2010_figure20.3

Figure 20.3



20.2 The Workbook Methods

There are a number of methods associated with the workbook object. These methods are Save, SaveAs, Open, Close and more.

Example 20.4

In this example, when the user clicks on the command button, it will open up a dialog box and ask the user to specify a path and type in the file name, and then click the save button, not unlike the standard windows SaveAs dialog, as shown in Figure 20.4.

Private Sub CommandButton1_Click()
 fName = Application.GetSaveAsFilename
 ThisWorkbook.SaveAs Filename:=fName
End Sub
vba2010_figure20.4

Figure 20.4

Another method associated with the workbook object is open. The syntax is
Workbooks.Open (“File Name”)

Example 20.5

In this example, when the user click on the command button, it wil open the file book1.xls under the path C:\Users\Toshiba\Documents\Liew Folder\VBA\vba2010\

Private Sub CommandButton1_Click()
 Workbooks.Open ("C:\Users\Toshiba\Documents\Liew Folder\VBA\vba2010\book1.xlsx")
End Sub

The close method is the command that closes a workbook. The syntax is

Workbooks (i).Close

Example 20.6

In this example, when the user clicks the command button, it will close Workbooks (1).

Private Sub CommandButton1_Click()
 Workbooks (1).Close
End Sub



 [Lesson 19]<<[Table of Contents]>>[Lesson 21]

Excel 2010 VBA Lesson 19: The Worksheet Object

 [Lesson 18]<<[Table of Contents]>>[Lesson 20]

19.1 The Worksheet Properties in Excel 2010 VBA 

Similar to the Range Object, the Worksheet has its own set of properties and methods. When we write Excel 2010 VBA  code involving the Worksheet object, we use Worksheets.

Some of the common properties of the worksheet are name, count, cells, columns, rows and columnWidth.

Example 19.1

Private Sub CommandButton1_Click()
 MsgBox Worksheets(1).Name
End Sub




Running the code will produce a pop-up dialog that displays the worksheet name as sheet 1, as shown in Figure 19.1.

Figure 19.1

The count property returns the number of worksheets in an opened workbook.

Example 19.2

Private Sub CommandButton1_Click()
 MsgBox Worksheets.Count
End Sub


Figure 19.2: The runtime screen



Example 19.3

The count property in this example will return the number of columns in the worksheet.

Private Sub CommandButton1_Click()
 MsgBox Worksheets(1).Columns.Count
End Sub

The output is shown below:

Figure 19.3

Example 19.4

The count property in this example will return the number of rows in the worksheet.

Private Sub CommandButton1_Click()
 MsgBox Worksheets(1).Rows.Count
End Sub

Figure 19.4

19.2 The Worksheet Methods

Some of the worksheet methods are add, delete, select, SaveAs, copy, paste and more.

Example 19.5

In this example, when the user clicks the first command button, it will add a new sheet to the workbook. When the user clicks the second command button, it will delete the new worksheet that has been added earlier.

Private Sub CommandButton1_Click()
 Worksheets. Add
End Sub

Private Sub CommandButton2_Click()
 Worksheets(1).Delete
End Sub




Example 19.6

The select method associated with worksheet lets the user select a particular worksheet. In this example, worksheet 2 will be selected.

Private Sub CommandButton1_Click()
‘Worksheet 2 will be selected
 Worksheets(2).Select
End Sub

The select method can also be used together with the Worksheet’s properties Cells, Columns, and Rows as shown in the following examples.

Example 19.7

Private Sub CommandButton1_Click()
‘Cell A1 will be selected
 Worksheets (1).Cells (1).Select
End Sub

Example 19.8

Private Sub CommandButton1_Click()
‘Column 1 will be selected
 Worksheets (1).Columns (1).Select
End Sub

Example 19.9

Private Sub CommandButton1_Click()
‘Row 1 will be selected
 Worksheets (1).Rows (1).Select
End Sub

Excel VBA 2010 also allows us to write code for copy and paste. Let’s look at the following Example:

 Example 19.10

Private Sub CommandButton1_Click()
‘To copy the content of a cell 1
 Worksheets(1).Cells(1).Select
 Selection.Copy
End Sub

Private Sub CommandButton2_Click()
‘To paste the content of cell 1 to cell 2
 Worksheets(1).Cells(2).Select
 ActiveSheet.Paste
End Sub




 [Lesson 18]<<[Table of Contents]>>[Lesson 20]

Excel 2010 VBA Lesson 18: The Range Object

 [Lesson 17]<<[Table of Contents]>>[Lesson 19]

The range is one of the most important and most commonly used Excel 2010  VBA  objects. In fact, we have dealt with the Range object in previous lessons.

18.1 The Select Method

The Range object contains two arguments that specify a selected area on the spreadsheet. The syntax is

Range(starting_cell,Ending_ Cell)

For example, to select the range from cell A1 to C6, the syntax is

Range(“A1:C6″).Select

where select is a method of the Range object.



Example 18.1

Private Sub CommandButton1_Click()
 Range(“A1:C6″).Select
End Sub

18.2 The Columns Property

The columns property of the Range object is to select certain columns in the particular range specified by the Range object.

The syntax is

Range(starting_cell,Ending_ Cell).Columns(i).Select

Example 18.2

This example select column C in the range A1 to C6

Private Sub CommandButton2_Click()
 Range(“A1:C6″).Columns(3).Select
End Sub

You can also use Cells(1,1) to Cells(6,3) instead of A1:C6, the syntax is

Range(Cells(1,1),Cells(6,3)).Columns(3).Select




The output is as shown in Figure 18.1

excelvba2010_figure18.1

                                         Figure 18.1

18.3 Using With Range……End With

You can also format font the cells in a particular column in terms of type, color, bold, italic, underlined and size using the With Range…..End With Structure. It can also be used to format other Range properties like the background color. Using With Range….End With structure can save time and make the code cleaner.

Example 18.3

Private Sub CommandButton1_Click()
With Range(“A1:C6″).Columns(2)
.Font.ColorIndex = 3
.Font.Bold = True
.Font.Italic = True
.Font.Underline = True
.Font.Name = “Times New Roman”
.Font.Size = 14
.Interior.Color = RGB(255, 255, 0)
End With
End Sub

* Without using With Range….End With, you need to write every line in full, like this

Range(“A1:C6″).Columns(2).Font.ColorIndex = 3

The output:

vba2010_figure18.2

                                        Figure 18.2


18.4 The Rows Property

Basically, the syntax for the Rows property is similar to that of the Columns property, you just need to replace Columns with rows.

The syntax of selecting a row within a certain range is

Range(starting_cell,Ending_ Cell).Rows(i).Select

Example 18.4

This following code selects the third row within the range A1 to F3

Private Sub CommandButton2_Click()
 Range(“A1:F3″).Rows(3).Select
End Sub
vba2010_figure18.3

                         Figure 18.3

Example 18.5: Using With Range…End With for Rows

Private Sub CommandButton1_Click()
With Range(“A1:F3″).Rows(2)
.Font.ColorIndex = 3
.Font.Bold = True
.Font.Italic = True
.Font.Underline = True
.Font.Name = “Times New Roman”
.Font.Size = 14
.Interior.Color = RGB(255, 255, 0)
End With
End Sub

The Output

vba2010_figure18.4

  Figure 18.4

18.5 Using the Set keyword to Declare Range

We can write Excel 2010 VBA code that can specify a certain range of cells using the Set keyword and then perform certain tasks according to a set of conditions.

In Example 18.6, we shall write the ExcelVBA code such that it can accept range input from the user and then change the mark to blue if it is more than or equal to 50 and change it to red if the mark is less than 50.

Example 18.6

Private Sub CommandButton1_Click()
Dim rng, cell As Range, selectedRng As String
 selectedRng = InputBox(“Enter your range”)
 Set rng = Range(selectedRng)
For Each cell In rng
 If cell.Value >= 50 Then
  cell.Font.ColorIndex = 5
 Else
  cell.Font.ColorIndex = 3
 End If
Next cell
End Sub

Explanation:

The InputBox function is used to accept value from the users.

rng and cell are declared as a Range variable using the Dim statement while selectedRng is declared as a string that receives input from the user.

Once the input is obtained from the user, it is stored using the Set method and the Range function.

For Each cell In rng ……Net cell is a loop that can iterate through the selected range, one cell at a time.

The If…Then…Else statements are to specify the color of the font according to the range of values determined by the conditions.

The Output

vba2010_figure18.5

 Figure 18.5

18.6 The Formula property

You can use the Formula property of the Range object to write your own customized formula.

Example 18.7

Private Sub CommandButton1_Click()
 Range(“A1:B3″).Columns(3).Formula = “=A1+B1″
End Sub

In this example, the formula A1+B1 will be copied down column 3 (column C) from cell C1 to cell C3. The program automatically sums up the corresponding values down column A and column B and displays the results in column C, as shown in Figure 18.6

vba2010_figure18.6

  Figure 18.6 

The above example can also be rewritten and produces the same result as below:

Range(“A1:B3″).Columns(3).Formula = “=Sum(A1:B1)”

There are many formulas in Excel VBA which we can use to simplify and speed up complex calculations. The formulas are categorized into Financial, Mathematical, Statistical, Date , Time and others. For example, in the statistical category, we have Average (Mean), Mode and Median

Example 18.8

In this example, the program computes the average of the corresponding values in column A and column B and displays the results in column C. For example, the mean of values in cell A1 and Cell B1 is computed and displayed in Cell C1. Subsequent means are automatically copied down Column C until cell C3.

Private Sub CommandButton1_Click()
 Range(“A1:B3″).Columns(3).Formula = “=Average(A1:B1)”
End Sub

Example 18.9: Mode

In this example, the program computes the mode for every row in the range A1:E4 and displays them in column F. It also makes the font bold and red in color, as shown in Figure 15.6.

Private Sub CommandButton1_Click()
 Range(“A1:E4″).Columns(6).Formula = “=Mode(A1:E1)”
 Range(“A1:E4″).Columns(6).Font.Bold = True
 Range(“A1:E4″).Columns(6).Font.ColorIndex = 3
End Sub

The Output

vba2010_figure 18.7

Figure 18.7




 [Lesson 17]<<[Table of Contents]>>[Lesson 19]

Excel 2010 VBA Lesson 16: Excel VBA Object

 [Lesson 15]<<[Table of Contents]>>[Lesson 17]

16.1 Object-Oriented Programming

Excel 2010  VBA  is an object-oriented programming language. An Excel 2010 VBA  object has certain functions and properties, and contain data. Some of the objects in Excel 2010 VBA  are Workbook, worksheet, Range, Cells, Chart, Pivot Table and more.

In order to view the Excel 2010 VBA  objects, click object browser in the Excel 2010 VBA  editor and you will be presented with a list of objects(or classes) together with their properties and methods, as shown in Figure 16.1.

vb2010 Figure 16.1

Figure 16.1

If you have inserted some Active-X controls into the UserForm of a worksheet, clicking on the UserForm will reveal the objects together with the associated events, as shown in Figure 16.2

vba2010_fig16.2

Figure 16.2

16.2: Object Properties

An Excel 2010 VBA  object has properties and methods. Properties are the characteristics or attributes of an object. For example, Range is an Excel 2010 VBA  object and one of its properties is value. We connect an object to its property by a period(a dot or full stop). The following example shows how we connect the property value to the Range object.

Example 16.1

Private Sub CommandButton1_Click()
 Range("A1:A6").Value = 10
End Sub

As value is the default property, it can be omitted. So the above procedure can be rewritten as:

Private Sub CommandButton1_Click()
 Range(“A1:A6″)= 10
End Sub



Cells is also an Excel 2010 VBA  object, but it is also the property of the range object. So an object can also be a property, depending on the hierarchy of the objects. Range has higher hierarchy than cells, and interior has lower hierarchy than Cells, and color has lower hierarchy than Interior, so you can write

Range(“A1:A3″).Cells(1, 1).Interior.Color = vbYellow

This statement will fill cells (1,1) with yellow color. Notice that although the Range object specifies a range from A1 to A3, the cells property specifies only cells(1,1) to be filled with yellow color, it sorts of overwriting the range specified by the Range object. If you wish to fill the cells from A1 to A3 with the yellow color you can use the following syntax:

Range(“A1:A3″).Interior.Color = vbYellow

Another object is the font that belongs to the Range object. The font object has its properties. In the following example

Range(“A1:A4”).Font.Color=vbYellow

, the color property of the object Font will fill all the contents from cell A1 to cell A4 with yellow color.

Sometimes it is not necessary to type the properties, Excel VBA IntelliSense will display a drop-down list of proposed properties after you type a period at the end of the object name. You can then select the property you want by double clicking it or by highlighting it then press the Enter key. The IntelliSense drop-down is shown in Figure 16.3

vba2010_figure16.3

Figure 16.3



  [Lesson 15]<<[Table of Contents]>>[Lesson 17]

Excel VBA Lesson 21: The Workbook Object

<<Lesson 20>> [Contents] <<Lesson 22>>

In the previous lesson, we have learned to write code associated with the worksheet object. In this lesson, we shall learn about the Workbook object. The Workbook object at the top of the hierarchy of the Excel VBA objects. We will deal with properties and methods associated the Workbook object.

21.1 The Workbook Properties

When we write Excel VBA code involving the Workbook object, we use Workbooks. The reason is that we are dealing with a collection of workbooks most of the time, so using Workbooks enables us to manipulate multiple workbooks at the same time.When will deal with multiple workbooks, we can use indices to denote different workbooks that are open, using the syntax Workbooks (i), where i is an index. For example, Workbooks (1) denotes Workbook1, Workbooks (2) denotes Workbook2 and more.Workbooks have a number of properties. Some of the common properties are Name, Path and FullName Let’s look at the following example:



Example 21.1

Private Sub CommandButton1_Click()
MsgBox Workbooks(1).Name
End Sub

Excel VBA

Running the program produces a message box that displays the first workbook name, i.e. workbook_object1.xls as shown in Figure 17.1 below:

Figure 21.1




If we have only one open workbook, we can also use the syntax ThisWorkbook in place of Workbook (1), as follows:

Private Sub CommandButton1_Click ()
MsgBox ThisWorkbook.Name
End Sub

Example 21.2

Private Sub CommandButton1_Click ()
MsgBox ThisWorkbook.Path
End SubOr you can use the following code:

Private Sub CommandButton1Click ()
MsgBox Workbooks (“workbook_object1.xls”).Path
End Sub

The output is shown below:

Excel VBA

Figure 21.2

Example 21.3

This example will display the path and name of the opened workbook. The code is:

Private Sub CommandButton1_Click ()
MsgBox ThisWorkbook.FullName
End Sub

Or

Private Sub CommandButton1Click()
MsgBox Workbooks(“workbook_object1.xls”).Fullname
End Sub

The output is shown in Figure 17.3.

Excel VBA

Figure 21.3

21.2 The Workbook Methods

There are a number of methods associated with the workbook object. These methods are Save, SaveAs, Open, Close and more.

Example 21.4

In this example, when the user clicks on the command button, it will open up a dialog box and ask the user to specify a path and type in the file name, and then click the save button, not unlike the standard windows SaveAs dialog, as shown in Figure 17.4.

Private Sub CommandButton1_Click()
fName = Application.GetSaveAsFilename
ThisWorkbook.SaveAs Filename:=fName
End Sub

Excel VBA

Figure 21.4

Another method associated with the workbook object is open. The syntax is
Workbooks.Open (“File Name”)

Example 21.5

In this example, when the user clicks on the command button, it will open the file workbook_object1.xls under the path C:\Users\liewvk\Documents\
Private Sub CommandButton1_Click()
Workbooks.Open (“C:\Users\liewvk\Documents\workbook_object1.xls”)
End Sub

The close method is the command that closes a workbook. The syntax is
Workbooks (i).Close

Example 21.6

In this example, when the user clicks the command button, it will close Workbooks (1).
Private Sub CommandButton1_Click()
Workbooks (1).Close
End Sub



<<Lesson 20>> [Contents] <<Lesson 22>>

Excel VBA Lesson 20: The Worksheet Object

<<Lesson 19>> [Contents] <<Lesson 21>>

20.1 The Worksheet Properties in Excel VBA

Similar to the Range Object, the Worksheet object has its own set of properties and methods. When we write Excel VBA code involving the Worksheet object, we use Worksheets. The reason is that we are dealing with a collection of worksheets most of the time, so using Worksheets enables us to manipulate multiple worksheets at the same time.

Some of the common properties of the worksheet are name, count, cells, columns, rows and columnWidth.


Example 20.1

Private Sub CommandButton1_Click()

MsgBox Worksheets(1).Name

End Sub

The above example will cause a pop-up dialog that displays the worksheet name as sheet 1, as shown below:

vba_Figure16.1

Figure 16.1

The count property returns the number of worksheets in an opened workbook.

Example 20.2

Private Sub CommandButton1_Click()
MsgBox Worksheets.Count
End Sub

The output is shown in Figure 20.2.

vba_Figure16.2

Figure 20.2



Example 20.3

The count property in this example will return the number of columns in the worksheet.

Private Sub CommandButton1_Click()

MsgBox Worksheets(1).Columns.Count

End Sub

The output is shown below:

vba_Figure16.3

 Figure 20.3

Example 20.4

The count property in this example will return the number of rows in the worksheet.

Private Sub CommandButton1_Click()

MsgBox Worksheets(1).Rows.Count

End Sub

vba_Figure16.4

 Figure 20.4

20.2 The Worksheet Methods

Some of the worksheet methods are add, delete, select, SaveAs, copy, paste and more.

 Example 20.5

In this example, when the user clicks the first command button, it will add a new sheet to the workbook. When the user clicks the second command button, it will delete the new worksheet that has been added earlier.

Private Sub CommandButton1_Click()

Worksheets. Add

End Sub

Private Sub CommandButton2_Click()

Worksheets(1).Delete

End Sub

Example 20.6

The select method associated with worksheet lets the user select a particular worksheet. In this example, worksheet 2 will be selected.

Private Sub CommandButton1_Click()

‘Worksheet 2 will be selected

Worksheets(2).Select

End Sub

The select method can also be used together with the Worksheet’s properties Cells, Columns and Rows as shown in the following examples.

Example 20.7

Private Sub CommandButton1_Click()

‘Cell A1 will be selected

Worksheets (1).Cells (1).Select

End Sub

Example 20.8

Private Sub CommandButton1_Click()

‘Column 1 will be selected

Worksheets (1).Columns (1).Select

End Sub

Example 20.9

Private Sub CommandButton1_Click()

‘Row 1 will be selected

Worksheets (1).Rows (1).Select

End Sub

Excel VBA also allows us to write code for copy and paste. Let’s look at the following Example:

 Example 20.10

Private Sub CommandButton1_Click()

‘To copy the content of a cell 1

Worksheets(1).Cells(1).Select

Selection.Copy

End Sub

Private Sub CommandButton2_Click()

‘To paste the content of cell 1 to cell 2

Worksheets(1).Cells(2).Select

ActiveSheet.Paste
End Sub




<<Lesson 19>> [Contents] <<Lesson 21>>

Excel VBA Lesson 19: The Range Object in Excel VBA

<<Lesson 18>> [Contents] <<Lesson 20>>

The Range object is one of the most important and most commonly used among the Excel VBA objects. In fact, we have dealt with the Range object in previous lessons. The Range object comprises some arguments and methods which can be used to perform certain tasks.

19.1 The Select Method

The Range object contains two arguments that specify a selected area on the spreadsheet. The syntax is

Range(starting_cell,Ending_ Cell)

For example, Range(“A1:C6”) means the specified range is from cell A1 to C6.

To select the specified range, the syntax is

Range(“A1:C6”).Select

where select is a method of the Range object



Example 19.1

Private Sub CommandButton1_Click()
Range(“A1:C6”).Select
End Sub

19.2 The Columns Property

The columns property of the Range object is to select certain columns in the particular range specified by the Range object.

The syntax is

Range(starting_cell,Ending_ Cell).Columns(i).Select

Example 19.2

This example select column C in the range A1 to C6Private Sub CommandButton2_Click()
Range(“A1:C6”).Columns(3).Select
End Sub

You can also use Cells(1,1) to Cells(6,3) instead of A1:C6, the syntax is

Range(Cells(1,1),Cells(6,3)).Columns(3).Select

* Notice that you don’t use double inverted commas and colon.



The output is as shown in Figure 19.1

Excel VBA

Figure 19.1

19.3 Using With Range……End With

You can also format font the cells in a particular column in terms of type, color, bold, italic, underlined and size using the With Range…..End With Structure. It can also be used to format other Range properties like the background color. Using With Range….End With structure can save time and make the code cleaner.

Example 19.3

Private Sub CommandButton1_Click()
With Range(“A1:C6”).Columns(2)
.Font.ColorIndex = 3
.Font.Bold = True
.Font.Italic = True
.Font.Underline = True
.Font.Name = “Times New Roman”
.Font.Size = 14
.Interior.Color = RGB(255, 255, 0)

End With

End Sub

* Without using With Range….End With, you need to write every line in full, like this

Range(“A1:C6”).Columns(2).Font.ColorIndex = 3

The output:

vba_Figure15.2

19.4 The Rows Property

Basically the syntax for the Rows property is similar to that of the Columns property, you just need to replace Columns with rows.

The syntax of selecting a row within a certain range is

Range(starting_cell,Ending_ Cell).Rows(i).Select

Example 19.4

This following code selects the third row within the range A1 to F3

Private Sub CommandButton2_Click()
Range(“A1:F3”).Rows(3).Select
End Sub

The output

vba_Figure15.3

Figure 19.3

Example 19.5: Using With Range…End With for Rows

Private Sub CommandButton1_Click()
With Range(“A1:F3”).Rows(2)
.Font.ColorIndex = 3
.Font.Bold = True
.Font.Italic = True
.Font.Underline = True
.Font.Name = “Times New Roman”
.Font.Size = 14
.Interior.Color = RGB(255, 255, 0)

End With

End Sub

The Output

vba_Figure15.4


Figure 19.4

19.5 Using the Set keyword to Declare Range

We can write Excel VBA code that can specify a certain range of cells using the Set keyword and then perform certain tasks according to a set of conditions.

In Example 15.6, we shall write the ExcelVBA code such that it can accept range input from the user and then change the mark to blue if it is more than or equal to 50 and change it to red if the mark is less than 50.

Example 19.6

Private Sub CommandButton1_Click()
Dim rng, cell As Range, selectedRng As String
selectedRng = InputBox(“Enter your range”)
Set rng = Range(selectedRng)
For Each cell In rng
If cell.Value >= 50 Then
cell.Font.ColorIndex = 5
Else
cell.Font.ColorIndex = 3
End If
Next cell
End Sub

Explanation:

The InputBox function is used to accept value from the users.

rng and cell are declared as a Range variable using the Dim statement while selectedRng is declared as a string that receives input from the user.

Once the input is obtained from the user, it is stored using the Set method and the Range function.

For Each cell In rng ……Next cell is a loop that can iterate through the selected range, one cell at a time.

The If…Then…Else statements are to specify the color of the font according to the range of values determined by the conditions.

The Output

19.6 The Formula property

You can use the Formula property of the Range object to write your own customized formula.

Example 19.7

Private Sub CommandButton1_Click()
Range(“A1:B3”).Columns(3).Formula = “=A1+B1”
End Sub

In this example, the formula A1+B1 will be copied down column 3 (column C) from cell C1 to cell C3. The program automatically sums up the corresponding values down column A and column B and displays the results in column C, as shown in Figure 19.5

vba_Figure15.5

The above example can also be rewritten and produces the same result as below:

Range(“A1:B3”).Columns(3).Formula = “=Sum(A1:B1)”



There are many formulas in Excel VBA which we can use to simplify and speed up complex calculations. The formulas are categorized into Financial, Mathematical, Statistical, Date , Time and others. For example, in the statistical category, we have Average (Mean), Mode and Median

Example 19.8

In this example, the program computes the average of the corresponding values in column A and column B and displays the results in column C. For example, the mean of values in cell A1 and Cell B1 is computed and displayed in Cell C1. Subsequent means are automatically copied down Column C until cell C3.

Private Sub CommandButton1_Click()
Range(“A1:B3”).Columns(3).Formula = “=Average(A1:B1)”
End Sub

Example 19.9: Mode

In this example, the program computes the mode for every row in the range A1:E4 and displays them in column F. It also makes the font bold and red in color, as shown in Figure 15.6.

Private Sub CommandButton1_Click()
Range(“A1:E4”).Columns(6).Formula = “=Mode(A1:E1)”
Range(“A1:E4”).Columns(6).Font.Bold = True
Range(“A1:E4”).Columns(6).Font.ColorIndex = 3
End Sub

vba_Figure15.6

<<Lesson 18>> [Contents] <<Lesson 20>>

Excel VBA Lesson 18: Introduction to Excel VBA Objects Part 2

<<Lesson 17>> [Contents] <<Lesson 19>>

In lesson 13, we have learned the concepts of Excel VBA objects. You have also learned that an Excel VBA object has properties and methods. We have already dealt with properties and we shall learn about methods in this lesson.

18.1: Methods

A method of an Excel VBA object normally do something or perform certain operations. For example, ClearContents is a method of the range object that clears the contents of a cell or a range of cells. For example, You can write the following code to clear the contents of certain range:

Example 18.1

‘Clear contents from cells A1 to A6
Private Sub CommandButton1_Click()
Range(“A1:A6”).ClearContents
End Sub
You can also let the user select his own range of cells and clear the contents by using the InputBox function, as shown in Example 18.2


Example 18.2

Private Sub CommandButton1_Click()
Dim, selectedRng As String
selectedRng = InputBox(“Enter your range”)
Range(selectedRng).ClearContents
End Sub

In order to clear the contents of the entire worksheet, you can use the following code:

Sheet1.Cells.ClearContents

But if you only want to clear the formats of an entire worksheet, you can use the following syntax:

Sheet1.Cells.ClearFormats

To select a range of cells, you can use the Select method. This method selects a range of cells specified by the Range object. The syntax is
Range(“A1:A5”).Select

Example 18.3

Private Sub CommandButton1_Click()
Range(“A1:A5”).Select
End Sub

Example 18.4

This example allows the user to specifies the range of cells to be selected.

Private Sub CommandButton1_Click()
Dim selectedRng As String
selectedRng = InputBox(“Enter your range”)
Range(selectedRng).Select
End Sub
To deselect the selected range, we can use the Clear method.
Range(“CiRj:CmRn”).Clear



Example 18.5

In this example, we insert two command buttons, the first one is to select the range and the second one is to deselect the selected range.
Private Sub CommandButton1_Click()
Range(“A1:A5”).Select
End Sub

Private Sub CommandButton2_Click()
Range(“A1:A5”).Clear
End Sub

Instead of using the Clear method, you can also use the ClearContents method.

Another very useful method is the Autofill method. This method performs an autofill on the cells in the specified range with a series of items including numbers, days of the week, months of year and more. The syntax is

Expression.AutoFill(Destination, Type)

Where Expression can be an object or a variable that returns an object. Destination means the required Range object of the cells to be filled. The destination must include the source range. Type means a type of series, such as days of the week, a month of the year and more. The AutoFill type constant is something like xlFillWeekdays, XlFillDays, XlFillMonths and more.

Example 18.6

Private Sub CommandButton1_Click()
Range(“A1”)=1
Range(“A2”)=2
Range(“A1:A2”).AutoFill Destination:=Range(“A1:A10”)
End Sub

In this example, the source range is A1 to A2. When the user clicks on the command button, the program will first fill cell A1 with 1 and cell A2 will 2, and then automatically fills the Range A1 to A10 with a series of numbers from 1 to 10.

Example 18.7

Private Sub CommandButton1_Click()
Cells(1, 1).Value = “Monday”
Cells(2, 1).Value = “Tuesday”
Range(“A1:A2”).AutoFill Destination:=Range(“A1:A10”), Type:=xlFillDays
End Sub

Example 18.8

This example allows the user to select the range of cells to be automatically filled using the Autofill method. This can be achieved with the use of the InputBox. Since each time we want to autofill a new range, we need to clear the contents of the entire worksheet using the Sheet1.Cells.ClearContents statement.

Private Sub CommandButton1_Click()
Dim selectedRng As String
Sheet1.Cells.ClearContents
selectedRng = InputBox(“Enter your range”)
Range(“A1”) = 1
Range(“A2”) = 2
Range(“A1:A2”).AutoFill Destination:=Range(selectedRng)
End Sub

<<Lesson 17>> [Contents] <<Lesson 19>

Excel VBA Lesson 17 : Introduction to Excel VBA Objects Part 1

<<Lesson 16>> [Contents] <<Lesson 18>>

17.1 The Concept of Object in Excel VBA

Most programming languages today deal with objects, a concept called object-oriented programming. Although Excel VBA is not a true object-oriented programming language, it does deal with objects. Excel VBA object is something like a tool or a thing that has certain functions and properties and can contain data. For example, an Excel Worksheet is an object, the cell in a worksheet is an object, the range of cells is an object, font of a cell is an object, a command button is an object, and a text box is an object and more.In order to view the ExcelVBA objects, click object browser in the Excel VBA IDE and you will be presented with a list of objects(or classes) together with their properties and methods, as shown in Figure 17.1.

Excel VBA




Figure 17.1

If you have inserted some Active-X controls into the worksheet, clicking on the relevant worksheet will reveal the objects together with the associated events, as shown in Figure 17.2

Excel VBA

Figure 17.2

17.2: Object Properties

An Excel VBA object has properties and methods. Properties are the characteristics or attributes of an object. For example, Range is an Excel VBA object and one of its properties is value. We connect an object to its property by a period(a dot or full stop). The following example shows how we connect the property value to the Range object.

Example 17.1

Private Sub CommandButton1_Click()
Range(“A1:A6”).Value = 10
End Sub

* Since value is the default property, it can be omitted and the above code can be rewritten as

Example 17.2

Private Sub CommandButton1_Click()
Range(“A1:A6”)= 10
End Sub



The Cells is also an Excel VBA object, but it is also the property of the range object. So an object can also be a property, it depends on the hierarchy of the objects. Range has higher hierarchy than cells, and interior has lower hierarchy than Cells, and color has lower hierarchy than Interior, so you can write

Range(“A1:A3”).Cells(1, 1).Interior.Color = vbYellow

This statement will fill cells (1,1) with yellow color. Notice that although the Range object specifies a range from A1 to A3, the cells property specifies only cells(1,1) to be filled with yellow color, it sorts of overwriting the range specified by the Range object.

Another object is the font that belongs to the Range object. And font has its properties.For example, Range(“A1:A4”).Font.Color=vbYellow, the color property of the object Font will result in all the contents from cell A1 to cell A4 to be filled in yellow color.

Sometimes it is not necessary to type the properties, Excel VBA IntelliSense will display a drop-down list of proposed properties after you type a period at the end of the object name. You can then select the property you want by double-clicking it or by highlighting it then press the Enter key. The IntelliSense drop-down is shown in Figure 17.3

Figure 17.3

We shall discuss object methods in the next lesson

<<Lesson 16>> [Contents] <<Lesson 18>>