はじめに
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