Excel便利技

Excelを使っていて、覚えたつもりでも忘れてしまっているテクニックを追記していく。

#１．ブック・シート・セル操作
###プルダウンリストを自動拡張する方法
Excelでリストのデータをドロップダウンリストから選択させるとき、元のリストにエントリが増えた際に自動でドロップダウンリストも増やす方法。

例）H4～H7セルに入力されたリスト(H4セルは表題)をもとにA1セルにプルダウンリストから選択入力できるようにする。

解説）OFFSET関数を使用することで、リストの範囲を動的に定義する。

①：リストの表題であるH4セルを起点とする（絶対参照）
②：取得したい範囲の左上のセルを起点からのOffset値で指定する。
　　ここでは、起点から1行下、0行右へ移ったセルを指定。
③：取得したい範囲の左上のセルを元に範囲の行数、列数を指定する。
　　COUNTAでH列のデータ数をカウントすることで、表にデータが追加された場合に対応できる。

#２．関数
##日付操作系
###月末・月初日付を取得
・指定した日付から、その月の末日日付を取得する
=EOMONTH(<日付シリアル値>,0)
・指定した日付から、翌月の末日日付を取得する
=EOMONTH(<日付シリアル値>,1)
・指定した日付から、前月の末日日付を取得する
=EOMONTH(<日付シリアル値>,-1)

これを応用すると、月初日の取得も簡単。求めた月末日付に"1"を足してやるだけ。
・今月の月初日付を取得する
=EOMONTH(Today(),-1)+1

#３．VBA関連
###イベント連動プロシージャの玉突き実行を抑止する
Worksheet_Change等、イベントをトリガーとして実行されるプロシージャは、そのプロシージャ内での処理結果をトリガーとして玉突き実行される。
例えば、ワークシートのセルの値が更新された場合に実行されるイベントプロシージャ「Worksheet_Change」プロシージャ内で、同シート上のセルを更新すると、その更新をトリガーとして同じイベントプロシージャが起動してします。
処理の記載内容によっては無限にプロシージャが起動してしまうことも。。。

そんな時にはApplication.EnableEventプロパティを操作して、イベントの発生を一時的に抑止するとよい。

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False     'イベントの発生を抑止
    Me.Range("A1").value = Now()
    Application.EnableEvents = True      'イベント発生抑止の解除
End Sub

###フォルダ・ファイルを選択するダイアログを表示し選択させる
ダイアログを表示してインプットファイルを選択させたり、アウトプットファイルの格納先パスをダイアログから選択させる場合Application.FileDialog(ダイアログの種別)を使用すると簡単に実現できる。

※ダイアログの種別は以下が指定できる
　ファイルを選択させる場合：msoFileDialogFilePicker
　フォルダを選択させる場合：msoFileDialogFolderPicker
　「ファイルを開く」　　　：msoFileDialogOpen
　「名前を付けて保存」　　：msoFileDialogSaveAs

例)フォルダ選択ダイアログを表示し、指定されたフォルダパスをA1セルに記載する

With Application.FileDialog(msoFileDialogFolderPicker)
    .InitialFileName = ThisWorkbook.path & "\"
    If .Show = True Then
        Me.Range("A1").Value = .SelectedItems(1)
    End If
End With

解説)
InitialFileNameプロパティでは、ダイアログを開いた際の初期パスを指定する。
このとき、フォルダパスを指定したとしてもその文字列の末尾が\になっていなければファイルとみなされるため、初期"フォルダ"を指定したい場合は必ず末尾に\を付けること。
Showメソッドはダイアログを表示するとともに、そのダイアログでアクションボタン(※)かキャンセルボタンのどちらがクリックされたかを返すため、アクションボタンがクリックされた場合のみSelectedItemsプロパティに格納された値を取り出す。
※アクションボタン：FileDialogで指定したダイアログの種類によりアクションボタンは以下の通りとなる
msoFileDialogFilePicker ・・・ 「開く」ボタン
msoFileDialogFolderPicker ・・・ 「OK」ボタン
msoFileDialogOpen ・・・ 「開く」ボタン
msoFileDialogSaveAs ・・・ 「保存」ボタン

###ActiveXコントロールを扱う
ワークシートに配置したチェックボックスなどのActiveXコントロールを扱うには、OLEObjectsコレクションを使う。
[ワークシートオブジェクト].OLEObjects([ActiveXコントロールのオブジェクト名]).Objcet.[プロパティ]

・アクティブシートに配置した"Checkbox1"の値(True/False)を取得する場合

ActiveSheet.OLEObjects("CheckBox1").Object.Value

###複数行におよぶ制御構文を１行で実行
通常複数行に分けて記載する処理（制御構文等）を一行に記載する方法。
改行の代わりに":"(コロン)を使う
イミディエイトウィンドウに記載して簡易的に実行する際に便利。

例）ブック内のシートをイミディエイトウィンドウにリスト表示させる
・複数行表記

For Each ws In Sheets
    Debug.Print ws.Name
Next

・１行表記

For Each ws In Sheets : Debug.Print ws.Name : Next

因みに、上記１行表記をイミディエイトウィンドウで実行すると、
ActiveになっているBookのシートリストが表示される。

###イミディエイトウィンドウに改行無しで出力する方法
Debug.Print文の末尾に;(セミコロン)を記載しておけば、出力に改行が入らない。