発掘する理由

Accessを始めるまえに簡単にパパッと作る関数ansiLenB Excelも
Windwos SystemはAccess2000のとき、そしてVistaのときに文字コードにサロゲートペアが導入されたときに問題が生じている。

交通整理(Disambiguation)

本記事はテキストファイル自体の文字コードの判定ではない
https://popozure.info/20190515/14201
むろん、これも大きな問題であるが、今回は文字単位のコード判定である。

テストツール

ユニコード文字はMsgBoxやイミディエイト、コードペインに表示できない。まずこのユニコードや代用対文字を表示するMessageBoxExのWindowsAPIとSub Procedure 及び、セルに代用対文字が1字入っていることを前提として、結合文字コードををイミディエイトに表示するSub Procedureを作る。このMessageBoxExはExcelだけではなく、Access,Word,Publisher全てに有効である。

MessageBoxEx

#If VBA7 Then
Declare PtrSafe Function MessageBox Lib "user32" Alias "MessageBoxW" _
    (ByVal hwnd As LongPtr, ByVal lpText As LongPtr, _
     ByVal lpCaption As LongPtr, ByVal wType As Long) As Long
Declare PtrSafe Function GetFocus Lib "user32" () As LongPtr
#Else
'標準モジュールに以下のDeclare ステートメントを追加
Declare Function MessageBox Lib "user32" Alias "MessageBoxW" _
    (ByVal hwnd As Long, ByVal lpText As Long, _
     ByVal lpCaption As Long, ByVal wType As Long) As Long
Declare Function GetFocus Lib "user32" () As Long
#End If
Sub MsgBoxEx(Text As String)
    Dim Caption As String
    Caption = "Microsoft Excel" 'キャプション
    'StrPtr()関数でポインター型（LongPtr）にキャスト
    MessageBox GetFocus(), StrPtr(Text), StrPtr(Caption), 0
End Sub
' -----ここまでをModuleのトップに置く(Option Explicitの下)
Sub TestSarrogatePair()
' For Excel
' ActiveCellにサロゲートペア文字が1字入っているとして、
' その上位サロゲートと下位サロゲートを求めて、試験のために
' MsgBoxExで表示する
' このため、Windows APIを使用する
Dim b As String
Dim bb() As Byte
Dim Text As String
b = ActiveCell.Value
bb = b
Debug.Print "AscW" & Hex(AscW(b))
Debug.Print "&H" & Hex(bb(1)) & Hex(bb(0))
Debug.Print "&H" & Hex(bb(2)) & Hex(bb(3))
Text = ChrW("&H" & Hex(bb(1)) & Hex(bb(0))) & ChrW("&H" & Hex(bb(3)) & Hex(bb(2)))
' 以下によりイミディエイトにChrWを2つ使ったVBAにおける表示方法が示される。
Debug.Print "ChrW(" & "&H" & Hex(bb(1)) & Hex(bb(0)) & ") & ChrW(&H" & Hex(bb(2)) & Hex(bb(3)) & ")"
MsgBoxEx Text
End Sub

Excel2013以降のUNICODE関数UNICHAR関数

Excel.WorksheetFunction.UNICODE関数

エクセル UNICODE 関数：文字の Unicode を取得する TipsFound
2013以降、Excelのワークシート関数にUNICODEとUNICHARという関数が加わっている。

=UNICODE("🔟")
128287
が返る。
通常は
=DEC2HEX(UNICODE("🔟"))
1F51F
のように16進数にする。
ただし、6桁なので
01F51Fと読む
=DEC2HEX(UNICODE("🔟"),6)
のように桁数を指定する。TextではFormatを指定できない。（最初10進数と同様にできるかと思い失敗）

エクセル UNICHAR 関数：Unicode を文字に変換する
こちらはその逆。さらに特殊文字の入力にも使用できる。Charでもできる。(VBAではCHR())
=UNICHAR(HEX2DEC("01F51F"))
サロゲート文字の「つちよし」（吉野家のよし）は20BB7。あたまに0をいれて020BB7
https://www.unicode.org/cgi-bin/GetUnihanData.pl?codepoint=20BB7
なので
=UNICHAR(HEX2DEC("020B07"))
VBAではサロゲートペアはChrWを2つ使って結合させる。
さらにその結合させるために16進数を得るには

セルの文字を一旦変数に代入する。（なぜか直接はエラーになる)
バイト型の配列にそのまま代入する
すると 0 1 2 3 の4つの要素が得られる。
UTF-16LEのため小さい方、つまり上位サロゲートでも下位から読まれる。このためこの配列は
上位サロゲート1 0 下位サロゲート 2 3
並べ替えて16進数に変換し、結合させる

という流れである。これはExcelのUnicodeとはUTF-16LEということを意味する。

文字コードから文字を取得（Chr、ChrB、ChrW）

ChrW関数の限界
ChrW関数は引数のUnicode文字コードを変換しますが、Unicodeには３バイト以上の文字があります。たとえば魚のほっけ（）は魚に花と書きますが、この文字はUnicodeでは「U+29E3D」と表現されます。これは数値にすると171581になります。
ChrW関数の引数は定義上はLong型になっていますが、おそらくInteger型の誤りと思われ、ほっけ（）の「U+29E3D（数値では171581）」を指定すると２バイトの65535を超えるためエラーになります。
このように、ヘルプなどでは「Unicodeの文字コードはChrWを使う」と書いてあっても、実際には変換できない文字があります。

たしかにVBAのChrWはLongではないのは本当で、ChrW1個ではホッケは取得できない。
はChrWを2回使う
たとえば、「つちよし」は ChrW(&HD842) & ChrW(&HB7DF)であり、ほっけはChrW(&HD867) & ChrW(&H3DDE)
これはサロゲートペアである限り容易にこの組み合わせが得られる。
そしてAscWは上位サロゲートのみ取得している。
UNICODE関数がないときはAscWを使っていたが、これでは容易に得られなかった。

基数プレフィックス

基数前置符号
数の前において進数を表す符号
&H 16進数 &o 8進数

VBAにおける10進数から16進数、16進数から10進数への変換

10進数から16進数
https://vbabeginner.net/vba%E3%81%A710%E9%80%B2%E6%95%B0%E3%81%8B%E3%82%8916%E9%80%B2%E6%95%B0%E3%81%B8%E5%A4%89%E6%8F%9B%E3%81%99%E3%82%8B/
=Hex()
16進数から10進数
https://vbabeginner.net/vba%E3%81%A716%E9%80%B2%E6%95%B0%E3%81%8B%E3%82%8910%E9%80%B2%E6%95%B0%E3%81%B8%E5%A4%89%E6%8F%9B%E3%81%99%E3%82%8B/
'=Val("&H" & str)'

ExcelのWorksheet関数

ExcelのWorksheetFunctionは
10進数から16進数
Dec2Hex
16進数から10進数
Hex2Dec

SarrogatePairのCharWの組み合わせを取得する関数

Excel VBA
ActiveCellに例えばつちよしのような代用対の文字が1文字入っている。
この状態で、下記のVBA TestSarrogatePairを実行する
Activecellの状態

このようにMessageBoxでつちよしが表示される

AscW Result(Dec -> Hex) D842
&HD842
&HB7DF
ChrW(&HD842) & ChrW(&HB7DF)

AscWは上位サロゲート &HD482しか取得していない。
もっとも、これを応用すればAscWで上位サロゲートを取得すれば、そこで2bitすすめていいことになる。
文字のカウントの場合は有効かもしれない。

Sub TestSarrogatePair()
' For Excel
' ActiveCellにサロゲートペア文字が1字入っているとして、
' その上位サロゲートと下位サロゲートを求めて、試験のために
' MsgBoxExで表示する
' このため、Windows APIを使用する
Dim b As String
Dim bb() As Byte
Dim Text As String
b = ActiveCell.Value
bb = b
Debug.Print "AscW Result(Dec -> Hex)" & vbTab & Hex(AscW(b))
Debug.Print "&H" & Hex(bb(1)) & Hex(bb(0))
Debug.Print "&H" & Hex(bb(2)) & Hex(bb(3))
Text = ChrW("&H" & Hex(bb(1)) & Hex(bb(0))) & ChrW("&H" & Hex(bb(3)) & Hex(bb(2)))
Debug.Print "ChrW(" & "&H" & Hex(bb(1)) & Hex(bb(0)) & ") & ChrW(&H" & Hex(bb(2)) & Hex(bb(3)) & ")"
MsgBoxEx Text
End Sub

Byteは配列型でないと文字を直接代入できない

次に、Byte配列に入れる方法も注意が必要。
~~このByte配列はダイレクトに文字を変換することはできない。~~
https://excel-ubara.com/excelvba4/EXCEL_VBA_409.html
たぶん byte配列にしなかったのが原因らしい。
Dim bb As Byteではなく必ず'()'をつけて Dim bb() As Byte
としなければならない。もしくは変数に文字列型（String型) が入っていなかったかである。
失礼しました。

Dim bb As Byte : bb = "abcの歌"
ということをやっても失敗する
文字型変数に代入して変換する。
もしくは以下のようにCStrを使う
しかし、この方法では煩雑であるが、Byte配列が使われていないので知られていないのかもしれない。
もう一つ変わっているのは直接代入するのは大丈夫なのに、CByte関数はエラーになること。
Dim bb() As Byte : bb = Cbyte("abcの歌")
のように明示的な型変換はエラーになる。

Sub ByteArrayTest()
Dim bb() As Byte
bb = CStr("abcの歌")
' または
Dim buf as String
buf = "abcの歌"
bb = buf
End Sub

Access

https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1035015305
テキストフィールド[A]に16進数が入っているとする。

あと、Int関数なども使えますし、関数など使わなくとも
式1: ("&h" & [A])+0
とか、
式1: ("&h" & [A])*1

&hFF のようにすでに16進数文字列に &h がついてるのならば、16進数から10進数に変換するのは、単に Val関数や CInt関数、CLng関数に入れるだけです。
式1: Val([A])

このやり方はExcelのWordksheetのCell, VBAで時間を表す文字列を時間に変換する場合に似ている。
"12:43:36" * 1
https://qiita.com/Q11Q/items/a37bcfc02b1458111f2d
なお
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q10211724625
Val(Nz([A]))
とするとNullが返る。

Val関数はExcelにもある

いうまでもなくEvaluateであるし、ValはEvaluateの略だと思われる。しかし、以下のValテストでも挙動は大きく異なる。

Access

Val 関数

文字列中に数字以外の文字が見つかると、Val 関数は読み込みを中止します。円記号 () やカンマ (,) など、通常は数値の一部とみなされる記号や文字も、Val 関数は数値として解釈しません。

Val関数もう一度学ぶMS-Access
https://www.accessdbstudy.net/entry/20101027/p1

Var関数と間違えるとエラーに見舞われますので注意してください_ _)
文字が現れるまでの数値部分を取り出してくれるというだけの関数です。\やカンマがあるだけでもそれ以降は返されませんし、ましてや漢字等を完全に取り除いてくれるような機能はありません。さらに全角数字も0になるなど注意すべき点は多く、使い勝手はそれほどよくありません。

AccessのVal関数はコンマから先は読まない。Excelと違ってシビアだとしている。
https://www.accessdbstudy.net/entry/20100916/p1
CIntのほうが数値は狭いが、コンマを超えて変換するので強いとしている。

Val関数（こちら）と違って\やカンマがあっても問題なく変換できるのがメリットです。
一方で変換できない値に対しては容赦なくエラーが出ますので注意が必要です。
また、扱える値の範囲が狭い（-32,768～32,767）ことにも注意してください。

VBA

Val関数は文字列の中で数値として認識できる部分までを返します。\記号やカンマ（,）などは数字として認識されません。文字列中にあるスペース、タブ、ラインフィードは無視されます。
~~Val(1,000) > 1000~~
Val(1,000)を入力するとVal(1)になってしまい、入力不可能。

Sub ValTest()
Debug.Print Val("1,000")
Debug.Print CInt("1,000")
Debug.Print CLng("1,000")
Debug.Print Evaluate("1,000")
End Sub

1
1000
1000
エラー 2015
たしかにCint,Clngはコンマを超えるが、Valは1までしか取れない。Evaluateに至ってはエラーである。Evaluateは1,000を評価したわけではない。単にエラーである。Evaluate("1000")で1000が返る。

以上のようなツールを用いる理由はこのサロゲートペアでLen等の文字列を操作する関数の挙動がワークシートとVBAで異なったり、見た目と違う結果が返るためである。このVBAとWorkSheetFunction比較を行って参照されていたページが下記であり、以上から、関連する「つちよしの入力」とともに発掘するものである。

サロゲートペアについて

http://pasofaq.jp/windows/ime/surrogatespair.htm
最終更新日：2009/5/23

Windows Vista から追加された文字（これらはすべて Unicode）の中には、サロゲートペアという特殊な文字が含まれています。 Unicode は16ビットのコード体系で発表され、16ビットなので2の16乗＝65,536種類（0x0000～0xFFFF）の文字を表現できることになりました。ところが、文字の追加要望が増えて65,536種類では足りなくなってしまったために、通常の文字は1文字2バイトを維持し一部の文字は1文字4バイトを使うという方法が導入されました。これが、サロゲートペアと呼ばれるものです。

1文字が4バイトの文字が導入されたことで、文字数をカウントしたり部分文字列を取り出すプログラムの結果が変わってしまうことがあります。下の表は、

1文字目にサロゲートペアのを含む文字
シフトJIS の文字だけで表現できる「吉田」
1文字目に通常の2バイトUnicode文字を含む文字

をテストデータとして使い、VBA関数とExcelワークシート関数で文字長を返す Len関数や部分文字列を取り出す Mid関数の結果をまとめたものです。

この検証により、次のようなことがわかります。

サロゲートペアを含む文字列の長さは、見た目の文字長は2でも、Len関数を使うと3になっている。
サロゲートペア文字は、Mid関数で第3引数の文字長を2にしないと取得できない。
VBA関数と Excelワークシート関数で、同じ名前の関数を使っても結果が異なる。

ということがわかります。そのため、文字数を数えたり部分文字列を取り出すプログラムでサロゲートペアを扱えるようにするには、適切に修正する必要があります。

このファイルを Windows XP で開くと、Windows Vista から追加された文字は表示できず「・」になっていることがわかります。

上部の表のコード

Sub SheetMake()
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Excel ワークシート関数"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "サロゲートペア"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "Sift-Jis"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = "通常のUNICODE"
    Range("B5").Select
    Columns("A:A").ColumnWidth = 12.75
    Columns("A:A").EntireColumn.AutoFit
    Range("B3").Select
    ActiveCell.FormulaR1C1 = ChrW(&HD842) & ChrW(&HB7DF) _
     & "田"

    Range("B4").Select
    ActiveCell.FormulaR1C1 = "吉田"

    Range("B5").Select
    ActiveCell.FormulaR1C1 = ChrW(&H4203) & "田"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "Len"
    Range("C3").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=LEN(RC[-1])"
    Range("C3").Select
    Selection.Copy
    Range("C4:C5").Select
    ActiveSheet.Paste
    Range("D2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "LenB"
    Range("D3").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=LENB(RC[-2])"
    Range("D3").Select
    Selection.Copy
    Range("D3:D5").Select
    ActiveSheet.Paste
    Range("E2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "MID(1,1)"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "MID(,1,1)"
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[-3],1,1)"
    Range("E3").Select
    Selection.Copy
    Range("E4:E5").Select
    ActiveSheet.Paste
    Range("F2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "MID(,2,1)"
    Range("F3").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[-4],2,1)"
    Range("F3").Select
    Selection.Copy
    Range("F4:F5").Select
    ActiveSheet.Paste
    Range("G2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "MIDB(,1,2)"
    Range("G3").Select
    ActiveCell.FormulaR1C1 = "=MIDB(RC[-5],1,2)"
    Range("G3").Select
    Selection.Copy
    Range("G4:G5").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "MIDB(,3,2)"
    Range("H3").Select
    ActiveCell.FormulaR1C1 = "=MIDB(RC[-6],3,2)"
    Range("H3").Select
    Selection.Copy
    Range("H4:H5").Select
    ActiveSheet.Paste
    Range("I2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "MID(,2,1)"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "MID(,1,2)"
    Range("I3").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[-7],1,2)"
    Range("I3").Select
    Selection.Copy
    Range("I4:I5").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

上記の記事のVBA用関数の一部

'ワークシート関数との比較用の関数
Function VBALENB(s As String)
VBALENB = VBA.LenB(s)
End Function

Function VBALEN(s As String)
VBALEN = VBA.Len(s)
End Function

' VBA MID Funciton ------

Function VBAMID11(s As String)
VBAMID11 = VBA.Mid(s, 1, 1)
End Function

Function VBAMID12(s As String)
VBAMID12 = VBA.Mid(s, 1, 2)
End Function

Function VBAMID21(s As String)
VBAMID12 = VBA.Mid(s, 2, 1)
End Function

' VBA MIDB Funciton ------

Function VBAMIDB11(s As String)
VBAMIDB11 = VBA.Mid(s, 1, 1)
End Function

Function VBAMIDB12(s As String)
VBAMIDB12 = VBA.Mid(s, 1, 2)
End Function


Function VBAMIDB32(s As String)
VBAMIDB32 = VBA.MidB(s, 3, 2)
End Function

Function VBALEFT1(s As String)
VBALEFT1 = VBA.Left(s, 1)
End Function

Function VBALEFTB1(s As String)
VBALEFTB1 = VBA.LeftB(s, 1)
End Function
Function LenBStrConv(buf As String)
If buf = "" Then LenBStrConv = 0: Exit Function
LenBStrConv = LenB(StrConv(buf, 128))
End Function

“つちよし”を入力する方法

最終更新:2009/9/6
http://pasofaq.jp/windows/ime/tsuchiyoshi.htm
人名漢字を出力するにあたって、必ずと言ってもいいほど問題になる文字に「吉」の下が長い文字、俗に言う“つちよし”があります。たとえば合格証書などでどうしても出力しなければならない場合、外字として作成して出力するのが一般的です。

Windows Vista で追加になった新フォント「メイリオ」を使えば、外字として作成しなくても“つちよし”を利用できます。IME パッドの文字一覧で「Unicode（追加漢字面）」-「CJK 統合漢字拡張 B」を選択し、文字コード U+20BB7 を表示させるか、またはメモ帳などで日本語入力の状態にして「20bb7」と入力し F5 キーを押します。

しかし、フォントをメイリオ以外にすると□で表示されてしまいますし、Unicode に対応していないプログラムでは ? と表示されるなど、かなり限定的な使い方しかできないので注意が必要です。しかも、この文字は Unicode 文字の中でもさらに特殊なサロゲートペアの文字なので、プログラムでこの文字を処理しているような場合、正常に動作しないことがあります。

この2009年頃までは外字で解決していた。

https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1215801687
げそ天うどんさん
2008/4/7 15:36
7回答
七が3つならんだ漢字1字の出し方と読みをお教えください。当用漢字でしょうか。
木が3つで森のように配置された字です。
老人会のタイトルに使われています。

fon********さん
2008/4/7 17:16（編集あり）
「喜」の異体字で、草書体の字形を印刷用の字体にデザインしたものがあります。
http://images.google.co.jp/images?q=%82%AB%82%EB%82%AD&hl=ja&btnG=%83C%83%81%81%5B%83W%8C%9F%8D%F5
http://www.unicode.org/cgi-bin/GetUnihanData.pl?codepoint=3402
Unicode：U+3402 (CJK統合漢字拡張Ａ領域)
JIS X 0213 面区点：1-14-03 (第三水準)
XPまでのMS明朝にはありませんが、VistaのMS明朝にはあります。
今Vistaの画面では『㐂』にその字が表示されています。
XP/2000では「・」になっていますが、『㐂』をWordなどにCopy&Pasteし、フォントをNew Gulimに変更すれば、
文字が表示されます。
ただし細めのゴシック系の書体で、他のフォントに混ぜて使うには適していません。
XP-SP2であれば、下記から「Windows XP および Windows Server 2003 向け JIS2004 対応 MSゴシック & MS明朝フォントパッケージ」がダウンロードできます。(編注：現在はDL不可能）
http://www.microsoft.com/japan/windows/products/windowsvista/jp_font/jis04/default.mspx
人名用漢字「祐」の旧字体『祐』など使える字が飛躍的に増えますが、XP用のMSゴシック/明朝との置き換えになり、併存はできません。
そのため『祇』がワープロ略字の「ネ氏」ではなく正字の「示氏」になり、『辻』のシンニョウは点が二つになるなるなど、
ある意味での“文字化け”が起こります。
いったんインストールした後でも、アンインストールして元に戻すことはできます。
もしインストールした場合、Word2002以降なら、半角で 3402[Alt]＋[ X ] と打ち、待っていれば変換されます。
◇フリーの外字ファイル (拡張子TTE) をダウンロードし、任意のフォントにリンクさせれば、9x/Me/2000/XPで使えます(明朝とゴシックがあります)。
http://gaiji.info/
http://gaiji.info/reference.htm
ページ最下段、検索キーワード：[よろこぶ]と記入＞[検索]ボタンをクリック
シフトJIS：#F060に登録されていることが確認できます。
ただ、個人が無料で配布しておられるものですので、精細度や筆画のバランスにおいて純正の外字に及ばない場合があることは御理解下さい。
もちろん、外字ですので、同じ設定がされていない別のPCではその字を表示することはできません。
PDFファイルにしてフォントを埋め込めば、どのPCでもオリジナル文書のまま表示・印刷されます。
この外字ファイルを使うなら、下記の手順に従ってください (XPの場合で説明します)。
――――――――――――――――――――
まず、Ｄドライブでもいいので (C:\WINDOWS\Fonts以外の場所を推奨)、
[MyGAIJI] (一例) フォルダを作っておく。
上掲サイトからダウンロードしたGaijiJinja_F040.lzhを解凍する (解凍先を[MyGAIJI]に指定)。
解凍の結果出来た[GaijiJinja_F040]フォルダの中に
GaijiJinja_F040_M.tte
GaijiJinja_F040_G.tte
という外字ファイルがあるのを確認。
(以下、前者＝明朝用として説明)
スタート＞すべてのプログラム＞アクセサリ＞外字エディタ
【コードの選択】
[キャンセル]
メニューバー[ファイル]＞フォントのリンク
↓
【フォントのリンク】
「指定したフォントにリンクする」を選択
リンクさせるフォント (一例「MS
「保存する場所」を[Fonts] ▼→マイコンピュータ＞……＞[MyGAIJI]＞ [GaijiJinja_F040]フォルダに変更
表示された[GaijiJinja_F040_M.tte]ファイルを選択、[保存]
aijiJinja_F040_M」のリンクを確認、
(ゴシックも設定するなら、続けて同様に行います)
[OK]
【外字エディタ】 (メイン画面)
[×]で外字エディタ終了、OS再起動
目的の字はシフトJIS #F060 にあるので、
全角[ あ ]モードで [ F ][ 0 ][ 6 ][ 0 ][ F5 ] と打ち、【IME パッド－文字一覧】でポイントされている字をクリック、[Enter]で確定。
――――――――――――――――――――
「き＠」などの読みで単語登録しておくと、一発で出せます。（以下略）