Excellent Office Adventures
David ShankMicrosoft Corporation
January 4, 2001
IT Is The First Week of The New Year and No Matter How We Spent Our Holidays, Most of US Have At Least Tw Things in Common This Week: WE Are Back in the office (as i like to say in column) and we are .
For some of us, when it comes to number crunching, it is visions of Microsoft Excel that dance in our heads. And since I have not talked about working with Excel in this column, as many of you have reminded me, I've decided That now is the perfect time.
This is the first part of a two-part discussion on working with Excel. In this monthÃ,Â's column, I will discuss the basics of what you need to know to work with Excel programmatically. Next month, I will delve in a bit deeper To Discuss Working with Ranges, Regions, and Cells.
The Excel Application Object
The Excel Application object is the top-level object in Excel's object model. You use the Application object to determine or specify application-level properties or execute application-level methods. The Application object is also the entry point into the rest of the Excel object Model.
When you work with properties and methods of the Application object by using Visual Basic for Applications (VBA) from within Excel, the Application object is available to you by default. This is known as an implicit reference to the object. To work with Excel objects from another Office application, you must The ShowNameFromInsideXL procedure first create an object variable that represents the Excel Application object. This is known as an explicit reference to the object. For example, the following two procedures return the name of the currently active Worksheet object. is designed to work from within Excel and uses an implicit reference to the Application object. In other words, it references the ActiveSheet property of the Application object without explicitly referencing the Application object itself. You run the ShowNameFromOutsideXL procedure from outside Excel and so must use An expender to the application object.sub shownamefrominsidexl ()
"'" "& Activeesheet.name &"' is the Active Worksheet. "
End Sub
Sub shownamefromoutsidexl ()
DIM XLAPP as Excel.Application
Const XL_Notrunning As Long = 429
ON Error Goto Showname_ERR
Set xlapp = getObject (, "Excel.Application")
"'" & Xlapp.activesheet.name & "' is active."
XLapp.quit
SET XLAPP = Nothing
Showname_end:
EXIT SUB
Showname_err:
IF ERR = XL_NOTRUNNING THEN
'Excel Is Not Currently Running.
Set xlapp = new excel.Application
XLapp.Workbooks.add
Resume next
Else
MsgBox Err.Number & "-" & Err.Description
END IF
Resume Showname_END
End Sub
Notice that the ShowNameFromOutsideXL procedure uses the GetObject function to get a reference to the currently running instance of Excel. If Excel is not running when this procedure is called, an error occurs. The error handler uses the New keyword to create a new instance of Excel , then adds a new Workbook object Since a workbook will contain at least one worksheet, the remaining code in the procedure will execute correctly.Note:. to use the New keyword, you must have a reference set to the Excel object model from the project That Contains the shownamefromoutsidexl procedure.
Understanding Excelã,'s Shortcuts to Active Objects
Like other Office application object models, the Excel Application object exposes several properties you can use to work with a currently active Excel object. For example, you will often write VBA procedures designed to work with information in the currently selected cell, or with the currently active worksheet. The Application object exposes the ActiveCell, ActiveChart, ActivePrinter, ActiveSheet, ActiveWindow, and ActiveWorkbook properties, which you can use to return a reference to the currently active cell, chart, printer, sheet, window, or workbook. The following examples Illustrate Various Ways You Might Use Some of these Properties:
'ActiveWorkbook Property Example:
Function Savebookas (StrfileName As String) AS Boolean
Activeworkbook.saveas Activeworkbook.path & "/" & strfilename
END FUNCTION
'Activecell Property Example:
Function CustomFormatcell ()
With activecell
If isnumeric (.text) and .formula <0 THEN
WITH .FONT
.Bold = true
.Italic = TRUE
.Borders.color = 255
END IF
End with
END FUNCTION
'ActiveSheet Property Example:
Function ChangeName (Strnewname As String) as boolean
Activeesheet.name = strnewname
END FUNCTION
In addition to the ActiveWorkbook property, you can use the Application object's Workbooks and Worksheets properties to return equivalent Excel objects. The Workbooks property returns the Workbooks collection that contains all the currently open Workbook objects. The Worksheets property returns the Sheets collection associated with the currently Active Workbook. The Following Example Uses The Workbooks Property To Determine IF A Workbook Is Already Open, And if Not, To Open IT:
Function OpenBook (StrfilePath As String) AS Boolean
'This Procedure Checks to See eti the workbook
'Specified in the strfilepath argument is open.
'IF it is open, the workbook is actid. If it is
'Not Open, The Procedure Opens It.
Dim Wkbcurrent As Excel.Workbook
DIM STRBOOKNAME AS STRING
ON Error Goto OpenBook_ERR
'DETERMINE The name portion of the strfilepath argument.
StrbookName = namefromPath (StrfilePath)
IF len (strbookname) = 0 THEN EXIT FUNCTION
IF Workbooks.count> 0 THEN
For Each Wkbcurrent in Workbooks
IF ucase $ (wkbcurrent.name) = ucase $ (strbookname) THEN
Wkbcurrent.actiVate
EXIT FUNCTION
END IF
Next wkbcurrent
END IF
Workbooks.open strbookname
Openbook = TRUE
Openbook_end:
EXIT FUNCTION
Openbook_err:
Openbook = false
Resume Openbook_end
END FUNCTION
In the preceding example, the OpenBook procedure calls a custom procedure named NameFromPath that returns the file name portion of the full path and file name passed to the OpenBook procedure in the strFilePath argument:
Function namefromPath (StrPath as string) AS String
'This Procedure Takes A File path and returns' The File Name Portion.
DIM LNGPOS As Long
DIM STRPART AS STRING
DIM BLNINCLUDESFILE AS BOOLEAN
'Check That this is a file path.
'Find the last path separator.
LNGPOS = INSTRREV (strpath, "/")
'DETERMINE IF STRING AFTER LAST Backslash
'Contains a period.
Blnincludesfile = Instrrev (strpath, ".")> LNGPOS
Strpart = ""
IF LNGPOS> 0 THEN
If blnincludesfile dam
Strpart = Right $ (StrPath, Len (StrPath) - LNGPOS)
END IF
END IF
NameFromPath = Strpart
END FUNCTION
The Excel Workbook Object
In the Excel object model, the Workbook object appears just below the Application object. The Workbook object represents an Excel .xls or .xla workbook file. You use the Workbook object to work with a single Excel workbook. You use the Workbooks collection to work WITH All Currently Open Workbook Objects.
You can also use the Application object's ActiveWorkbook property to return a reference to the currently active workbook. The Workbooks collection has a Count property you can use to determine how many visible and hidden workbooks are open. By default, Excel typically has one hidden workbook named Personal.xls. Excel uses the Personal.xls workbook as the default location to store macros. If the hidden Personal.xls workbook is the only open workbook, the ActiveWorkbook property returns Nothing, but the Workbooks collection's Count property returns 1. The Workbooks collection's Count Property Will Return 0 Only When are no hidden or visible open workbooks.
Working with Workbook Objects
You create a new Workbook object by using the Workbooks collection's Add method. The Add method not only creates a new workbook, but also immediately opens the workbook. The Add method also returns an object variable that represents the new workbook just created. The new workbook will contain the number of worksheets specified in the sheets in new workbook box on the General tab of the Options dialog box (Tools menu). You can also specify the number of sheets a new workbook will have by using the Application object's SheetsInNewWorkbook property.You can save a new workbook by using the Workbook object's SaveAs method and specifying the name of the workbook you want to save. If a workbook by that name already exists, an error occurs. Once a workbook has been saved by using the SaveAs method, additional Changes are saved by using the workbook object's save method. You can also save a copy of an existing workbook with a different file name by useing the savecopyas method. You can support A file name to be used with the SaveAs or SaveCopyAs method, or you can use the Application object's GetSaveAsFileName method to let the user supply the name to be used to save the workbook. If the user clicks Cancel in the Save As dialog box, the GetSaveAsFileName Method Returns False.
Before you save a new workbook by using the SaveAs method, the Workbook object's Name property setting is a value assigned by Excel, such as Book1.xls. After you save the workbook, the Name property contains the name you supplied in the Filename argument of ....................... ..
Note:. A Workbook object's FullName property contains the object's path and file name, whereas the Path property contains only the saved path to the current workbook Before a new workbook is saved, the FullName property has the same value as the Name property, and the Path property has no value.The Workbooks collection's Open method opens an existing workbook. When you open a workbook by using the Open method, it also becomes the active workbook. you can supply a file name to be used with the Open method, or you CAN Use the application Object's getopenfilename method's get the user select the workbook to open. if the user click, the getopenfilename method.
You use a Workbook object's Close method to close an open workbook. To specify whether pending changes to the workbook should be saved before the object is closed, you use the SaveChanges argument. If the SaveChanges argument is omitted, the user is prompted to save pending changes. You can also use the Close method of the Workbooks object to close all open workbooks. If there are unsaved changes to any open workbook when this method is used, the user is prompted to save changes. If the user clicks Cancel in this Save dialog box, an error occurs that your code must handle. you can suppress this Save dialog box by setting the Application object's DisplayAlerts property to False before executing the Close method. When you use the Workbooks object's Close method in this manner, any unsaved changes to Open Workbooks Are Lost. After The Close Method Has Run, Remember To Set The Displayalerts Property To True.
Note: The Auto_Open and Auto_Close event procedures are ignored when a workbook is opened or closed by using the Open or Close methods You can force these procedures to run by using the Workbook object's RunAutoMacros method The VBA code in a workbook's Open and BeforeClose event.. procedures will be executed when the workbook is opened or closed by using the Open or Close methods.The following example illustrates how to create a new workbook and specify the number of worksheets it will have:
Function Createnewwbookbook (optional strbookname as string = ", _
Optional Intnumsheets as integer = 3) As workbook
'This Procedure Creates A New Workbook File and
'Saves it by using the path and name specified
'in the strbookname argument. You Use the intnumsheets
'Argument to Specify The Number of Worksheets in the Number of Worksheets in
'Workbook; The default is three.
DIM INTORIGNUMSHEETS AS INTEGER
Dim Wkbnew as Excel.Workbook
ON Error Goto CreateNew_ERR
INTORIGNUMSHEETS = Application.sheetsinnewwbook
IF INTORIGNUMSHEETS <> intNumsheets Then
Application.sheetsinnewwbook = intnumsheets
END IF
SET WKBNEW = Workbooks.Add
If Len (STRBOOKNAME) = 0 THEN
STRBOOKNAME = Application.getsaveasfilename
WKBNEW.SAVEAS STRBOOKNAME
Set createnewwbook = wkbnew
Application.sheetsinnewwbook = intORIGNUMSHEETS
CreateNew_END:
EXIT FUNCTION
CreateNew_ERR:
Set createnewwbook = nothing
WKBNEW.CLOSE FALSE
Set wkbnew = Nothing
ResMe CreateNew_END
END FUNCTION
A Workbook object's Saved property is a Boolean value that indicates whether the workbook has been saved. The Saved property will be True for any new or opened workbook where no changes have been made and False for a workbook that has unsaved changes. You can set the saved property to True. Doing this prevents the user from being prompted to save changes when the workbook closes but does not actually save any changes made since the last time the workbook was saved by using the Save method.A note about working with workbooks through automation
When You are using Automation To Edit An Excel Workbook, Keep The Following In Mind.
Creating a new instance of Excel and opening a workbook results in an invisible instance of Excel and a hidden instance of the workbook. Therefore, if you edit the workbook and save it, the workbook is saved as hidden. The next time the user opens Excel Manually, The Workbook Is Invisible and The User Has To Click UnHide On The windowbook.
To Avoid this Behavior, Your Automation Code Should Unhat The Workbook Before Editing It And Saving It. Note That Does Not Mean That Excel Itself Has To Be Visible.
The Excel Worksheet Object
Most of the work you will do in Excel will be within the context of a worksheet. A worksheet contains a grid of cells you can use to work with data, and hundreds of properties, methods, and events you can use to work with the data IN a worksheet.
To work with the data contained in a worksheet, in a cell or within a range of cells, you use a Range object. The Worksheet and Range objects are the two most basic and most important components of any custom solution you create within Excel. I will talk more about the Range object in next monthÃ,Â's column.The Workbook object's Worksheets property returns a collection of all the worksheets in the workbook. The Workbook object's sheets property returns a collection of all the worksheets and chart sheets in the workbook.
Each Excel workbook contains one or more Worksheet objects and can contain one or more chart sheets as well. Charts in Excel are either embedded in a worksheet or contained on a chart sheet. You can have only one chart on a chart sheet, but you can have multiple charts on a worksheet. Each embedded chart on a worksheet is a member of the Worksheet object's ChartObjects collection. Worksheet objects are contained in the Worksheets collection, which you can access by using the Workbook object's Worksheets property. When you use VBA to create A New Workbook, You CAN Specify How Many Worksheets It Will Contain By Using The Application Object's SheetSinnewwbook property.
Referring to a Worksheet
Because a Worksheet object exists as a member of a Worksheets collection, you refer to a worksheet by its name or its index value In the following example, both object variables refer to the first worksheet in a workbook.:
Sub ReferenceoWorkshetexample ()
'This Procedure Illustrates How To Programmatically Refer To
'a Worksheet.
Dim wkssheetbyindex as Excel.Worksheet
Dim wkssheetbyname as Excel.Worksheet
With Activeworkbook
Set wkssheetbyindex = worksheets (1)
Set wkssheetByname = Worksheets ("main") if wkssheetbyndex.index = wkssheetbyname.index THEN
Msgbox "The Worksheet Indexed AS #"
& wkssheetByindex.index & vbcrlf_
& "Is The Same as The Worksheet Named '"
& wkssheetByname.name & "'", _
Vbokonly, "Worksheets Match!"
END IF
End with
End Sub
Working with a worksheet
You can add one or more worksheets to the Worksheets collection by using the collection's Add method. The Add method returns the new Worksheet object. If you add multiple worksheets, the Add method returns the last worksheet added to the Worksheets collection. If the Before or After arguments of the Add method are omitted, the new worksheet is added before the currently active worksheet The following example adds a new worksheet before the active worksheet in the current collection of worksheets.:
Dim wksnewsheet as Excel.Worksheet
SET WKSNEWSHEET = Worksheets.Add
With WKSNewsheet
'Work with Properties and Methods of The PROPERTIES AND Methods
'New Worksheet Here.
End with
You use the Worksheet object's Delete method to delete a worksheet from the Worksheets collection When you try to programmatically delete a worksheet, Excel will display a message (alert);. To suppress the message, you must set the Application object's DisplayAlerts property to False, as illustrate in the folload example:
Function deleteWorksheet (strsheetname as string) as boolean
ON Error ResMe next
Application.displayAlerts = false
ActiveWorkbook.worksheets (strsheetname). Delete
Application.displayalerts = true
'Return True if no error occurred;
'OtherWise Return False.
Deleteworksheet = not cbool (err.number)
End Functionnote: When You set The Displayalerts Property to False, Always Set It Back to True Before your procedure Has finished Executing, AS Shown In The Preceding Example.
You can copy a worksheet by using the Worksheet object's Copy method. To copy a worksheet to the same workbook as the source worksheet, you must specify either the Before or After argument of the Copy method. You move a worksheet by using the Worksheet object's Move Method. for esample:
Worksheets ("Sheet1"). Copy After: = Worksheets ("Sheet3")
Worksheets ("sheet1"). Move after: = Worksheets ("Sheet3")
The next esample illustrates how to move a worksheet so thing it is the laast worksheet in a workbook:
Worksheets ("sheet1"). Move after: = Worksheets (Worksheets.count)
NOTE: WHEN You Use Either The Copy or After Argument, Excel Creates A New Workbook and Copies The Specified Worksheet To IT.
Where to get more info
TECHNIQUES Discussed in this column will get you started Working with Excel Programmatical, Check Out The Following Resources:
For more information on the Excel object model see the Microsoft Excel Object Model. As always, check in regularly at the Office Developer Center for information and technical articles on Office solution development.
David Shank is a programmer / writer on the Office team specializing in developer documentation. Rumor has it he lives high in the mountains to the east of Redmond and is one of the few native Northwesterners who still lives in the Northwest .----- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------------------ Excellent Office Adventures, Part 2
David ShankMicrosoft Corporation February 1, 2001 In last monthÃ,Â's column, I provided an overview of what you need to know to work with the Excel object model. This month I will build on that discussion with information on working with the Range object and some of its properties and methods. Understanding the Range Object ExcelÃ,Â's Range object is one of the most powerful, dynamic, and often-used objects in the Excel object model. Once you develop a full understanding of the Range object and how to use it effectively in Visual Basic for Applications (VBA) procedures, you will be well on your way to programmatically harnessing the power of Excel. The Excel Range object is unique among objects. in most cases, an object is a thing with some clearly identifiable corollary in the Excel user interface. For example, a Workbook object is recognizable as an .xls file. in a workbook, the collection of Worksheet objects is represented in the user interface by separate tabbed sheets. But the R ange object is different. A range can be a different thing in different circumstances. A Range object can be a single cell or a collection of cells. It can be a single object or a collection of objects. It can be a row or column, or it can represent a three-dimensional collection of cells that span multiple worksheets. in addition, unlike other objects that exist as objects and as members of a collection of objects, there is no Ranges collection that contains all Range objects in a workbook or worksheet . It is probably easiest to think of the range ject as your hand to work with.
Because the Range object is such a fundamental entity within Excel, you will find that many properties and methods return a Range object that you can use to work with the data in your custom solution. The following sections discuss some basic aspects of Range objects and many of the ways you can return a Range object from a built-in property or method. Working with the Range property you will use the Range property to return a Range object in many different circumstances. The Application object, the Worksheet object, and the Range object all have a Range property. The Application object's Range property returns the same Range object as that returned by the Worksheet object. In other words, the Application object's Range property returns a reference to the specified cell or cells on the active worksheet. The Range Property Of The Range Object Has A Subtle Difference That IT IS IMPORTANT TO Understand. Consider The Following Example: DIM RNG1 AS RANGEDIM RNG2 AS RANGE
DIM RNG3 AS RANGE
SET RNG1 = Application.range ("b5")
SET RNG2 = Worksheets ("sheet1"). Range ("b5")
SET RNG3 = RNG2.Range ("b5")
These three Range objects do not all return a reference to the same cell. In this example, rng1 and rng2 both return a reference to cell B5. But rng3 returns a reference to cell C9. This difference occurs because the Range object's Range property returns a reference relative to the specified cell. In this case, the specified cell is B5. Therefore, the B means that the reference will be one column to the right of B5, and the 5 means the reference will be the fifth row below the row specified by B5, including the current (fifth) row. In other words, the Range object's Range property returns a reference to a cell that is n columns to the right and y rows down from the specified cell). Typically, you will use the Range property to return a Range object, then use the properties and methods of that Range object to work with the data in a cell or group of cells The following table contains several examples that illustrate how you might work with the Range property.:
DescriptionRange Property ExampleSet the value of cell A1 on Sheet1 to 100Worksheets ( "Sheet1") Range ( "A1") Value = 100Set the value for a group of cells on the active worksheetRange... ( "B2: B14") Value = 10000Set The Formula For Cell B15 on The Active WorksheetRange ("B15"). Formula = "= SUM (B2: B14)" Set The Font to Boldrange ("B15"). Font.Bold = TrueSet The Font Color To GreenRange ("B15 ") .Font.color = rgb (0, 255, 0) set an Object variable to refer to a single cellset RNGCURRENT = RANGE (" a1 ") set an Object variable to refer to a group of cellsset RNGCURRENT = Range (" A1 : ".) Font.Bold = TrueSet an object variable to a named rangeSet rngCurrent = Range (" L1 ") Format all the cells in a named rangeRange (" YTDSalesTotals NovemberReturns ") Set an object variable representing all the used cells on the Employees worksheetSet rngCurrent = Worksheets ( "Employees"). UsedRangeSet an object variable representing the group of related cells that surround the active cellSet rngCurrent = ActiveCell.CurrentRegionSet an obje ct variable representing the first three columns in the active worksheetSet rngCurrent = Range ( "A: C") Set an object variable representing rows 3, 5, 7, and 9 of the active worksheetSet rngCurrent = Range ( "3: 3, 5: 5, 7: 7, 9: 9 ") SET An Object Variable Representing Multiple Noncontiguous Groups of Cells on The Active SheetSet RNGCURRENT = Range (" A1: C4, D6: G12, I2: L7 ") Remove The Contents for All Cells within A Specified Group of Cells (B5: B10) While Leaving The Formatting IntactRange ("B5", "B10"
) .ClearContents As you can see from the examples, the Cell argument of the Range property can be either an A1-style string reference or a string that represents a named range within the current workbook. You can also use the Range property to return Range objects in arguments for other methods in the Excel object model. When you use the Range property in this way, make sure you fully qualify the Worksheet object to which the Range property applies. Failing to use fully qualified references to the Range property in arguments for Excel methods is one of the most common sources of error in range-related code. Working with the active cell or the current selection The ActiveCell property returns a Range object that represents the currently active cell. When a single cell is selected, the ActiveCell property Returns a Range Object That Reresents That Single Cell. When Multiple Cells Are Selected, The Active Cells Are Selected, The Active Cell The CURRENT Selection .when A cell or group of cells is selected, the Selection property returns a Range object representing all the cells within the current selection. To understand how the ActiveCell and Selection properties relate, consider a case in which a user selects cells A1 through F1 by clicking cell A1 and dragging until the selection extends over cell F1. In this case, the ActiveCell property returns a Range object that represents cell A1. The Selection property returns a Range object that represents cells A1 through F1. When you work with Excel '
s user interface, you typically select a cell or group of cells and then perform an action on the selected cell or cells, such as entering a value for a single cell or formatting a group of cells. When you use VBA to work with cells, you do not need to make a selection before performing an action on a cell or group of cells. Instead, you need only return a Range object that represents the cell or cells you want to work with. For example, to enter January as the value for cell A1 by using the user interface, you would select cell A1 and type January The following sample performs the same action in VBA:.. ActiveSheet.Range ( "A1") value = "January" Using VBA to work with a Range object in this manner does not change the selected cells on the current worksheet. However, you can make your VBA code act upon cells in the same way as a user working through the user interface by using the Range object's Select method to select a cell or Range of Cells, Then Using The Range Object's Activate Method T O Activate a Cell With, The Following Code Selects Cells A1 THROUGH A6, THEN MAKES CELL A3 The Active Cell: with activesheet
.RANGE ("A1: A6"). SELECT
.Range ("a3"). Activate
End with
When you use the Select method to select multiple cells, the first cell referenced will be the active cell. For example, in the preceding sample, after the Select method is executed, the ActiveCell property returns a reference to cell A1, even though cells A1 through A6 are selected. After the Activate method is executed in the next line of code, the ActiveCell property returns a reference to cell A3 while cells A1 through A6 remain selected. The next example illustrates how to return a Range object by using the ActiveCell property OR The Selection Property: DIM RNGACTIVERANGE AS Excel.Range 'Range Object Returned from The Selection Property.
Set RNGActiveRange = SELECTION
Call PrintRangeInfo (RNGActiveRange)
'Range Object Returned from the ActiveCell Property.
Set RNGActiveRange = Activecell
Call PrintRangeInfo (RNGActiveRange)
The PrintRangeInfo custom procedure called in the preceding example prints information about the cell or cells contained in the Range object passed in the argument to the procedure.Sub PrintRangeInfo (rngCurrent As Excel.Range)
DIM RNGTEMP AS Excel.Range
DIM STRVALUE AS STRING
DIM STRRANGENAME AS STRING
DIM straddress as string
Dim Strformula As String
ON Error ResMe next
StrRraname = rngcurrent.name.name_
& "(" & RNGCURRENT.NAME.REFERSTO & ")"
straddress = RNGCurrent.address
For Each RNGTEMP in RNGCURRENT.CELLS
IF ISempty (RNGTEMP) THEN
Strvalue = Strvalue & "Cell (" & RNGTEMP.Address_
& ") IS Empty." & VBCRLF
Else
Strvalue = Strvalue & "Cell (" & RNGTEMP.Address_
& ") =" & rngTemp.value _
& "Formula" & rngtemp.formula & vbcrlfendiff
Next RNGTEMP
Debug.Print Iif (Len (StrRaname)> 0, "Range Name =" _
& stranamename, "Range Not Named" & Vbcrf & "Address =" _
& straddress & vbcrlf & strvalue
Debug.print "************************"
Debug.print
IF Err> 0 Then Err = 0
End Sub
Working with cells and groups of cells You will often have to write code to work against a range of cells, but at the time you write the code you will not have information about the range. For example, you may not know the size or location of a range or the location of a cell in relation to another cell. you can use the CurrentRegion and UsedRange properties to work with a range of cells whose size you have no control over. you can use the Offset property to work with cells in relation to other cells where the cell location is unknown. The Range object's CurrentRegion property returns a Range object that represents a range bounded by (but not including) any combination of blank rows and blank columns or the edges of the worksheet. For example, if you had a table of data within cells D3 through E12 and the focus was in cell D3, then the CurrentRegion property would return a Range object that represents cells D3 through E12. The CurrentRegion property can return many ranges on a single w orksheet. This property is useful for operations where you need to know the dimensions of a group of related cells, but all you know for sure is the location of a cell or cells within the group. For example, if the active cell were inside a table of cells, you could use the following line of code to apply formatting to the entire table: ActiveCell.CurrentRegion.AutoFormat xlRangeAutoFormatAccounting4You could also use the CurrentRegion property to return a collection of cells For example:. Dim rngCurrentCell As Excel.Range
For Each RNGCurrentcell in Activecell.currentregion.cells
'Work with Individual Cells Here.
Next RNGCURRENTCELL
Every Worksheet object has a UsedRange property that returns a Range object representing the area of a worksheet that is being used. The UsedRange property represents the area described by the farthest upper-left and farthest lower-right cells that contain data on a worksheet, and includes all cells in between regardless of whether they contain data For example, imagine a worksheet with entries in only two cells:.. A1 and G55 The worksheet's UsedRange property would return a Range object that contains all the cells from A1 to G55 You might. use the UsedRange property together with the SpecialCells method to return a Range object that represents all cells of a specified type on a worksheet For example, the following code returns a Range object that includes all the cells in the active worksheet that contain a formula.: DIM RNGFORMULAS AS Excel.rangeset Rngformulas = Activesheet.Usedrange.Specialcells (XLCellTypeformulas)
You can use the Cells property to loop through a range of cells on a worksheet or to refer to a range by using numeric row and column values. The Cells property returns a Range object that represents all the cells, or a specified cell, in a worksheet. to work with a single cell, you use the Item property of the Range object returned by the Cells property, and specify the index of a specific cell you want to work with. The Item property accepts arguments that specify the row or the row . and column index for a cell Since the Item property is the default property of the Range object, it is not necessary to explicitly reference it for example, the following Set statements both return a reference to cell B5 on Sheet1:. Dim rng1 As Excel . Range
DIM RNG2 AS Excel.Range
SET RNG1 = Worksheet ("Sheet1"). Cells.Item (5, 2) SET RNG2 = Worksheet ("Sheet1"). Cells (5, 2)
. The row and column index arguments of the Item property return references to individual cells, beginning with the first cell in the specified range For example, the following message box displays G11 because that is the first cell in the specified Range object: MsgBox Range ( "G11: M30"). Cells (1, 1) .address
The following procedure illustrates how you would use the Cells property to loop through all the cells in a specified range. The OutOfBounds procedure looks for values that are greater than or less than a specified range of values, and changes the font color for each cell with Such a value: Function Outofbounds (RNGToCheck as Excel.Range, _
LNGLOWVALUE AS Long, _
LNGHIGHVALUE As Long, _
OPTIONAL LNGHIGHLIGHTCOLOR As long = 255) as boolean
'This Procedure Illustrates How To Use the Cells Property
'To Itereate Through a collection of cells in a ing.
'For Each Cell in The RNGTOCHECK RANGE, IF The Value of The
'Cell Is Numeric and It Falls Outside The Range of Values
'specified by lnglowValue to lnghighue, The Cell Font
'is change to the value of lnghighlightcolor
'(Default is red).
DIM RNGTEMP AS Excel.Range
DIM LNGROWCOUNTER AS Long
DIM LNGCOLCOUNTER AS Long
'Validate Bounds Parameters.
IF LNGLOWVALUE> LNGHIGHVALUE THEN
Err.raise VbobjectError 512 1, _
"Outofbounds Procedure", _
"Invalid Bounds Parameters Submitted:" _
& "Low Value Must Be Lower Than High Value."
EXIT FUNCTION
END IF
'Itereate Through Cells and Determine IF Values
'Are Outside Bounds Parameters. if so, highlight value.for lngrowcounter = 1 to RNGTOCHECK.ROWS.COUNT
For LNGCOLCOUNTER = 1 to RNGTOCHECK.COLUMNS.COUNT
Set rngtemp = rngtocheck.cells (LNGROWCUNTER, _
LNGCOLCOUNTER)
IF isnumeric (RNGTEMP.VALUE) THEN
If RNGTEMP.VALUE RNGTEMP.VALUE> LNGHIGHVALUE THEN RNGTEMP.FONT.COLOR = lnghighlightcolor Outofbounds = TRUE END IF END IF Next LNGCOLCOUNTER Next lngrowcounter END FUNCTION You can also use a For EachÃ, ... Next statement to loop through the range returned by the Cells property The following code could be used in the OutOfBounds procedure to loop through cells in a range:. 'Iterate through cells and determine if values 'Are Outside Bounds Parameters. if So, Highlight Value. For Each RNGTEMP in RNGTOCHECK.CELLS IF isnumeric (RNGTEMP.VALUE) THEN If RNGTEMP.VALUE RNGTEMP.VALUE> LNGHIGHVALUE THEN RNGTEMP.FONT.COLOR = lnghighlightcolor Outofbounds = TRUE END IF END IF Next RNGTEMP You can use the Offset property to return a Range object with the same dimensions as a specified Range object, but offset from the specified range. For example, you could use the Offset property to create a new Range object adjacent to the active cell to contain calculated values based on the active cell. The Offset property is useful when you do not know the specific address of the cells you will need to work with, but you do know where the cell is located in relation to other cells you need to work with . For example, you may have a command bar button in your custom solution that fills the active cell with the average of the values in the two cells immediately to the left of the active cell: ActiveCell.Value = (ActiveCell.Offset (0, -2) Activecell.offset (0, -1) / 2) Where to get More Info for Additional Information About Working with Excel, Check Out The Following Resources: For an overview of working with the Excel object, see last month's column. For more information on the Excel object model see http://msdn.microsoft.com/library/default.asp?URL=/library/officedev/odeomg/deovrmicrosoftexcel2000 .htm. for more information on working with Office application object models, see Understanding Office Objects and Object Models. As always, check in regularly at the Office Developer Center for information and technical articles on Office solution development.
