カテゴリー: Excel操作

コードコピー

'Excelを手動計算にする
Application.Calculation = xlCalculationManual

コードコピー

'Excelを自動計算にする
Application.Calculation = xlCalculationAutomatic

Excelの再計算の動作設定を変更するには、Application.Calculation プロパティを使用します。

このプロパティに以下の値を設定することで、再計算の動作を切り替えることができます。

自動計算 = xlCalculationAutomatic（-4105）
Excelの初期設定。セルの値が変更されると自動的に再計算が行われます。

手動計算 = xlCalculationManual（-4135）
自動的には再計算されず、ユーザーが再計算を実行したときにのみ更新されます。
VBAでは Calculate メソッドを呼び出した際に再計算が行われます。

再計算の切り替え

再計算の切り替えは、Excelのオプション設定からも行えます。
そのため、ブックによっては手動計算に設定されている場合があります。
これは、再計算に時間がかかるケースで、セルの更新ごとに再計算を行わせることによる待ち時間を解消するために利用されています。

VBAでも同様に扱うことができます。処理中に必要なタイミングで再計算を実行できるため、必ずしも処理開始時に手動へ切り替える必要はありません。

ただし、処理中にセルの更新が多数発生し、そのたびに数秒の待ち時間が積み重なり、最終的に数分かかってしまうような場合には、このプロパティを「xlCalculationManual」に設定してから処理を実行し、最後に「xlCalculationAutomatic」に戻すことで待機時間をなくし、大幅な処理速度向上につながります。
なお、自動計算に設定した時点で再計算が実行されるため、別途再計算を行うコードを実行する必要はありません。

再計算の例外仕様

手動計算モードでは通常、セルの更新による再計算は行われませんが、一部例外として「データテーブル（What-If分析のデータテーブル）」は計算モードに関わらず常に再計算される仕様になっています。

ただ使用頻度がかなり低い機能であるため、かなり特殊なケースと考えてもらって問題ないので普段は再計算を手動にしていれば再計算が勝手に行われることはないと認識していてOKです。
再計算を抑止することができないので、もし使用されたファイルを処理化する場合は再計算されることを念頭に置いて処理作成を行ってください。

ワークシートを再計算する

特定セルのみ再計算を実行する

特定のブックの全シートを再計算する

コードコピー

'必要なセルのみ再計算（高速）
Application.Calculate

コードコピー

'全セルを強制再計算（重い処理）
Application.CalculateFull

再計算を行うにはApplication.Calculateメソッドを使用します。
このメソッドは、再計算が必要なセルだけを更新するため、高速に処理できます。

Excelに対して再計算の実行を行いますが、対象は開かれている全てのブックのうち再計算が必要なセルです。
全セルを強制的に再計算したい場合はApplication.CalculateFullメソッドを使用します。

このメソッドは全てのセルを対象とするため処理が重く、負荷が大きくなります。
更新漏れがなくなるという安心感はありますが、通常の処理では基本的にApplication.Calculateメソッドの利用が推奨されます。

利用場面としては、処理中に再計算を手動にして停止している状態で、一度再計算を実行しておきたい時などが挙げられます。
処理の最後に1回だけ実行するなど頻繁に利用しない場合は、実際に使ってみて動作の重さが気になるようであれば切り替える形でも良いでしょう。
なお、自動計算を処理中に手動計算へ切り替えている場合は、自動計算に設定を戻せば再計算されるため、このメソッドを使う必要はありません。

なお指定のブックのみを再計算する場合は、Workbookオブジェクトに直接「Calculate」メソッドは存在しません。
そのため、対象ブックの全シートをループして「Worksheet.Calculate」を実行する方法が一般的です。
同様に、セル範囲単位では「Range.Calculate」を使えます。
必要に応じてループ処理を組み合わせることで柔軟に再計算を制御できます。
詳しくは以下の記事で解説しています。

特定のブックの全シートを再計算する

コードコピー

'警告音を鳴らす
Beep

警告音を鳴らすには、「Beep」ステートメントを使用します
このコードを実行するとシステム設定された警告音を鳴らすことが出来ます
この音の設定はシステム設定になるのでVBAから任意に変更することは出来ません

あまり落とを鳴らすというような処理は好まれない場面が多いとは思いますが、例えば何らかの資料を見ながら入力処理を行う場合に、画面上で注意表示をしてもユーザーに認知されない可能性があります

またメッセージボックスでも音を鳴らすことが出来ますが、処理が停止するため入力処理の作業がそこで停止してしまいます
メッセージを確認しないと次の入力も行えません

入力値が制限外のデータであれば、それで構わないのですが、例えば数字を入力する際に前回入力値との差が10％以上あったら注意を促したい場合には有効です

その差が織り込み済みであれば、無視して操作を続けられるし、音が鳴ればユーザーは一度は画面を確認しますので入力時点で異常の検知や間違いに気づくことができるかもしれません

何らかの注意を促す場合に最低限で使用するようにしましょう

コードコピー

'このExcelブックのフォルダを初期表示する
Application.FileDialog(msoFileDialogFilePicker).InitialFileName = ThisWorkbook.Path

「FileDialog.InitialFileName」プロパティでは、ダイアログの表示時に初期表示されるフォルダを指定することが出来ます
このプロパティに指定フォルダまでの絶対パスを代入することで、設定を行うことが出来ます

このプロパティを設定しない場合は、カレントフォルダという作業中のフォルダが指定されます

ダイアログを使用する場合に、ある程度フォルダ位置が限定できている場合は使用するようにするとユーザーにやさしい設計になります

記事コードでは、このコードを記載したExcelブックのあるフォルダを指定しています

それがコード右辺の「ThisWorkbook.Path」という部分です

「ThisWorkbook」というオブジェクトは、このVBAが作成されているExcelブックのことになります
ActiveWorkbookは可変ですが、こちらは常に固定のExcelブックを取得できるので便利です

「Path」は、そのオブジェクトの絶対パス文字列のプロパティです
ファイルのあるフォルダ名までが取得されますので、こういった同じ場所を指定するには便利なプロパティです

コードコピー

'単一選択のみ可能にする
Application.FileDialog(msoFileDialogFilePicker).AllowMultiSelect = False

ファイル選択のダイアログボックスで、1つのファイルのみ選択可能にするには、「FileDialog.AllowMultiSelect」プロパティに「False」を設定します
この設定を行った後に、Showメソッドを使用してダイアログを表示します

初期設定では、複数のファイル選択が可能（Trueの状態）です
フォルダの選択は複数選択ができませんので、このプロパティはファイル選択のダイアログボックスで使用します

通常、複数選択であっても処理する対象が1つで、インデックスが1番目のもののみを行っていればエラーが発生することはありません

ただ、この複数選択を行った場合はインデックスの1番目に来るものが最初に選択したものにはならないので想定外のファイルが指定される可能性があります

そういったことを無くすために、最初から単一のファイル指定しか行えないようにする場合に使用します

コードコピー

'ダイアログのタイトル文字列の設定
Application.FileDialog(msoFileDialogFilePicker).Title = "タイトル"

「FileDialog.Title」プロパティでは、表示されるダイアログボックスのタイトル部分の文字列の設定を行えます
このプロパティ変更後に、Showメソッドでダイアログを表示することで確認できます

コードを実行して、ダイアログを表示すると枠内のタイトル部分の文字列が変更されます

ダイアログボックスの種別には影響はしないので、どの種別を使用しても問題ありません

簡単な操作の説明をここに表示することで、ユーザーが今なんのために選択をしているかが分かり易くなります

逆に名前を付けて保存のダイアログを変えてしまうと、かえって分かりにくくなる場合もありますので、使用頻度はあまり無いかもしれません

コードコピー

'2003までのExcelファイル形式のみ有効にする
Application.FileDialog(msoFileDialogFilePicker).Filters.Add "旧Excelファイル", "*.xls"

ダイアログボックスでファイル選択を行う際に、指定の種類のファイルのみに限定する場合は「FileDialog.Filters.Add」メソッドを使用します
このメソッドは設定を変更するだけです、この後にShowメソッドでダイアログを表示させて確認してください

通常未設定の状態では、全てのファイルを指定することができます

ただ、Excelファイル以外のファイルを指定しても開く場合はExcelによって開かれるのでデータによっては内容が変化したり、欠損したりします

また、処理上必要なファイルに限定しておかないとバグの元にもなります
ファイルを開くダイアログの場合は基本的には使用するメソッドです

画像の様にコードで設定後に、ダイアログを表示することで制限がかかっていることが分かります

この状態であれば、限定された拡張子しか表示されないので後続の処理に影響を与えません

画像枠内の「旧Excelファイル(*.xls)」が引数に指定した内容になります

Application.FileDialog(msoFileDialogFilePicker).Filters.Add "旧Excelファイル", "*.xls"

1つ目の引数の文字列が、説明用の文字列です
2つ目の引数の文字列が、許可するファイルの拡張子です

これは、任意の文字列になるのでWordファイルと表示しても.xlsで指定することは可能です、混乱するだけですが

コードコピー

Application.FileDialog(msoFileDialogFilePicker).Filters.Add "Excelファイル", "*.xls;*.xlsx"

複数の種別を指定する場合は、この様に「;」を入れてから別の拡張子を指定することで可能です

コードコピー

'ファイル選択ダイアログでキャンセルした場合に分岐する
If Application.FileDialog(msoFileDialogFilePicker).Show = -1 Then
MsgBox Application.FileDialog(msoFileDialogFilePicker).SelectedItems(1)
Else
MsgBox "キャンセル実行"
End If

ダイアログボックスを表示した際に、指定の操作を行わなかった場合（キャンセルを行った場合）にそのまま処理を継続はできません
取得するアイテムが存在しないためエラーになります

その場合はキャンセルされた場合の処理分岐を作成する必要があります
それが、例コードになります

If Application.FileDialog(msoFileDialogFilePicker).Show = -1 Then

まず最初のIF条件文の箇所です
これはダイアログの表示と同時に、戻り値を判定基準にしています

ダイアログは選択されると「-1」を返します

なので、これを条件にすることで選択されたかどうかが判定できます
このコードでいえば、「-1」（選択した）であれば真（True）となります

MsgBox Application.FileDialog(msoFileDialogFilePicker).SelectedItems(1)
Else
MsgBox "キャンセル実行"
End If

残りのコードは、分岐によって処理される部分です

1行目は真の時に実行されるので、選択された状態であることが確認されているので
その指定ファイルの絶対パスをメッセージに表示します

Else以降は偽（False）の場合になるので、キャンセルされた場合に当てはまりますので、メッセージでキャンセルを知らせます

このコードは、ダイアログ全ての種類で有効です
名前を付けて保存のダイアログでも、キャンセルすればこの条件分岐で処理できます

選択個数で分岐

一応、選択個数で判定を行うこともできます
すこし条件文が冗長になるので、ここで解説しておきます

コードコピー

'ファイル選択ダイアログでキャンセルした場合に分岐する
If Application.FileDialog(msoFileDialogFilePicker).SelectedItems.Count >= 1 Then
MsgBox Application.FileDialog(msoFileDialogFilePicker).SelectedItems(1)
Else
MsgBox "キャンセル実行"
End If

データの選択個数を取得するプロパティを使用します

その個数が1以上であれば選択していると判断します
なので、「>= 1」として1以上なら真になるように条件をたてます

どちらの条件分岐でもいいのですが、少し長くなるこちらはあまり意味はないかもしれません
指定したときに「-1」が返されることが分かっていれば、トップのコードでいいですね

コードコピー

'選択したファイルを開く
Application.FileDialog(msoFileDialogOpen).Execute

コードコピー

'保存を実行する
Application.FileDialog(msoFileDialogSaveAs).Execute

ダイアログボックスでの指定を行った後に、ファイルを開くか名前を付けて保存を実行するには「Application.FileDialog().Execute」メソッドを使用します

このコードは単体では意味はありません
事前にユーザーにダイアログで指定を行ってもらう必要があります
そのダイアログの表示に関する解説は以下の記事にあります

ダイアログボックスの表示（FileDialog.Showメソッド）

表示したダイアログの種類と同じものにメソッドを使用すると実行されます

Application.FileDialog(msoFileDialogOpen).Show
Application.FileDialog(msoFileDialogOpen).Execute

この様に、Showメソッドの後に実行する事で操作が実行されます
ただ、Showメソッドでキャンセルを行ったり、開く操作を対象とした以外の行動をしている可能性もありますので、その辺は対応に合わせてプロパティを変更していく必要があります

コードコピー

'ファイル選択で指定したファイル1つ目の絶対パスを取得
Debug.Print Application.FileDialog(msoFileDialogFilePicker).SelectedItems(1)

コードコピー

'ファイル選択で指定したファイルの個数を取得
Debug.Print Application.FileDialog(msoFileDialogFilePicker).SelectedItems.Count

ダイアログボックスで指定したデータの絶対パスを取得するには、「SelectedItems」プロパティを使用します

このプロパティは、指定したデータの絶対パスが文字列で返されます

また、ダイアログボックスで複数選択していた場合は、引数の数値がインデックス番号になっているので数値を変更することで取得していくことができます

このプロパティには、さらにプロパティがあります
その場合はこのプロパティがオブジェクトに切り替わるわけですが、あまりどうでもいい話です

いくつかありますが、使用するのは「Count」プロパティです

Application.FileDialog(msoFileDialogFilePicker).SelectedItems.Count

このコードではユーザーがダイアログで選択したデータの個数が取得できます

ダイアログの複数選択は個数が可変なので、いくつになるか分かりませんので処理をする際にこのプロパティで個数を取得することで、選択した全てを処理することが出来るようになります