Excelマクロ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技術解説」の記事

Applicationを省略できるApplicationのメソッド・プロパティ一覧
PowerQueryの強力な機能をVBAから利用する方法
ShapesとDrawingObjectsの相違点と使い方
新規挿入可能なシート名の判定
VBAにおける配列やコレクションの起点について
VBAのマルチステートメント(複数のステートメントを同じ行に)
クリップボードに2次元配列を作成してシートに貼り付ける
ユーザー定義型の制限とクラスとの使い分け
シングルクォートの削除とコピー(PrefixCharacter)
空文字列の扱い方と処理速度について(""とvbNullString)
VBAにおける変数のメモリアドレスについて


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

VBAにおける変数のメモリアドレスについて|VBA技術解説(11月8日)
空文字列の扱い方と処理速度について(""とvbNullString)|VBA技術解説(1月7日)
Errオブジェクトとユーザー定義エラー|VBA入門(11月5日)
シングルクォートの削除とコピー(PrefixCharacter)|VBA技術解説(11月4日)
ユーザー定義型の制限とクラスとの使い分け|VBA技術解説(11月3日)
クリップボードに2次元配列を作成してシートに貼り付ける|VBA技術解説(11月1日)
VBAクラスを使ったイベント作成(Event,RaiseEvent,WithEvents)|VBA技術解説(10月31日)
VBAクラスのAttributeについて(既定メンバーとFor Each)|VBA技術解説(10月19日)
VBAの用語について:ステートメントとは|VBA技術解説(10月16日)
VBAのマルチステートメント(複数のステートメントを同じ行に)|VBA技術解説(10月14日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|ExcelマクロVBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数宣言のDimとデータ型|ExcelマクロVBA入門
6.繰り返し処理(For Next)|ExcelマクロVBA入門
7.マクロって何?VBAって何?|ExcelマクロVBA入門
8.ひらがな⇔カタカナの変換|エクセル基本操作
9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説
10.セルに文字を入れるとは(Range,Value)|VBA入門



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

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


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




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