Excel VBAのコーディング事例をご紹介しています。
事例iにするテーマを決めて、その機能を実現するためのコーディングを動く形でお示していますが、今回と次回は、2回に渡り「Excel VBAで文字コード表を作る」をテーマといたします。
なお2回に分けているのは、今回が「SHIFT-JISの文字コード表」、次回が「UTF-8の文字コード表」にしているためです。
「文字コード表を作る」ためのコーディング自体はほぼほぼ同じなのですが、それぞれのコード体系の違いを実装する部分が大きく異なるので、分けざるを得ない認識です。
コーディング自体は「構造化・標準化は考慮しつつも極力コーディング量は減らし、その上で可読性を維持する」という思いは持ちつつも「テーマに沿って動くことを優先して実装」しています。
※動作は32bit版Excel 2016と64bit版Excel 2021の バージョン2302(ビルド 16130.20306)を使用して検証しています。
SHIFT-JISの文字コードとは
SHIFT-JISでは「一つの文字を1バイトまたは2バイト」で表します。
これに対してUTF-8では「一つの文字を1バイトから4バイト」で表します。このように両者はまったく異なるコード体系になっています。
従いましてSHIFT-JISの方がコード表としてはコンパクトであり、文字コード表を作成するための「ファーストステップ」としては向いていると思います。
ただし日本で作られた体系であるために、ISO(国際標準化機構)の体系では無い事から、現在では「過去に作られた文文字データの互換性のために残されている」という認識です。
「SHIFT-JISの歴史」につきましては、下記のウィキペディアの「Shift_JIS」をご参照いただければ幸いです。
1バイト文字
ご存じのように1バイト(byte)は8ビット(bit)で構成されています。
そして1ビット(bit)は2進数の1桁になり「”0″または”1″」のどちらかの値です。
つまり1バイト(byte)は2進数の8桁であり「2ビット(bit)^8=256」種類の文字を割り当てる事ができます。
文字コード表を作る際に、256種類の文字を表現するのに10進数の区切りで縦10文字づづ並べるよりも、16進数で、つまり縦16文字づづに配置した方が見た目の収まりが良くなります。
※これにより256文字全体を16列×16行のマス目で表す事ができます。
なお10進数の”256″の値は16進数では”FF”と表現されます。従って最大でも2桁の16進数になるため、1桁目を上位4ビット(bit)、2桁目を下位4ビット(bit)と呼ぶ事があります。
コード体系として、上位4ビット(bit)が「0x8、0x9、0xE、0xF」で始まる時は、下位4ビット(bit)に何が来ても、「文字」は割り振られていません。
この理由は次章「2バイト(byte)文字」の中でご説明いたします。
※「文字」とはドットパターンのイメージであり、「一文字は一つの小さな画像である」と捉える事ができます。
※文字コード表には「文字」の他に「制御機能」が割り振られている領域も存在しますが、これは「文字データを処理するためのマーカーである」と表現する事もできます。
なお256種類の中には実際には使用されていない「未使用」のマス目が存在していますが、これにつきましては後述いたします。
2バイト(byte)文字
2バイト(byte)文字は文字通り2バイト(byte)を使って1つの文字を表現しています。
では『テータストリームの中から「2バイト(byte)文字であるか?ないか?」をどうやって判断しているのか?』なのですが、『1バイト(byte)目の上位4ビット(bit)が「1バイト(byte)文字では使われていない」「0x8、0x9、0xE、0xF」になっているか?』を確認する事で判定できます。
なお実際にはShift_JIS-2004(次の章でご説明いたします)では(16進表記で)つぎの範囲が「2バイト文字の1バイト目」の領域として割り当てられています。
| 場所 | 2バイト文字の 1バイト目① | 〃② |
|---|---|---|
| 1バイト目 | 0x81～0x9F | 0xE0～0xFC |
領域が2つに分かれているのは、0xA1から0xDFに「半角カナ」が割り当てられているためです。
※これを言い換えますと、前述したウィキペディアの「Shift_JIS」に書かれているように、「1バイト文字体系の空き領域に後から定義した」ためです。
日本におけるコンピュータでの文字処理の歴史は、ASCIIコード(0x00～0x7F)、半角カナ領域(0xA1～0xDF)の追加、2バイト文字の1バイト目の追加、というステップを踏んで進化してきた認識です。
そして、その次にくる2バイト(byte)目は何か来ても大丈夫なのですが、2バイト(byte)目にも「未使用」のマス目が存在するので、その詳細は次の章でご説明いたします。
SHIFT-JIS文字コード表における「未使用」箇所
前述したウィキペディアの「Shift_JIS」に記述されているようにSHIFT-JISは「2004年改定時にShift_JIS-2004と名称が変更されたShift_JISX0213が最終になり、その後は更新が停止」されている状態になりますので、現在はShift_JISX0213が使用されている認識です。
そこでShift_JISX0213の未使用領域を確認して行きます。
未使用箇所
1バイト(byte)文字と2バイト(byte)文字、それぞれに未使用のマス目が存在しています。
| コード部分 | 未使用領域① | 〃② | 〃③ |
|---|---|---|---|
| 1バイト文字 | 80 | A0 | FD～FF |
| コード部分 | 未使用領域 ① | 〃② | 〃③ |
|---|---|---|---|
| 2バイト文字の 2バイト目 | 00～3F | 7F | FD～FF |
下図は今回ご紹介するVBAで作成する文字コード表の画面キャプチャになりますが、それぞれセル背景色灰色の所が未使用領域です。
※背景黄色の領域は、2バイト文字の1バイト目
※1バイト目が0X81の時の文字コード表
SHIFT-JISの文字コード表のコード範囲に関する要件まとめ
SHIFT-JISの文字コード表を作成する先には、どのようなコード範囲を処理すれば良いのか?を下記にまとめます。
- 1バイト文字
- 上位4ビットは0x0から0xFの16種類、下位4ビットも同様に0x0から0xFの16種類で、掛け合わせた合計256のマス目が存在しますが、未使用のマス目が5箇所あり、更に2バイト文字の1バイト目に割り振られたマス目が60箇所あるので、使用できるのは191のマス目になります。
- 2バイト文字
- 1バイト目
- 上位4ビットは0x8から0x9と0xEから0xFの4種類で、下位4ビットはそれぞれ0x0から0xFの16種類あるので64(=4×16)のマス目になりますが、未使用のマス目が4箇所あるので、使用されているのは60のマス目になります。
- 2バイト目
- 上位4ビットは0x3から0xFの12種類で、下位4ビットはそれぞれ0x0から0xFの16種類あるので192(=12×16)のマス目になりますが、未使用のマス目が4箇所あるので、使用されているのは188のマス目になります。
- 1バイト目
※下図のそれぞれ背景色が付いていないセルが、実際に使用されるマス目になります。
文字コードを文字に変換するには?
文字コードの範囲は前章でご説明した形になりますが、では指定された文字コードを文字に変換するのはどうしたら良いのでしょうか?
前述しましたが「文字」とはドットパターンのイメージに当たるので、「変換する」と言うは、『「文字コード」と「文字」を紐づける仕組み』を呼び出して処理をする事になります。
いろいろとコーディングのやり方はあることと存じますが、いまいまメジャーになっている方法を2つご紹介いたします。
Chr関数を使う
VBAの関数(の中の変換関数)に「Chr関数」が存在します。これは10進数の長整数型(Long)数値を引数にして文字を返してくれます。
ちなみに、16進数値を10進数値に変換するには、Val関数を使用します。Val関数の引数は文字列式なのですが、この関数は基数プレフィクス「&O(8 進数)」と「&H(16 進数)」を認識してくれるので、16進数値の表記に”&H”を付与してセットすれば変換してくれます。
下記Microsoft Office ドキュメントのそれぞれのページをご参照いただければ幸いです。
ADO(ActiveX Data Objects)のStreamオブジェクトを使う
Chr関数だけでも十分なのですが、次回の「UTF-8編」で使用する事になるADO(ActiveX Data Objects)のStreamオブジェクトでのコーディング事例もここでご紹介いたします。
この理由としては、『「文字変換を1つの方法にまとめたい」といったケースがある』かもしれないためです。
ただそうは言っても、「もともとVBAにある関数を使用した方が処理速度が速いのでは?」という疑問が起こると思いますので、その辺りも含めてご説明いたします。
Streamオブジェクトとは
ADOはデータベースを操作するためのCOMコンポーネント(オブジェクト)です。
データベースなどのデータを取り出すのはRecordsetオブジェクトを使ってレコード単位に処理するのですが、Streamオブジェクトは「バイト ストリームの読み取り、書き込み、管理を行う手段」を提供してくれます。
※詳細はMicrosoft SQL Serverの下記ドキュメントをご参照いただければ幸いです。
ポイントとなるのは処理対象が「バイト ストリーム」、つまり「バイトのシーケンスとしてファイルが扱われる」点です。
※詳細はMicrosoft Visual Studioの下記「テキスト ストリームとバイナリ ストリーム」ドキュメントをご参照いただければ幸いです。最後にバイトス トリームの説明があります。
Streamオブジェクトで文字コード変換をするコーディングについて
Microsoft SQL Serverの下記ドキュメントに、バイナリ StreamをWriteして、テキスト StreamとしてReadTextをするVBのサンプルコーディングが掲載されています。
※VBAで下記サンプルコーディングを実行する時は、「ツール→参照設定」で「Microsoft ActiveX Data Objects 6.1 Library」(ADOの最新バージョン)を参照可能なライブラリに設定してください。
なお上記のような参照設定ではなくて、CreateObject関数でCOMクラス「ADODB.Stream」を有効化して使用する事ができます。
※後述するコーティング事例では、こちらを使っています。
その他のトピックスを3つ程、列挙いたします。
- ReadTextをする際にCharsetプロパティを設定する事ができます。
- Openメソッドでソースを省略すると、Streamオブジェクトのインスタンスはメモリ内に作成され、Streamを閉じると失われます。
- Charsetプロパティに設定する「文字セット名」は下記を参照します。
システムによって認識される文字セット名の一覧については、Windows レジストリのHKEY_CLASSES_ROOT\MIME\Database\Charset のサブキーを参照してください。
Charset プロパティ (ADO)
ちなみに弊社環境でレジストリの内容を確認すると、右図赤線部分「shift_jis」「shift-jis」「utf-8」の登録がされているので、シフトジスのつなぎはアンダースコアでもハイフォンでも大丈夫になっていました。
Streamオブジェクトで文字コード変換をするコーディング事例
Functionとして記述してあり、引数に「バイナリ配列と文字コード」を指定する事で変換結果が得られるようにしています。
- 引数のチェック機能やエラー時の対応はレスポンスを優先して省略しています。
- 文字コードの指定は「shift-jis」で大丈夫と思いますが、念のため皆様の環境をご確認いただければ幸いです。
'ADODB.Streamで文字コードを変換
Private Function AdoStrmConv(bytAry() As Byte, stCharset As String) As String
Const ADTYPEBINARY As Integer = 1
Const ADTYPETEXT As Integer = 2
With CreateObject("ADODB.Stream")
'引数のバイト配列をストリームにバイナリーで書き込む
.Type = ADTYPEBINARY
.Open
.Write bytAry '引数で指定されたバイト配列をストリームに書き込む
'ストリームから文字コードを指定してテキストで読み込む
.Position = 0 '引数はファイルではないのでBOMはなし
.Type = ADTYPETEXT
.Charset = stCharset '引数で指定された文字コード
AdoStrmConv = .ReadText() 'ストリームから読み込まれた値をセット
.Close
End With
End Function
このコーディングを実際に使用した時の処理時間については、後述する文字コード表を作成するコーディング事例の中でご紹介いたします。
Execlのシートで文字コード表を作成する際の仕様
「どのような文字コード表にするか?」はいろいろご意見が出るところであると存じますが、今回は次のような仕様でコーディングしていますので、お含み置きいただければ幸いです。
- VBAを実行するブックにシートをシート名を指定して追加して、縦・横ともに0x0～0xF(=15)を設定した16行×16列のマトリクスの形で文字コード表を作成する。
- 列タイトルと行タイトルには水色の背景色を付けて設定
- 表の左上隅のセルに、1バイト文字は「0x」、2バイト文字は「1バイト目の16進表記」でセットする。
- 文字コードは、「文字」をセットするセルに「HyperlinkコレクションのAddメソッド」でハイパーリンクを設定して、Hyperlink.ScreenTipプロパティとして表示する。
- ハイパーリンクにマウスを重ねると文字コードが表示される。
上図参照 - なおハイパーリンクの飛び先には「各表の左上隅のセル」を設定する。
- ハイパーリンクにマウスを重ねると文字コードが表示される。
- 文字コードは「SHIFT-JISの領域に関する要件まとめ」にもとづき表記する。
- 不使用セルにはねずみ色の背景色を付ける。
- 2バイト文字の1バイト目には黄色の背景色を付ける。
- 1バイト目は、上位4ビットで0x8～0x9と0xE～0xFに分割されているので、本来であればFor文を2つに分けて、処理が共通するところはサブFunctionとして外だしすべきところですが、今回は0x8～0xFの範囲で回して、0xA～0xDの範囲はIF文で除外しています。
- 「文字」への変換は、コンスタント変数「bCHRFUN」(Boolean型)に”True”をセットした時はChr関数、”False”をセットした時は自作の「AdoStrmConv」関数(前章でご紹介済み)で処理する。
- 自作の「AdoStrmConv」関数は2バイト目不使用領域の0x00を引数にセットすると「パラメータ間違い」のエラーが発生します。これはADODB.Streamの仕様のようです。そのため0x00を回避するIF文のコーティングを実装していますので、お含み置きいただければ幸いです。
※不使用領域は処理しないようFor文を指定すれば良いのですが、「1」の仕様を実装するためにこのような形にしています。
- 自作の「AdoStrmConv」関数は2バイト目不使用領域の0x00を引数にセットすると「パラメータ間違い」のエラーが発生します。これはADODB.Streamの仕様のようです。そのため0x00を回避するIF文のコーティングを実装していますので、お含み置きいただければ幸いです。
- 1バイト文字に含まれる「制御コード」で「戻り値がvbNullCharや空文字になる場合」は、そのままではハイパーリンクの形式でセルに値をセットできないので、このような条件に該当する時は、戻り値にシングルコーテーションを付加する。
- 1バイト文字の「シングルコーテーション」は、そのままセルに代入してもExcelでは表示されないので、強制的に「”」(シングルコーテーション×2)をセットする。
- 2バイト文字の1バイト目が下記に該当する時は、すべてのマス目に「文字」が設定されていないのでシートに文字コードは出力しないようにFilter関数で制御していますが、その1バイト目がどの値だったか解るようにタイトル行だけは残して出力する。
- 該当するのは下記の場合です。
0x85, 0x86, 0xEB, 0xEC, 0xEF, 0xF0, 0xF1, 0xF2, 0xF3, 0xF4, 0xF5, 0xF6, 0xF7, 0xF8, 0xF9
- 該当するのは下記の場合です。
SHIFT-JIS文字コード表のコーディング事例
実際のコーディング事例は下記になります。
※注釈を多めに設定していますので、細かな仕様につきましては注釈をご参考いただければ幸いです。
Sub shiftjis()
Dim i As Integer, i1 As Integer, i2 As Integer, j As Integer
Dim st_cd As String, st_cell As String
Dim l_cd As Long
Dim i_row As Integer, i_col As Integer
Dim st_rtn() As String, st_chk As String
Dim v_omit As Variant
Dim by_buf() As Byte
'---------------------------------
Const bCHRFUN As Boolean = True 'True or False
Const stMOJICD As String = "shift_jis"
Dim dbl_t As Double
dbl_t = Timer '経過時間表示のために開始時刻をセット
Application.ScreenUpdating = False '画面固定
With Sheets.Add(after:=Sheets(Sheets.Count)) '新しいシートを右端に追加
.Columns("A:Q").Font.Size = 12 'フォントの大きさ
.Cells(1, 1).Value = "0x": Cells(1, 1).Font.Size = 14 'タイトル左端の表記
For i = 0 To 15: .Cells(1, i + 2).Value = "〃_" & Hex(i): Next i '列タイトルセット
Range(.Cells(1, 2), .Cells(1, 17)).Interior.ColorIndex = 34 ' 列タイトル背景を色付け
st_cell = .Cells(1, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False) 'リンク先セルを保存
i_row = 1 '最初の行
'1バイト
ReDim by_buf(0)
For i1 = Val("&H0") To Val("&HF") '上位4ビット
i_col = 1 '最初の列
i_row = i_row + 1
.Cells(i_row, i_col).Value = "〃" & Right("0" & Hex(i1), 1) & "_" '行タイトルセット
.Cells(i_row, i_col).Interior.ColorIndex = 34 '行タイトル背景を色付け
For i2 = Val("&H0") To Val("&HF") '下位4ビット
i_col = i_col + 1
l_cd = i1 * 16 + i2 '16進上位4ビットと下位4ビットを合わせて10進変換
st_cd = "0x" & Right("00" & Hex(l_cd), 2) '16進表記
If bCHRFUN Then 'bCHRFUNがTrueの時Chr関数で文字コード変換
.Cells(i_row, i_col).Value = Chr(l_cd)
Else 'Falseの時ADODB.Streamで文字コードを変換
by_buf(0) = Val("&H" & Hex(l_cd)) 'バイト配列に10進数でセット
.Cells(i_row, i_col).Value = AdoStrmConv(by_buf, stMOJICD)
End If
If l_cd = 39 Then 'l_cd = 39 はシングルコーテーションの10進表記
.Cells(i_row, i_col).Value = "''" 'シングルコーテーション×2
ElseIf .Cells(i_row, i_col).Value = vbNullChar Or .Cells(i_row, i_col).Value = "" Then
.Cells(i_row, i_col).Value = Chr(39) & .Cells(i_row, i_col).Value 'NullCharと空文字には「'」を付ける
End If
'シート内セルを飛び先にしたハイパーリンク設定。ヒント表示で16進表記を見せる
.Hyperlinks.Add anchor:=.Cells(i_row, i_col), Address:="", SubAddress:=st_cell, ScreenTip:=st_cd
If st_cd = "0x80" Or st_cd = "0xA0" Or st_cd >= "0xFD" Then ' 不使用背景を色付け
.Cells(i_row, i_col).Interior.ColorIndex = 15
ElseIf (st_cd >= "0x81" And st_cd <= "0x9F") Or (st_cd >= "0xE0" And st_cd <= "0xFC") Then
.Cells(i_row, i_col).Interior.ColorIndex = 36 '2バイト文字の1バイト目の背景に色付け
End If
Next i2
Next i1
'2バイト ↓表示を省略する1バイト目の16進数
v_omit = Array("85", "86", "EB", "EC", "EF", "F0", "F1", "F2", "F3", "F4", "F5", "F6", "F7", "F8", "F9")
ReDim by_buf(1) 'ADODB.Streamのためのバイト配列を初期設定
For i1 = Val("&H81") To Val("&HFC") '1バイト目
If i1 < Val("&HA0") Or i1 > Val("&HDF") Then '未使用・半角 カナエリアを除く
st_chk = Right("00" & Hex(i1), 2) '1バイト目の16進数
st_rtn = Filter(v_omit, st_chk) '除外対象かを判定
i_row = i_row + 1
.Cells(i_row, 1).Value = "0x" & st_chk: Cells(i_row, 1).Font.Size = 14 '1バイト目を左隅にセット
For i = 0 To 15: .Cells(i_row, i + 2).Value = "〃_" & Hex(i): Next i '列タイトルセット
Range(.Cells(i_row, 2), .Cells(i_row, 17)).Interior.ColorIndex = 34 ' 列タイトル行の色付け
'↓ハイパーリンクの飛び先(タイトル行の左上端)セルを保存
st_cell = .Cells(i_row, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)
If UBound(st_rtn) = -1 Then '戻り値が配列になっていないので対象
For j = Val("&H00") To Val("&HF0") Step 16 '2バイト目。ステップを16に設定
i_col = 1 '最初の列
i_row = i_row + 1
.Cells(i_row, i_col).Value = "〃" & Left(Hex(j), 1) & "_" '行タイトルセット
.Cells(i_row, i_col).Interior.ColorIndex = 34 ' 行タイトルに色付け
For i2 = j To (j + 15) '2バイト目の下位4ビット。&H0~&HF
i_col = i_col + 1
l_cd = 16& ^ 2 * i1 + i2 '16進1バイト目と2バイト目を合わせて10進変換
st_cd = "0x" & Right("0000" & Hex(l_cd), 4) '16進表記
If bCHRFUN Then
.Cells(i_row, i_col).Value = Chr(l_cd)
Else
If i2 = Val("&H00") Then
.Cells(i_row, i_col).Value = Chr(39)
Else
by_buf(0) = Val("&H" & Hex(i1))
by_buf(1) = Val("&H" & Hex(i2))
.Cells(i_row, i_col).Value = AdoStrmConv(by_buf, stMOJICD)
End If
End If
.Hyperlinks.Add anchor:=.Cells(i_row, i_col), Address:="", SubAddress:=st_cell, ScreenTip:=st_cd
If Right(st_cd, 2) <= "3F" Or Right(st_cd, 2) = "7F" Or Right(st_cd, 2) >= "FD" Then ' 不使用背景を色付け
.Cells(i_row, i_col).Interior.ColorIndex = 15
End If
Next i2
Next j
End If
End If
Next i1
.Name = "S-JIS." & Abs(bCHRFUN) 'シート名を変更
.Columns("A:Q").HorizontalAlignment = xlCenter '横配置
.Columns("A:Q").VerticalAlignment = xlCenter '縦配置
.Columns("A").ColumnWidth = 6 '列幅を設定
.Columns("B:Q").ColumnWidth = 5 '列幅を設定
.Cells(1, 1).Select 'A1セルを選択して範囲選択を解消
End With
Application.ScreenUpdating = True '画面固定を解除
MsgBox (Timer - dbl_t) & " 秒"
End Sub
Chr関数と自作の「AdoStrmConv」関数での処理時間の差を検証
それぞれを3回実行した上で、平均時間を計算して比較しています。
| 関数 | 1回目 | 2回目 | 3回目 | 平均 |
|---|---|---|---|---|
| Chr関数 | 2.43秒 | 2.44秒 | 2.70秒 | 2.52秒 |
| AdoStrmConv関数 | 2.98秒 | 3.05秒 | 3.04秒 | 3.02秒 |
結果は上記のようになり、AdoStrmConv関数の方が0.5秒遅い事になりした。
AdoStrmConv関数を使用する際に2バイト文字の2バイト目の0x00を回避するためのIF文のコーティングがあるので「遅いのは想定内」ではありますが、0.5秒だったので、AdoStrmConv関数自体は「それほど低スピードにはなっていない」と思います。
なお実際に関数が実行された回数は16×16×46=11,776回になるので、「1回あたりの秒数から100万回実行した場合の差」を計算すると「43秒」になります。
従いまして、どれだけの文字数を処理しなければならないか?によって使い分けをご検討いただければ幸いです。
最後に
今回はSHIFT-JIS文字コード表を作成するコーディング事例をご紹介いたしました。
COMクラス「ADODB.Stream」で文字に変換する処理は、メモリ上で実行されるので、最近のパソコンの処理能力の力を借りると、「組み込みのChr関数とそれほど処理時間に差ができない」というのは、新しい発見でした。
なお、「Chr関数は2バイト文字の2バイト目の0x00を処理できる」のに対して、「ADODB.Streamではエラーを返す」というのは納得がいかない所ではあります。(他の不使用コードは何かしら値を返すのに…)
ただし次回はUTF-8文字コード表になりますが、そこでは「不使用コードがエラーを返す」文字領域が出て来ますので、これは「致し方ない事」なのかもしれません。
以上、最後までご一読いただき誠にありがとうございました。
