VBA 見えない文字(制御文字)を疑似的に可視化する関数を作成

事例

本日後輩からVBAで相談を受けた。

あるデータをExcelに張り付けるため、Tab区切りでクリップボードに入れているが、張り付けたときにすべて1セルに入ってしまうとのこと。

こういうタブ区切りのテキストをコピーして、

そのままExcelに張り付けると普通ならこうなる。

それが1セルに張り付いてしまうというのである。
実際にメモ帳で張り付けているわけではなく、コードで文字列をvbTabで区切っているが、それがうまくいかないのだとか。
コードを少しいじったらそうなったらしい。

電話越しなので実際どう張り付いたのかは不明。

恐らく、こうなったのだと思うけれど。

こうかもしれないし、

あるいはこうかもしれない。

ダブルクォーテーションを疑う

手元にコードがないので事象は再現できないが、まず疑うべきはダブルクォーテーション。
こういう風にダブルクォーテーションが連なったデータでも、

このようにダブルクォーテーションが消えて張り付く。

ただし、行全体をダブルクォーテーションで囲むと、

このように一塊として扱われる。

ただ、どうやらこの事象では無いらしい。

制御文字を疑う

ここからが本題。
ダブルクォーテーションのせいではないとなると、Tab文字が変質しているか、何か変な制御文字が紛れ込んでしまった可能性もある。

水平タブや改行など、一応変化が見える制御文字もあれば、ヌル、垂直タブ、バックスペース、ビープなどのVBAでは可視化できないものもある。
試しに次のようなコードを実行してみると、

Sub hoge()
    Debug.Print vbNullChar & "これはテストです。" & vbVerticalTab & vbBack & vbTab & Chr(7)
End Sub

イミディエイトウインドウにはこのように表示された。

一部の制御文字は完全に表示されず、表示された文字も一律「・」で表されている。

これでは何か変な制御文字がまぎれていても何なのか分からない。

そこで、制御文字を可視化する関数を作成した。
コードは以下のとおり。

Function 制御文字見える化(文字列 As String) As String
    Dim ret As String
    Dim 制御コード() As String
    制御コード = Split( _
        "vbNullChar SOH STX ETX EOT ENQ ACK BEL vbBack vbTab " & _
        "vbLf vbVerticalTab vbFormFeed vbCr SO SI DLE DC1 DC2 DC3 " & _
        "DC4 NAK SYN ETB CAN EM SUB ESC FS GS " & _
        "RS US", " ")
    Dim 文字コード As Long
    Dim i As Long
    For i = 1 To Len(文字列)
        文字コード = Asc(Mid(文字列, i, 1))
        If 0 <= 文字コード And 文字コード <= UBound(制御コード) Then
            ret = ret & "{" & 制御コード(文字コード) & "}"
        Else
            ret = ret & Chr(文字コード)
        End If
    Next
    制御文字見える化 = ret
End Function

この関数を用いるサンプルがこちら。

Sub hogehoge()
    文字列 = vbNullChar & "これはテストです。" & vbVerticalTab & vbBack & vbTab & Chr(7)
    Debug.Print "Invisible:" & 文字列
    Debug.Print 制御文字見える化("Visible:" & 文字列)
End Sub

実行すると、普通に表示させている上段の方は制御文字が見えないが、関数を用いた下段の方は制御文字が疑似的に可視化できている。

今回の事例はまだ未解決のようだが、このように一旦可視化して、成功例と失敗例を比較すれば原因が見えてくると思う。

コード解説

まず以下の部分。

    Dim 制御コード() As String
    制御コード = Split( _
        "vbNullChar SOH STX ETX EOT ENQ ACK BEL vbBack vbTab " & _
        "vbLf vbVerticalTab vbFormFeed vbCr SO SI DLE DC1 DC2 DC3 " & _
        "DC4 NAK SYN ETB CAN EM SUB ESC FS GS " & _
        "RS US", " ")

制御コードという動的配列を宣言して、そこにSplit関数を使って配列の中身を作成している。
配列の内容は基本的にASCIIコード表の制御文字から取ってきたが、VBAの定数として存在するものはそちらに置き換えた。(vbTabなど)
https://e-words.jp/p/r-ascii.html

つぎにこちら

    Dim 文字コード As Long
    Dim i As Long
    For i = 1 To Len(文字列)
        文字コード = Asc(Mid(文字列, i, 1))
        If 0 <= 文字コード And 文字コード <= UBound(制御コード) Then
            ret = ret & "{" & 制御コード(文字コード) & "}"
        Else
            ret = ret & Chr(文字コード)
        End If
    Next

Forの中身の1行目は、文字列をMidで一文字ずつ切り出して、その文字をAsc関数でInteger型の文字コードに変換している。

文字コード = Asc(Mid(文字列, i, 1))

そして、一文字ずつ取得した文字コードが、0～制御コードの個数に収まっていれば、制御コード配列から該当の文字列を取り出して表示、そうでなければChr関数で普通の文字に戻して表示させている。

If 0 <= 文字コード And 文字コード < UBound(制御コード) Then

以上