VBScript Excel.Range Object
last modified April 9, 2025
The Excel.Range object in VBScript represents a cell, a row, a column,
or a selection of cells in an Excel worksheet. It is the primary object for
interacting with worksheet data. The Range object provides properties and
methods to read, write, and manipulate Excel cells programmatically.
With the Range object, you can access cell values, apply formatting, perform calculations, and more. This tutorial covers the Range object with practical examples to demonstrate its usage in Excel automation with VBScript.
Excel.Range Object Overview
The Excel.Range object is part of the Excel object model accessed
through VBScript. It can reference single cells (like "A1") or multiple cells
(like "A1:B10"). The object provides numerous properties and methods for cell
manipulation.
Key properties include Value for cell content, Formula
for formulas, and Font for text formatting. Methods include
Copy, Clear, and Select. Understanding
this object is essential for Excel automation with VBScript.
Accessing a Single Cell
This example demonstrates how to access and modify a single cell's value using the Range object. It shows basic Excel automation with VBScript. The script creates an Excel instance, accesses a worksheet, and modifies cell A1.
Set excel = CreateObject("Excel.Application")
excel.Visible = True
Set workbook = excel.Workbooks.Add()
Set worksheet = workbook.Worksheets(1)
' Access and modify cell A1
Set rng = worksheet.Range("A1")
rng.Value = "Hello, Excel!"
' Clean up
Set rng = Nothing
Set worksheet = Nothing
Set workbook = Nothing
Set excel = Nothing
The script starts Excel, creates a new workbook, and gets the first worksheet. It then accesses cell A1 using the Range object and sets its value. Finally, it cleans up by releasing all object references. Excel remains visible.
Working with Multiple Cells
This example shows how to work with a range of multiple cells. It demonstrates setting values for a block of cells and reading them back. The Range object can reference rectangular cell areas using the "A1:B2" notation.
Set excel = CreateObject("Excel.Application")
excel.Visible = True
Set workbook = excel.Workbooks.Add()
Set worksheet = workbook.Worksheets(1)
' Set values for range A1:B2
Set rng = worksheet.Range("A1:B2")
rng.Value = Array(Array(1, 2), Array(3, 4))
' Read and display values
For Each cell In rng
WScript.Echo "Cell " & cell.Address & " = " & cell.Value
Next
' Clean up
Set rng = Nothing
Set worksheet = Nothing
Set workbook = Nothing
Set excel = Nothing
The script creates a 2x2 array of values and assigns it to range A1:B2. It then iterates through each cell in the range and displays its address and value. The Array function creates a two-dimensional array for the range assignment.
Formatting Cells
This example demonstrates cell formatting using the Range object. It shows how to change font properties, cell colors, and number formatting. Formatting enhances data presentation in automated Excel reports.
Set excel = CreateObject("Excel.Application")
excel.Visible = True
Set workbook = excel.Workbooks.Add()
Set worksheet = workbook.Worksheets(1)
' Set cell value and apply formatting
Set rng = worksheet.Range("A1")
rng.Value = 1234.56
rng.Font.Bold = True
rng.Font.Color = RGB(255, 0, 0) ' Red
rng.NumberFormat = "$#,##0.00"
' Format a range of cells
Set rng2 = worksheet.Range("B1:D3")
rng2.Interior.Color = RGB(200, 200, 255) ' Light blue
rng2.Font.Name = "Arial"
rng2.Font.Size = 12
' Clean up
Set rng2 = Nothing
Set rng = Nothing
Set worksheet = Nothing
Set workbook = Nothing
Set excel = Nothing
The script formats cell A1 with bold red font and currency formatting. It then formats range B1:D3 with light blue background, Arial font, and 12pt size. The RGB function specifies colors using red, green, and blue components.
Using Cells Property
This example shows how to use the Cells property to access ranges
by row and column numbers. The Cells property provides an alternative to A1
notation. It's useful for programmatic cell access in loops.
Set excel = CreateObject("Excel.Application")
excel.Visible = True
Set workbook = excel.Workbooks.Add()
Set worksheet = workbook.Worksheets(1)
' Fill 5x5 grid using Cells property
For row = 1 To 5
For col = 1 To 5
Set cell = worksheet.Cells(row, col)
cell.Value = row * col
Next
Next
' Access a range using Cells
Set rng = worksheet.Range(worksheet.Cells(1,1), worksheet.Cells(5,5))
rng.Font.Bold = True
' Clean up
Set rng = Nothing
Set worksheet = Nothing
Set workbook = Nothing
Set excel = Nothing
The script fills a 5x5 grid with multiplication table values using nested loops. It then accesses the entire range using the Cells property and makes all text bold. The Cells property takes row and column numbers as parameters.
Working with Formulas
This example demonstrates how to work with Excel formulas using the Range object. It shows setting formulas, reading calculated values, and using relative references. Formulas are powerful tools for automated calculations.
Set excel = CreateObject("Excel.Application")
excel.Visible = True
Set workbook = excel.Workbooks.Add()
Set worksheet = workbook.Worksheets(1)
' Set values and formulas
worksheet.Range("A1").Value = 10
worksheet.Range("A2").Value = 20
worksheet.Range("A3").Formula = "=A1+A2"
worksheet.Range("A4").Formula = "=SUM(A1:A3)"
' Display formula and value
Set rng = worksheet.Range("A3")
WScript.Echo "Formula: " & rng.Formula & ", Value: " & rng.Value
' Clean up
Set rng = Nothing
Set worksheet = Nothing
Set workbook = Nothing
Set excel = Nothing
The script sets values in A1 and A2, then adds formulas in A3 and A4. The A3 formula adds A1 and A2, while A4 sums the range A1:A3. It then displays A3's formula and calculated value. Excel automatically calculates formula results.
Source
Excel Range Object Documentation
In this article, we have explored the Excel.Range object in VBScript, covering its usage and practical applications. From basic cell access to advanced formatting and formulas, these examples demonstrate Excel automation capabilities. With this knowledge, you can create powerful Excel automation scripts using VBScript.
Author
List all VBScript tutorials.