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、これはＡ１です。
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 ・・・新着記事一覧を見る

TRIMRANGE関数（セル範囲をトリム：端の空白セルを除外）｜エクセル入門(2024-08-30)
正規表現関数（REGEXTEST,REGEXREPLACE,REGEXEXTRACT）｜エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない｜エクセル雑感(2024-04-11)
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)


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

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



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


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

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ
同じテーマの記事
新着記事
アクセスランク

↑