VBA技術解説
クリップボードに2次元配列を作成してシートに貼り付ける

ExcelマクロVBAの問題点と解決策、VBAの技術的解説
最終更新日:2019-11-01

クリップボードに2次元配列を作成してシートに貼り付ける


タイトルはいささかとっぴな感じですが、
目的はクリップボードのデータ取得とクリップボードへデータを送る方法と、
そもそもクリップボードの中に、セル範囲(つまり2次元)のデータがどのように入っているかを確認してみようという事です。
そして、それが解ればクリップボードに2次元配列としてデータを用意してシートに貼り付けられるだろうという事です。


クリップボードと言っても、ここではあくまでテキストデータだけを扱います。

2次元配列云々というのは、それで特に実務で何かをしようと言う意図ではなく、
あくまで、クリップボードをVBAで扱う題材としての意味合いとしてお考え下さい。
以下で記載のVBAは、普通に2次元配列を作成してできることは言うまでもありません。

クリップボードのデータ取得/クリップボードへデータ送信

DataObjectを利用する
恐らく検索すると一番良く見かける方法です。



'クリップボードのデータ取得
Public Sub SetClipboard(ByRef aText As String)
  With New DataObject
    .SetText aText
    .PutInClipboard
  End With
End Sub

'クリップボードへ送信
Public Function GetClipboard() As String
  On Error Resume Next
  With New DataObject
    .GetFromClipboard
    GetClipboard = .GetText
  End With
End Function

参照設定:Microsoft Forms 2.0 Object Library
もしくは、New DataObjectを以下に変更してください。
CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

Windows API を使用する
以下のVBAは、
Docs / Office VBA リファレンス / Access / 概念 / Windows API /
クリップボードに情報を送信する
https://docs.microsoft.com/ja-jp/office/vba/access/concepts/windows-api/send-information-to-the-clipboard
ここに掲載されているものを、64bitで使えるように変更したものです。

Option Explicit

Private Declare PtrSafe Function OpenClipboard Lib "user32.dll" (ByVal hWnd As LongPtr) As LongPtr
Private Declare PtrSafe Function EmptyClipboard Lib "user32.dll" () As LongPtr
Private Declare PtrSafe Function CloseClipboard Lib "user32.dll" () As LongPtr
Private Declare PtrSafe Function IsClipboardFormatAvailable Lib "user32.dll" (ByVal wFormat As LongPtr) As LongPtr
Private Declare PtrSafe Function GetClipboardData Lib "user32.dll" (ByVal wFormat As LongPtr) As LongPtr
Private Declare PtrSafe Function SetClipboardData Lib "user32.dll" (ByVal wFormat As LongPtr, ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function GlobalAlloc Lib "kernel32.dll" (ByVal wFlags As LongPtr, ByVal dwBytes As LongPtr) As LongPtr
Private Declare PtrSafe Function GlobalLock Lib "kernel32.dll" (ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function GlobalUnlock Lib "kernel32.dll" (ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function GlobalSize Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function lstrcpy Lib "kernel32.dll" Alias "lstrcpyW" (ByVal lpString1 As LongPtr, ByVal lpString2 As LongPtr) As LongPtr

'クリップボードのデータ取得
Public Sub SetClipboard(ByRef sUniText As String)
  Dim iStrPtr As LongPtr
  Dim iLen As LongPtr
  Dim iLock As LongPtr
  Const GMEM_MOVEABLE As LongPtr = &H2
  Const GMEM_ZEROINIT As LongPtr = &H40
  Const CF_UNICODETEXT As LongPtr = &HD
  OpenClipboard 0&
  EmptyClipboard
  iLen = LenB(sUniText) + 2&
  iStrPtr = GlobalAlloc(GMEM_MOVEABLE Or GMEM_ZEROINIT, iLen)
  iLock = GlobalLock(iStrPtr)
  lstrcpy iLock, StrPtr(sUniText)
  GlobalUnlock iStrPtr
  SetClipboardData CF_UNICODETEXT, iStrPtr
  CloseClipboard
End Sub

'クリップボードへ送信
Public Function GetClipboard() As String
  Dim iStrPtr As LongPtr
  Dim iLen As LongPtr
  Dim iLock As LongPtr
  Dim sUniText As String
  Const CF_UNICODETEXT As LongPtr = 13&
  OpenClipboard 0&
  If IsClipboardFormatAvailable(CF_UNICODETEXT) Then
    iStrPtr = GetClipboardData(CF_UNICODETEXT)
    If iStrPtr Then
      iLock = GlobalLock(iStrPtr)
      iLen = GlobalSize(iStrPtr)
      sUniText = String$(CLng(iLen \ 2& - 1&), vbNullChar)
      lstrcpy StrPtr(sUniText), iLock
      GlobalUnlock iStrPtr
    End If
    GetClipboard = sUniText
  End If
  CloseClipboard
End Function

この下のVBAでは、上記どちらでも動作するようにしています。

エクセルの行・列のデータ

クリップボードの中身を見てみましょう。

マクロ VBA クリップボード



Sub Test
  ActiveSheet.Range("A1:C2").CurrentRegion.Copy
  Dim buf
  buf = GetClipboard

  'バイト配列に入れてコード確認
  Dim b() As Byte
  b = buf
  Stop
End Sub

これを実行してBufに入っているデートを見てみましょう。

マクロ VBA クリップボード
Byte配列と文字コード関数について
マクロVBAのデータ型にByte型がありますが、使う機会はかなり限られています。バイト型は、8ビット(1バイト)の変数で、0~255の範囲の単精度の正の数値が格納されます。1バイトで使う事もなくはありませんが、Byte型配列として使われることがしばしばあります。
2バイトで1文字です。

インデックス 0 1 2 3 4 5 6 7 8 9 10 11 12 13
文字コード 65 0 49 0 9 0 66 0 49 0 13 0 10 0
文字 A 1 Tab B 1 Cr Lf

b(0) b(1) b(2) b(3)、65 0 49 0、これはA1です。
b(4) b(5)、9 0、これはTabで列区切りです。
b(10) b(11) b(12) b(13)、13 0 10 0、こりはCrLfで改行になります。
つまり、
改列はvbTab改行はvbCrLfで入っているという事です。

という事は、これと同じデータをクリップボードに入れてやれば、
2次元配列として、シートに貼り付けられるという事になります。

data1 vbTab data2 vbTab data3 vbCrLf
data11 vbTab data12 vbTab data13 vbCrLf
data21 vbTab data22 vbTab data23 vbCrLf

このようにデータが並んでいるという事です。
つまり、これと同様の並びデータをクリップボードに入れてやれば良いという事です。

クリップボードに2次元配列を作成してシートに貼り付けるVBA

'5列20行のデータを作成してシートに貼り付ける
Sub sample1()
  '100件の1次元サンプルデータ作成
  Dim ary(1 To 100)
  Dim i As Long
  For i = 1 To 100
    ary(i) = IIf(i Mod 2 = 0, i, "Data" & i)
  Next
  
  '1次元配列を5列の2次元配列としてクリップボード作成
  Call Array2Clipboard(ary, 5)
  
  'クリップボードをシートに貼り付け
  With ActiveSheet
    .Cells.Clear
    .Range("A1").Select
    .Paste
    .Range("A1").Select
  End With
End Sub

'セル範囲のデータを10列のデータにしてシートに貼り付ける
Sub sample2()
  'A1セル領域をコピー
  ActiveSheet.Range("A1").CurrentRegion.Copy
  
  'クリップボードのデータ取得
  Dim buf
  buf = GetClipboard
  
  'クリップボードのデータを1次元配列に
  buf = Split(Replace(buf, vbCrLf, vbTab), vbTab)
  
  '1次元配列を10列の2次元配列としてクリップボード作成
  Call Array2Clipboard(buf, 10)
  
  'クリップボードをシートに貼り付け
  With ActiveSheet
    .Cells.Clear
    .Range("A1").Select
    .Paste
    .Range("A1").Select
  End With
End Sub

'1次元配列を指定列数の2次元配列としてクリップボード作成
Public Sub Array2Clipboard(ByRef ary, _
              ByVal col As Long)
  Dim i As Long, j As Long
  Dim buf
  
  'クリップボードへ送る2次元データ作成
  j = 0
  For i = LBound(ary) To UBound(ary)
    If Not IsEmpty(buf) Then
      buf = buf & IIf(j = 0, vbCrLf, vbTab)
    End If
    buf = buf & ary(i)
    j = j + 1
    If j >= col Then j = 0
  Next
  
  'クリップボードへ送信
  Call SetClipboard(CStr(buf))
End Sub

sample1
5列20行のデータを作成してシートに貼り付ける

・100件の1次元サンプルデータ作成
・'1次元配列を5列の2次元配列としてクリップボード作成
・クリップボードをシートに貼り付け

マクロ VBA クリップボード


sample2
セル範囲のデータを10列のデータにしてシートに貼り付ける

・A1セル領域をコピー
・クリップボードのデータ取得
・クリップボードのデータを1次元配列に
・1次元配列を10列の2次元配列としてクリップボード作成
・クリップボードをシートに貼り付け

マクロ VBA クリップボード

直前でCopyしているので、クリップボードにデータが入っているかの確認は省略しています。


Array2Clipboard
1次元配列を指定列数の2次元配列としてクリップボード作成

・クリップボードへ送る2次元データ作成
 →列区切りはvbTab、行区切りはCrLf
・クリップボードへ送信

クリップボードに2次元配列の最後に

ここに記載したVBAについては、エラー処理等についてはあまり考慮していません。
クリップボードを扱う基本的なVBAのみとしています。
また、クリップボードをVBAで扱う場合、環境によっては安定動作しない事もあり得ます。

最初に書きましたが、2次元配列云々については、これを使って実務で何かしようという事ではありません。
あくまでクリップボードの扱い方を覚えようという事です。
ただし、少量データであれば、ここで試したように処理時間も特段にはかかりませんので、
何らかの折には使えることもあるかもしれません。

ただし、クリップボードをVBAで扱う事は時々必要になりますので、基本だけはぜひ覚えておいてください。



同じテーマ「マクロVBA技術解説」の記事

新規挿入可能なシート名の判定
VBAにおける配列やコレクションの起点について
VBAのマルチステートメント(複数のステートメントを同じ行に)
クリップボードに2次元配列を作成してシートに貼り付ける
ユーザー定義型の制限とクラスとの使い分け
シングルクォートの削除とコピー(PrefixCharacter)
空文字列の扱い方と処理速度について(""とvbNullString)
VBAにおける変数のメモリアドレスについて
Evaluateメソッド(文字列の数式を実行します)
Rangeオブジェクトの論理演算(差集合と排他的論理和)
VBAで写真の撮影日時や音楽動画の長さを取得する


新着記事NEW ・・・新着記事一覧を見る

Variantの数値型と文字列型の比較|エクセル雑感(7月1日)
VBAのVariant型について|VBA技術解説(6月30日)
VBAのString型の最大文字数について|エクセル雑感(6月20日)
VBAで表やグラフをPowerPointへ貼り付ける|VBAサンプル集(6月19日)
アクティブシート以外の表示(Window)に関する設定|VBA技術解説(6月17日)
マクロ記録での色のマイナス数値について|エクセル雑感(6月16日)
ツイッター投稿用に文字数と特定文字で区切る|エクセル雑感(6月15日)
日付の謎:IsDateとCDate|エクセル雑感(6月14日)
IFステートメントの判定|エクセル雑感(6月13日)
インクリメンタルサーチの実装|ユーザーフォーム入門(6月12日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ