#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で使用するとよい
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より前の版はそのままでよい
このため、ヴァージョン番号で切り替える
これだと古いバージョンに移植しても安全に同じ結果が得られる。
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共通版
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 ClubWayback
####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
もう一つはオーバーフローするためコメントアウト