#Accessではユーザー定義関数作る必要がある

AccessのRound

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

###Excelでもある
これはさらにExcelでMougで指摘されていた。
文字列の長さを取得する(Len/LenB関数)-Moug

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で使用するとよい

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より前の版はそのままでよい
このため、ヴァージョン番号で切り替える
これだと古いバージョンに移植しても安全に同じ結果が得られる。

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共通版

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
Accessのバージョンを調べる： Access(アクセス) - feedsoft
変数の型を調べるには？－ローカルウィンドウ・TypeName関数-relief
Database.Version プロパティ (DAO)
Len関数と LenB関数：一日一見 -Access Club Wayback

####Unicode と ANSI
Access2000からUnicodeが採用されました。これは、全ての文字（全角、半角、漢字）を２バイトであらわします。逆に、ANSI形式は、全角文字と漢字は２バイト、半角文字は１バイトで表します。
####Access2000以降のLenB関数
よく、解説書で「LenB関数は、半角なら１バイト、全角なら２バイトで戻り値を返します」と記載されていますが、Access2000以降、この記述は誤りです。
しかし、Unicode形式の文字をANSI形式の文字を変換する関数が用意されているので、これを利用すると無事うまくいきます。

####この問題はVB6でも起きる
ｖｂ６でひとつ質問があります。- Yahoo知恵袋

####MACはさらに要注意
ExcelのVBA入門>文字列操作関数>ChrB関数 - VBのIE制御

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

####特に古いシステムを移植する場合はLenBをこの関数に変える必要がある
LenB関数、ExcelとACCESSで結果が異なる。- 情報システム部のインフラ・プログラム開発日誌

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

#公式は違うことを言っている
##[ACC2000] Unicode と文字列操作関数の留意点[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に変えている

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 関数
例えば、"あ" という文字は、それぞれ次のように記述することで取得できます。

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

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

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
構文
InputB(num,[#]filenumber)
引数numには、ファイルから読み込む文字数を表す数式を指定します。
引数filenumberには、対象となるファイルのファイル番号を指定します。
Input関数 Access2007以降サポート
解説
シーケンシャル入力モードまたはバイナリモードで開いたファイルから、指定したバイト数のバイトデータを読み込み文字列型の値を返します。
Input関数でファイル内のデータを読み込むと、ファイルの読込位置は読み込んだデータの次に移動します。

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

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
もう一つはオーバーフローするためコメントアウト