1

I’ve a routine that effectively pastes a link to a cell or cells that a user has copied to the clipboard, putting a space after the "=" (a personal preference, for readability) and changing the anchoring to row-only before pasting. If the link is to another sheet, the font is changed to blue. The code is as follows:

Sub QuickLink2()
'   Copies a link,putting a space after the "=" and changing the
'   anchoring to row-only. If the link is to another sheet, the
'   font is changed to blue.
    Dim r As Long, c As Long
    Dim FormulaArr() As Variant
    Dim Destination As Range

    Application.ScreenUpdating = False

'   Paste link
    On Error Resume Next
    ActiveSheet.Paste Link:=True
    If Err.Number = 1004 Then GoTo NoSelection '1004 is a paste failure
    On Error GoTo 0

'   Transfer pasted link to array
    If Selection.Cells.Count = 1 Then
        ReDim FormulaArr(1 To 1, 1 To 1)
        FormulaArr(1, 1) = Selection.Formula
    Else
        FormulaArr = Selection.Formula
    End If

'   Adjust formula spaces and anchoring
    For r = 1 To UBound(FormulaArr, 1)
        For c = 1 To UBound(FormulaArr, 2)
            FormulaArr(r, c) = Replace(FormulaArr(r, c), "=", "= ")
            FormulaArr(r, c) = Application.ConvertFormula _
            (FormulaArr(r, c), xlA1, xlA1, xlAbsRowRelColumn)
        Next c
    Next r

    Set Destination = Selection
    Destination.Formula = FormulaArr

'   Change font to blue if link is to another sheet
    If Destination(1).Formula Like "*!*" Then _
    Destination.Font.Color = RGB(0, 0, 255)
    Exit Sub

NoSelection:
    Application.CutCopyMode = False

End Sub

The idea here is to speed up the code by assigning the pasted link to a variant array, doing the necessary work on the array, and then assigning the array to a range. What I really want to do, however, is to access the copied cell formulas directly from the clipboard, and assign to the variant array without the intermediate ActiveSheet.Paste Link:=True step.

The following code would allow me to get the copied cell value, but of course I'm looking for the copied formulas.

Dim DataObj As New MSForms.DataObject
Dim S As String
DataObj.GetFromClipboard
S = DataObj.GetText
STim
  • 65
  • 2
  • 7
  • This doesn't make much sense to me, but maybe I'm missing something. In normal operation of Excel you would have no reason to involve the clipboard in this way. Perhaps if you could provide example data, both before, and after your task. – ashleedawg Aug 11 '18 at 13:34
  • It's just a utility in which the user can copy cells and paste them as links with automatic anchoring and formatting. That's why the VBA code starts with pasting what the user has copied as a link. What I'm looking to do is to access the clipboard (which the user will have already copied to) directly. – STim Aug 11 '18 at 13:50

1 Answers1

0

To get formula's: 

Private Sub PutCellFormulaInClipBoard(ByVal Cell As Range)

    Dim oDataObject As Object

    Set oDataObject = _
        GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    With oDataObject
        .Clear
        .SetText Cell.Cells(1).Formula
        .PutInClipboard
    End With

End Sub

Ref

Jeremy Thompson
  • 61,933
  • 36
  • 195
  • 321
  • Note the user will already have copied the cells to the clipboard, so it's a question of getting the formulas from the clipboard, rather than copying the formulas to the clipboard in the first place. – STim Aug 11 '18 at 13:25
  • Sorry, can't you just paste special: https://stackoverflow.com/a/15522593/495455 I'll try it tomorrow, I'm certain it can be done. You should do it by setting the clipboard with this https://stackoverflow.com/a/2294087/495455 – Jeremy Thompson Aug 11 '18 at 13:47
  • The crux of the question is how to avoid pasting in the first place. Bear in mind, the user will have copied cells to the clipboard. The code is activated when the user presses a keyboard shortcut to "paste" the links. What I want the code to do is to assign the formulas from the clipboard directly into a variant array. Once the various operations have been performed on this array, the code will then assign the array to the user-selected range. From the user's point of view, they have pasted the links, but I want to avoid pasting operations if possible in the code. – STim Aug 11 '18 at 14:02