autocomplete feature turns on if you declare an object variable at the top of your procedure. The following lines of code declare three object variables, one as a worksheet, one as a range, and one as a PivotTable.

Sub Test()

Dim WSD as Worksheet Dim MyCell as Range Dim PT as PivotTable

Set WSD = ThisWorkbook.Worksheets("Data")

Set MyCell = WSD.Cells(65536, 1).End(xlUp).Offset(1, 0)

Set PT = WSD.PivotTables(l)

In this example, you can see that just an equals statement is not used to assign object variables. You need to use the Set statement to assign a specific object to the object variable.

There are many great reasons for using object variables, not the least of which is the fact that it can be a great shorthand notation. It is a lot easier to have a lot of lines of code referring to WSD instead of ThisWorkbook.Worksheets("Data").

Also, as mentioned earlier, the object variable inherits all the properties of the object to which it refers.

The For Each...Loop employs an object variable rather than a Counter variable. The following code loops through all the cells in Column A. The code uses the .CurrentRegion property to define the current region and then uses the .Resize property to limit the selected range to a single column. The object variable is called Cell. I could have used any name for the object variable, but Cell seems more appropriate than something arbitrary like Fred.

For Each cell in Range("A1").CurrentRegion.Resize(, 1) If cell.value = "Total" then cell.resize(1,8).Font.Bold = True End if Next cell

This code sample searches all open workbooks, looking for one with a particular sheet name.

For Each wb in Workbooks

If wb.Worksheets(1).Name = "Menu" then WBFound = True WBName = wb.Name Exit For End if Next wb

In this code sample, all shapes on the current worksheet are deleted:

For Each Sh in ActiveSheet.Shapes

Sh.Delete Next Sh

This code sample deletes all pivot tables on the current sheet:

For Each pt in ActiveSheet.PivotTables pt.TableRange2.Clear Next pt

0 0

Post a comment