LoginSignup
10

More than 5 years have passed since last update.

Excel VBAで、マクロを作る時のTip

Posted at

はじめに

Office for Mac 2011で動作確認。おそらくWindowsでも動きます
Mac版はWin版に比べて使えない機能が多く(createObjectなどは一切使えない)、必ずしもWin版にとっては最適ではないかもしれません。

参考:Mac版Excel(Office2011)のVBAを使う場合の注意点

少しですが、使えるものを

昨日までJavaJavaしてた人がいきなりExcelのVBAを実装する羽目になったときのためのメモ
ExcelExcel594VBAVBA354mvcmvc71

この記事も参考になります。

モデル定義が面倒なとき

Excelのシートは肥大化しがち。それぞれにモデルを定義していたらきりがない。そんな時は、連想配列が便利でした。

Office for MacのVBAだとDictionaryが使えないため、以下ではCollectionを用いています。Win環境であれば、Dictionaryを使ったほうが良いと思います。(Containsメソッドなどが使えるため)

MaxRow = Cells(Rows.Count, 1).End(xlUp).Row
MaxCol = Cells(1, Columns.Count).End(xlToLeft).Column

で最終行、最終列を取得できる
最終行からたどっていくイメージ

それぞれに対し、データを連想配列に格納する。ヘッダ列を基準にカラムを決定しています

' 連想配列のキーにする列
Const HEAD_ROW_INDEX = 1

' 連想配列にする対象のデータの開始列
Const MIN_COLUMN_INDEX = 2

Private Function CreateParamsFromRow(ByVal sheet As Worksheet, ByVal rowIndex As Long) As Collection
    Dim lastColumnIndex As Long
    lastColumnIndex = sheet.Cells(HEAD_ROW_INDEX, sheet.Columns.Count).End(xlToLeft).Column

    Dim params As Collection
    Set params = New Collection

    With sheet
        Dim j As Lon
        For j = MIN_COLUMN_INDEX To lastColumnIndex
            Dim key As String
            key = NormalizedKeyString(.Cells(HEAD_ROW_INDEX, j).Value)
            ' 本来重複検査するべき
            params.Add .Cells(rowIndex, j).Value, key
        Next j
    End With

    Set CreateParamsFromRow = params
End Function

' キーにする際に、改行を抜く
Private Function NormalizedKeyString(ByVal s As String)
    NormalizedKeyString = Replace(Replace(s, vbCr, ""), vbLf, "")
End Function

本当であれば、連想配列に追加する前にparams.Contains(key)でチェックするべきだが、MacのVBAでは使えないため諦め。

その連想配列に基づいて、マッピングする

ワークシートをコピーしたい時

Excel VBAの関数は副作用がとても多い。操作対象がコロコロ変わるので。その副作用を消す関数を作るととても便利でした。

' ActiveWorksheetを維持したまま、ワークシートをコピー、ワークシートの参照を返す
Function CopyWorksheet(ByVal templateSheet As Variant) As Worksheet

    Dim preActivesheet As Worksheet
    Set preActivesheet = ActiveSheet

    Dim succeeded As Boolean
    succeeded = templateSheet.Copy(After:=Worksheets(Worksheets.Count))

    Dim newSheet As Worksheet
    Set newSheet = ActiveSheet
    preActivesheet.Activate

    Set CopyWorksheet = newSheet
End Function

拡張子を除いたファイル名を取得したい時

今あるファイルから、別のファイルに書き出したい時、名前は維持して後ろにsuffixつけるのが便利
そんな時に使えるメソッドです

'拡張子を除いたファイル名の取得
Function GetPureName(sFileName As String) As String
    Dim pureName As String
    pureName = Left(sFileName, InStrRev(sFileName, ".") - 1)
    GetPureName = pureName
End Function

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
10