How do I return the location of the marching ants in Excel?

Question

I know about Application.CutCopyMode, but that only returns the state of the CutCopyMode (False, xlCopy, or xlCut).

How do I return the address of the currently copied range in Excel using VBA? I don't need the currently selected range (which is Application.Selection.Address). I need the address of the range of cells with the moving border (marching ants) around it.

In other words, if you select a range of cells, hit CTRL+C, and then move the selection to another cell, I need the address of the cells that were selected when the user hit CTRL+C.

Thanks!

Answer 1

As far as I know you can't do that with vba. You can however code your own copy sub and store the source in a global variable.

Something like this:

Option Explicit
Dim myClipboard As Range

Public Sub toClipboard(Optional source As Range = Nothing)
    If source Is Nothing Then Set source = Selection
    source.Copy
    Set myClipboard = source
End Sub

Answer 2

10 years later you still can't refer directly to a copied Range
(shown by the "marching ants border" aka "dancing border", "moving border").

But you can get its address by copying the cells as link to a temporary worksheet. There you can collect the desired range's address.

Private Sub ThereAreTheMarchingAnts()
    Dim rngCopied As Range          ' the copied range with the marching ants border
    Dim rngSelected As Range        ' the selected range
    Dim tmpWorksheet As Worksheet   ' a temporary worksheet
    Dim c As Range                  ' a cell for looping

    ' Exit, if nothing was copied (no marching ants border):
    If Not (Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut) Then Exit Sub

    ' Exit, if no range is selected (just for demonstration)
    If Not TypeName(Selection) = "Range" Then Exit Sub

    ' remember selected Range:
    Set rngSelected = Selection

    ' add a temporary sheet and paste copied cells as link:
    Set tmpWorksheet = ActiveWorkbook.Sheets.Add
    tmpWorksheet.Paste link:=True

    ' go through all pasted cells and get the linked range from their formula:
    For Each c In tmpWorksheet.UsedRange
        If rngCopied Is Nothing Then
            Set rngCopied = Range(Mid(c.Formula, 2))
        Else
            Set rngCopied = Union(rngCopied, Range(Mid(c.Formula, 2)))
        End If
    Next c

    ' delete the temporary worksheet without asking:
    Application.DisplayAlerts = False
    tmpWorksheet.Delete
    Application.DisplayAlerts = True

    ' show the addresses:
    MsgBox "Copied Range: " & rngCopied.Address(0, 0, xlA1, True) & vbLf & _
           "Selected Range: " & rngSelected.Address(0, 0, xlA1, True)
End Sub

The code also works with multiranges and also if the copied range and the selected range are on different sheets.

Answer 3

When you copy a Range, the address is copied to the Clipboard along with other formats. You can check that with Clipboard Viewer application. So if you need the copied Range, get it from Clipboard. It will be something like> $A2:$B5 or similar

Answer 4

The only way i can think of doing this is tracking the last range selected with a global variable and then waiting until you think a copy action is done. Unfortunately neither is easy.

The following is a quick attempt that has two problems;

1. If you copy the same data twice it isn't updated
2. If a copy or paste is fired from another app, the results may vary.

This is one of those last hope tricks when tracking events that don't really exist. Hope this helps.

''# Add a reference to : FM20.dll or Microsoft Forms 2.0
''# Some more details at http://www.cpearson.com/excel/Clipboard.aspx

Option Explicit

Dim pSelSheet As String
Dim pSelRange As String

Dim gCopySheet As String
Dim gCopyRange As String

Dim gCount As Long
Dim prevCBText As String

Dim DataObj As New MSForms.DataObject



Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
        ByVal Target As Excel.Range)

    CopyTest
    pSelSheet = Sh.Name
    pSelRange = Target.Address


    ''# This is only so you can see it working
    gCount = gCount + 1
    application.StatusBar = gCopySheet & ":" & gCopyRange & ", Count: " & gCount
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
    CopyTest ''# You may need to call CopyTest from other events as well.

    ''# This is only so you can see it working
    gCount = gCount + 1
    application.StatusBar = gCopySheet & ":" & gCopyRange & ", Count: " & gCount
End Sub




Sub CopyTest()
    Dim curCBText As String
    Dim r As Range
    DataObj.GetFromClipboard

    On Error GoTo NoCBData
      curCBText = DataObj.GetText
    On Error Resume Next


    ''# Really need to test the current cells values
    ''# and compare as well. If identical may have to
    ''# update the gCopyRange etc.

    If curCBText <> prevCBText Then
      gCopySheet = pSelSheet
      gCopyRange = pSelRange
      prevCBText = curCBText
    End If

  Exit Sub


NoCBData:
  gCopySheet = ""
  gCopyRange = ""
  prevCBText = ""
End Sub

Oh and excuse the wierd comments ''# they're just there to help the syntax highlighter of SO.

Answer 5

I think you can use this method https://learn.microsoft.com/en-us/office/vba/api/Excel.Application.OnKey

This method assigns a function to the hot key Ctrl+C, every time this combination is used, the function will be triggered and you can get the address of the range.