【VBA】直前に開いたディレクトリをデフォルトルートとして表示する

VBAでマクロを書くときに、ボタンをクリックするとディレクトリ選択のダイアログを表示することがよくある。
でもApplication.FileDialogを使っているとWindowsのように直前に開いた、または選択したディレクトリが表示されず、Excelファイルがあるディレクトリがルートになってしまう。

そこでダイアログ表示時に直前に開いたディレクトリをデフォルトルートにする方法をまとめる。

【VBA】2つのExcelファイルを高速で比較する＋性能改善のコツ

SIerはなんでもExcelで管理したがる。データベースの中身やコード管理など…。
たとえばテストのエビデンスを取得するときも、データをExcelに貼り付けて確認フローに回される。
そのため、Excelファイルを比較することが場面によく出会うだろう。

そこで、VBAで2つのExcelファイルを高速で比較するマクロをつくってみた。
また性能改善のTips、コツもあわせてまとめる。

(SIerを退職してから早1年半、Excelをまったく触らなくなった。また、この記事のメモも2年前に書いたものなので説明が雑になることがありますｗ)

【VBA】セル参照の性能の違い（Value, Text, なし）

VBAで集計するマクロをつくる場合、セル参照の方法で性能を大幅に改善することができる。ということで効率的なセル参照の方法をまとめる。

（SIerを退職してからExcelはまったく触らなくなったが、wri.peのNotesに埋もれていてせっかくなので記事にしてみたｗ）

【VBA】三項演算子（IIf関数）の扱いに注意せよ！

VBAでツールをつくっているとき、ついC#の感覚で三項演算子を使ったら大変なことになった。
ちなみにVBAで三項演算子はIIf(Expression, TruePart, FalsePath)と書く。
問題となったコードがこちら

Sub ConvertToCurrency(ByVal value As String)
    Dim price As Currency
    price = IIf(Trim(value) = "", 0, CCur(value))
    '-- 以下略
End Sub

【VBA】例外処理（Try-Catch-Finally）を使う方法

結論から言うと、VBAにはTry-Catch-Finallyが存在しない。
そこでOn Errorステートメントとラベルを使う。

一番簡単な例外処理（エラーハンドリング）は以下のとおり。

Sub ErrHandlingTest()
    On Error GoTo ErrorHandler
    
    Dim value As Long
    value = 2 / 0     '-- ゼロ除算のためエラー発生

    Exit Sub
ErrorHandler:
    '-- 例外処理
    MsgBox Err.Number & ":" & Err.Description, vbCritical & vbOKOnly, "エラー"
End Sub

このようにすると、エラーが発生した時点で「On Error GoTo ラベル名」に指定したラベル名（上記の例ではErrorHandler）に飛ぶ。

【VBA】性能を求めるならWithステートメントはループの外で

VB系の言語がもつ割りと使用頻度が高いWithステートメント。
Withステートメントは使い方次第で性能（パフォーマンス）が上がったり、落ちてたりしまう。

ということで、Withステートメントを「使わなかった場合」、「ループの外側で使った場合」、「ループの内側で使った」場合の3つの性能をまとめる。

【VB系】変数の宣言と初期化を1行で書く（VB.NET,VBA,VBScript）

VBAやVBScriptって、変数宣言と初期化がものっそい面倒なイメージがある。

C#やJavaみたいにint num = 0;みたいに書ければいいのに…って思っている方は多いのではないでしょうか？

実は、1行でも書ける！

やっとVisual Basic系（VB、VB.NET、VBA、VBScriptなど）で変数の宣言と初期化を1行で書く方法を見つけたのでまとめていく。

【VBA】指定フォルダ配下のブックを操作するためのマクロテンプレート

日本のSEはExcelがだぁ～いすき！
だから、なんでもかんでもExcelを使うよ。

そんなとき便利なのが、指定フォルダ配下のブックを操作するVBAマクロ。

例えば、「データの集計」や「ヘッダ・フッタ・更新履歴の修正」などなど。

毎回そんなマクロ組んでいたら1日が終わってしまう。
Office2003までの「FileSearch」は何かと便利だった。
しかし、Office2007以降の「FileSystemObject」は何かと厄介。

詳しくは、以下のエントリーを参照してほしい。

参考
【VBA】FileSearchの代わりにFileSystemObjectを使用する
【VBA】FileSystemObjectでファイル名（拡張子）の指定＋ソートして取得する

ということで、そんなときに使えるVBAマクロのテンプレートを紹介する。

【VBA】Excelのシート名の一覧を取得し、変換する

【VBA】Excelのシート名を一括置換するというエントリを投稿したところ、『VBAマクロ書かなくても、ソフト使えば一括変換できるよ』的なコメントを頂いた。（ちょっとスパムっぽかったけどｗ）

以前のエントリは文字列置換を用いたシート名の変換だったが、教えてもらったものは「今のシート名」と「変更後のシート名」を一覧にして一括で置換するものだった。


便利そう！


でもシステム屋さんはお客様のPCを借りたり、サーバなど好き勝手インストールできない環境で仕事をしているので、インストールが必要なソフトは使えない。


でも、便利そう！！

これは、私のパーソナルマクロ集に加えたい！！！



ってことで、VBAマクロで再現してみた。

【VBA】選択範囲の絶対参照、相対参照を相互変換する

 セル内に「=A1」、「=A1+A2」と入力したあとに、参照先はそのままでコピーして他の場所で使いたいときがある。
だがセルを相対的に参照しているため、移動した時点で参照先が変わってしまい結果がおかしくなってしまう。

解決するには、「=$A$1」、「=$A$1+$A$2」のように全て絶対参照に修正すればよい。


反対に、絶対参照で数式を入力したあとに、やっぱり相対参照がよかった。
なんてこともよくあることだ。

しかし、「=A1+A2」と「=$A$1+$A$2」を毎回手作業で修正するのは馬鹿らしい。


それを一括で行うマクロが以下になる。
※以下の例は、行・列ともに全て変換する方法だ。
   行のみ、列のみは「マクロの内容説明」を参照してほしい。

【VBA】印刷範囲の開始行（列）と最終行（列）を取得する

   Excelの印刷範囲を取得するためには「WorkSheet.PageSetup.PrintArea」を使用する。

印刷範囲の取得

 下図のような印刷設定のとき、「ActiceSheet.PageSetup.PrintArea」を使うと、
「$C$4:$H$10」という値が返ってくる。

 
 このPrintAreaを使用すれば印刷範囲が設定されているかがわかる。

If ActiveSheet.PageSetup.PrintArea = vbNullString Then
    MsgBox "印刷設定がされていません。"
End If

印刷範囲の開始行（列）と最終行（列）を取得する

【VBA】Excelのシート名を一括置換する

 Excelでシート内の文字列は置換できるのに、なぜシート名の置換ができないのか。
シート名を一括で編集したい場面は多々ある。

シート名を一括置換する

 それを可能にするのが、以下のVBAマクロ
For Eachでワークシートオブジェクトを取得し、文字列の置換を行う。

Sub シート名置換()
    Dim ws As Worksheet
    Dim myFind As String
    Dim myReplace As String
    
    myFind = Application.InputBox("検索文字列は？", "シート名置換", Type:=2)
    myReplace = Application.InputBox("置換文字列は？", "シート名置換", Type:=2)
    
    For Each ws In ActiveWorkbook.Worksheets
        On Error Resume Next
        ws.name = Replace(ws.name, myFind, myReplace, 1, -1, 2)
    Next ws
End Sub

【VBA】Excelでユーザー定義関数を使う

 Excelには最初からあるExcel（エクセル）関数が複数ある。


 しかし、用意されている関数には限界があり、それを使おうとすると

=IF(xx = yy, IF(xx = zz, SUM(A1:A10), AVG(A1:A10)), IF(COUNTIF(A1:A10, "@") > 0 ・・・・・)

みたいな何やってるのか、メンテもできないような数式になってしまう。


それを解消するためにExcelにはユーザー定義関数がある。

【VBA】FileSystemObjectでファイル名（拡張子）の指定＋ソートして取得する

 前回、「FileSearchの代わりにFileSystemObjectを使用する」で紹介したとおり、Office2007以上はFileSearchオブジェクトが使えなくなり、FileSystemObjectを使うようになった。


FileSystemObjectに替えることで、下記のことができなくなった。

1. サブディレクトリ配下の検索
2. ファイル名でソート
3. ファイル名（拡張子）の指定

1.サブディレクトリの検索は、こちらを参照→ 【VBA】FileSearchの代わりにFileSystemObjectを使用する

今回は、「2.ファイル名でソート」と「3.ファイル名（拡張子）の指定」をメインに紹介する。



【追記：2014/05/24】
サブディレクトリ検索、ソート、拡張子フィルタを実装したテンプレートを作成した。
→ 【VBA】指定フォルダ配下のブックを操作するためのマクロテンプレート

【VBA】FileSearchの代わりにFileSystemObjectを使用する

 Office2003から2007以降のバージョンに変えた時に使えなくなる代表的なモノが「FileSearch」オブジェクトではないだろうか？

作業効率向上のため、ファイルを検索、読み込んで、編集して、書き込んで、保存のような使い方など…

そのため、Office2007、2010、2013などは「FileSystemObject」に替える必要がある。


【追記：2014/05/24】
サブディレクトリ検索、ソート、拡張子フィルタを実装したテンプレートを作成した。
→ 【VBA】指定フォルダ配下のブックを操作するためのマクロテンプレート

【VBA】同じ値が連続するセルを結合する

 前エントリの「デシジョンテーブルを自動生成する」に於いて、
「YNYN・・・」と延々と続くのは美しくない。

ある程度、整形されていたほうが見やすいし、間違いも発見しやすい。

そこで、同じ値が連続してセルに設定されている場合、
セル同士を結合するマクロを作りました。

【VBA】デシジョンテーブルを自動生成する

ビジネスルールの整理、要件・仕様の整理、ソフトウェアテスト（特に単体テスト）のテスト仕様書の作成でよく用いられる手法が「デシジョンテーブル」です。

デシジョンテーブル（判断表）
複数の条件の組み合わせと、それに対応してどのような処理を行うのかを表す、表形式表現。
仕様を記述する上では日本語等の文章表現で記述するよりも誤謬性が低く、有用性が高い。

引用元：はてなキーワード（デシジョンテーブル）

このようにデシジョンテーブルは使われています。
ただ、条件が少ないうちは良いのですが、多くなるとデシジョンテーブルを作ること自体に時間がかかってしまいます。

その中でも、「Y/N」を作るところだけでも自動化しようと思いマクロを作りました。

【VBA】仕事でよく使うVBAマクロ Best 3

IT業界の会社に入社して一番衝撃だったのは…

なんでもかんでも Excel で作る！


お客様に提示する資料から、規約をまとめた文書、設計書に至るまで
すべてExcelで作られていました。

IT業界は　Excel至上主義　なんです！！


そんなこんなで私が仕事でよく使うVBAマクロの Best3 を紹介いたします。