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


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