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

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