【プログラミング】文字コードを変換するユーザー定義関数(ASCII⇒BASE64/EBCDIC)【エクセル】

仕事のついでに作った副産物的なものです。

また使う機会があるか微妙ですが、どなたかの参考になる可能性も微粒子レベルで存在しているかもしれないので、一応φ(..)メモしておきます。

作成した関数と変換結果

エクセルのマクロ(VBA)で、文字列の形式を変換するユーザー定義関数を作成した。関数の引数にセル番地を入力すると、参照先の文字列を各形式に変換する。

作成した関数と変換結果の例は以下の通り(例：セルA1=Natsume)。

No	文字列	関数	形式	備考
1	Natsume	－	文字列	プレーンテキスト(ASCII)
2	01001110011000010111010001110011011101010110110101100101	=Str2Bin(A1)	バイナリ	ASCIIコード値(2進)
3	7897116115117109101	=Str2Dec(A1)	デシマル	ASCIIコード値(10進)
4	4E617473756D65	=Str2Hex(A1)	ヘキサ	ASCIIコード値(16進)
5	TmF0c3VtZQ==	=Str2Base64(A1)	Base64	電子メール等で使用される文字コード
6	D581A3A2A49485	=Asc2Ebc(A1)	EBCDIC	メインフレーム等で使用される文字コード

VBAのコード

[1]参考サイトを参考に作成した関数

'文字列をバイナリ列に変換
Function Str2Bin(strText)
    hData = Str2Hex(strText)
    Str2Bin = Hex2Bin(hData)
End Function

'文字列をHexに変換
Function Str2Hex(strText)
  bData = String2Bytes(strText)
  sHex = Bytes2Hex(bData)
  Str2Hex = strConv(Replace(sHex, vbLf, ""), vbUpperCase)
End Function

'HexをBase64に変換
Function Hex2Base64(strText)
  bData = Hex2Bytes(strText)
  sBase64 = Bytes2Base64(bData)
  Hex2Base64 = Replace(sBase64, vbLf, "")
End Function

'文字列をBase64に変換
Function Str2Base64(strText)
  hData = Str2Hex(strText)
  sBase64 = Hex2Base64(hData)
  Str2Base64 = Replace(sBase64, vbLf, "")
End Function

 参考①:VBScriptでBase64やHex、バイナリを扱う関数をまとめてみた – でじたぬ

'Hexをバイナリ列に変換
Function Hex2Bin(strText)
s = "0123456789ABCDEF"
t = "0000000100100011010001010110011110001001101010111100110111101111"
For i = 1 To Len(strText)
    p = InStr(s, Mid(strText, i, 1))
    bit1 = Mid(t, (p - 1) * 4 + 1, 1)
    bit2 = Mid(t, (p - 1) * 4 + 2, 1)
    bit3 = Mid(t, (p - 1) * 4 + 3, 1)
    bit4 = Mid(t, (p - 1) * 4 + 4, 1)
    bita = bit1 & bit2 & bit3 & bit4
    Hex2Bin = Hex2Bin & bita
    Next i
End Function

'文字列をDec(ASCIIコード)に変換
Function Str2Dec(strText)
For i = 1 To Len(strText)
    p = Mid(strText, i, 1)
    d = Asc(p)
    Str2Dec = Str2Dec & d
    Next i
End Function

参考②:VBAで4桁の16進数から16桁の2進数変換 -VBAで4桁の16進数(FFFFとか)を2- Visual Basic（VBA） | 教えて!goo

'ASCIIをEBCDICに変換
Function Asc2Ebc(strText)
Dim AscCode As String
Dim EbcCode As String
Dim ArrayLen As Integer: ArrayLen = 256
Dim AscArray() As String
Dim EbcArray() As String
ReDim AscArray(ArrayLen)
ReDim EbcArray(ArrayLen)
Dim InCode As Long
Dim HexCode As String

AscCode = "000000100020001A00200026002D25332546254F2560256A007B007D003F0030" & _
          "000100110021001A001A2525002F25350061006A2141256B0041004A001A0031" & _
          "000200120022003221232527252425370062006B0073256C0042004B00530032" & _
          "000300130023003321562529252625390063006C0074256D0043004C00540033" & _
          "0004001400240034215725632528253B0064006D0075256F0044004D00550034" & _
          "000500150025003521222565252A253D0065006E007625730045004E00560035" & _
          "000600160026003621262567252B253F0066006F0077212B0046004F00570036" & _
          "000700170027003725722543252D2541006700700078212C0047005000580037" & _
          "00080018002800382521213C252F2544006800710079001A0048005100590038" & _
          "0009001900290039252325222531212E00690072007A001A00490052005A0039" & _
          "000A001A002A003A003F0021003F003A254825522561001A001A001A001A001A" & _
          "000B001B002B003B002E0024002C0023254A25552562001A001A001A001A001A" & _
          "000C001C002C003C003C002A00250040254B25582564001A001A001A001A001A" & _
          "000D001D002D003D00280029005F0027254C255B2566001A001A001A001A001A" & _
          "000E001E002E003E002B003B003E003D254D255E2568001A001A001A001A001A" & _
          "000F001F002F003F2822224C003F0022254E255F2569001A001A001A001A00FF"

EbcCode = "00102030405060708090A0B0C0D0E0F001112131415161718191A1B1C1D1E1F1" & _
          "02122232425262728292A2B2C2D2E2F203132333435363738393A3B3C3D3E3F3" & _
          "04142434445464748494A4B4C4D4E4F405152535455565758595A5B5C5D5E5F5" & _
          "06162636465666768696A6B6C6D6E6F607172737475767778797A7B7C7D7E7F7" & _
          "08182838485868788898A8B8C8D8E8F809192939495969798999A9B9C9D9E9F9" & _
          "0A1A2A3A4A5A6A7A8A9AAABACADAEAFA0B1B2B3B4B5B6B7B8B9BABBBCBDBEBFB" & _
          "0C1C2C3C4C5C6C7C8C9CACBCCCDCECFC0D1D2D3D4D5D6D7D8D9DADBDCDDDEDFD" & _
          "0E1E2E3E4E5E6E7E8E9EAEBECEDEEEFE0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFFF"

For i = 1 To ArrayLen
    a = i * 4 - 3
    e = i * 2 - 1
    AscArray(i) = Mid(AscCode, a, 4)
    EbcArray(i) = Mid(EbcCode, e, 2)
Next i

For k = 1 To Len(strText)
    InCode = Asc(Mid(strText, k, 1))
    HexCode = Right$("0000" & Hex$(InCode), 4)
    For l = 1 To ArrayLen
        If HexCode = AscArray(l) Then
            Asc2Ebc = Asc2Ebc & EbcArray(l)
        End If
    Next l
Next k

End Function

参考③:VBS/EBCDICファイルをシフトJISに変換する | Tipi

参考④:EBCDIC - Wikipedia

参考⑤:ASCII 文字セットと EBCDIC 文字セット(IBM Knowledge Center)

参考⑥:【69542】１０進→１６進８桁への変換(Excel VBA質問箱 IV )

[2]参考サイトから引用させて頂いた関数

注:以下も[1]と一緒にマクロに保存しておかないと[1]の関数も動きません([1]の関数内で使用しているため)

'文字列をバイナリに変換
Function String2Bytes(strText)
  Set objEncoder = CreateObject("System.Text.UTF8Encoding")
  String2Bytes = objEncoder.GetBytes_4(strText)
  Set objEncoder = Nothing
End Function

'バイナリを文字列に変換
Function Bytes2String(Bytes)
  Set objEncoder = CreateObject("System.Text.UTF8Encoding")
  Bytes2String = objEncoder.GetString((Bytes))
  Set objEncoder = Nothing
End Function

'文字列をバイナリに変換 (Shift_JIS)
Function SJIS2Bytes(strText)
  Set objStream = CreateObject("ADODB.Stream")
  objStream.Open
  objStream.Type = 2  'adTypeText
  objStream.Charset = "Shift_JIS"
  objStream.WriteText strText
  objStream.Position = 0
  objStream.Type = 1  'adTypeBinary
  SJIS2Bytes = objStream.Read
  objStream.Close
  Set objStream = Nothing
End Function

'バイナリを文字列に変換 (Shift_JIS)
Function Bytes2SJIS(Bytes)
  Set objStream = CreateObject("ADODB.Stream")
  objStream.Open
  objStream.Type = 1  'adTypeBinary
  objStream.Write Bytes
  objStream.Position = 0
  objStream.Type = 2  'adTypeText
  objStream.Charset = "Shift_JIS"
  Bytes2SJIS = objStream.ReadText
  objStream.Close
  Set objStream = Nothing
End Function

'バイナリをBase64に変換
Function Bytes2Base64(Bytes)
  Set objElement = CreateObject("Msxml2.DOMDocument").createElement("base64")
  objElement.DataType = "bin.base64"
  objElement.NodeTypedValue = Bytes
  Bytes2Base64 = objElement.Text
  Set objElement = Nothing
End Function

'Base64をバイナリに変換
Function Base642Bytes(strText)
  Set objElement = CreateObject("Msxml2.DOMDocument").createElement("base64")
  objElement.DataType = "bin.base64"
  objElement.Text = strText
  Base642Bytes = objElement.NodeTypedValue
  Set objElement = Nothing
End Function

'バイナリをHexに変換
Function Bytes2Hex(Bytes)
  Set objElement = CreateObject("Msxml2.DOMDocument").createElement("hex")
  objElement.DataType = "bin.hex"
  objElement.NodeTypedValue = Bytes
  Bytes2Hex = objElement.Text
  Set objElement = Nothing
End Function

'Hexをバイナリに変換
Function Hex2Bytes(strText)
  Set objElement = CreateObject("Msxml2.DOMDocument").createElement("hex")
  objElement.DataType = "bin.hex"
  objElement.Text = strText
  Hex2Bytes = objElement.NodeTypedValue
  Set objElement = Nothing
End Function

引用元:VBScriptでBase64やHex、バイナリを扱う関数をまとめてみた – でじたぬ

ASCII⇒EBCDICの変換について

変換テーブルは以下のように作成した。

①wikiからEBCDICコード表をエクセルにコピペ

 参考:EBCDIC - Wikipedia

②制御コード等を手動でASCIIコード(10進)に置換

④DEC2HEX関数(桁数=4)でHEXに変換

⑤CONCAT関数で結合 

⇒ASCIIコード配列(HEX4桁区切り)ができる。実際の関数では、4桁区切りで配列に格納する。

⑥EBCDICコード表を作る

⑦CONCAT関数で結合

⇒EBCDICコード配列ができる。実際の関数では、2桁区切りで配列に格納する。

 おまけ

エクセル表のはてなブログへの貼り付け

エクセルの表をはてなブログに貼り付けるには、Googleスプレッドシートを利用するのがよい。

参考：ブログ記事に表（table）を入れる時はGoogleスプレッドシートを活用すべし｜はてな・ワードプレス対応 | ANAマイルdeうりと行くスカイ？

文字コード・基数変換

下記サイトが色々できて大変便利。

単位変換、進数変換等の便利なWEBアプリ集

 基数変換

2進数、8進数、10進数、16進数相互変換ツール

 BASE64変換

データ変換ツール（BASE64, URLエンコード(URLデコード), HEX(16進ダンプ), MD5, SHA-1変換フォーム）

ASCII⇔EBCDIC

金融・決済関連事業者を支援する目的のサイト。メインはEMVというICカードの規格に関するツールだが、一部文字コードを変換するツールもある。

Character encoding conversion(emvlab.org)

応援チップについて

サイドバーに「応援チップ」を配置しました。

もし記事が何かの役にたつことがありましたら、チップして頂けるととっても喜びます。

　　　喜びの舞　(ﾉﾟдﾟ)ﾉｿｲﾔ!ﾞ(ﾉﾟдﾟ)ﾉｿｲﾔ!ﾞ(*'Д')bｷﾞｭﾝ!ﾞ(*'Д')bﾞ

また、「はぁ～何このクソ記事、つっかえ…」という場合は、「ごっこ」からビットコインに似た何かをぶつけることができるので、憂さ晴らしに遊んでみてください。

ブロックチェーン系Webアプリ第二弾リリースしました！
「応援チップ」
１４種類の仮想通貨が受けとれるブログパーツ＆URL生成サービス
なんと日本円まで受け取れます。さらにコイン無くても投げれますｗ
クリエイター支援や仮想通貨系ブログ、プロフ、VTuberと相性良いかもhttps://t.co/LEIFjEYjDZ pic.twitter.com/zqcpZUTL8K

— ひさしApp＠ゲームアプリ本やブロックチェーン開発 (@Hisashi_vc) 2018年3月16日

おわり

BASE64はともかく、EBCDICて馴染みない人も多いかと思いますが、汎用機系の古いシステムではよく使われるんです…

そもそも、2進数とか16進数とかも、IT関係にお勤めでない方には意味不明かとは思いますが…私も、最初に2進とか16進の勉強をした時は理解不能でした(×_×)

2進だと1+1は2じゃなくて10になる…とかねw

では最後に一句…

文字コード　みんな違って　みんないい

 　　　　　　　　　　　　　　　　　　　なつめ

ごめなさいちょっと言ってみたかっただけです><;