読者です 読者をやめる 読者になる 読者になる

t-hom’s diary

主にVBAネタを扱っているブログです。

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

VBA

事例

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

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

こういうタブ区切りのテキストをコピーして、
f:id:t-hom:20151113234330p:plain

そのままExcelに張り付けると普通ならこうなる。
f:id:t-hom:20151113234412p:plain

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

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

恐らく、こうなったのだと思うけれど。
f:id:t-hom:20151113234714p:plain

こうかもしれないし、
f:id:t-hom:20151113234636p:plain

あるいはこうかもしれない。
f:id:t-hom:20151113234753p:plain

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

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

このようにダブルクォーテーションが消えて張り付く。
f:id:t-hom:20151113234412p:plain

ただし、行全体をダブルクォーテーションで囲むと、
f:id:t-hom:20151113235430p:plain

このように一塊として扱われる。
f:id:t-hom:20151113235528p:plain

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

制御文字を疑う

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

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

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

イミディエイトウインドウにはこのように表示された。
f:id:t-hom:20151114000400p:plain
一部の制御文字は完全に表示されず、表示された文字も一律「・」で表されている。

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

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

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

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

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

コード解説

まず以下の部分。

    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など)
ASCII文字コード : IT用語辞典

つぎにこちら

    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

以上

当ブログは、amazon.co.jpを宣伝しリンクすることによってサイトが紹介料を獲得できる手段を提供することを目的に設定されたアフィリエイト宣伝プログラムである、 Amazonアソシエイト・プログラムの参加者です。