Using Super-Variables: Object Variables in Excel 2007:-

In typical programming languages, a variable holds a single value. You might use x = 4 to assign a value of 4 to the variable x.

Many properties describe a single cell in Excel. A cell might contain a value such as 4, and the cell also has a font size, a font color, a row, a column, possibly a formula, possibly a comment, a list of precedents, and more. It is possible to use VBA to create a super-variable that contains all the information about a cell or any other object. A statement to create a typical variable such as x = Range(“A1”) assigns the current value of cell A1 to the variable x. You can use the Set keyword to create an object variable:

Set x = Range(“A1”)

This formula creates a super-variable that contains all the properties of the cell. Instead of having a variable with only one value, you now have a variable in which you can access the value of many properties associated with that variable. You can reference x.Formula to learn the formula in cell A1 or x.Font.ColorIndex to learn the color of the cell.

Using object variables can make it easier to write code. Rather than continuously referring to ThisWorkbook.Worksheets(“Income Statement”), you can define an object variable and use that as shorthand. For example, the following code repeatedly refers to the same workbook:

ThisWorkbook.Worksheets(“Income Statement”).ChartObjects(“Chart1”).Chart _.SetSourceData Source: = ThisWorkbook.Worksheets(“Income Statement”) _.Range(“A1:E4”)

ThisWorkbook.Worksheets(“Income Statement”).ChartObjects(“Chart1”).Left = 10

ThisWorkbook.Worksheets(“Income Statement”).ChartObjects(“Chart1”).Top = 30

ThisWorkbook.Worksheets(“Income Statement”).ChartObjects(“Chart1”).Width = 300

ThisWorkbook.Worksheets(“Income Statement”).ChartObjects(“Chart1”).Height = 200

If you define an object variable first, the code becomes shorter and easier to write.

Dim WS as Worksheet

Set WS = ThisWorkbook.Worksheets(“Income Statement”)

WS.ChartObjects(“Chart1”).Chart.SetSourceData Source: = WS.Range(“A1:E4”)

WS.ChartObjects(“Chart1”).Left = 10

WS.ChartObjects(“Chart1”).Top = 30

WS.ChartObjects(“Chart1”).Width = 300

WS.ChartObjects(“Chart1”).Height = 200