|
|
The Range Object (SARange)
The Range Object allows you to make a modification to a range of cells within a worksheet, such as the application of a style, by the assignment of methods and properties. To create a Range object, use, Cells.Range, Cells.Rows, or Cells.Columns.
A Range object may include non-adjacent rectangular areas*.
Range can be used to assign a certain group of cells to ExcelWriter's PrintArea* property. PrintArea is a Range that is read by the PageSetup* object when determining the portion of the page that should be printed.
| Range Properties | Range Methods |
|---|---|
| Range Properties | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Area* | A Range may include non-adjacent rectangular areas. The Area property takes an index number, and returns - as a Range object - the corresponding rectangular area within the range. Signature: [VBScript] Property Area(Index As Integer) As SARange (read-only) Example: [VBScript] '--- "MyRange" is a SARange with 2 areas '--- Get the first area from MyRange Set rangeOne = MyRange.Area(1) '--- Get the second area from MyRange Set rangeTwo = MyRange.Area(2) |
||||||||||||||
| AreaCount* | A Range may include non-adjacent rectangular areas. AreaCount returns the number of rectangular areas in a Range object. Signature: [VBScript] Property AreaCount As Integer (read-only) Example: [VBScript] //--- "MyRange" is a SARange object //--- Get the number of areas in MyRange NumAreas = MyRange.AreaCount |
||||||||||||||
| Cell | Returns the value of a specified cell in the range. Cell takes one argument, the cell's name (e.g. "a1"). Signature: [VBScript] Property Cell(CellName As String) As SACell (read-only) Example:
[VBScript]
Dim XlwApp, WrkSht, MyRange, FirstCell
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
SAWorksheet WrkSht = XlwApp.Worksheets[1];
'--- This is the range A2:B4
Set MyRange = WrkSht.Cells.Range(2,1,2,2)
'--- Get the first cell in the range
Set FirstCell = MyRange.Cell("A1")
Important: Range.cell's reference is relative to the first cell in the range,
not the first cell in the worksheet. For example, if the range spans the cells b2:d4,
|
||||||||||||||
| ColumnCount | Returns the number of columns in the range. Signature: [VBScript] Property ColumnCount As Integer (read-only) Example:
[VBScript]
Dim XlwApp, WrkSht, MyRange, ColCount
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
SAWorksheet WrkSht = XlwApp.Worksheets[1];
'--- This is the range A2:B4
Set MyRange = WrkSht.Cells.Range(2,1,2,2)
'--- Get the number of columns in the range
ColCount = MyRange.ColumnCount ' returns 2
|
||||||||||||||
| ColumnWidth | Use ColumnWidth to set the width of columns in a specified range. Signature: [VBScript] Property ColumnWidth As Double (write-only) Example:
[VBScript]
Dim XlwApp, WrkSht, MyRange
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
SAWorksheet WrkSht = XlwApp.Worksheets[1];
'--- This range has columns A through D
Set MyRange = WrkSht.Cells.Columns(1,4)
'--- Set the column width for the range to 10
MyRange.ColumnWidth = 10
|
||||||||||||||
| FirstColumn | Returns the number of the first column in the range. Signature: [VBScript] Property FirstColumn As Integer (read-only) Example:
[VBScript]
Dim XlwApp, WrkSht, MyRange, FirstCol
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
SAWorksheet WrkSht = XlwApp.Worksheets[1];
'--- This range has columns D through F
Set MyRange = WrkSht.Cells.Columns(4,3)
'--- Get the first column in the range
FirstCol = MyRange.FirstColumn ' returns 4
|
||||||||||||||
| FirstRow | Returns the number of the first row in the range. Signature: [VBScript] Property FirstRow As Integer (read-only) Example:
[VBScript]
Dim XlwApp, WrkSht, MyRange, FirstRow
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
SAWorksheet WrkSht = XlwApp.Worksheets[1];
'--- This range has Rows 4 through 6
Set MyRange = WrkSht.Cells.Rows(4,3)
'--- Get the first column in the range
FirstRow = MyRange.FirstRow ' returns 4
|
||||||||||||||
| Item | Returns the value of a specified cell in the range. Item takes either the cell name (e.g. "a1"), or the cell row and column (e.g. (1,1)). Item is the default property of the Range object, so range(1,1) is equivalent to range.item(1,1). Signature: [VBScript] In VBScript, this is the default property for the SARange class. Property Item(Row As Variant[, Column As Long]) As SACell (read-only) Example:
[VBScript]
Dim XlwApp, WrkSht, MyRange, FirstCell
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
SAWorksheet WrkSht = XlwApp.Worksheets[1];
'--- This range is B4:E9
Set MyRange = WrkSht.Cells.Range(4,2,6,4)
'--- Get the first cell in the range
Set FirstCell = MyRange(1,1) ' MyRange("A1") is also valid in VBScript
Important: Item's cell reference is relative to the first cell in the range,
not the first cell in the worksheet. For example, if the range spans the cells b2:d4,
|
||||||||||||||
| Name* | Sets or returns the name of a specified range of cells. In an Excel spreadsheet, named ranges are listed in the name box (above the top left corner of the spreadsheet); to move to a named range, select the name from the name list. A range name must not include spaces. Signature: [VBScript] Property Name As String (read/write) Example:
[VBScript]
WrkSht.Cells("A1").Value = "Last Name"
WrkSht.Cells("B1").Value = "First Name"
WrkSht.Cells("C1").Value = "Address"
WrkSht.Cells("D1").Value = "Phone"
'--- Create a range that contains cells A1:D1
Set HeadingRange = WrkSht.Cells.Range(1,1,1,4)
'--- Create a name for the range
HeadingRange.Name = "Headings"
When assigning a range name, do not use any of the following built-in names:
Example: Top |
||||||||||||||
| RowCount | Returns the number of rows in the range. Signature: [VBScript] Property RowCount As Integer (read-only) Example:
[VBScript]
Dim XlwApp, WrkSht, MyRange, NumRows
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
SAWorksheet WrkSht = XlwApp.Worksheets[1];
'--- This range is B4:E9
Set MyRange = WrkSht.Cells.Range(4,2,6,4)
'--- Get the number of rows in the range
NumRows = MyRange.RowCount ' returns 6
|
||||||||||||||
| RowHeight | Use RowHeight to set the height of rows in a specified range. Signature: [VBScript] Property RowHeight As Integer (write-only) Example:
[VBScript]
Dim XlwApp, WrkSht, MyRange
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)
'--- This range is rows 2 through 6
Set MyRange = WrkSht.Cells.Rows(2,5)
'--- Set the row height to 10
MyRange.RowHeight = 10
|
||||||||||||||
| Style* | Setting this property allows for the easy application of a pre-defined custom or intrinsic ExcelWriter style to each and every cell that is a member of the range to which the style assignment is made.
Signature: [VBScript] Property Style As SAStyle (write-only) If a cell is part of more than one range, and each range has a different style, the final applied style will overwrite all previously assigned styles. To overwrite the style for a single cell, use the Cell.Style property. To supplement the previously applied style for a single cell with additional formatting properties, use the Cell.Format property. Example: The following example creates a custom “TextStyle” with property settings. It defines a range object (Rng1) that encompasses two rows and spans 5 columns and then assigns the custom “TextStyle” to this range. Finally, it adds an additional formatting property (bold faced font) to Cell “E5” only (which is a member of Rng1). [VBScript] Set TextStyle = xlw.CreateStyle TextStyle.Number = 49 TextStyle.Font = MyFont TextStyle.WrapText = True set Rng1 = Ws.Cells.Range(3,3,2,5) Rng1.Style = TextStyle Ws.Cells(3,5).Format.Font.Bold = True Important: It is essential, when designing the layout of a workbook, to understand the ramifications of using multiple styles and formats and to carefully pre-plan a schema for applying them. For more information on applying styles and formats correctly, see Adding Formatting. |
||||||||||||||
| UsedRange V5 |
Signature: [VBScript] Property UsedRange As SARange (read-only) Example:
[VBScript]
Dim XlwApp, WrkSht, MyRange, UsedRng
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)
'--- This range is A1:B6
Set MyRange = WrkSht.Cells.Range(1,1,6,2)
'--- Get the range of used cells
Set UsedRng = MyRange.UsedRange
Description
|
||||||||||||||
| Value* | Sets or returns, as a two-dimensional array, the cell values contained in a Range object. Signature: [VBScript] Property Value As Variant (read/write) Example: The following example creates column headings for the first worksheet in a workbook, and uses the Range object's Value property to assign identical headings to the second worksheet.
[VBScript]
set ws1 = xlw.worksheets(1)
set RngColHeadings1 = ws1.cells.range(1,1,1,4)
ws1.cells("A1").value = "Last Name"
ws1.cells("B1").value = "First Name"
ws1.cells("C1").value = "Address"
ws1.cells("D1").value = "Phone"
set ws2 = xlw.worksheets(2)
set RngColHeadings2 = ws2.cells.range(1,1,1,4)
RngColHeadings2.value = RngColHeadings1.value
|
||||||||||||||
| *This feature is not available in ExcelWriterSE, ExcelWriterLE, or ExcelWriterFree. | |||||||||||||||
| Range Methods | |||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| AddHyperlink* | Inserts a hyperlink into a range of cells. Signature: [VBScript] Sub AddHyperLink(Address As String[, SubAddress As String]) To insert a hyperlink in a range,
Note: Excel 95 does not support this feature. Example:
[VBScript]
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set Cells = XlwApp.Worksheets(1).Cells
Set Rng = Cells.Range(15,1,2,2)
Rng.MergeCells
Rng.AddHyperlink "http://www.softartisans.com"
Cells(15,1).value = "Click here for the SoftArtisans Home Page"
Cells(15,1).Style = XlwApp.Style("Hyperlink")
|
||||||||||||||||||||||||||||||
| AutoFitHeight | When WrapText is set to True, AutoFitHeight will adjust the row height of all rows in the range, according to the column width. Signature: [VBScript] Sub AutofitHeight() Note: To ensure that AutoFitHeight is applied correctly, set the values of the range before setting AutoFitHeight. Example: [VBScript] range.ColumnWidth = 5 Set mystyle = xlw.CreateStyle mystyle.WrapText = True range.Style = mystyle range.AutoFitHeight |
||||||||||||||||||||||||||||||
| AutoFitWidth | Adjusts the width of all columns in the range to the width of their longest values. Signature: [VBScript] Sub AutofitWidth() Note: To ensure that AutoFitWidth is applied correctly, set the values of the range before setting AutoFitWidth. Example: [VBScript] range.ColumnWidth = 5 Set mystyle = xlw.CreateStyle mystyle.WrapText = True range.Style = mystyle range.AutoFitWidth |
||||||||||||||||||||||||||||||
| BorderAround V5 | Syntax
Signature: [VBScript] Sub BorderAround(BorderLineStyle As SABorderLineStyle, BorderColor As Long) Description
Parameters
Example[VBScript] '--- This range is A1:B6 Set MyRange = WrkSht.Cells.Range(1,1,6,2) '--- Apply a thin blue border around cells A1:B6 MyRange.BorderAround sadgThin, RGB(0,0,255) |
||||||||||||||||||||||||||||||
| GetCellAt | Gets the value contained in a specified cell. GetCellAt takes two arguments, row and column. Signature: [VBScript] Function GetCellAt(Row As Integer, Column As Integer) As SACell Important: GetCellAt's parameters are relative to the first cell in the range,
not the first cell in the worksheet. For example, if the range spans the cells b2:d4,
Example: [VBScript] Set FirstRangeCell = range.GetCellAt(1,1) |
||||||||||||||||||||||||||||||
| GroupColumns* | Outlines specified columns within a range. Signature: [VBScript] Sub GroupColumns(FirstColumn As Long, _ [NumColumns As Long = 1], _ [Collapsed As Boolean = False])
Important: If you wish to designate the location of the summary column as the last column of the outlined group, then the Cells.SummaryColumn property must be set using the saxlSummaryOnRight (0) value. The summary column should always be visible. Example: [VBScript] '--- Define a range from cell A2 to cell E11 Set range1 = ws.Cells.Range(2,1,10,5) '--- Outline columns A to D, and hides the outline detail columns. '--- Column A is the summary column and will be visible. This assumes that you have '--- set the "SummaryColumn" property to place summary columns to the left of detail '--- columns. Columns B to D are the outline detail columns and will be hidden. range1.GroupColumns 1, 4, True See also, GroupRows. |
||||||||||||||||||||||||||||||
| GroupRows* | Outlines specified rows within a set of cells. Signature: [VBScript] Sub GroupRows(FirstRow As Long, [NumRows As Long = 1], [Collapsed As Boolean = False])
Important: If you wish to designate the location of the summary row as the last row of the outlined group, then the Cells.SummaryRow property must be set using the saxlSummaryBelow (0) value. The summary row should always be visible. Example: [VBScript] '--- Define a range from cell A2 to cell E11 Set range1 = ws.Cells.Range(2,1,10,5) '--- Outline rows 2 to 4, and hide the outline detail rows. '--- Row 2 is the summary row and will be visible. This assumes that you have '--- set the "SummaryRow" property to place the summary row above the detail rows. '--- Rows 3 and 4 are the outline detail rows and will be hidden. range1.GroupRows 1, 3, True See also, GroupColumns. |
||||||||||||||||||||||||||||||
| Intersect* | Creates an intersection of two Range objects. Signature: [VBScript] Sub Intersect(Range As SARange) Example: [VBScript] set range3 = ws1.cells.range(22,7,5,4) set range4 = ws1.cells.range(23,5,5,4) range3.intersect range4 |
||||||||||||||||||||||||||||||
| MergeCells* | Combines a range of specified cells into a single cell. Use the address of the upper-left cell in the range to reference the merged cell. Note: Excel 95 does not support this feature. Signature: [VBScript] Sub MergeCells() Example: [VBScript] '--- Create a range and merge the cells into one Set Rng = Cells.Range(15,1,2,2) Rng.MergeCells |
||||||||||||||||||||||||||||||
| Union* | Creates a union of two Range objects. Signature: [VBScript] Sub Union(range As SARange) Example: [VBScript] '--- Create a union of two different ranges Set range4 = ws1.Cells.Range(16,1,5,4) Set range5 = ws1.Cells.Range(23,5,5,4) range4.Union range5 |
||||||||||||||||||||||||||||||
| *This feature is not available in ExcelWriterSE, ExcelWriterLE, or ExcelWriterFree. | |||||||||||||||||||||||||||||||
Copyright © 2005, SoftArtisans, Inc.