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

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

クリップボードに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}")
参照設定の「参照可能なライブラリ ファイル」に「Microsoft Forms 2.0 Object Library」が表示されていない場合は、
挿入でユーザーフォームを作成すると自動的に参照設定されます。


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配列と文字コード関数について
・文字列をByte配列に入れる ・文字コードについて ・文字列をByte配列に入れて、文字列に戻す ・文字列をByte配列に入れて、自力で文字列に戻す ・文字列をSJISに変換してから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 ・・・新着記事一覧を見る

VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




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


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



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