Accessを始めるまえに簡単にパパッと作る関数ansiLenB Excelも

#Accessではユーザー定義関数作る必要がある
## AccessのRound
Accessのround関数はExcelと異なる挙動をするため、[Accessを始めるまえに簡単にパパッと作る関数xlRnd（おまけ付き）](https://qiita.com/Q11Q/items/a3c23012cf51872182ea)で最初にユーザ定義関数を作らなければいけないということを以前書いたが、これだけではなかった。
##ExcelとAccessのLenB
[AccessVBAの文字コードにハマった話](https://qiita.com/yamashi/items/dd42da29c5e555091731)によるとLenBがユニコード処理のため、半角英数字が1byteして返らない問題があると判明した。
これをさらに調べると、Office2000以降、ユニコードで内部処理をするようになってから発生した。問題であることが分かった。

###Excelでもある
これはさらにExcelでMougで指摘されていた。
[文字列の長さを取得する(Len/LenB関数)-Moug](https://www.moug.net/tech/exvba/0140023.html)

 - ExcelのWorksheetFunctionのLenBは半角を1文字として数えるが、Excel2000以降のVBAのLenBは2文字としてカウントする
 - WordkSheetFunctionでLenBを呼び出すことはできない
 - 呼び出すとすればEvaculeteを使う
 
# コード
##Office2000以降 Access Excel共通版
RoundのようにWorksheetfunctionで呼び出せないので、以下のようになる。とくに参照設定は必要とせずEXCEL Access共通して使用できる。またMougのEvaculeteは移植できなかった。
まずAccess 2000以降 Excel2000以降で特に移植を意識しないのであれば次の関数をAccess、Excelで使用するとよい

```vb:fnAnsiLenB
Function AnsiLenb(str as String) as Long
' Access 2000 And Excel 2000 Later Function LenB uses unicode
' So LenB("a") returns 2 Byte
' fnAnsiLenb("a") retruns 1 Byte
fnAnsiLenB = LenB(StrConv(str, vbFromUnicode))
End Function
```

## Access共通版
LenBは2000より前の版はそのままでよい
このため、ヴァージョン番号で切り替える
これだと古いバージョンに移植しても安全に同じ結果が得られる。

```vb:AcAnsiLenB
Public Function AcAnsiLenB(str As String) As Long
' Access2000 Later Function LenB is use unicode
' So LenB("a") returns 2 Byte
' AcAnsiLenb("a") returns 1 Byte
' For All Verion Access
If TypeName(str) <> "String" Then AcAnsiLenB = 0: Exit Function
If SysCmd(acSysCmdAccessVer) >= 9 Then
AcAnsiLenB = LenB(StrConv(str, vbFromUnicode)) 'vbFromUnicode = 128
Exit Function
Else
AcAnsiLenB = LenB(str)
Exit Function
End If
On Error GoTo 0
If Err.Number <> 0 Then Debug.Print Err.Number, Err.Description, "str=", str, Date
AcAnsiLenB = 0
End Function
```
## Excel共通版

```vb:xlAnsiLenB
Public Function xlAnsiLenB(str As String) As Integer
' Excel 2000 Later Function LenB is use unicode
' So LenB("a") returns 2 Byte
' xlAnsiLenb("a") returns 1 Byte
' For All Verion Excel
If TypeName(str) <> "String" Then xlAnsiLenB = 0: Exit Function
If Application.Version >= 9 Then
xlAnsiLenB = LenB(StrConv(str, vbFromUnicode)) 'vbFromUnicode = 128
Exit Function
Else
xlAnsiLenB = LenB(str)
Exit Function
End If
On Error GoTo 0
If Err.Number <> 0 Then Debug.Print Err.Number, Err.Description, "str=", str, Date
xlAnsiLenB = 0
End Function
```

# 参考文献
[すぐに役立つエクセルＶＢＡマクロ集 FAQ](http://www.asahi-net.or.jp/~zn3y-ngi/YNxv9872.html)
[Accessのバージョンを調べる ： Access(アクセス) - feedsoft](https://www.feedsoft.net/access/tips/tips72.html)
[変数の型を調べるには？－ローカルウィンドウ・TypeName関数-relief](www.relief.jp/docs/vbe-check-variable-type.html)
[Database.Version プロパティ (DAO)](https://msdn.microsoft.com/ja-jp/library/office/ff192887.aspx?f=255&MSPPError=-2147217396)
[Len関数 と LenB関数 ： 一日一見 -Access Club](http://www.accessclub.jp/supbeg/131.html)[Wayback](https://web.archive.org/web/20160309074819/http://www.accessclub.jp/supbeg/131.html)
>####Unicode と ANSI
Access2000からUnicodeが採用されました。これは、全ての文字（全角、半角、漢字）を２バイトであらわします。逆に、ANSI形式は、全角文字と漢字は２バイト、半角文字は１バイトで表します。
####Access2000以降のLenB関数
よく、解説書で「LenB関数は、半角なら１バイト、全角なら２バイトで戻り値を返します」と記載されていますが、Access2000以降、この記述は誤りです。
しかし、Unicode形式の文字をANSI形式の文字を変換する関数が用意されているので、これを利用すると無事うまくいきます。

####この問題はVB6でも起きる
[ｖｂ６でひとつ質問があります。- Yahoo知恵袋](https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1396539268)

####MACはさらに要注意
[ExcelのVBA入門>文字列操作関数>ChrB関数 - VBのIE制御](https://www.vba-ie.net/function/chrb.html)

>Visual Basic for the Macintosh では、Unicode 文字列をサポートしていません。したがって、AscW(n) 関数は、Windows 環境では 128 ～ 65535 の範囲のすべての Unicode 文字を返すことができますが、Macintosh 環境では正確な Unicode 文字を返すことができません。このため、 Macintosh 環境では AscW 関数を使用しないようにしてください。

####特に古いシステムを移植する場合はLenBをこの関数に変える必要がある
[LenB関数、ExcelとACCESSで結果が異なる。- 情報システム部のインフラ・プログラム開発日誌](http://ewigsystem.blog.fc2.com/tb.php/22-9d2fa2f1)

>エクセルのワークシートでのLenB関数と
AccessでのLenB関数の結果が違うことに・・・
あるDBから違うDBにデータをインポートする際、
文字数の仕様が違う場合があり、
事前にチェックしようとしていて気がつきました。

#公式は違うことを言っている
##[[ACC2000] Unicode と文字列操作関数の留意点](https://support.microsoft.com/ja-jp/help/404928)[Wayback]
(https://web.archive.org/web/20100304134552/http://support.microsoft.com/kb/404928/ja)
###編纂者補足
404928 is dead link, now.
このページで行くと結果が変わる可能性があるのは95からということになる。もっともこの原文はAccess Basicとの差異に重点が置かれているのでVBAは97から差異があるということでよいだろう。（実際に97以降で発見されているため）

>###概要
この資料は、以前のバージョンの Access Basic の関数と異なる結果を返す Microsoft Visual Basic Programming System Applications (以下 VBA) の文字列操作関数につい て説明しています。
###詳細
Access 95 以降では Unicode が採用されました。それに伴い VBA の文字列を操作する関 数が、Access 1.1 や Access 2.0 の Access Basic の関数と異なる結果を返す場合があ ります。ここでは、Access 95 以降で文字列を操作する関数を使う上での留意点につい て説明します。

>###Access Basic と VBA の文字の格納形式の違い
VBA での文字列のメモリ上での格納形式は、Access 1.1 や Access 2.0 の Access Basic と異なっています。Access Basic では ANSI 形式で格納されていましたが、Access 95 以降では Unicode 形式で格納されます。
これは、VBA が密接な関係をもっている、OLE 内部での文字列形式にあわせるためです。

###VBA と Access Basic で結果の異なる可能性のある関数、ステートメント
VBA と Access Basic で結果の異なる可能性のある関数、ステートメントには、次のものがあります。

>- Asc 関数
- Chr 関数
- InputB 関数
- InstrB 関数
- LeftB 関数
- LenB 関数
- RightB 関数
- MidB 関数
- および、これら関数に対等するステートメント

>VBA では、その他に AscB 関数、AscW 関数、ChrB 関数、ChrW 関数がサポートされています。以下に、VBA でのコーディングをする上で留意する必要のある点を記述します。
###文字のバイト数を利用したプログラム
  文字列内の文字数の取得             Len 関数
  文字列のバイト数の取得             LenB 関数
 ***Access Basic では、ある文字が漢字かどうかを、文字数とバイト数の関係から判断する ことができました。***漢字では、バイト数が、文字数の 2 倍になります。しかし、VBA で 採用している Unicode では、すべての文字で、この関係が成り立ちます。VBA で Access Basic と同じロジックでプログラムを作成するには、バイト数を求める前に、Unicode 文 字を ANSI 文字に変換する必要があります。
###文字コードを利用したプログラム
VBA では、システムの既定のコードページ内の文字コードと Unicode を対象とする関数があります。そのため、どちらの文字コードで処理するのかを意識してコーディング する必要があります。文字と文字コードの変換関数として、Asc 関数と Chr 関数があります。VBA では、その他に AscB 関数と AscW 関数、ChrB 関数が用意されています。

>  システムの既定のコード ページ内の文字コードの取得               Asc 関数
  Unicode の取得                                            AscW 関数

> VBA の Asc 関数は、引数として Unicode 文字列をとります。***1 バイト文字の文字コー ドを取得するには、AscB 関数を使用しなければいけません。***例えば、"あ"という文字 の文字コードの 2 バイト目を表示するような、バイト単位で操作するプログラムでは 注意が必要です。
Access Basic では、Print Hex(Asc(MidB("あ", 2, 1))) ‘ 結果 A0 のように記述します。VBA では、1. の記述ではエラーが発生します(下記テストでもエラーが発生する)。MidB 関数の戻り値が Unicode として適切な値を返さないためです。VBA で は、2. のように AscB 関数を使用しなければいけません。2. の記述では、Unicode の 2 バイト目の値 (&H30) が取得できます。Access Basic と同じ結果を得るには、 3. のように、StrConv 関数を使って、Unicode 文字をシステムの既定のコードページ内の文字へ変換します。


編注：ここではこれをAccess VBAに変えている

```vb
Sub test()
’For Access
On Error Resume Next
Debug.Print Hex(Asc(MidB("あ", 2, 1))) ' 結果 実行時エラー5 プロシージャの呼び出し、または引数が不正です。
Debug.Print Err.Number, Err.Description
Debug.Print Hex(AscB(MidB("あ", 2, 1))) ' 結果 30
Debug.Print Hex(AscB(MidB(StrConv("あ", vbFromUnicode), 2, 1))) ' 結果 A0
End Sub
```

>###文字コードから文字を取得する
システムの既定のコードページ内の文字コードから文字を取得するには、Chr 関数を使用します。また、Unicode から文字を取得するには、ChrW 関数を使用します。
どちらのコードを扱っているのかによって、使い分ける必要があります。
  システムの既定のコード ページ内の文字コードから文字を取得         Chr 関数
  Unicode から文字を取得                                      ChrW 関数
例えば、"あ" という文字は、それぞれ次のように記述することで取得できます。

```vb
  Print Chr(&H82A0)
  Print ChrW(&H3042)
```

>***VBA では、Chr 関数は Unicode 文字を返します。つまり、必ず 2 バイト文字を返します。***
1 バイト文字を結合して 2 バイト文字を取得するような場合には、ChrB 関数を使用します。ここでも、システムの既定のコードページ内の文字コードと Unicode の違いを考慮する必要があります。例えば、"A" という文字は、

```vb
Sub test()
  Debug.Print ChrB(&H41) & ChrB(&H0) 'VBA
  Debug.Print StrConv(ChrB(&H41), vbUnicode) ' VBA
'以下も同じ結果が得られる
  Debug.Print ChrB$(&H41) & ChrB$(&H0) 'VBA
  Debug.Print StrConv(ChrB(&H41), vbUnicode) 'VBA
End Sub
```

>####ファイル I/O(編注：INPUTB関数は特殊過ぎるので無視した方がいいみたい)
ファイルからデータを読み込む関数として Input 関数、InputB 関数があります。***Input 関数は、文字をファイルから読み込むときに、Unicode 文字列に変換します。***それに対して、InputB 関数は、データをバイナリデータとしてみなして、Unicode への変換をおこないません。
[InputB関数 -OfficeTanaka](http://officetanaka.net/excel/vba/function/InputB.htm)
構文
InputB(num,[#]filenumber)
引数numには、ファイルから読み込む文字数を表す数式を指定します。
引数filenumberには、対象となるファイルのファイル番号を指定します。
[Input関数 Access2007以降 サポート]()
解説
シーケンシャル入力モードまたはバイナリモードで開いたファイルから、指定したバイト数のバイトデータを読み込み文字列型の値を返します。
Input関数でファイル内のデータを読み込むと、ファイルの読込位置は読み込んだデータの次に移動します。

>####バイト データ型の利用
VBA では、新しいデータ型としてバイト型 (Byte) が追加されています。バイナリデー タを操作する際に文字列変数を使うと入出力時に ANSI - Unicode 変換がおこなわれて バイナリ データが変更されてしまいます。バイナリ データを扱う場合はバイト型の変数を利用するようにしてください。

```vb
Public Function Test2()
Dim ByteData() As Byte
Dim bytePlus As Byte
Dim i As Long
'ByteData = InputB(10, #1) ' バイナリデータが格納される。 <エラーが発生する
ByteData = "文字列"                       ' Unicode 形式で格納される。
Debug.Print ByteData(3) ' 配列としてデータを操作可能。
Debug.Print "-In Case 文字列 Unicode------------------------------------------------"
For i = LBound(ByteData) To UBound(ByteData)
Debug.Print i + 1, "/", UBound(ByteData) + 1, ByteData(i), Chr(ByteData(i)), ChrB(ByteData(i))
Next i
For i = LBound(ByteData) To UBound(ByteData) - 1 Step 2
bytePlus = CByte(ByteData(i)) + CByte(ByteData(i + 1))
Debug.Print i + 1, "/", UBound(ByteData), Chr(bytePlus), ChrB(bytePlus)
Next i
Debug.Print "-In Case abcdef  Unicode------------------------------------------------"
ByteData = "abcdef"                       ' Unicode 形式で格納される。
For i = LBound(ByteData) To UBound(ByteData)
Debug.Print i + 1, "/", UBound(ByteData) + 1, ByteData(i), Chr(ByteData(i)), ChrB(ByteData(i))
Next i
For i = LBound(ByteData) To UBound(ByteData) - 1 Step 2
bytePlus = CByte(ByteData(i)) + CByte(ByteData(i + 1))
Debug.Print i + 1, "/", UBound(ByteData), Chr(bytePlus), ChrB(bytePlus)
Next i
Debug.Print "-In Case 文字列 Unicode Ansi------------------------------------------------"
ByteData = "文字列"                       ' Unicode 形式で格納される。
ByteData = StrConv("文字列", vbFromUnicode)  ' ANSI に変換
For i = LBound(ByteData) To UBound(ByteData)
Debug.Print i + 1, "/", UBound(ByteData) + 1, ByteData(i), Chr(ByteData(i)), ChrB(ByteData(i)), ChrW(ByteData(i))
Next i
'OverFlow
'For i = LBound(ByteData) To UBound(ByteData) - 1 Step 2
'bytePlus = CByte(ByteData(i)) + CByte(ByteData(i + 1))
'Debug.Print i + 1, "/", UBound(ByteData), Chr(bytePlus), ChrB(bytePlus)
'Next i
End Function
```

この結果は次のようになる。漢字というか全角はうまく戻らない。半角英数字はコードから戻る。
またUNICODEは文字数の２倍になるのがわかるのでVBAが確かにユニコードで処理していることがわかる

 91 
-In Case 文字列 Unicode------------------------------------------------
  1/6 135 ・
  2/6 101 e
  3/6  87 W
  4/6  91 [
  5/6  23 
  6/6  82 R
  1/5     ・
  3/5     ｲ
  5/5     i
-In Case abcdef  Unicode------------------------------------------------
 1         /     12          97   a 
 2         /     12          0
 3         /     12          98   b 
 4         /     12          0
 5         /     12          99   c 
 6         /     12          0
 7         /     12          100   d
 8         /     12          0
 9         /     12          101   e
 10        /    12           0
 11        /    12           102   f
 12        /    12           0      
 1         /     11         a
 3         /     11         b
 5         /     11         c
 7         /     11         d
 9         /     11         e
11        /    11          f
-In Case 文字列 Unicode Ansi------------------------------------------------
 1         /     6  149       ・     ?
 2         /     6  182       ｶ     ¶
 3         /     6  142       ・     ?
 4         /     6  154       ・     ?
 5         /     6  151       ・     ?
 6         /     6  241       ・     n
もう一つはオーバーフローするためコメントアウト