VBA Code - Excel Cells & Ranges

VBA Code

Empty Cells

The best way to test if a cell is empty is to use the IsEmpty() function.

If VBA.IsEmpty = False Then

Testing for a zero-length string will be true when a formula returns a zero-length string

If rgeCell.Value <> "" Then

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 cellsBe 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 cellThis 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 objectThis 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 objectThe 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" Range("A1:B3").Address(False,False) = "A1:B3"

Using the parameters allows you to control the transformation into a string (absolute vs relative).

Range.Address([RowAbsolute], [ColumnAbsolute], [ReferenceStyle], [External], [RelativeTo]) As String

RowAbsolute - True or False, default is TrueColumnAbsolute - True or False, default is TrueReferenceStyle - xlReferenceStyle.xlA1External - True to return an external reference, default is falseRelativeTo - Range representing 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 rgeCell As Cell For Each rgeCell In Range("A1:D30").Cells rgeCell.Value = 20 Next rgeCell 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

Save Shape As PNG

Sub SaveShapeAsPicture() Dim cht As ChartObject Dim ActiveShape As Shape Dim UserSelection As Variant On Error GoTo ErrorHandler Set UserSelection = ActiveWindow.Selection Set ActiveShape = ActiveSheet.Shapes(UserSelection.Name) 'Create a temporary chart object (same size as shape) Set cht = ActiveSheet.ChartObjects.Add( _ Left:=ActiveCell.Left, _ Width:=ActiveShape.Width, _ Top:=ActiveCell.Top, _ Height:=ActiveShape.Height) 'Format temporary chart to have a transparent background cht.ShapeRange.Fill.Visible = msoFalse cht.ShapeRange.Line.Visible = msoFalse 'Copy/Paste Shape inside temporary chart ActiveShape.Copy cht.Activate ActiveChart.Paste 'Save chart to User's Desktop as PNG File cht.Chart.Export Environ("USERPROFILE") & "\Desktop\" & ActiveShape.Name & ".png" 'Delete temporary Chart cht.Delete 'Re-Select Shape (appears like nothing happened!) ActiveShape.Select Exit Sub ErrorHandler: End Sub

Save Range as JPG

Sub SaveRangeAsPicture() Dim cht As ChartObject Dim ActiveShape As Shape On Error GoTo ErrorHandler 'Confirm if a Cell Range is currently selected If TypeName(Selection) <> "Range" Then MsgBox "You do not have a single shape selected!" Exit Sub End If 'Copy/Paste Cell Range as a Picture Selection.Copy ActiveSheet.Pictures.Paste(link:=False).Select Set ActiveShape = ActiveSheet.Shapes(ActiveWindow.Selection.Name) 'Create a temporary chart object (same size as shape) Set cht = ActiveSheet.ChartObjects.Add( _ Left:=ActiveCell.Left, _ Width:=ActiveShape.Width, _ Top:=ActiveCell.Top, _ Height:=ActiveShape.Height) 'Format temporary chart to have a transparent background cht.ShapeRange.Fill.Visible = msoFalse cht.ShapeRange.Line.Visible = msoFalse 'Copy/Paste Shape inside temporary chart ActiveShape.Copy cht.Activate ActiveChart.Paste 'Save chart to User's Desktop as PNG File cht.Chart.Export Environ("USERPROFILE") & "\Desktop\" & ActiveShape.Name & ".jpg" 'Delete temporary Chart cht.Delete ActiveShape.Delete 'Re-Select Shape (appears like nothing happened!) ActiveShape.Select Exit Sub ErrorHandler: End Sub © 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext