Catégorie : Excel VBA Course

When working in Excel, it is useful to use comments, as they simplify viewing text attached to cells. To create and manage comments in MS Excel 2010, there is a Comments group on the Review tab of the ribbon. Alternatively, you can use the DisplayCommentIndicator property of the Application object to work with comments programmatically.

The DisplayCommentIndicator property of the Application object allows you to control the display style of comments. Acceptable values for this property are the following XlCommentDisplayMode constants:

• xlNoIndicator — no indicator;
• xlCommentIndicatorOnly — indicator only;
• xlCommentAndIndicator — both comment and indicator.

In the demonstration example, when the workbook is opened, comments are added to cells A1 and A4. When cell A1 is selected, both the comment and its indicator are displayed; when any other cell is selected, the comments are hidden.

Managing the Display of Comments and Their Indicators. ThisWorkbook Module

Private Sub Workbook_Open()
    Worksheets(1).Range("A1").ClearComments
    Worksheets(1).Range("A1").AddComment
    Worksheets(1).Range("A1").Comment.Visible = True
    Worksheets(1).Range("A1").Comment.Text Text:="This is cell A1"
    Worksheets(1).Range("A4").ClearComments
    Worksheets(1).Range("A4").AddComment
    Worksheets(1).Range("A4").Comment.Visible = True
    Worksheets(1).Range("A4").Comment.Text Text:="This is cell A4"
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
    If Sh.Name = Worksheets(1).Name Then
        If Target.Address = "$A$1" Then
            Application.DisplayCommentIndicator = xlCommentAndIndicator
        Else
            Application.DisplayCommentIndicator = xlNoIndicator
        End If
    End If
End Sub

The Replace method of the Range object performs replacements within a specified range.

Replace(What, Replacement, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat, ReplaceFormat)

• What — required parameter specifying the string to be replaced.
• Replacement — required parameter specifying the string to replace with.
• LookAt — optional parameter indicating how to search. Acceptable XlLookAt constants: xlWhole, xlPart.
• SearchOrder — optional parameter specifying the order in which to search the range. Acceptable XlSearchOrder constants: xlByRows, xlByColumns.
• SearchDirection — optional parameter specifying the search direction. Acceptable XlSearchDirection constants: xlNext, xlPrevious.
• MatchCase — optional parameter indicating whether to consider case in the search.
• MatchByte — optional parameter, rarely used.
• SearchFormat — optional parameter specifying the search format.
• ReplaceFormat — optional parameter specifying the replacement format.

For example, the following code replaces the string « MS » with « Microsoft » in column A:

Columns("A").Replace What:="MS", Replacement:="Microsoft", _
    SearchOrder:=xlByColumns, MatchCase:=True

The FindNext and FindPrevious methods of the Range object allow repeating the Find method to continue a specified search. The first method searches for the next cell, while the second searches for the previous cell that meets the search criteria.

FindNext(After)
FindPrevious(After)

Here, After is an optional parameter indicating the cell after which the search should continue.

As an example, the following code searches for the substring « BHV » case-insensitively in the range A1:A10. All found cells are filled with yellow.

Finding All Occurrences of a Substring in a Range

Sub Find2()
    Dim firstAddress As String
    Dim rng As Range   
    Set rng = Range("A1:A10").Find(What:="BHV", LookIn:=xlValues, _
        LookAt:=xlPart, MatchCase:=False)   
    If Not (rng Is Nothing) Then
        firstAddress = rng.Address
        Do
            rng.Interior.Color = RGB(255, 255, 0)
            Set rng = Range("A1:A10").FindNext(rng)
        Loop While Not (rng Is Nothing) And rng.Address <> firstAddress
    End If
End Sub

Commands from the Find & Select list on the Home tab in the Editing group allow you to quickly find and replace cell content according to specified criteria or simply perform a search. With VBA, you can also specify criteria for searching data within a specific range, perform replacements, etc. Let’s look at some examples.

Finding a Value in a Range
The Find method of the Range object searches for specified information within a given range and returns a reference to the first cell where the value is found. If the data is not found, the method returns Nothing.

Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

• What — required parameter specifying the data to search for.
• After — optional parameter indicating the cell after which to start the search.
• LookIn — optional parameter specifying where to search. Acceptable XlFindLookIn constants: xlComments, xlFormulas, xlValues.
• LookAt — optional parameter specifying how to search. Acceptable XlLookAt constants: xlWhole, xlPart.
• SearchOrder — optional parameter specifying the order of scanning the range. Acceptable XlSearchOrder constants: xlByRows, xlByColumns.
• SearchDirection — optional parameter specifying the search direction. Acceptable XlSearchDirection constants: xlNext, xlPrevious.
• MatchCase — optional parameter indicating whether to consider case.
• MatchByte — optional parameter, rarely used.
• SearchFormat — optional parameter specifying the search format.

For example, the following code  searches for the value 17 in the range A1:A10. If found, a message box displays the address of the first found cell.

Finding a Value

Sub Find1()
    Dim rng As Range
    Set rng = Range("A1:A10").Find(What:=17, LookIn:=xlValues)
    If Not (rng Is Nothing) Then
        MsgBox rng.Address
    Else
        MsgBox "Value not found"
    End If
End Sub

The code searches for the substring « BHV » case-insensitively in the range A1:A20. If found, a message box displays the Value of the found cell.

Finding a Substring Case-Insensitive

Sub DemoFindNoMatchCase()
    Dim rng As Range
    Set rng = Range("A1:A20").Find(What:="BHV", LookIn:=xlValues, _
        LookAt:=xlPart, MatchCase:=False)
    If Not (rng Is Nothing) Then
        MsgBox rng.Value
    Else
        MsgBox "No matching value found"
    End If
End Sub

AutoCorrect allows you to automatically replace certain typed characters (words) or abbreviations that were previously defined in the AutoCorrect dialog box.

The AutoCorrect property of the Application object returns an AutoCorrect object, which allows you to manage auto-correction on the worksheet. The properties of this object configure the parameters set in the AutoCorrect dialog on the AutoCorrect tab in the Replace as you type group: go to the File tab of the ribbon, click Options, in the Excel Options window select Proofing on the left, and in the AutoCorrect options group on the right, click the AutoCorrect Options button next to Correct spelling and formatting as you type.

For example, in the following code, the first procedure handles the Open event of the workbook and adds three new items to the AutoCorrect list. Specifically, спб will automatically be replaced with Санкт-Петербург, мск with Москва, and гр with Гродно. The second procedure handles the BeforeClose event triggered when closing the workbook, removing these three items from the AutoCorrect list.

The AutoFill method can be used to solve the problem of function tabulation, i.e., outputting its values as its parameter changes. For example, we may want to find the values of the function sin(x) for the parameter x changing from 0 to 2 in steps of 0.2.

First, enter the first term of the arithmetic sequence of the required parameter values into cell A1, and then use the DataSeries method to build the entire sequence down column A. Next, define the current range containing these values. The range where the corresponding function values will be placed is in column B, which can be obtained using the Offset property.

Finally, enter the formula =SIN(A1) in cell B1 to calculate the function value for the parameter equal to 0, and then copy this formula across the entire range allocated for the function values.

Function Tabulation

Sub DemoDataSeries()
    Range("A1").Value = 0
    Range("A1").DataSeries Rowcol:=xlColumns, Type:=xlDataSeriesLinear, _
        Step:=0.2, Stop:=2  
    Dim rgn As Range
    Set rgn = Range("A1").CurrentRegion
    Set rgn = rgn.Offset(0, 1)  
    Range("B1").Formula = "=SIN(A1)"
    Range("B1").AutoFill Destination:=rgn, Type:=xlCopy
End Sub

The AutoFill method of the Range object performs auto-filling of a range with sequence elements. The AutoFill method differs from the DataSeries method in that the range in which the progression will be placed is explicitly specified. The AutoFill method simulates the action of copying data to a range when the user places the mouse pointer on the fill handle of the source range and drags it down or to the right, selecting the entire range into which the source data is transferred.

expression.AutoFill(Destination, Type)

• expression — a required element that specifies the range from which filling begins.
• Destination — a required parameter that defines the range to be filled. This range must include the range specified in expression.
• Type — an optional parameter that specifies the type of fill. The permissible values are the following XlAutoFillType constants:
  xlFillDefault, xlFillSeries, xlFillCopy, xlFillFormats, xlFillValues, xlFillDays, xlFillWeekdays, xlFillMonths, xlFillYears, xlLinearTrend, xlGrowthTrend.
  By default, the type of fill that best matches the data in the range specified in expression is used.

For example, the following instructions  fill the range A1:A5 with the terms of an arithmetic progression, where the first two terms are 1 and 3 (i.e., the values that were previously entered into cells A1 and A2) .

Sequences. Arithmetic Sequence

Sub Progr4()
    Range("A1").Value = 1
    Range("A2").Value = 3
    Range("A1:A2").AutoFill Destination:=Range("A1:A5"), Type:=xlLinearTrend
End Sub

Listing demonstrates generating several terms of a geometric progression on a worksheet with the same two initial values in the range B1:B5 .

Listing. Sequences. Geometric Sequence

Sub Progr5()
    Range("B1").Value = 1
    Range("B2").Value = 3
    Range("B1:B2").AutoFill Destination:=Range("B1:B5"), Type:=xlGrowthTrend
End Sub

The following instructionsoutput into the range C1:C3 the sequence of values Summer 2010, Summer 2011, and Summer 2012 with a step of 1, determined by default by the AutoFill method.

Sequences. AutoFill

Sub Progr6()
    Range("C1").Value = "Summer 2010"
    Range("C1").AutoFill Destination:=Range("C1:C3"), Type:=xlFillSeries
End Sub

The following outputs into the range D1:D3 the first three items of a list — month names, starting with January.

Sequences. Months

Sub Progr7()
    Range("D1").Value = "January"
    Range("D1").AutoFill Destination:=Range("D1:D3"), Type:=xlFillSeries
End Sub

The following instructions copy the contents of cell E1 into all cells of the range E1:E3.

Sequences. Copying

Sub Progr8()
    Range("E1").Value = "January"
    Range("E1").AutoFill Destination:=Range("E1:E3"), Type:=xlCopy
End Sub

The DataSeries method of the Range object allows you to fill a range with a sequence (arithmetic, geometric, date-based, or AutoFill).
This method programmatically replicates the Fill | Series command available on the Ribbon.

DataSeries(RowCol, Type, Date, Step, Stop, Trend)

Parameters:

• RowCol (optional) – Direction of the series:
  • xlRows → fill across rows.
  • xlColumns → fill down columns.
    If omitted, Excel uses the size of the selected range.
• Type (optional) – The type of series. Possible values:
  • xlDataSeriesLinear (arithmetic, default).
  • xlGrowth (geometric).
  • xlChronological (date series).
  • xlAutoFill (pattern-based autofill).
• Date (optional) – Defines the type of date sequence when Type = xlChronological:
  • xlDay (days, default).
  • xlWeekday (weekdays only).
  • xlMonth (months).
  • xlYear (years).
• Step (optional) – Increment of the series (default = 1).
• Stop (optional) – The upper limit of the series. If omitted, Excel fills the entire selected range.
• Trend (optional) – Boolean.
  • True → generates arithmetic or geometric progression.
  • False → generates a list.

Examples

1. Arithmetic Progression
   The following macro fills range A1:A6 with an arithmetic progression starting at 0, step = 2, ending at 10.
   Result: 0, 2, 4, 6, 8, 10

Sub Progr1()
    Range("A1").Value = 0
    Range("A1").DataSeries Rowcol:=xlColumns, Type:=xlDataSeriesLinear, _
        Step:=2, Stop:=10
End Sub

2. Geometric Progression
   This macro fills range B1:B5 with a geometric progression starting at 1, multiplied by 3 each step.
   Result: 1, 3, 9, 27, 81

Sub Progr2()
    Range("B1").Value = 1
    Range("B1:B5").DataSeries Rowcol:=xlColumns, Type:=xlGrowth, Step:=3
End Sub

3. Date Progression
   This macro fills range C1:C4 with dates that increase by one month each step.
   Result: 01/01/2011, 01/02/2011, 01/03/2011, 01/04/2011

Sub Progr3()
    Range("C1").Value = "1/01/2011"
    Range("C1:C4").DataSeries Rowcol:=xlColumns, Type:=xlChronological, _
        Date:=xlMonth
End Sub

The Range object has a large collection of methods, giving developers the ability to program a wide variety of actions—from copying a range to the clipboard to solving nonlinear equations.

The most commonly used methods of the Range object include:

• Activate
• AddComment
• AutoFill
• AutoFit
• BorderAround
• Clear
• ClearComments
• ClearContents
• ClearFormats
• ClearNotes
• Copy
• CopyPicture
• Cut
• DataSeries
• Delete
• FillDown
• FillLeft
• FillRight
• FillUp
• Find
• FindNext
• FindPrevious
• FunctionWizard
• GoalSeek
• Insert
• PasteSpecial
• Replace
• Select
• Show

You can find detailed information about each method in the VBA Help system. Below are some basic examples.

Activating and Selecting a Range

• The Activate method makes a specific range the active one.
• The Select method highlights a range and returns a Selection object.

For example, the code below first activates cell A2, assigns it the value 1, then selects the range A3:A4 and assigns the value 3 to the selected cells:

Range("A2").Activate
ActiveCell.Value = 1
Range("A3:A4").Select
Selection.Value = 3

Automatically Adjusting Range Size to Fit Data

The AutoFit method automatically adjusts column width and row height to fit the entered data.

The following example demonstrates how to use AutoFit when creating the header row of a report table:

Sub DemoAutoFit()
    Range("A1").Value = "June"
    Range("B1").Value = "July"
    Range("C1").Value = "August"
    Columns("A:C").AutoFit   
    Range("D1").Value = "Total Sales Volume"
    Range("D1").Columns.AutoFit
End Sub

Filling a Range with a Single Value

• FillDown – fills a range from top to bottom, copying the values from the top row into all other cells of the range.
• FillUp – fills a range from bottom to top, copying the values from the bottom row.
• FillLeft – fills a range from right to left, using the values from the rightmost column.
• FillRight – fills a range from left to right, using the values from the leftmost column.

Example: The following instruction copies the value from cell A10 into every cell in the range A1:A9:

Range("A1:A10").FillUp

Adding Borders Around a Range

The BorderAround method applies a border around a range.

BorderAround(LineStyle, Weight, ColorIndex, Color)

• LineStyle (optional) – specifies the line style. Possible constants:
  xlContinuous, xlDash, xlDashDot, xlDashDotDot, xlDot, xlDouble, xlLineStyleNone, xlSlantDashDot.
• Weight (optional) – specifies the thickness. Possible constants:
  xlHairline, xlMedium, xlThick, xlThin.
• ColorIndex (optional) – specifies the color from the current palette. Constants:
  xlColorIndexAutomatic, xlColorIndexNone.
• Color (optional) – specifies the color using the RGB model.

Example: The following instruction applies a thick double green border around the range A1:B2:

Range("A1:B2").BorderAround LineStyle:=xlDouble, Weight:=xlThick, _
    Color:=RGB(0, 255, 0)

Clearing a Range

• Clear – clears everything (content, formatting, comments, notes).
• ClearComments – clears only comments.
• ClearContents – clears only the values and formulas (but keeps formatting).
• ClearFormats – clears only formatting.
• ClearNotes – clears notes.

Example: The following instruction clears the range A1:G37:

Range("A1:G37").Clear

Copying, Cutting, and Deleting Data

• Copy(Destination) – copies the range to another range or to the clipboard.
  • If Destination is omitted → copied to the clipboard.

Example: Copy range A1:D4 to E5:H8 on Sheet2:

Range("A1:D4").Copy Worksheets("Sheet2").Range("E5")

• Cut(Destination) – cuts (copies + deletes) the range to another range or to the clipboard.

Example: Cut A1:D4 from Sheet1 to the clipboard:

Worksheets("Sheet1").Range("A1:D4").Cut

• Delete – deletes a range.

Example: Delete the third row of the active worksheet:

Rows(3).Delete

Paste Special

The PasteSpecial method pastes data from the clipboard with special options.

expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)

• expression – a Range object that specifies the top-left cell of the paste area.
• Paste (optional) – specifies what to paste. Constants include:
  xlPasteAll, xlPasteAllExceptBorders, xlPasteColumnWidths, xlPasteComments, xlPasteFormats, xlPasteFormulas, xlPasteFormulasAndNumberFormats, xlPasteValidation, xlPasteValues, xlPasteValuesAndNumberFormats.
• Operation (optional) – specifies how the pasted data combines with existing data. Constants:
  xlPasteSpecialOperationAdd, xlPasteSpecialOperationDivide, xlPasteSpecialOperationMultiply, xlPasteSpecialOperationNone, xlPasteSpecialOperationSubtract.
• SkipBlanks (optional) – Boolean. If True, blanks in the source range are ignored.
• Transpose (optional) – Boolean. If True, transposes rows and columns.

Example 1: Add values from C1:C5 to existing values in D1:D5 on Sheet1:

With Worksheets("Sheet1")
    .Range("C1:C5").Copy
    .Range("D1:D5").PasteSpecial Operation:=xlPasteSpecialOperationAdd
End With

Example 2: Same as above, but paste starting at the top-left cell D1:

With Worksheets("Sheet1")
    .Range("C1:C5").Copy
    .Range("D1").PasteSpecial Operation:=xlPasteSpecialOperationAdd
End With

Example 3: Copy only values from A1:C1 and paste them into A5:C5:

Range("A1:C1").Copy
Range("A5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone

Pasting a Range with Transposition

The PasteSpecial method allows you to paste a copied range with transposed orientation (rows become columns, and columns become rows) by setting the parameter Transpose = True.

Example: The following code copies the values from range A1:C2 and pastes them transposed into the range E1:F3:

Sub Transp()
    Range("A1:C2").Copy
    Range("E1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
        Transpose:=True
End Sub

Removing Selection After Paste Special

When data is copied or pasted into the clipboard, the source range remains highlighted, even after a PasteSpecial operation.
To clear this selection, set the Application.CutCopyMode property to False.

Example:

Range("C1:C5").Copy
Range("D1").PasteSpecial Operation:=xlPasteSpecialOperationAdd
Application.CutCopyMode = False

Inserting Cells, Rows, or Columns

The Insert method of the Range object adds a new cell, row, or column to a worksheet.

Insert(Shift, CopyOrigin)

• Shift (optional) – specifies how existing cells should be shifted. Possible values:
  • xlShiftToRight – shifts existing cells to the right.
  • xlShiftDown – shifts existing cells down.
• CopyOrigin (optional) – specifies how formatting and data sources should be copied.

Examples:

• Insert a new row before row 4:

Rows(4).Insert

• Insert a new cell to the left of G9, shifting existing cells to the right:

Range("G9").Insert Shift:=xlShiftToRight