Excel(VBA)マクロでセルの値を取得する基本セオリー #Excel

最近ときどき仕事で依頼されて、Excelのマクロを組む機会があります。
せっかくなので改めて便利だなぁと思ったExcelVBAの内容なんかをまとめておきます。

Win8 Excel2013で動作しましたが2003〜とかなら問題ないはずです。

１．セルの値取り出しの定石

特定のファイルからデータを抽出するようなツールでは想定外の文字が入っていることがあります。
念の為取得後は値判定などの前に下記のような処理を挟んで柔軟性を上げておくと良いです。

ExcelVBA

Dim strBuf As String

With ThisWorkbook.Sheets("sheet1")     ' 操作シートを指定（複数使う場合は有用）
  strBuf = .Cells(1,1).value            ' 値を取り出し
  strBuf = Trim(strBuf)                 ' トリミング：前後の空白抜き出し
  strBur = Replace(strBuf, vbLf, "")    ' 置換：改行削除
  strBuf = strConv(strBuf, vbNarrow)    ' コンバート：全角文字を半角文字へ変換
  strBuf = strConv(strBuf, vbUpperCase) ' コンバート：小文字を大文字へ変換

  MsgBox strBuf
End With

なお、アサーションチェックなども必須で使いましょう。
ExcelVBAではDebug.Assert [評価式]と書いておけば式が偽になった時点で処理が止まります。

２．文字列の抜き出しの定石

基本的にMid()とInStr()が使えれば大体対応可能です。
Mid()は文字列の前から何文字目から、何も自分文字列を取り出すという使い方をします。
（第三引数を省略すると指定文字位置から最後まで取得してくれる。）
InStr()で特定の文字が幾つ目にあるかを探してそのまま渡すことが多いです。

【例】括弧で囲まれたメールアドレスの取得、ホスト・ドメインの分割

ExcelVBA

Private Sub CommandButton1_Click()
  Dim stBruf As String, email As String, host As String, domain As String
  strBuf = "メールアドレス (email@gmail.com)"

  email = blockTrim(strbuf)
  host  = Mid(email, 1, InStr(email, "@") - 1)
  domain = Mid(email, InStr(email, "@")+1)

  MsgBox "email :" & email & vbCrLf & _
         "host  :" & host & vbCrLf & _
         "domain:" & domain
'        #=> email : email@gmail.com
'            host  : email
'            domain: gmail.com
End Sub

Function blockTrim(ByVal strBuf As String) As String
  Dim trimStart, trimEnd As Integer
  strbuf = StrConv(strBuf, vbNarrow)

  trimStart = InStr(strBuf, "(") + 1
  trimEnd = InStr(strBuf, ")") - trimStart    ' メールアドレスの長さ取得
  If trimEnd <= trimStart Then               ' ")"が無い場合などのエラー避け
    blockTrim = ""
    Exit Function
  End If
  blockTrim = Mid(strBuf, trimStart, trimEnd) ' トリミングした文字列返却
End Function

取り敢えず基本は大事ですね。