VBA Code


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"


Text Property

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" 


Range Object

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 )


Cells Property

Application.Cells 
ActiveSheet.Cells
ActiveCell
Cells

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
   Next icolumn
Next irow


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("") ) 


Range Property

Application.Range 
ActiveSheet.Range
Range

When Range is not prefixed and used in a worksheet module, then it refers to that specific worksheet and not the active worksheet.


Selection Property

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.

Selection.Resize(4,4).Select 

It is always worth checking what is currently selected before using the Selection property.

If TypeName(Selection) = "Range" Then 
End If


Relative References

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.


Counting

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".

Range("B2").Range("B2").Select 


Range("B2").Cells(2).Select 

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.

Range("B2").Cells(2,2).Select 

An alternative to this is to use the Offset which is more intuitive.


Range.Address

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 releative).

Range.Address([RowAbsolute], 
              [ColumnAbsolute],
              [ReferenceStyle],
              [External],
              [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 reprensing a relative to cell. Only relevant when ReferenceStyle = xlR1C1


Range.AddressLocal

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

ActiveCell.Select 
ActiveWindow.ScrollColumn = ActiveCell.Column
ActiveWindow.ScrollRow = ActiveCell.Row


Selection

Selection.PasteSpecial Paste:=xlValues 
Selection.Copy
Selection.Cut
Set objRange = Selection
Selection.Rows.Count


Looping through all the selected cells

For Each objCell In Selection.Cells 

Next objCell


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")


Formatting

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 cllCell As Cell 

For Each cllCell In Range("A1:D30").Cells
   cllCell.Value = 20
Next cllCell
Dim rgeCurrent As Range

Do While Not IsEmpty(rgeCurrent)
'do something
   Set rgeCurrent = rgeCurrent.Offset(1,0)
Loop


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.

Function CellType(Rng) 
' Returns the cell type of the upper left
' cell in a range
    Application.Volatile
    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"
    End Select
End Function


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
            End If
        End If
    End If
End Function



© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Top

PrevNext