No Cell Object
There is no Cell object nor is there a Cells collection.
Individual cells are treated as Range objects that refer to one cell.
Contents of a Cell
The easiest way to find what the contents of a cell are is to use the Visual Basic TypeName function
Range("A1").Value = 100
Range("B1").Value = VBA.TypeName(Range("A1").Value) = "Double"
Range("A2").Value = #1/1/2006#
Range("B2").Value = VBA.TypeName(Range("A2").Value) = "Date"
Range("A3").Value = "some text"
Range("B3").Value = VBA.TypeName(Range("A3").Value) = "String"
Range("A4").Formula = "=A1"
Range("B4").Value = VBA.TypeName(Range("A4").Value) = "Double"
You can assign a value to a cell using its Value property.
You can give a cell a number format by using its NumberFormat property.
The Text property of a cell returns the formatted appearance of the contents of a cell.
Range("A1").Text = "$12,345.00"
The Range object can consist of individual cells or groups of cells.
Even an entire row or column is considered to be a range.
Although Excel can work with three dimensional formulas the Range object in VBA is limited to a range of cells on a single worksheet.
It is possible to edit a range either using a Range object directly (e.g. Range("A1").BackColor ) or by using the ActiveCell or Selection methods (e.g. ActiveCell.BackColor )
When the cells property is applied to a Range object the same object is returned.
It does have some uses though:
Range.Cells.Count - The total number of cells in the range.
Range.Cells(row, column) - To refer to a specific cell within a range.
ActiveSheet.Cells(2,2) = ActiveSheet.Range("B2")
To loop through a range of cells
For irow = 1 to 4
For icolumn = 1 to 4
ActiveSheet.Cells(irow,icolumn).Value = 10
Dim objRange As Range
Set objRange = ActiveSheet.Range(ActiveSheet.Cells(1,4), ActiveSheet.Cells(2,6))
Cells automatically refer to the active worksheet.
If you want to access cells on another worksheet then the correct code is:
Range( Worksheets(n).Cells(""), Worksheets(n).Cells("") )
When Range is not prefixed and used in a worksheet module, then it refers to that specific worksheet and not the active worksheet.
Selection will return a range of cells
Be aware that the Selection will not refer to a Range object if another type of object, such as a chart or shape is currently selected.
Using the Selection object performs an operation on the currently selected cells.
If a range of cells has not been selected prior to this command, then the active cell is used.
It is always worth checking what is currently selected before using the Selection property.
If TypeName(Selection) = "Range" Then
It is important to remember that when a Cells property or a Range property is applied to a Range object, all the references are relative to the upper-left corner of that range.
ActiveSheet.Range("B2:D4").Cells(2,2) = ActiveSheet.Range("C3")
ActiveSheet.Range("B2:D4").Range("B2") = ActiveSheet.Range("C3")
ActiveCell returns a reference to the currently active cell
This will only ever return a single cell, even when a range of cells is selected.
The active cell will always be one of the corner cells of a range of cells. - will it what if you use the keyboard ??
Total number of populated cells
iTotal = Application.CountA(ActiveSheet.Cells)
Window Object Only
This property applies only to a window object
This will enter the value 12 into the range that was selected before a non-range object was selected.
ActiveWindow.RangeSelection.Value = 12
Window.RangeSelection property is read-only and returns a Range object that represents the selected cells on the worksheet in the active window.
If a graphic object is active or selected then this will returns the range of cells that was selected before the graphic object was selected.
Dim ltotal As Long
ltotal = Cells.Count 'returns a long
Dim dbtotal As Double
dbtotal = Cells.CountLarge ' returns a double
Range of a Range
It is possible to treat a Range as if it was the top left cell in the worksheet.
This can be used to return a reference to the upper left cell of a Range object
The following line of code would select cell "C3".
Remember that cells are numbered starting from A1 and continuing right to the end of the row before moving to the start of the next row.
An alternative to this is to use the Offset which is more intuitive.
The Address method returns the address of a range in the form of a string.
Range("A1:B3").Address = "$A$1:$B$3"
Using the parameters allows you to control the transformation into a string (absolute vs relative).
[RelativeTo]) As String
RowAbsolute - True or False, default is True
ColumnAbsolute - True or False, default is True
ReferenceStyle - xlReferenceStyle.xlA1
External - True to return an external reference, default is false
RelativeTo - Range representing a relative to cell. Only relevant when ReferenceStyle = xlR1C1
This is similar to Address but it returns the address in the regional format of the language of the particular country.
Cells.Replace SearchFormat:=True, ReplaceFormat:=True
lLastRowNo = ActiveCell.End(xlDown).Row
Moving large amounts of data quickly
Dim vArray As Variant
vArray = Range("A1").Resize(10,10)
Range("H6").Resize(10,10) = vArray
Obtaining the cell reference of the active cell
Dim lrownumber As Long
Dim icolumnno As Integer
lrownumber = ActiveCell.Row
icolumnno = ActiveCell.Column
Call MsgBox("The currently active cell is " & lrowno & " , " & icolumnno)
Makes the active cell the top left cell in the window
ActiveWindow.ScrollColumn = ActiveCell.Column
ActiveWindow.ScrollRow = ActiveCell.Row
Set objRange = Selection
Looping through all the selected cells
For Each objCell In Selection.Cells
Range of cells currently selected
ifirstcol = Range(Selection.Address).Column
inoofcols = Range(Selection.Address).Column + Selection.Columns.Count
lfirstrow = Range(Selection.Address).Row
lnoofrows = Range(Selection.Address).Row + Selection.Rows.Count
Selection.Font.Size = 12
If TypeName(Selection) = "Range" Then MsgBox("More than one cell currently selected")
Call MsgBox( Range("A3").Font.ColorIndex )
Call MsgBox( Range("A3").Interior.ColorIndex )
If (Range("A3").Font.ColorIndex < 0) Then Call MsgBox (" When ?")
If (Range("A3").Interior.ColorIndex < 0) Then Call MsgBox (" When ?")
Range("A2").Font.Bold = True
Range("A4:D10").NumberFormat = "mmm-dd-yyyy"
Looping Through Cells
Dim rgeCell As Cell
For Each rgeCell In Range("A1:D30").Cells
rgeCell.Value = 20
Dim rgeCurrent As Range
Do While Not IsEmpty(rgeCurrent)
Set rgeCurrent = rgeCurrent.Offset(1,0)
You can prevent the user scrolling around a worksheet by defining the scroll area. Worksheets("Sheet1").ScrollArea = "A1:D400". To set the scrolling back to normal just assign the ScrollArea to an empty string. Note that this setting is not saved so it may be necessary to include it in the WorkBook_Open() event procedure.
You can quickly assign an Excel Range of cells to an array and visa-versa. Be aware that these arrays will start at 1 and not 0. vArrayName = Range(---).Value.
Determining a Cell Datatype
Help determine the type of data contained in a cell.
This accepts a range of any size but only operates on the upper left cell in the range.
' Returns the cell type of the upper left
' cell in a range
Set Rng = Rng.Range("A1")
Select Case True
Case IsEmpty(Rng): CellType = "Blank"
Case Application.IsText(Rng): CellType = "Text"
Case Application.IsLogical(Rng): CellType = "Logical"
Case Application.IsErr(Rng): CellType = "Error"
Case IsDate(Rng): CellType = "Date"
Case InStr(1, Rng.Text, ":") <> 0: CellType = "Time"
Case IsNumeric(Rng): CellType = "Value"
Determining whether a range is contained in another range
Function InRange(rng1, rng2) As Boolean
' Returns True if rng1 is a subset of rng2
InRange = False
If rng1.Parent.Parent.Name = rng2.Parent.Parent.Name Then
If rng1.Parent.Name = rng2.Parent.Name Then
If Union(rng1, rng2).Address = rng2.Address Then
InRange = True
© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext