Excel VBA 文字列操作

Excel VBA のトップに戻る
Excel VBA 目次

Excel & VBAの文字列処理
 ・文字列操作関数
 ・検索した文字列の出現回数
 ・活用事例
 ・検索した文字列の出現回数と出現位置
 ・1バイト2バイト混在文字列から固定文字数を抽出
 ・全角文字数を数える
 ・.Value と .Text の違い
 ・Str と CStr の違い
 ・年月の連続文字データを作る
 ・DateValue 関数
 ・改行などの特殊文字(ASCII文字コード)
 ・文字列の中の引用符
 ・数字と文字(数字以外)の判定
 ・数字に見られるセルを、文字列にする
 ・全角文字の文字コード
 ・全角英数を半角に変換
 ・全角を半角に大文字を小文字に空白を無しに
 ・計算式のエラーを文字列にする関数
 ・UNICODE の文字コードに変換

ワイルドカードを使った検索
 ・Access データベースをシーケンシャルに更新する
 ・Access データベースをシーケンシャルに更新する(正規表現カウント)

索引

Excel & VBAの文字列処理

 文字列操作では、ASC、CONCATENATE、FIND、LEFT、LEN、LENB、MID、MIDB、RIGHT、TEXT、VALUE、UPPER(UCase) の関数をマスターしておくと、色々な場面で役立つでしょう。
 かく言う私も、うろ覚えなので、ここに備忘録として書いています。

 VBAの場合
 注:文字列を比較するときに、文字列の後ろに「空白」が入っていないか注意する必要があります。目で見たときには、同じ文字なのに、計算式で合致しないときは、この空白を疑ってみます。セルにカーソルを持っていったり、LENで比較したりします。
 もし空白がくっ付いている場合は、Trim関数を使って、文字部分を抽出して、比較します。
文字列操作の例1

 下の例は、インターネットからコピーした、曲名と歌手の文字列が一体になったセル・データから、それぞれを分離しています。
 項目中の、「(」や「/」を検索して、文字数をカウントして、該当部分を抽出しているのです。
文字列操作の例2


 検索を、「前方一致」で検索したい場合は、Like 演算子 を使います。

 例えば、"Watanabe" Like "a*"
とすると、前方一致なので、「マッチしない」、になります。


Replace 関数は、指定された文字列の一部を、別の文字列で指定された回数分で置換した文字列を返します。

 参考:Excelのセル操作の関数では、置換は SUBSTITUTE です。

 Replace(expression, find, replace[, start[, count[, compare]]])

 Replace 関数の構文は、次の名前付き引数から構成されます。
指定項目説明
expression必ず指定します。置換する文字列を含む文字列式 を指定します。
find必ず指定します。検索する文字列を指定します。
replace必ず指定します。置換する文字列を指定します。
start省略可能です。引数 expression 内の内部文字列の検索開始位置を指定します。省略すると、1 が使用されます。
count省略可能です。置換する文字列数を指定します。省略すると、既定値の -1 が使用され、すべての候補が置換されます。
compare省略可能です。文字列式を評価するときに使用する文字列比較のモードを表す数値を指定します。設定する値については、次の「設定値」を参照してください。

 引数 compare の 設定値 は次のとおりです。
定数説明
vbUseCompareOption-1Option Compare ステートメントの設定を使用して比較を行います。
(Option Compareステートメントの設定がない場合はバイナリモードになります)
vbBinaryCompare0バイナリ モードで比較を行います。
デフォルトの比較方法です。
(全角/半角、大文字/小文字、ひらがな/カタカナを区別します)
vbTextCompare1テキスト モードで比較を行います。
(全角/半角、大文字/小文字、ひらがな/カタカナを区別しません)
vbDatabaseCompare2Microsoft Access の場合のみ有効。
データベースに格納されている設定に基づいて比較を行います。
(Accessの設定の既定値はテキストモードです)

 Replace 関数の 戻り値 は次のとおりです。
条件Replace の戻り値
expression is zero-length長さ 0 の文字列 ("")
expression is Nullエラー
find is zero-lengthexpression のコピー
replace is zero-lengthfind がすべて削除された expression のコピー
start > Len(expression)長さ 0 の文字列 ("")
count is 0expression のコピー

 引数 start を指定した場合、Replace 関数は、指定された位置から引数 expression の文字列の末尾までを返します。
この種類の目次に戻る↑ 索引へ↓ トップページに戻る


検索した文字列の出現回数

文字列の検索だけではなく、対象文字列に検索文字列が合致した回数をカウントする関数です。

http://msdn.microsoft.com/ja-jp/library/cc326068.aspx

 次のユーザ定義関数は、特定の 1 文字または文字の集合が文字列の中に出現する回数を数えます。
このユーザ定義関数を呼び出すには、被検索文字列、検索する部分文字列、そして検索で大文字小文字を区別するかどうかを示す定数を渡します。
 ユーザ定義関数は InStr 関数を使って指定されたテキストを検索し、そのテキストが最初に出現する位置の値を返します。
 たとえば出現位置が文字列中の 3 番目の文字であれば、InStr 関数は 3 を返します。この値は、次の InStr 関数呼び出しの後も使用できるように、一時変数に格納されます。そして、ユーザ定義関数は出現回数を記録するカウンタ変数をインクリメントして、次の InStr 関数呼び出しの開始位置を設定します。新しい開始位置は、検索テキストが見つかった位置に検索文字列の長さを加えた値です。このようにして開始位置を設定することにより、2 文字以上の長さのテキストを検索するときに同じ部分文字列が 2 回検索されることがなくなります。

 正規表現オブジェクトを使って出現回数を検索する関数も参照下さい。
 検索した文字列パターン数(または文字数)をカウントも参照下さい。



Function 出現回数(検索される文字列 As String, _
                          検索する文字列 As String, _
                          Optional lngCompare _
                          As VbCompareMethod) As Long

   ' 特定の文字または文字列が見つかった回数を数えます。
   ' lngCompare 引数を省略すると、バイナリで比較を行ないます。

   Dim lngPos       As Long
   Dim lngTemp      As Long
   Dim lngCount     As Long

   ' 開始位置を指定してください。
   ' 最初のループでは開始位置を必要としませんが、以降のパスで必要になります。
   lngPos = 1
   ' ループを少なくとも一度実行します。
   Do
      '「検索する文字列」が、最初に見つかった位置を保存します。
      lngPos = InStr(lngPos, 検索される文字列, 検索する文字列, lngCompare)
      ' 位置を一時的な変数に保管します。
      lngTemp = lngPos
      '「検索する文字列」があるかどうかチェックします。
      If lngPos > 0 Then
         ' 増加カウンター変数。
         lngCount = lngCount + 1
         ' 新しい検索開始位置を設定します。
         lngPos = lngPos + Len(検索する文字列)
      End If
   ' 最後まで見つけるためにループします。
   Loop Until lngPos = 0
   ' 見つかった発生回数を返します。
   出現回数 = lngCount
End Function
 この関数をたとえば次のように Visual Basic Environment(VBE)の[イミディエイト]ウィンドウから呼び出すと、3 を返します。
? 出現回数("This is a test", "t", vbTextCompare)
この種類の目次に戻る↑ 索引へ↓ トップページに戻る

活用事例

 例えば、パソコンの型番が、LX50H、LX50J、LX50K・・とあるときのように、3文字目以降に数字が何桁か並び、その後に経歴の英字が来る場合、LX50系として、英字部分を削除してまとめたいことが有ります。数字部分の桁数は、モデルによって異なる場合でも、以下のようにすれば、かんたんに削除できます。

Option Explicit

Sub 英字削除()
Dim i As Integer
Dim j As Integer

ThisWorkbook.Worksheets("Sheet1").Activate
For i = 1 To Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row - 1
                        'A2以下のセルにデータがあるものを対象にします。
                        'Cells(行,列)の列を1としてA列の最終行までの行数をカウントしますが、
                        '下でA2を開始行にしているため、1引いています。
                        'Excelの最終行を取得の項を参照下さい。
    If Range("A2").Cells(i, 1).Value <> "" Then
                        'A2を基準に、Cells(i,1)で、行を下にずらしながら作業します。
                        'この場合、Cells(1,1)は、A2のセルです。
                        '3文字め以降、最後の文字までチェックします。
        For j = 3 To Len(Range("A2").Cells(i, 1).Value)
            If Mid(Range("A2").Cells(i, 1).Value, j, 1) > "9" Then Exit For
                         '英字なら抜けます。これで英字部分の開始桁数が分かります。
        Next j
        Range("B2").Cells(i, 1).Value = Left(Range("A2").Cells(i, 1).Value, j - 1)
    End If
Next i

End Sub

 解説:
 文字の大小は、ASCIIコードの順になります。例えば、モデルの型番の数字の後ろに英字だけでなく「-(ハイホン)」もある場合は、
 or Mid(Range("A2").Cells(i, 1).Value, j, 1) < "0"
を追加します。

 ThisWorkbook プロパティ は、現在実行中のマクロ コードが記述されているブック (Workbook オブジェクト) を返します。このプロパティは、マクロ コードが記述されているブックにアクセスするために使います。
 私は、マクロが他のファイルに影響しないよう、安全のために、このプロパティを付けるようにしています。
 アドインの内部からアドイン ブック自体にアクセスするには、ThisWorkbook プロパティが唯一の方法です。ActiveWorkbook プロパティを使うと、アドイン ブックではなく、そのアドインを呼び出したブックが返されます。Workbooks プロパティを使うと、ほとんどの場合でアドインを作成したときにブック名が変更されてしまうので失敗します。ThisWorkbook プロパティを使うと、実行されているコードのあるブックが常に返されるので、アドイン ブック自体を返すことができます。

 Activate メソッド  Activate メソッドを Worksheet オブジェクトに指定した場合、指定されたシートをアクティブにします。シート見出しをクリックする動作と同じです。
 VBAで、表とグラフを連続して作る場合など、Activate を使って、対象を切り替えます。これを忘れると、書き出しできません。

 Activate メソッドを Range オブジェクトに指定した場合、1 つのセルをアクティブにします。選択範囲の中の単一セルをアクティブにするときに使います。セル範囲を選択するときは、Select メソッドを使います。
 注意:
 セルを選択したり、Range を取得する前に、そのセルが所属するワークシートをアクティブにしておく必要があります。


Range オブジェクト は、セル、行、列、1 つ以上のセル範囲を含む選択範囲、または 3-D 範囲を表します。
 1 つのセルまたはセル範囲を表す Range オブジェクトを取得するには、Range(arg) プロパティを使用します。引数 arg には、範囲を指定します。
次の使用例は、セル A1 の値をセル A5 に入力します。
Worksheets("Sheet1").Range("A5").Value = _
    Worksheets("Sheet1").Range("A1").Value

列名(列文字)は文字を使って、行番号に変数を使う場合は、次のように書きます。
   '前回取得の銘柄データを削除
   Range("A2:G" & 前回最終行).ClearContents

列名(列文字)に変数を使って、行番号は数字を使う場合は、次のように書きます。
Range(列文字 & "1")

【別解】下のように、列名を列番号に変換すると、Cells プロパティだけを使って、Range オブジェクトを指定できます。
 この方法は、行列を入れ替えたいときには、有効ですね。
'★列名を列番号に変換★
Function 列番号(列名 As String) As Integer

   If Len(列名) = 1 Then
      列番号 = Asc(UCase(列名)) - 64
      If 列番号 < 1 Or 列番号 > 26 Then
         MsgBox "列名が不正です。列名:" & 列名 & " 列番号:" & 列番号
            End
      End If
        
   ElseIf Len(列名) = 2 Then
      列番号 = Asc(UCase(Right(列名, 1))) - 64 + (Asc(UCase(Left(列名, 1))) - 64) * 26
      If 列番号 < 26 Or 列番号 > 256 Then
         'この関数は、Excel 2003までの行と列の最大数、行数 65,536 、列数 256(列名 IV) を前提。
         'http://bicycle.life.coocan.jp/takamints/index.php/techtips/xlsColNumMap
         'Excel 2007〜では、ワークシートのサイズは、 1,048,576 行 x 16,384 列 に拡大した。

         MsgBox "列オーバ。マクロを修正して下さい。列名:" & 列名 & " 列番号:" & 列番号
         End
      End If
   Else
      MsgBox "列オーバ。マクロを修正して下さい。列名:" & 列名
   End If

End Function

 解説:

 次の使用例は、セル範囲 A1:H8 の各セルに、数式を使用して乱数を設定します。
オブジェクト修飾子 (ピリオドの左側に記述するオブジェクト) を指定せずに使用した場合、Range プロパティは作業中のワークシートのセル範囲を返します。作業中のワークシートではない場合、このメソッドは失敗します。
オブジェクト ID を指定せずに Range プロパティを使用するときは、その前に Activate メソッドを使用して、ワークシートをアクティブにします。
Worksheets("Sheet1").Activate
Range("A1:H8").Formula = "=Rand()"    'Range is on the active sheet

 次の使用例は、Criteria という名前のセル範囲の内容を消去します。
Worksheets(1).Range("Criteria").ClearContents
メモ:
セル範囲の参照を引数に指定する場合、参照は A1 形式で指定します。R1C1 形式は使用できません。

 Range プロパティは、セルまたはセル範囲を表す Range オブジェクトを返します。

 expression.Range(Cell1, Cell2)
 expression 必ず指定します。対象となるオブジェクトへの参照を返すオブジェクト式を指定します。
 Range の範囲の指定方法は、下記を参照下さい。
  CurrentRegion
  UsedRange


 Mid 関数は、バリアント型 (内部処理形式 String の Variant) の値を返します。文字列から指定した文字数分の文字列を返します。

 構文:Mid(string, start[, length])

 string 必ず指定します。
文字列を取り出す、元の文字列式を指定します。名前付き引数 string に Null 値が含まれている場合は、Null 値を返します。

 start 必ず指定します。長整数型 (Long) の値を指定します。
名前付き引数 string の先頭の位置を 1 として、どの位置から文字列を取り出すかを先頭からの文字数で指定します。
start が string の文字数を超える場合、Mid 関数は長さ 0 の文字列 ("") を返します。

 length 省略可能です。バリアント型 (内部処理形式 Long) の値を指定します。
取り出す文字数を指定します。
名前付き引数 length を省略した場合、または文字列内に length より短い文字数しかない場合には、start から後のすべての文字が返されます

 名前付き引数 string の文字数を決定するときに Len 関数を使用します。
 文字列をバイト データとして扱う場合は、MidB 関数を使用します。MidB 関数はバイト数で文字列を取り出します。MidB 関数には、文字数でなく、バイト数を指定します。

 文字列を、Shift_JISベースのバイト データとして扱う場合は、ユーザ定義関数 MidMbcs 関数を使用します。

 Mid 関数とは別に、Mid 命令文が有ります。Mid 命令文を使うと、文字列の対象桁数を指定して、文字列置換できます。
 Mid 命令文:
http://makoto-watanabe.main.jp/freebasic/PgMidstatement.html

この種類の目次に戻る↑ 索引へ↓ トップページに戻る

検索した文字列の出現回数と出現位置

 ネットでググッたら、正規表現を使って、検索文字の出現回数と、出現位置(文字目)を調べる方法を公開いただいているものを見つけました。
 ここで、それを無断転載させていただきます。


Sub 出現位置テスト()

   Dim 文字列 As String
   Dim パターン As String
   Dim 回数 As Integer

   文字列 = "瀬を早み" & vbTab & "岩にせかるる滝川の" & vbTab & "われても末に" & vbTab & "あはむとぞ思ふ"
   パターン = "\t"

   MsgBox "文字列 = " & 文字列 & vbNewLine _
      & "パターン = " & パターン & vbNewLine _
      & "出現回数= " & 出現回数(文字列, パターン)

   For 回数 = 1 To 出現回数(文字列, パターン)
      MsgBox "回数= " & 回数 & " 文字目= " & 出現位置(文字列, パターン, 回数)
   Next 回数


   文字列 = "お米の国と呼ばれる日本。お米のお国処は、秋田"
   パターン = "お米.*?国" '最短一致

   MsgBox "文字列 = " & 文字列 & vbNewLine _
      & "パターン = " & パターン & vbNewLine _
      & "出現回数= " & 出現回数(文字列, パターン)

   For 回数 = 1 To 出現回数(文字列, パターン)
      MsgBox "回数= " & 回数 & " 文字目= " & 出現位置(文字列, パターン, 回数)
   Next 回数


   文字列 = "H:\Excelマクロ\テスト"
   パターン = "\\" '一つ目の \ は、エスケープ文字

   MsgBox "文字列 = " & 文字列 & vbNewLine _
      & "パターン = " & パターン & vbNewLine _
      & "出現回数= " & 出現回数(文字列, パターン)

   For 回数 = 1 To 出現回数(文字列, パターン)
      MsgBox "回数= " & 回数 & " 文字目= " & 出現位置(文字列, パターン, 回数)
   Next 回数

End Sub


Function 出現位置(検索される文字列 As String, 検索する文字列パターン As String, 出現目 As Integer) As Integer
'ある文字列で2回目3回目に出現するtabの位置を得るには?
'http://okwave.jp/qa/q2325128.html

   Dim 正規表現オブジェクト As RegExp
   Dim Matches As Object

   Set 正規表現オブジェクト = New RegExp

   正規表現オブジェクト.Pattern = 検索する文字列パターン
   正規表現オブジェクト.Global = True

   Set Matches = 正規表現オブジェクト.Execute(検索される文字列)
   '0 オリジンなので 1引く

   出現位置 = Matches(出現目 - 1).FirstIndex + 1

   Set Matches = Nothing
   Set 正規表現オブジェクト = Nothing

End Function


Function 出現回数(検索される文字列 As String, 検索する文字列パターン As String) As Integer
'Excelで自然言語処理
'http://pub.ne.jp/arihagne/

'VBAで正規表現パターンにマッチした数を返す関数
'http://pub.ne.jp/arihagne/?entry_id=2114432

   Dim Matches As Object

   With CreateObject("VBScript.RegExp")
       .Pattern = 検索する文字列パターン
       .IgnoreCase = True
       .Global = True
       Set Matches = .Execute(検索される文字列)
       出現回数 = Matches.Count
   End With

   Set Matches = Nothing

End Function

この種類の目次に戻る↑ 索引へ↓ トップページに戻る

1バイト2バイト混在文字列から固定文字数を抽出

 文字列操作の関数の結果は、Excelシートの場合と VBA の場合は違っています。
 ワークシート関数の LenB や MidB は、全角=2バイト、半角=1バイトという計算をしてくれます。しかしVBAではそうはならないのです!

 同じ名前の関数でも、VBA関数とワークシート関数では、結果の値が違うのですね。
http://support.microsoft.com/kb/408879/ja
http://support.microsoft.com/kb/404928/ja
Excel関数LEN("abあい")=4 LENB("abあい")=6 
VBALen("abあい")=4 LenB("abあい")=8 LenMbcs("abあい")=6 

全角文字数を数える

2バイト文字を2文字と数える  そこで、全角、半角の混在文字列から、固定桁数の部分を抽出するのに困っていたら、見つけました。
 Access Club のフォーラムでの、hatena さんの投稿です。
http://www.accessclub.jp/bbs5/0015/vba4190.html
 下の「全角文字数」の式で、1バイト2バイト混在文字列から、「2バイト文字」の数を計算してくれるものです。

 文字抽出@ では、この「全角文字数」を使って、Mid関数で取り出す位置と文字数を調節しています。
 文字抽出A では、VBAのヘルプに有る「ユーザ定義関数」を使っています。


Sub 文字抽出@()
    Dim 全角文字数 As Integer
    Dim i As Integer
    Dim 文字列

    For i = 1 To 2
        文字列 = Mid(Range("A1").Cells(i, 1), 3, 5)
        
        全角文字数 = LenB(StrConv(文字列, vbFromUnicode)) - Len(文字列)

        Range("B1").Cells(i, 1).Value = Mid(Range("A1").Cells(i, 1), 3, 5 - 全角文字数)
        Range("C1").Cells(i, 1).Value = Mid(Range("A1").Cells(i, 1), 8 - 全角文字数, 2)
    Next i
End Sub

Sub 文字抽出A()
    Dim i As Integer

    For i = 1 To 2
        Range("B1").Cells(i, 1).Value = MidMbcs(Range("A1").Cells(i, 1), 3, 5)
        Range("C1").Cells(i, 1).Value = MidMbcs(Range("A1").Cells(i, 1), 8, 2)
    Next i
End Sub

Function MidMbcs(ByVal str As String, start, length)
    MidMbcs = StrConv(MidB(StrConv(str, vbFromUnicode), start, length), vbUnicode)
End Function

 注:目的が、半角英数文字以外の文字の存在をチェックしたい場合は、正規表現を使って、下記のようにします。
   Dim 正規表現オブジェクト As RegExp

   Set 正規表現オブジェクト = New RegExp
   正規表現オブジェクト.Pattern = "[^\x01-\x7E]" '2バイト文字

   If 正規表現オブジェクト.Test(文字列) = True Then
      MsgBox "半角英数以外の文字が含まれています!"
   End If
上の実装例
Sub 全角文字の存在チェック()

   Dim 正規表現オブジェクト As RegExp
   Dim 行数 As Integer
   Dim 文字列 As String

   Set 正規表現オブジェクト = New RegExp
   正規表現オブジェクト.Pattern = "[^\x01-\x7E]" '2バイト文字

   ThisWorkbook.Worksheets("Sheet2").Activate

   For 行数 = 1 To Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

      文字列 = Range("A1").Cells(行数, 1).Value

      If 正規表現オブジェクト.Test(文字列) = True Then
         Range("C1").Cells(行数, 1).Value = "半角英数以外の文字が含まれています!"
      End If

   Next 行数

End Sub

 解説:
 Len 関数
指定した文字列の文字数を表す長整数型 (Long) の値を返します。
全角/半角を区別せず、ともに1文字としてカウントします。

 LenB 関数
指定した文字列のバイト数を返します。
MS-Office の 97 以降のバージョンは、文字コードとして Unicode(UTF-16) が採用されています。
UTF-16 では、どの文字も「2バイト」で表現されます。
従って、全角/半角を区別せず、ともに2バイトとしてカウントしてしまうわけです。
(Shift_JISは、半角が1バイト、全角が2バイトです。)


 ユーザー定義関数 LenMbcs と MidMbcs
 VBAのヘルプで紹介されています。
 LenB 関数とユーザー定義関数 LenMbcs を使用して、指定した文字列の、Shift_JISベースのバイト数を返します。
 MidB 関数とユーザー定義関数 MidMbcs を使用して、文字列内の、Shift_JISベースのバイト数で指定した位置の文字を返します。

Function LenMbcs (ByVal str as String)
    LenMbcs = LenB(StrConv(str, vbFromUnicode))
End Function 

Function MidMbcs(ByVal str as String, start, length)
    MidMbcs = StrConv(MidB(StrConv(str, vbFromUnicode), start, length), vbUnicode)
End Function

 上記の全角文字数の計算式は、これの応用です。


 StrConv 関数 は、引数で指定した形式に文字列を変換して、バリアント型 (内部処理形式 String の Variant) で返します。

 構文: StrConv(string, conversion, LCID)

 StrConv 関数の構文は、次の名前付き引数から構成されます。
指定項目 内容
string 必ず指定します。変換する文字列式を指定します。
conversion 必ず指定します。整数型 (Integer) の値を指定します。実行する変換の種類の値の合計を指定します。
LCID 省略可能です。システムとは異なる国別情報識別子 (LCID) を指定できます。既定値はシステムが使用する LCID です。

 名前付き引数 conversion には、次のいずれかの定数または値を指定します。
定数 内容
vbUpperCase 1 文字列を大文字に変換します。
vbLowerCase 2 文字列を小文字に変換します。
vbProperCase 3 文字列の各単語の先頭の文字を大文字に変換します。
vbWide* 4* 文字列内の半角文字 (1 バイト) を全角文字 (2 バイト) に変換します。
vbNarrow* 8* 文字列内の全角文字 (2 バイト) を半角文字 (1 バイト) に変換します。
vbKatakana** 16** 文字列内のひらがなをカタカナに変換します。
vbHiragana** 32 文字列内のカタカナをひらがなに変換します。
vbUnicode 64 システムの既定のコード ページを使って文字列を Unicode に変換します。
vbFromUnicode 128 文字列を Unicode からシステムの既定のコード ページに変換します。

 *  国別情報の設定が中国、韓国、および日本の場合に適用されます。
 ** 国別情報の設定が日本の場合のみ有効です。

 大文字/小文字を正しく区別する単語セパレータは、Null 値 (Chr$(0))、水平タブ (Chr$(9))、ライン フィード (Chr$(10))、垂直タブ (Chr$(11))、フォーム フィード (Chr$(12))、キャリッジ リターン (Chr$(13))、およびスペース (SBCS の場合) (Chr$(32)) です。DBCS のスペースの実際の値は、国によって異なります。

 ANSI 形式のバイト型配列を文字列に変換する場合は、StrConv 関数を使用してください。Unicode 形式の配列を変換する場合は、代入式を使用してください。

 次の例は、StrConv 関数を使って、Unicode 文字列をANSI 文字列に変換します。

Dim i As Long
Dim x() As Byte
x = StrConv("ABCDEFG", vbFromUnicode)    ' 文字列を変換します。
For i = 0 To UBound(x)
    Debug.Print x(i)
Next

 Excelのワークシート関数は、UNICODE でも、B付きの関数で、全角/半角を識別して結果を表示します。ところが、VBA の文字列関数は UNICODE→Shift_JIS に自動で対応してくれないのです。
 このため、上の例では、StrConv 関数を使って、文字列を、いったん Shift_JISに変換して、B付き文字列関数を適用した上で、また UNICODE に戻しているわけです。

 vbFromUnicode
文字列を Unicode からシステムの既定のコード ページ(日本語の場合は、ANSI932(シフトJIS))に変換します。

 vbUnicode
 システムの既定のコード ページを使って文字列を Unicode に変換します。

この種類の目次に戻る↑ 索引へ↓ トップページに戻る

.Value と .Text の違い

.Value と .Text の表示の違い  Range オブジェクトの Value プロパティは、指定されたセル範囲の値を設定します。値の取得および設定が可能です。

 Text プロパティは、Range オブジェクトの場合は、値の取得のみ可能です。
 Value が数値だけを表示するのに対して、Text は、書式設定まで含めて出力します。

 下の例は、数値が入力されているセルで、表示形式が指定された場合の、Text プロパティと Value プロパティの違いを示しています。


Option Explicit
Sub 表示の違い()
    MsgBox ".value= " & Range("A1").Value _
        & Chr(13) & Chr(10) _
        & ".text  = " & Range("A1").Text
End Sub

この種類の目次に戻る↑ 索引へ↓ トップページに戻る

Str と CStr の違い

 数値を文字列に変換する関数に、Str と CStr が有ります。この違いは、先頭に符号を表示する空白の有無です。
 Str は、符号の1桁を常に確保します。数値が正の場合は、Str 関数の戻り値の先頭には、プラス記号を意味するスペースが挿入されます。
 CStr の場合は、プラスのときにスペースは挿入されません。このため、通常は「CStr」の方を使います。
VBAのStrとCStrの表示の違い

Option Explicit
Sub 文字変換()
Dim i As Integer
    For i = 1 To 2
        Range("B1").Cells(i, 1).Value _
        = Chr(34) & Str(Range("A1").Cells(i, 1).Value) & Chr(34)
        Range("C1").Cells(i, 1).Value _
        = Chr(34) & CStr(Range("A1").Cells(i, 1).Value) & Chr(34)
    Next i
End Sub


 勝間和代の「脳力UP」を読んでいたら、下記の問題がありました。

 ひらがなに、0〜9 のいずれかの数字を入れて、計算式が成り立つようにして下さい。
かち×かちふかかち

 頭で考えるのが面倒なので、下記のマクロを書いてみました。
 どうぞ、もっとスマートなマクロに挑戦してみて下さい。「脳力UP」になる?でしょう。

 勝間和代は、NHK 教育テレビで、「怒ること、ねたむこと、グチること」の三つの毒(三毒)の話しをしていました。
 「妬む・怒る・愚痴る」という行動を取ると、責任が相手に転嫁されて、考えない、行動しないという言い訳になり、現実逃避になります。
 それが、「意識的に三毒を追放しよう」とすると、妬む暇があったら相手に追いつく努力をする、怒る暇があったら相手と問題解決を図る、愚痴る暇があったら現実を正しく見つめるようにするなど、自身に向かった行動が加速され、経験や持っている能力を最大限に発揮できるようになるわけです。
 現実を受け止め、前向きに行動していくことが、「脳力UP」になるのでしょう。

 また、仕事や社会の人間関係で、過剰適応することは、自己の思考・判断を停止し、行動の責任を転嫁することで、最終的に自己喪失(私が私でない状態)になります。自己責任で考えながら行動する過程で、優先順位を常に意識する思考パターンと判断力が身につきます。
 仕事の内容では、上司と充分にコミュニケーションして、現状認識を共有するように努めます。そうすると、やるべきこと、止めるべきことは、おのずと明確になると、私は思います。優先順位を客観的に評価して、主体性をもって「断る」ことは、組織の生産性を向上するのです。


Option Explicit

Dim か As Integer
Dim ち As Integer
Dim ふ As Integer
Dim ふかかち As Double

Sub 価値かける価値は付加価値()

   For ち = 0 To 9
      For か = 1 To 9
         ふかかち = (か * 10 + ち) ^ 2
         If Len(CStr(ふかかち)) <> 4 Then GoTo スキップ
         
         If Right(CStr(ふかかち), 1) <> CStr(ち) _
            Or Mid(CStr(ふかかち), 2, 1) <> CStr(か) _
            Or Mid(CStr(ふかかち), 3, 1) <> CStr(か) _
            Then GoTo スキップ
                        
         GoTo 解答
スキップ:
      Next か
   Next ち
   
解答:
   If Val(Left(CStr(ふかかち), 1)) * 1000 + か * 100 + か * 10 + ち = ふかかち Then
   
      MsgBox ("か= " & か & " 、ち= " & ち & " 、ふかかち= " & ふかかち)
   Else
      MsgBox ("解答が見つかりませんでした。")
   End If

End Sub

この種類の目次に戻る↑ 索引へ↓ トップページに戻る

年月の連続文字データを作る

 上で紹介した Text プロパティは、年月の連続文字データを作りたいときに、利用できます。
 下のマクロは、当月から12カ月の年月の文字列を、0610 0611 0612 0701 0702 0703のような形式で作るものです。

Sub 年月連続データ()
    Dim 年月(12) As String
    Dim i As Integer
        
    Range("A1").NumberFormat = "yymm"
    Range("A1").Value = Date
    
    Range("A1").AutoFill _
    Destination:=Range("A1").Resize(12, 1), Type:=xlFillMonths
    
    For i = 1 To 12
        年月(i) = Range("A1").Cells(i, 1).Text
    Next i

    '以下は、できたデータを確認するだけ。
    Range("C1").Resize(1, 13).Value = 年月
    
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A1").Select

End Sub


 NumberFormat プロパティは、 CellFormat オブジェクト、または Range オブジェクトに指定して、オブジェクトに含まれる文字列や数値の表示形式を示す文字列を設定します。指定されたすべてのセル範囲の表示形式が同じではないとき、Null 値を返します。値の取得および設定が可能です。バリアント型 (Variant) の値を使用します。

 下の例は、A 列の表示形式を、「文字列」に設定します。

   Columns("A:A").NumberFormat = "@"        '文字列


 Date 関数は、現在のシステムの日付を含むバリアント型 (内部処理形式 Date の Variant) の値を返します。


この種類の目次に戻る↑ 索引へ↓ トップページに戻る

DateValue 関数

 文字列の「日付」を、「日付けデータ」に変換する場合は、DateValue 関数を使います。
 解説:
DateValue 関数は、日付を表すバリアント型 (内部処理形式 Date の Variant) の値を返します。
 構文は、DateValue(date)で、引数 date には、通常、100 年 1 月 1 日から 9999 年 12 月 31 日までの範囲の日付を表す文字列式を指定します。また、この範囲内の日付や時刻、または日付と時刻の両方を表す任意の式を指定することもできます。
 引数 date が有効な日付の区切り文字によって区切られた数字だけが含まれる場合、DateValue 関数はコントロール パネルで設定した短い日付の書式に従って、年、月、および日の順序を認識します。また DateValue 関数は、日付に月の名前がそのまま記述してあっても、また省略した形式であっても認識します。たとえば、"1991/12/30" や "91/12/30" だけでなく、"平成 3 年 12 月 30 日"、"H3 - 12 - 30" などの形式で指定することもできます。
 引数 date を指定するときに年を省略すると、DateValue 関数はシステムの日付を現在の年として使用します。
 引数 date に時刻の値を含むとき、DateValue 関数はその時刻を戻り値として返しません。ただし、引数 date に不正な時刻 (たとえば "89:89") を指定したときには、エラーが発生します。


この種類の目次に戻る↑ 索引へ↓ トップページに戻る

改行などの特殊文字

ASCIIの文字コード表  Chr(34)は、ASCIIの文字コード34で、「"」を表示します。

 Chr(13)は、Carriage Return(昔のタイプライターの紙の台座を左に戻す)、Chr(10)は Line Feed(紙を一行送る) で、組合せて、Windows 標準の「改行」を意味します。
 実際には、Chr(13) か Chr(10) の一方だけでも、「改行」してくれます。

 ASCII文字コードの代わりに、VBA のタイプ ライブラリで定義されている定数(下の表参照)を使うこともできます。

 逆に、文字列の先頭の文字の文字コードを知りたいときには、Asc 関数を使います。
Asc 関数 は、整数型 (Integer) の値を返します。

 次の定数は Visual Basic for Applications のタイプ ライブラリで定義されており、実際の値の代わりに、コード内のどの部分でも使うことができます。
定数 内容
vbCrLf Chr(13) + Chr(10) キャリッジ リターンとライン フィードの組み合わせ
vbCr Chr(13) キャリッジ リターン文字
vbLf Chr(10) ライン フィード文字
←図形テキスト・ボックス内の改行や、
シートのセル内改行は、はこれです

UNIX系OSの改行も、これです
vbNullChar Chr(0) 値 0 を持つ文字
vbNewLine Chr(13) + Chr(10)
または Chr(13)
プラット フォームで指定した改行文字。
現在のプラット フォームで適切ないずれかを使用します。
Windows の改行はこれです。
vbNullString 値 0 を持つ文字列 長さ 0 の文字列 ("") とは異なります。
外部プロシージャを呼び出す場合に使用します。
vbTab Chr(9) タブ文字
vbBack Chr(8) バックスペース文字
vbFormFeed Chr(12) Microsoft Windows または Macintosh では使用できません。
vbVerticalTab Chr(11) Microsoft Windows または Macintosh では使用できません。


この種類の目次に戻る↑ 索引へ↓ トップページに戻る

文字列の中の引用符

 文字列の中に、引用符(" ダブル・クオーテーション・マーク double-quotation mark)を含めたい場合は、引用符を重ねます。
"→""

文字列の中の引用符
Sub 文字列の中の引用符テスト()
   MsgBox "彼は""男の中の男""だ"
End Sub

 
この種類の目次に戻る↑ 索引へ↓ トップページに戻る

数字と文字(数字以外)の判定

 IsNumeric 関数 は、式が数値として評価できるかどうかを調べ、結果をブール型 (Boolean) で返します。
ちなみに、Excel の関数で、数値として評価できるか調べるときには下記を使います。
=ISERROR(A1*1)
でTRUEなら文字列、FALSEなら数値(空白を含む)です。
http://okwave.jp/qa/q3364483.html

 変数の値が、数値として評価できないのに、演算をしようとすると、エラーになります。こんな事か想定される場合には、この関数を使って、判定・対応するコードにします。

 注:IsNumeric 関数は「空白のセル = 0 = 数値」と判定します。
このため、「空白のセル = 数値ではない」と判定したい場合は、Excel の関数 IsNumber を使います。
WorksheetFunction.IsNumber

 しかし、これだと、" 1 " を数値として評価できなくなります。
 ヴィーバ VeaBa! Excel VBA Tips によると、IsNumeric と CStr を組合せるのがベストのようです。
http://veaba.keemoosoft.com/2012/12/322/
 IsNumeric(CStr(.Value))


 次の例は、IsNumeric 関数を使って、変数が数値として評価できるかどうかを調べます。

Dim MyVar, MyCheck
MyVar = "53"                        ' 値を代入します。
MyCheck = IsNumeric(MyVar)        ' True を返します。

MyVar = "459.95"                    ' 値を代入します。
MyCheck = IsNumeric(MyVar)        ' True を返します。

MyVar = "45 Help"                    ' 値を代入します。
MyCheck = IsNumeric(MyVar)        ' False を返します。

 次の例は、数値と解釈できる値が入力されるまで、入力を繰り返し求めます。

Dim 変数

Sub 数値判定()
    Do
        変数 = InputBox("数値を入力して下さい。")
    Loop Until IsNumeric(変数)
    
    MsgBox "入力された値の平方根は " & Sqr(変数) & " です。"
End Sub

この種類の目次に戻る↑ 索引へ↓ トップページに戻る


数字に見られるセルを、文字列にする

 品目番号など、数字だけの並びでも、「文字列」として扱いたいのに、Excel は勝手に数字だと判断しています。
 例えば、品目番号に、数字だけの並びの形式と、途中に英記号が入った形式が混在する場合、この品目番号のリストを並び替え(ソート)すると、期待した並び順にはなりません。
 こんな時には、前もって、数字だけのセルを、明示的に「文字列」と認識させる必要があります。
 下の、二つの方法があります。

   'A 列を、文字列属性にする
   Columns("A:A").NumberFormat = "@"        '文字列
   Range("A1").Select
   
   最終行 = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
   '数字に認識されるものを、文字に変更する。
'   For 処理行 = 2 To 最終行
'      品目番号 = Range("A1").Cells(処理行, 1).Value
'      If IsNumeric(品目番号) = True Then                      '数字と見られるセルについて
'         Range("A1").Cells(処理行, 1).Value = "'" & 品目番号  '@データの頭にアポストロフィを付ける
'      End If
'   Next 処理行
   
   For 処理行 = 2 To 最終行
      品目番号 = Range("A1").Cells(処理行, 1).Value
      If IsNumeric(品目番号) = True Then                       '数字と見られるセルについて
         Range("A1").Cells(処理行, 1).Value = CStr(品目番号)   'A文字属性に変更する
      End If
   Next 処理行

この種類の目次に戻る↑ 索引へ↓ トップページに戻る


全角文字の文字コード


Asc 関数は、整数型 (Integer) の値を返します。指定した文字列内にある先頭の文字の文字コードを返す変換関数です。
 逆に、指定した文字コードに対応する文字を知りたい時は、Chr 関数を使います。

 ユーザ定義関数 列番号 では、半角英大文字の Ascii文字コードから 64 減数して、列名を列番号(列のインデックス)に換算しています。


 全角文字の文字コード区分
文字区分ASC
コード範囲
漢字(その他)-949-1444
漢字(第二水準)-5468-26415
漢字(第一水準)-26510-30561
カタカナ-31850-31936
ひらがな-32015-32097
句読点-32446-32447

全角の英数字文字コード
-32177
-32168
-32160
-32135
-32127
-32102
 日本語は、文字コードセットとして、DBCS(Double Byte Character Set,ディービーシーエス:1文字を、2バイトで表現する文字コード体系)を使います。
 一般に、1文字を、2バイト以上の複数バイトで表現する文字コード体系を、MBCS(Multiple Byte Character Set)言い、DBCSはその内の一つです。
 アルファベットと数字、基本記号だけで構成されるASCIIは、1文字を1バイトで表現できるので、SBCS(Single Byte Character Set)と言います。

正規表現で検索 の項も参照下さい。

この種類の目次に戻る↑ 索引へ↓ トップページに戻る


全角英数を半角に変換

 覚書 - My Knowledge Base - FC2ブログ版の、下記サイトに、半角カタカナを全角カタカナに、全角英数字を半角に変換するユーザー定義関数サンプルを掲示いただいているので、転載させていただきます。
http://billyboy.blog81.fc2.com/blog-entry-84.html


Function 半角(文字列 As String)
   'http://billyboy.blog81.fc2.com/blog-entry-84.html
   Dim i As Long
   Dim 暫定文字列 As String
   Dim 一文字 As String
     
   For i = 1 To Len(文字列)
       一文字 = Asc(Mid(文字列, i, 1))
       If 一文字 <= -32102 And 一文字 >= -32177 Then '全角英数字を半角
           一文字 = StrConv(Chr(一文字), vbNarrow)
   '    ElseIf 一文字 <= 221 And 一文字 >= 177 Then '半角カタカナを全角カタカナ
   '        一文字 = StrConv(Chr(一文字), vbWide)
       Else
           一文字 = Chr(一文字)
       End If
             
       暫定文字列 = 暫定文字列 & 一文字
         
   Next i
     
   半角 = 暫定文字列
  
End Function

この種類の目次に戻る↑ 索引へ↓ トップページに戻る


全角を半角に大文字を小文字に空白を無しに

 Excel のセルの文字列を比較するときに、セル内改行や、単語間の空白の数や、大文字小文字が違うと、照合できません。
 半角小文字空白無しに統一して、セルの文字列合致をチェックできるようにするために作った、ユーザ定義関数です。
Function 半角小文字空白無し(文字列 As String) As String

   半角小文字空白無し = Replace(文字列, vbLf, "") 'セル内改行を削除
   半角小文字空白無し = StrConv(半角小文字空白無し, vbNarrow) '全角を半角に
   半角小文字空白無し = Replace(半角小文字空白無し, " ", "")  '空白を削除
   半角小文字空白無し = LCase(半角小文字空白無し) '大文字を小文字に
   
End Function

 悪い例:
 下の例は、引数の変数の値を、ユーザ定義関数の中で変えています。
 VBA では、文字列変数は、デフォルトで「参照渡し」なので、この関数の中で、引数で受け取った変数の値が、変えられてしまいます。
Function 半角小文字空白無し(文字列 As String) As String

   文字列 = Replace(文字列, vbLf, "") 'セル内改行を削除
   文字列 = StrConv(文字列, vbNarrow) '全角を半角に
   文字列 = Replace(文字列, " ", "")  '空白を削除
   半角小文字空白無し = LCase(文字列) '大文字を小文字に
   
End Function

この種類の目次に戻る↑ 索引へ↓ トップページに戻る


計算式のエラーを文字列にする関数

 セルの値を変数に格納する時に、セルが計算式になっていて、計算式の結果がエラーだと、型エラーで、変数に格納できません。
 このため、セルの計算式の結果がエラーの場合は、文字列にして変数に格納するための関数を用意してみました。

Function エラーは文字型に変換(入力変数 As Variant) As Variant

   If IsError(入力変数) = True Then
      エラーは文字型に変換 = CStr(入力変数)
   Else
      エラーは文字型に変換 = 入力変数
   End If

End Function

 解説:
 IsError 関数 は、関数は、指定した数式がエラー値であるかどうかを調べるために使います。
 結果は、ブール型 (Boolean) (エラー値の場合は、真 (True)、そうでない場合は、偽 (False) )で返されます。

 構文IsError(expression)
 引数 expression は必ず指定します。引数 expression には、有効な任意の式を指定できます。
 エラー値は、CVErr 関数を使用して実数を変換することによって作成されます。

 次の例は、IsError 関数を使って、数式がエラー値であるかどうかを調べます。
 ユーザー定義の Function プロシージャの戻り値として、エラー値 (VarType 10) を戻すには、CVErr 関数を使います。
 この例では、ユーザー定義の Function プロシージャを UserFunction、そのプロシージャの戻り値を CVErr(32767) と仮定します。32767 は、ユーザー定義のエラー番号です。

Dim ReturnVal, MyCheck
ReturnVal = UserFunction()
MyCheck = IsError(ReturnVal)        ' True を返します。

この種類の目次に戻る↑ 索引へ↓ トップページに戻る


UNICODE の文字コードに変換

 html で、文字コードを使った表現を使いたいときに、文字から文字コードに変換する必要が有ります。

 参考1:文字参照(数値文字参照)
http://ja.wikipedia.org/wiki/%E6%96%87%E5%AD%97%E5%8F%82%E7%85%A7
 参考2:10進、16進文字コードin HTMLユニコード
http://code.cside.com/3rdpage/jp/unicode/converter.html

 下の例は、全角文字が有る場合に、全角文字の頭の5文字までを、html用の10進ユニコードに変換するものです。


      '英数以外が含まれているかどうかを判定
      全角文字数 = LenB(StrConv(文字列, vbFromUnicode)) - Len(文字列)
      '全角があれば、エンコード
      If 全角文字数 > 0 Then
         全角文字数 = Len(文字列)
         If 全角文字数 > 5 Then 全角文字数 = 5
         変換後文字列 = ""
         For 文字カウント = 1 To 全角文字数
            文字コード = AscW(Mid(文字列, 文字カウント, 1))
            If 文字コード < 0 Then
               文字コード = 文字コード + 65536
            End If
            変換後文字列 = 変換後文字列 & "&#" & CStr(文字コード) & ";"
         Next 文字カウント
         文字列 = 変換後文字列
      End If

 解説:
 AscW 関数は、指定した文字列内にある先頭の文字の Unicode 文字セットの文字コードを返す変換関数です。整数型 (Integer) の値を返します。
 引数 string には、任意の文字列式を指定します。この引数は必ず指定します。引数 string に文字が含まれていないときは、実行時エラーが発生します。
 -32768 〜 32767 の範囲の値が返されます。
数値文字参照用のコードに変換
 ユニコードとして使うときは、マイナスの場合には、65,536 を加算します。
 こうすると 型は、Long 型になる点に、要注意です。
 
この種類の目次に戻る↑ 索引へ↓ トップページに戻る


ワイルドカードを使った検索

 文字列を、ワイルドカードを使って照合する方法を、紹介します。

 ここで取り上げる事例は、汎用部品の品目番号体系から、品目名称を設定するものです。
 ボルトなどの、汎用部品は、どこの会社でも、パーツ・ナンバリング・システム(品目番号体系)を使って、設定しています。
 参考:ヤマハ流パーツナンバー命名法
http://www003.upp.so-net.ne.jp/chiiyama/partsnumber.htm

 この事例では、以下のナンバリング・システムを使います。
123 −4567
品目区分 サイズ 材質

 品目名称は、サイズには無関係で、品目区分(部品種別)と材質区分で決まります。
 事例のマクロは、サイズの違う、個別の品目名称を、寸法部分をワイルドカードにした、名称辞書を使って、自動設定するものです。

 この Excel 事例をダウンロードできます。→WildCard.xls
 検索方法として、
@.Like 関数を使う
A.VBScriptの正規表現を使う
の2つを対比しています。

 正規表現の使い方については、下記を参考にさせていただきました。(正規表現の事例はこちら)
さなみ (サナミ)さんの、「VBAで正規表現を使う(RegExpオブジェクトの利用)」
http://codezine.jp/a/article/aid/1655.aspx?p=1


Option Explicit
Option Base 1

Dim 辞書()
Dim 品目 As String
Dim 名称 As String
Dim 辞書件数 As Integer
Dim 処理件数 As Integer
Dim 検索位置 As Integer
Dim 開始日時 As Variant
Dim 終了日時 As Variant

Sub 名称設定()

    開始日時 = Now
    
'★辞書データの読み込み
    ThisWorkbook.Worksheets("辞書").Activate
    
    辞書件数 = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row - 1
    ReDim 辞書(辞書件数, 2)
    辞書 = Range("A2").Resize(辞書件数, 2) '処理速度をあげるため、辞書部分は配列(メモリ)に登録。
    '注:この会社の辞書データでは、なぜか、一文字ワイルドカードとして、*が使われていました。
    '注:辞書データは、前もって条件数が多いものが先に来るように、ソートしておく必要があります。
    
'@Like 関数 を使う★★★★★★★★★★★

    For 検索位置 = 1 To 辞書件数
        辞書(検索位置, 1) = Replace(辞書(検索位置, 1), "*", "?")
        'ワイルドカードの文字を、Excelの文法(複数の*から、一文字の?)に置換。
    Next 検索位置
    
    ThisWorkbook.Worksheets("データ").Activate
    処理件数 = 0

    Do   '品目を行方向に検索して行く。
    
        処理件数 = 処理件数 + 1
        品目 = Range("A2").Cells(処理件数, 1).Value
        If 品目 = "" Then Exit Do   '品目が無くなったら、ループを抜ける。
    
        For 検索位置 = 1 To 辞書件数
            If 品目 Like 辞書(検索位置, 1) _
            Then 'Likeを使って、ワイルドカード検索。
                Range("B2").Cells(処理件数, 1).Value = 辞書(検索位置, 2)
                Exit For
            End If
            Range("B2").Cells(処理件数, 1).Value = "★辞書無し★"
        Next 検索位置
        
    Loop '品目の登録が、無くなるまで繰返し。
    

'A.VBScriptの正規表現を使う★★★★★★★★★★★

Dim 正規表現オブジェクト As RegExp
Set 正規表現オブジェクト = New RegExp
    処理件数 = 0
    
    For 検索位置 = 1 To 辞書件数
        辞書(検索位置, 1) = Replace(辞書(検索位置, 1), "?", ".")
        'ワイルドカードの文字を、?から、正規表現一文字の.に置換。
    Next 検索位置
    
    Do   '品目を行方向に検索して行く。
    
        処理件数 = 処理件数 + 1
        品目 = Range("A2").Cells(処理件数, 1).Value
        If 品目 = "" Then Exit Do   '品目が無くなったら、ループを抜ける。
    
        For 検索位置 = 1 To 辞書件数
            正規表現オブジェクト.Pattern = 辞書(検索位置, 1)
            正規表現オブジェクト.IgnoreCase = False
            '大文字小文字を区分する。デフォルトは、True(区別しない)、False(区分する)
            
            If 正規表現オブジェクト.Test(品目) _
            And Len(品目) = Len(辞書(検索位置, 1)) _
            Then '正規表現を使って、ワイルドカード検索。
                Range("C2").Cells(処理件数, 1).Value = 辞書(検索位置, 2)
                Exit For
            End If
            Range("C2").Cells(処理件数, 1).Value = "★辞書無し★"
        Next 検索位置
    
    Loop '品目の登録が、無くなるまで繰返し。
    
    終了日時 = Now
    MsgBox "処理時間は、" _
    & Format(終了日時 - 開始日時, "hh時間nn分ss秒") & " でした。"

End Sub

 解説:

VBScript の RegExpオブジェクトを利用できるように RegExp 正規表現オブジェクト

 VBAで正規表現を使うには、VBScript の RegExpオブジェクトを利用します。
 そのために、事前にVBE(Visual Basic Editor)で、以下の設定をします。

ツール(T)→参照設定(R)を選択して、表示される画面で、
「Microsoft VBScript Regular Expressions 5.5」にチェックを付けて、
OKボタンをクリック します。(右図参照)

 コードの書き方は、「A.VBScriptの正規表現を使う」を参照下さい。

参考:
 正規表現による Visual Basic Scripting Edition (VBScript) の機能強化
http://www.microsoft.com/japan/msdn/columns/scripting/scripting051099.aspx

Pattern プロパティ は、正規表現を定義するために使用される文字列です。
 これは正規表現オブジェクトを使用する前に設定されていなくてはなりません。

 例えば、パソコンの型番が、LX50H、LX50J、LX50K・・
(つまりモデルを表す文字列+英字一桁の季節変更追い番)のときに、LX50系としてマッチさせたい場合は、
正規表現オブジェクト.Pattern = "LX50" & "[A-Z]{1}$"
とします。
 ここで注意は、最後に、行末を示す「$」を入れておくことです。
 そうしないと、
正規表現オブジェクト.Test(データ)

で、別のモデル系列の「LX50DTV」もマッチしてしまうからです。

IgnoreCase プロパティ は、大文字小文字を区別するかどうかを示す読み取り専用のブール値です。
 デフォルトでは、IgnoreCase は False(大文字小文字を区分する) に設定されています。 大文字小文字を区分しないようにするには、True(区別しない) を指定します。

Global プロパティ は、正規表現を、文字列内のすべての可能なマッチに対してテストすべきかどうか、を示す読み取り専用のブール値です。
 デフォルトでは、Global は False に設定されています。
 同一行内で、マッチした複数の箇所を置換したい場合には、True を指定します。

Testメソッド
object.Test(string)
パターンに一致する文字列が検索されたら True を返します。見つからないと False を返します。

 下記で、Test メソッドを使った、検索のユーザ定義関数が紹介されていました。
http://codezine.jp/article/detail/1655?p=3

'[返り値]
'検索文字列内に正規表現パターンが見つかった場合:True
'検索文字列内に正規表現パターンが見つからなかった場合:False
Function 正規表現検索(正規表現パターン As String, 検索文字列 As String) As Boolean
    Dim 正規表現オブジェクト As RegExp
    Set 正規表現オブジェクト = New RegExp
    正規表現オブジェクト.Pattern = 正規表現パターン
    正規表現検索 = 正規表現オブジェクト.Test(検索文字列)
End Function


Replaceメソッド
object.Replace(string1, string2)
検索されたら置換文字列(string2)と置き換えます。
置換文字列に改行を使いたい場合は、vbCrLf などを指定します。

 参考1:VBAで正規表現を使う
http://codezine.jp/article/detail/1655?p=3
 後方参照を使うと、"( )"でグループ化した文字列を再度利用することができます。
 Testメソッドなどで、正規表現を定義するときに後方参照を利用する場合は、"\"文字の後にグループの番号を数字で指定します。
 Replaceメソッドなどで、置換文字列を指定するときに後方参照を利用する場合は、"$"文字の後にグループの番号を数字で指定します。

 参考2:正規表現置換のユーザ定義関数
http://codezine.jp/article/detail/1655?p=3
 で、正規表現置換のユーザ定義関数が紹介されていました。

 参考3:正規表のマクロ・サンプル
インターネットから株価を取得2 (MSXML2.XMLHTTP を使う ShiftJIS)


Executeメソッド
object.Execute(string)
指定された文字列を正規表現で検索します。
文字列内で見つかった文字列ごとに存在するMatch オブジェクトを含む、Matches コレクションを返します。
 正規表現で、最初に見つかった桁(文字目)は、下記で取得できます。
Matchオブジェクト.Item(0).FirstIndex + 1
 この方法は、「Hajilyn's Papa(はじりんパパ)」の 「Excel Tips」の「エクセルで正規表現(VBScriptをつかって)」(下記 URL)で教えていただきました。
http://www.geocities.co.jp/SiliconValley-Bay/1992/excel/moji/vbreg01.html


 Match オブジェクトを使うと、正規表現で一致した文字列の読み取り専用プロパティにアクセスできます。
 Match オブジェクトは、RegExp オブジェクトの Execute メソッドによってのみ作成されます。このメソッドが実際に返すのは、Match オブジェクトのコレクションです。Match オブジェクトのプロパティは、すべて読み取り専用です。
 正規表現で検索を実行すると、Match オブジェクトが 0 個以上作成されます。それぞれの Match オブジェクトは、正規表現で検索された文字列、文字列の長さ、および文字列が検索された場所のインデックスにアクセスする手段を提供します。

 次のコードは、Match オブジェクトの使用例です。

Function RegExpTest(patrn, strng)
   Dim regEx, Match, Matches   ' 変数を作成します。

   Set regEx = New RegExp      ' 正規表現を作成します。
   regEx.Pattern = patrn       ' パターンを設定します。
   regEx.IgnoreCase = True     ' 大文字と小文字を区別しないように設定します。
   regEx.Global = True         ' 文字列全体を検索するように設定します。

   Set Matches = regEx.Execute(strng)   ' 検索を実行します。
   For Each Match in Matches   ' Matches コレクションに対して繰り返し処理を行います。
      RetStr = RetStr & "一致 " & I & " が、"
      RetStr = RetStr & Match.FirstIndex & " で見つかりました。一致した文字列は、"
      RetStr = RetStr & Match.Value & " です。" & vbCRLF
   Next
   RegExpTest = RetStr
End Function

MsgBox(RegExpTest("is.", "IS1 is2 IS3 is4"))

 Match オブジェクトのプロパティMatch オブジェクトを使用すると、正規表現で一致した文字列の読み取り専用プロパティにアクセスできます。

 FirstIndex プロパティ:検索対象の文字列内で一致が見つかった場所を返します。
 Length プロパティ:検索対象の文字列内で一致した文字列の長さを返します。
 Value プロパティ:検索対象の文字列内で一致した値またはテキストを返します。

 Matches コレクションは、正規表現の Match オブジェクトのコレクションです。
 Matches コレクションには、個別の Match オブジェクトが格納されます。このコレクションは、RegExp オブジェクトの Execute メソッドによってのみ作成できます。個別の Match オブジェクトのプロパティと同様、Matches コレクションのプロパティは読み取り専用です。
 正規表現で検索を実行すると、Match オブジェクトが 0 個以上作成されます。それぞれの Match オブジェクトは、正規表現で検索された文字列、文字列の長さ、および文字列が検索された場所のインデックスにアクセスする手段を提供します。

 次のコードは、正規表現の検索で Matches コレクションを取得し、コレクションに対して繰り返し処理を行う例です。

Function RegExpTest(patrn, strng)
   Dim regEx, Match, Matches   ' 変数を作成します。

   Set regEx = New RegExp      ' 正規表現を作成します。
   regEx.Pattern = patrn       ' パターンを設定します。
   regEx.IgnoreCase = True     ' 大文字と小文字を区別しないように設定します。
   regEx.Global = True         ' 文字列全体を検索するように設定します。

   Set Matches = regEx.Execute(strng)   ' 検索を実行します。

   For Each Match in Matches   ' Matches コレクションに対して繰り返し処理を行います。
      RetStr = RetStr & "一致する文字列が見つかった位置は、"
      RetStr = RetStr & Match.FirstIndex & " です。一致した文字列は、"
      RetStr = RetStr & Match.Value & " です。" & vbCRLF
   Next

   RegExpTest = RetStr
End Function

MsgBox(RegExpTest("is.", "IS1 is2 IS3 is4"))

 
この種類の目次に戻る↑ 索引へ↓ トップページに戻る


Access データベースをシーケンシャルに更新する(正規表現置換)(ADO接続)

 上の処理は、業務の場面では、品目件数が、Excelの取り扱える行数を超えていたので、Access DB を直接更新するようにしました。

 この Excel 事例をダウンロードできます。→WildCard.xls

 ExcelのVBAで、Access のDBを参照・更新する方法は、下記で紹介したサイトを参考にさせていただきました。感謝いたします。
ExcelのVBAで、Accessのデータを参照・更新する

 ADO を使うためには、参照設定を追加する必要が有ります。
 VBE の、[ツール(T)]→[参照設定(R)]を選択します。
 表示される、参照設定用のダイアログボックスで、
「Microsoft ActiveX Data Objects *.* Library」にチェックを入れて、[OK]ボタンを押します。
 (注)"*.*"のところは、ADOのバージョンです。新しいものにすると、古いバージョンの Office では動かなくなるので、2.6 あたりを選択すると良いでしょう。

サンプルDB test.zip(test.mdb) をダウンロードできます。
 拡張子が mdb の test.mdb をそのまま UP すると、ダウンロードしようとして IEの「対象をファイルに保存」を使うと、下記のエラーになって開けません。このため、zip 圧縮しています。

Microsoft Access
このファイルを開けません。
このファイルは、イントラネット外または信頼されていないサイトに保存されています。セキュリティ上問題がある可能性があるため、ファイルを開きません。
ファイルを開くには、ローカル マシンまたはアクセスできるネットワークの保存場所にいったんコピーしてください。



Option Explicit
Option Base 1

Dim 辞書()
Dim 辞書正規表現()
Dim 品目 As String
Dim 名称 As String
Dim 辞書件数 As Integer
Dim 処理件数 As Integer
Dim 検索位置 As Integer
Dim 開始日時 As Variant
Dim 終了日時 As Variant
Dim 名称Like As String
Dim 名称正規表現 As String
Dim 正規表現オブジェクト As RegExp

Dim 接続 As ADODB.Connection
Dim ファイル名 As String
Dim Accessテーブル As ADODB.Recordset
Dim 選択SQL As String


Sub ADO接続でAccessテーブルを更新()

    開始日時 = Now
    Set 正規表現オブジェクト = New RegExp

'★辞書データの読み込み
    ThisWorkbook.Worksheets("辞書").Activate
    
    辞書件数 = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row - 1
    ReDim 辞書(辞書件数, 2)
    
    辞書 = Range("A2").Resize(辞書件数, 2) '処理速度をあげるため、辞書部分は配列(メモリ)に登録。
    ThisWorkbook.Worksheets("データ").Activate

    For 検索位置 = 1 To 辞書件数
        辞書(検索位置, 1) = Replace(辞書(検索位置, 1), "*", "?")
        'ワイルドカードの文字を、複数の*から、一文字の?に置換。
    Next 検索位置
    
    ReDim 辞書正規表現(辞書件数, 2)
    辞書正規表現 = 辞書
    For 検索位置 = 1 To 辞書件数
        辞書正規表現(検索位置, 1) = Replace(辞書(検索位置, 1), "?", ".")
        'ワイルドカードの文字を、?から、正規表現一文字の.に置換。
    Next 検索位置

        
'★Accessのデータベースに接続して、テーブルをシーケンシャルに検索して更新
'http://members.at.infoseek.co.jp/kenchan_h/index18.html

    'New キーワードを使用して新規Connectionオブジェクトを生成
    Set 接続 = New ADODB.Connection
    
    '接続先のデータベース
    ファイル名 = "c:\temp\test.mdb"
    
    '接続
    接続.Open _
      "Provider=Microsoft.Jet.OLEDB.4.0;" _
      & "Data Source=" & ファイル名
    
    'レコードセットの作成(SELECT文の実行)
    Set Accessテーブル = New ADODB.Recordset
    選択SQL = "SELECT * FROM データ"
    
    'ADOでレコードセットを作成するとき、レコードの更新、変更、削除ができるように、
    'RecordsetオブジェクトのOpenメソッドを使います。
    'http://www.geocities.jp/cbc_vbnet/ADO/recordset.html
    'Recordsetオブジェクトの作成(ADO編)
    
    Accessテーブル.Open 選択SQL, 接続, adOpenKeyset, adLockOptimistic
    
    '最終レコードまで順読み込みを行う
    'http://www.happy2-island.com/vbs/cafe02/capter00506.shtml
    Do Until Accessテーブル.EOF = True

       'Accessテーブルの、特定フィールドの値を所得します
       品目 = Accessテーブル("品目")

        'Like を使って、ワイルドカード照合
        For 検索位置 = 1 To 辞書件数
            If 品目 Like 辞書(検索位置, 1) _
            Then 'Likeを使って、ワイルドカード検索。
                名称Like = 辞書(検索位置, 2)
                Exit For
            End If
            名称Like = "★辞書無し★"
        Next 検索位置

        'フィールドの値を変更
        Accessテーブル.Update "名称1", 名称Like
         
         
        '正規表現を使って、ワイルドカード照合
         
        For 検索位置 = 1 To 辞書件数
            正規表現オブジェクト.Pattern = 辞書正規表現(検索位置, 1)
            正規表現オブジェクト.IgnoreCase = False
            '大文字小文字を区分する。デフォルトは、True(区別しない)
            
            If 正規表現オブジェクト.Test(品目) _
            And Len(品目) = Len(辞書正規表現(検索位置, 1)) _
            Then '正規表現を使って、ワイルドカード検索。
                名称正規表現 = 辞書正規表現(検索位置, 2)
                Exit For
            End If
            名称正規表現 = "★辞書無し★"
        Next 検索位置

        'フィールドの値を変更
        Accessテーブル.Update "名称2", 名称正規表現
         
       'レコードの順読み
       Accessテーブル.MoveNext

    Loop
    
    'レコードセットのクローズ
    Accessテーブル.Close
    
    '接続を解除
    接続.Close
    'オブジェクトをクリア
    Set Accessテーブル = Nothing
    Set 接続 = Nothing
    Set 正規表現オブジェクト = Nothing
    
    終了日時 = Now
    MsgBox "処理時間は、" _
    & Format(終了日時 - 開始日時, "hh時間nn分ss秒") & " でした。"

End Sub

この種類の目次に戻る↑ 索引へ↓ トップページに戻る


Access データベースをシーケンシャルに更新する(正規表現カウント)(ADO接続)

 Access の項目に含まれる、指定文字の文字数をカウントします。
 ここでは、Excel の「辞書」テーブルに登録した、AccessDBのテーブルの項目に、同じく、「辞書」テーブルで指定した文字列が含まれる数をカウントして、Accessのテーブルの該当項目に書き込みます。

 例えば、品目番号に、下のような、ハイフンの数が違う 3 種類の体系があるとすれば、そのハイホンの数を数えて、指定項目に書き出します。
 1234567890 :0
 1234-56789 :1
 123-456-78 :2

 この Excel 事例をダウンロードできます。→CountSpecifiedCharacter.xls
サンプルDB test.zip(test.mdb) をダウンロードできます。

 ADO を使うためには、参照設定を追加する必要が有ります。
 VBE の、[ツール(T)]→[参照設定(R)]を選択します。
 表示される、参照設定用のダイアログボックスで、
「Microsoft ActiveX Data Objects *.* Library」にチェックを入れて、[OK]ボタンを押します。
 (注)"*.*"のところは、ADOのバージョンです。新しいものにすると、古いバージョンの Office では動かなくなるので、2.6 あたりを選択すると良いでしょう。


Option Explicit
Option Base 1

Dim 辞書()
Dim 品目 As String
Dim 名称 As String
Dim 辞書件数 As Integer
Dim 処理件数 As Integer
Dim 検索位置 As Integer
Dim 開始日時 As Variant
Dim 終了日時 As Variant
Dim 名称Like As String
Dim 名称正規表現 As String
Dim 正規表現オブジェクト As RegExp
Dim Matches

Dim 接続 As ADODB.Connection
Dim ファイル名 As String
Dim Accessテーブル As ADODB.Recordset
Dim 選択SQL As String
Dim AccessDB名 As String
Dim Accessテーブル名 As String
Dim 対象項目名 As String
Dim 現在のパス As String


Sub ADO接続でAccessテーブルを更新()

    開始日時 = Now
    Set 正規表現オブジェクト = New RegExp

   '処理の前提のパラメータを設定
   現在のパス = ThisWorkbook.Path
   ThisWorkbook.Worksheets("辞書").Activate
   対象項目名 = Range("A1").Value
   AccessDB名 = Range("B1").Value
   Accessテーブル名 = Range("C1").Value
    
    '★辞書データの読み込み
    辞書件数 = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row - 1
    ReDim 辞書(辞書件数, 2)
    
    辞書 = Range("A2").Resize(辞書件数, 2) '処理速度をあげるため、辞書部分は配列(メモリ)に登録。

   '注意:セルB2以下の項目名は、前もって Access のテーブルデザインで、項目追加されていること。

        
'★Accessのデータベースに接続して、テーブルをシーケンシャルに検索して更新
'http://members.at.infoseek.co.jp/kenchan_h/index18.html

    'New キーワードを使用して新規Connectionオブジェクトを生成
    Set 接続 = New ADODB.Connection
    
   '接続先のデータベース
    ファイル名 = 現在のパス & "\" & AccessDB名
    
    '接続
    接続.Open _
      "Provider=Microsoft.Jet.OLEDB.4.0;" _
      & "Data Source=" & ファイル名
    
    'レコードセットの作成(SELECT文の実行)
    Set Accessテーブル = New ADODB.Recordset
    選択SQL = "SELECT * FROM " & Accessテーブル名
    
    'ADOでレコードセットを作成するとき、レコードの更新、変更、削除ができるように、
    'RecordsetオブジェクトのOpenメソッドを使います。
    'http://www.geocities.jp/cbc_vbnet/ADO/recordset.html
    'Recordsetオブジェクトの作成(ADO編)
    
    Accessテーブル.Open 選択SQL, 接続, adOpenKeyset, adLockOptimistic
    
    '最終レコードまで順読み込みを行う
    Do Until Accessテーブル.EOF = True

       'Accessテーブルの、特定フィールドの値を取得します
       品目 = Accessテーブル(対象項目名)

        '正規表現を使って、ワイルドカード照合
         
        For 検索位置 = 1 To 辞書件数
            正規表現オブジェクト.Pattern = 辞書(検索位置, 1)
            正規表現オブジェクト.IgnoreCase = False
            '大文字小文字を区分する。デフォルトは、True(区別しない)
            正規表現オブジェクト.Global = True   ' 文字列全体を検索するように設定します。

            Set Matches = 正規表現オブジェクト.Execute(品目)   ' 検索を実行します。
            'フィールドの値を変更
             Accessテーブル.Update 辞書(検索位置, 2), Matches.Count
             'http://codezine.jp/article/detail/1655?p=2
        Next 検索位置
                     
       'レコードの順読み
       Accessテーブル.MoveNext

    Loop
    
    'レコードセットのクローズ
    Accessテーブル.Close
    
    '接続を解除
    接続.Close
    'オブジェクトをクリア
    Set Accessテーブル = Nothing
    Set 接続 = Nothing
    Set 正規表現オブジェクト = Nothing
    
    終了日時 = Now
    MsgBox "処理時間は、" _
    & Format(終了日時 - 開始日時, "hh時間nn分ss秒") & " でした。"

End Sub


この種類の目次に戻る↑ 索引へ↓ トップページに戻る

ホームページのトップに戻る