excelのセルの値ををクリップボードにコピーする関数

Question

'excelのセルの値ををクリップボードにコピーする関数。A4の一行上のセルA3の値です。
'セルA1=5
'セルA2=6
'セルA3=A1+A2
'セルA4=myex2cl()
'--------------------------------------------------------------------------------
'問題点①2行目を直したら正しいですが、1行目を直したら2行目が入ります。
'問題点②セルを移動せずに、参照で対応できますか。短くしたいです。
'問題点③さらに、myex2cl(A3)とセルのアドレス指定ができると助かります。
'よろしくお願いします。
'--------------------------------------------------------------------------------
Function myex2cl()
Application.Volatile
i = ActiveCell.Row
j = ActiveCell.Column
Range(myfuncpos()).Select
myClipboardIn (Cells(ActiveCell.Row + 1, ActiveCell.Column).Value)
Cells(i, j).Select
myex2cl = ""
End Function
Function myfuncpos()
Dim r As Range
Set r = Application.Caller
myfuncpos = r.Address(False, False)
End Function
Sub myClipboardIn(x)
Dim myData As DataObject
Dim myCb As Variant
Set myData = New DataObject
myData.SetText x
myCb = myData.GetText
myData.PutInClipboard
End Sub
'--------------------------------------------------------------------------------
'(参考)VBEの参照設定で『Microsoft Forms 2.0 Object Library』が見つからない
'http://www.tomodachihiroba.org/square/2016/01/vbemicrosoft-fo.html
'(参考)EXCEL/VBA 変数の値をクリップボードにコピーする方法
'http://oshiete.goo.ne.jp/qa/5650002.html

Answer 1

その１ 現状コードの問題点を修正

まずセルの入力内容とコーディングの実装内容に若干の取り違えがあるようです。

A3セルでA1+A2を計算し、A4セルに記述したマクロでA3セルの値をクリップボードにコピーしたいものと思いますが、コーディングではマクロ記述したセル(A4)からRow+1したセル(A5)をコピーするような記述になっています。

これに加えて、VBAマクロをユーザー定義関数として使用する場合は、アクティブセルの変更やアクティブシートの変更などは（エラーにはなりませんが）行うことができません。

このため、

Range(myfuncpos()).Select

の部分でマクロ記述セルがアクティブとならず、結果、

myClipboardIn (Cells(ActiveCell.Row + 1, ActiveCell.Column).Value)

では処理前のアクティブセルのひとつ下のセルから値をコピーしてしまいます。

A1を変更するとA2の値がコピーされるというのはこのためです。

改善方法ですが、そもそも今回のように単一セルにマクロ記述した場合、Application.Callerは単一セルを指すRangeオブジェクトを返してきます。
このRangeオブジェクトがマクロ記述セルを指していますので、これをActiveCellの代わりに使用すればよいと思います。

Function myex2cl()
    Application.Volatile
    
    ''処理前のアクティブセル座標を退避（アクティブセルは変更しないので退避不要）
    'i = ActiveCell.Row
    'j = ActiveCell.Column
    
    ''マクロ記述セルをアクティブ化
    'Range(myfuncpos()).Select
    ''マクロ記述セルの下のセルをクリップボードにコピー
    'myClipboardIn (Cells(ActiveCell.Row + 1, ActiveCell.Column).Value)
    
    If TypeName(Application.Caller) = "Range" Then
        '念のためApplication.CallerがRange型の場合のみ処理
        
        'マクロ記述セルを取得
        Dim rng As Range
        Set rng = Application.Caller
        
        'マクロ記述セルの下のセルをクリップボードにコピー
        myClipboardIn (Cells(rng.Row + 1, rng.Column).Value)
    End If
    
    ''元セルのアクティブ化（アクティブセルは変更しないので戻す必要なし）
    'Cells(i, j).Select
    
    myex2cl = ""

End Function

その２ 引数で対象セルを指定できるようにする

関数にRange型引数を設けることで、対象セルを指定させることができます。

引数で対象セルを指定できるようにすると、現状行っている
①処理前のアクティブセルの座標退避
②マクロ記述セル(Application.Caller)の取得、アクティブ化
③マクロ記述セルからコピー対象セルを取得
④処理後に元のアクティブセルをアクティブ化
といった処理は全て不要になり、かなり簡潔になります。

Function myex2cl(rngCpy As Range)
    Application.Volatile
    
'    '処理前のアクティブセル座標を退避
'    i = ActiveCell.Row
'    j = ActiveCell.Column
'
'    'マクロ記述セルをアクティブ化
'    Range(myfuncpos()).Select
'    'マクロ記述セルの下のセルをクリップボードにコピー
'    myClipboardIn (Cells(ActiveCell.Row + 1, ActiveCell.Column).Value)
'
'    '元セルのアクティブ化
'    Cells(i, j).Select
    
    '引数指定のセルをクリップボードにコピー
    myClipboardIn (Cells(rngCpy.Row, rngCpy.Column).Value)
    
    myex2cl = ""

End Function

myfuncpos関数は不要になります。

これで
A3セル：=A1+A2
A4セル：=myex2cl(A3)
とすることでA1+A2の結果がクリップボードに格納されるようになると思います。

Comments

[mrrclb48z]

ttyp03様　jawa様
ていねいな回答ありがとうございました。参考にしました。
excelのセルの値ををクリップボードにコピーする関数
http://www.jwcad.net/bbs2/c-board.cgi?cmd=ntr;tree=49446;id=004

Answer 2

コードをブラッシュアップしてみました。

Function myex2cl(add As String)
    With Range(add)
        myClipboardIn .Value
        myex2cl = .Value
    End With
End Function

Sub myClipboardIn(x)
    Dim myData As New DataObject
    myData.SetText x
    myData.PutInClipboard
End Sub

セルA4は=myex2cl("A3")と指定します。
ただし、当然ながら数式を入力した後にA1、A2を更新してもA4には反映されませんのでご注意ください。