カテゴリー: ExcelVBA

ExcelVBAに関する記事

Excelの再計算モードを自動／手動に切り替える

Application.Calculationで再計算モードを制御し、手動・自動を切り替えるVBAコード

'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です。
再計算を抑止することができないので、もし使用されたファイルを処理化する場合は再計算されることを念頭に置いて処理作成を行ってください。

関連の記事

Excelの再計算の実行（全ブック）
ワークシートを再計算する
特定セルのみ再計算を実行する
特定のブックの全シートを再計算する

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

セル単位の再計算は Range.Calculate。Range(“A1”).Calculate のコード例で基本を解説します

'A1セルのみ再計算する
Range("A1").Calculate
'指定シートのA1セルのみ再計算する
Worksheets("指定シート").Range("A1").Calculate

セルのみを再計算するには Range オブジェクトに対して 「Calculate」 メソッドを使用します。
このメソッドは 指定するオブジェクトによって再計算の範囲を細かく制御できます。

コードのようにセル範囲を指定することで、指定したセルだけを再計算できます。

手動計算にしている状態で、シート全体の再計算では時間がかかり無駄が生じる場合に、セル単位で限定的に再計算箇所を指定できます。

Excelでは基本的に自動で再計算されますが、処理負荷が大きく遅延が発生する場合や、一部のセルだけを更新して他の計算結果に影響を与えたくない場合には、セル単位で再計算を実行する方法が役立ちます。

特に、このサイトの基本理念である「既存データを改変しない」という観点からも、重たい関数に直面する場面は多々あります。
こうした場合には再計算の抑制をうまく活用し、効率的に処理を進めていきましょう。

Worksheets("指定シート").Range("A1").Calculate

例コード2つ目では、特定のワークシートを指定しています。
1つ目の例コードでは Worksheet オブジェクトを省略しているため、その場合はアクティブシートが対象となります。
アクティブではないシートや、処理上アクティブシートが変動する可能性がある場合には、Worksheet オブジェクトを明示的に指定しておくと処理が安定するためおすすめです。

関連の記事

Excel再計算の実行（全ブック対象）
ワークシートを再計算する
Excel再計算の実行（全ブック対象）
Excelの再計算の自動・手動の切替

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

ワークシート単体を再計算するには Worksheet.Calculate。コード例で基本的な使い方を解説します。

'アクティブシートを再計算
ActiveSheet.Calculate
'特定のシートを再計算
Worksheets("Sheet1").Calculate
'指定のブックの特定のシートを再計算
Workbooks("Book1.xlsx").Worksheets("Sheet1").Calculate

ワークシートの再計算を行うには 「Calculate」 メソッドを使用します。
このコードを実行すると、アクティブシートに含まれる関数がすべて再計算されます。
処理途中で計算方法を手動に切り替えている場合など、コード内で関数計算を更新したいときによく利用されます。

ワークシートオブジェクトを指定することで、特定のシートだけを再計算することも可能です。
2つ目の例では、ワークシート名を指定することで対象シートを限定しています。

さらに、3つ目のコードのように ブックを指定することで別のブックのシートを再計算させることもできます。
この方法を応用すれば、特定のブック内の全シートをまとめて再計算することも可能です。
詳細は以下の記事で解説しています。

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

すべてのブックやセル単位で再計算を行うことも可能です。
詳しくは以下の記事で解説しています。

Excelの再計算の実行（全ブック）
特定セルのみ再計算を実行する

関連の記事

Excelの再計算の自動・手動の切替

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

特定ブックの全シートのみを再計算する方法です。
全ブック再計算より処理時間を短縮できます。

'ループ用の変数宣言
Dim zz対象Ws As Worksheet

'特定のブックの全シートを再計算
For Each zz対象Ws In Workbooks("Book1.xlsx").Worksheets
zz対象Ws.Calculate
Next zz対象Ws

再計算を行うには対象のオブジェクトに対して「Calculate」メソッドを使用します。
ただし、Workbookオブジェクトにはこのメソッドが存在しません。

そのため、複数のブックを開いている状態で特定のブックだけを再計算するには、対象ブック内の全シートをループして「Worksheet.Calculate」を実行する必要があります。

このコードを利用すれば、複数のブックを開いている場合でも特定のブックだけを再計算することが可能となり、処理に時間のかかる不要なブックの再計算を除外できます

コード解説

コードの流れは非常にシンプルです。
対象のブック内の Worksheet オブジェクトをループ処理し、それぞれに対して再計算を実行するだけです。

'ループ用の変数宣言
Dim zz対象Ws As Worksheet

まずはループ処理で使用するための変数を宣言します。
この変数には Worksheet オブジェクトを代入するため、型は Object 型でも構いませんが、Worksheet 型にしておくとコード入力時にインテリセンスが利用できるので効率的です。

'特定のブックの全シートを再計算
For Each zz対象Ws In Workbooks("Book1.xlsx").Worksheets
    ～～～
Next zz対象Ws

次にループ処理の部分です。
For Each ループを使うことで、特定の Workbook に含まれるすべての Worksheet を順番に処理できます。

ここで注意すべき点として、Excel のシートには複数のオブジェクトが存在します。
主に Worksheets と Sheets があり、特に後者の Sheets オブジェクトはマクロ記録でよく利用されます。
ただし、通常のコード記述では Worksheets を明示的に扱う方が分かりやすく、インテリセンスも効くため効率的にコード作成が行えます。

また、Workbooks(“Book1.xlsx”) の引数の文字列を対象とするブックの名前に変更すれば、任意のブックを指定することができます。

For Each zz対象Ws In Workbooks("Book1.xlsx").Sheets
    ～～～
Next zz対象Ws

このコードのように Worksheet オブジェクトではなく、Sheets オブジェクトとしても多くの場合問題なく動作します。

ただし Sheets オブジェクトには、グラフシートやマクロシートなど通常の Excel で使用するワークシート以外のものも含まれます。
そのため再計算を目的とする場合は、Worksheet オブジェクトを指定する方が適切です。

zz対象Ws.Calculate

ループ内の処理は Worksheet オブジェクトに対して Calculate メソッドを実行しているだけです。

このようにすることで、複数のブックを開いている状態でも特定のブックのみの全シートを再計算させることができます。
使いどころは限定的ですが、知っていれば処理時間を短縮できる場合もあるため有用です。

「全ブックまとめて再計算したい」という方は、以下の記事をご確認ください。

Excelの再計算の実行（全ブック）

関連の記事

Excelの再計算の実行（全ブック）
ワークシートを再計算する
特定セルのみ再計算を実行する
Excelの再計算の自動・手動の切替

Excel再計算の実行（全ブック対象）

Application.Calculate は開いている全ブックの必要セルのみを再計算します。Application.CalculateFull は全セルを強制再計算します。

'必要なセルのみ再計算（高速）
Application.Calculate
'全セルを強制再計算（重い処理）
Application.CalculateFull

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

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

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

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

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

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

関連の記事

ワークシートを再計算する
特定セルのみ再計算を実行する
Excelの再計算の自動・手動の切替

セル内文字列を置き換えする

セルに入力された文字列の置き換えについてのコードです（完全・部分一致）

'ABC列で部分一致の検索置換を実行する
Range("A:C").Replace What:="検索文字", Replacement:="置換文字", LookAt:=xlPart
'ABC列で完全一致の検索置換を実行する
Range("A:C").Replace What:="検索文字", Replacement:="置換文字", LookAt:=xlWhole

セル内文字列の置き換えには「Replace」メソッドを使用します
Excelの標準機能と同じように、セルに入力された内容と条件一致するものを検索して、見つかった場合に置き換えを実行する際に使用します

引数の「What」が検索対象の文字列です
置き換えする文字列は「Replacement」に指定します
この2つの指定により、任意の文字列を検索して置き換えを実行することができます

引数「LookAt」の設定で部分一致か完全一致かの選択ができます
部分一致の場合は「xlPart」を設定し、完全一致の場合は「xlWhole」と設定します
検索処理でも同じ事を言えますが引数の設定は前回実行した設定が引き継がれます
なので引数の設定は必要のない部分でも一応設定しておくほうが安心です
部分一致と完全一致が意図せず入れ替わっていたりします

完全一致とすれば、セルの中にある文字列全体が検索と全く同じ時のみ置換の対象になります
部分一致にすれば、セルの中にある文字列の一部だけを置換することが出来ますが、複数当てはまる場合は全て置換対象になる点だけ注意してください
あまりにも短い文字を検索対象にしていると、意図しない変換が行われてしまいます

テキストファイルを一括読み込みする

FileSystemObjectを利用して、テキストファイルを一括で読み込みを行うコードについて

'FSOの宣言
Dim bhFSO As Object, bhFSOT As Object
Set bhFSO = CreateObject("Scripting.FileSystemObject")
'指定テキストファイルを開く
Set bhFSOT = bhFSO.OpenTextFile("指定テキストファイルの絶対パス")

'開いたテキストを1つのデータに一括読み込み
Dim bhTxt As String
bhTxt = bhFSOT.ReadAll

'FSOの解放
Set bhFSOT = Nothing
Set bhFSO = Nothing

テキストファイルの文字列を1つのデータに一括で読み込みをするコードです
通常VBAでテキストファイルを読み込む場合は以下のInputステートメントを利用します

テキストデータからデータを取得（Input#）

ただ、このコードの場合にはデータが配列として分割されます
たとえばテキストデータ全体で検索や置換をしたい場合にそのようなデータとなった場合配列をループでまわして検証する必要が出てきてしまいます
またあえて1セルにまとめて入力したい場面も想定されます
これは新しいテキスト関数が非常に便利になったので実務上有用です

そんな場合に記事コードを利用します
このコードを利用すると文字列変数に一括でデータを読み込むことで
全体的な処理や関数処理が行いやすくなります

印刷時のカラーとモノクロを切り替える

特定のシートの印刷時のカラーとモノクロの印刷配色設定を切り替えるコードについて

'モノクロ印刷に設定する
Worksheets("Sheet1").PageSetup.BlackAndWhite = True

「Sheet1」をモノクロ印刷にする

'カラー印刷に設定する
Worksheets("Sheet1").PageSetup.BlackAndWhite = False

「Sheet1」をカラー印刷にする

印刷を行う際にカラーかモノクロかを切り替えるには、SheetオブジェクトのPageSetup.BlackAndWhiteプロパティを利用します

このプロパティにTrueを設定することでモノクロ印刷となり
Falseを設定することでカラー印刷となります

印刷する際に確認用はモノクロで、提出用にカラー印刷を行うような場合の処理作成で活用できます
プリンターのプロパティから設定をいちいち切り替える必要が無くなります
そもそもプリンターのプロパティはVBAでは設定を変更できません

モノクロ印刷時の印刷プレビューの画面の画像
モノクロ印刷時の印刷プレビュー(設定：True)
カラー印刷設定時の印刷プレビューの画像
カラー印刷設定時の印刷プレビュー(設定：False)

上記の画像の通りで、プロパティをそれぞれに設定することで簡単に印刷時の配色設定を変更することが出来ます

この設定はページ設定になるのでプリンターでの印刷だけでなく
PDF出力に対しても有効な点は注意が必要です

指定セル以下の行を全て削除する

指定セル以下の行全てを削除・消去するコード、見出しを除外した範囲全てを初期化する際に使用します

'2行目以降を削除
Range(Range("A2"), Range("A" & Cells.Rows.Count)).EntireRow.Delete
'2行目以降を消去
Range(Range("A2"), Range("A" & Cells.Rows.Count)).EntireRow.Clear

関数などで参照しているセル範囲である場合にはこちらを利用してください

データ集計を行ったり、フィルタデータの貼り付けなどで元々あるデータは削除してデータを貼り付けする処理はよくあります
この際に見出し行は削除したくない場合があります
そんな場合に使用するコードです

指定セル以下の行全体を削除する動き
コード実行時の動き

このコードを使用すると2行目以降のすべてのセルを削除しますが、1行目はそのままなので見出しを再度作成したりする必要が無くなります

ちなみに画像ではテーブル機能のデータ範囲以下全てを削除しています
テーブル機能の場合はデータが無くても、データ範囲1行目には背景色の設定が表示されますので、実際にはちゃんと削除されています

データ範囲のみを選択する場合は以下の記事で解説しています

見出しを除外したデータ範囲選択

ですがこの選択範囲はデータ行のみになるので、もし未保存の削除セルがあった場合LastCellなどでうまく最終セルが取得できない場合があったり、コピー元のセル範囲がそういったセルを作成してしまっていたりすることがあるので、なるべく例コードの様にデータ範囲を初期化する場合は全てのセル範囲を指定する方が無難です

コード解説

Range(Range("A2"), Range("A" & Cells.Rows.Count)).EntireRow.Delete

このコードは1行ステートメントですが、複数のプロパティを使用しているのでプロパティごとに分割して解説を行います

Range("A2").Select
コードを実行した場合

そのRangeオブジェクトの1つ目の引数はこれまたRangeオブジェクトとなっています
その引数にはA2が指定されています
なのでこの引数でSelectメソッドを実行すると画像のセルが選択されます
ここが始点のセルになるということですね
なのでここのA2を変更することで任意の行数に変更することが出来るということになります
A3にすれば3行目以降全てが削除対象となり、1・2行目はそのまま保持される事になります

Range("A" & Cells.Rows.Count).Select
コード実行後のセル選択

次の2つ目の引数にもRangeオブジェクトが指定されていますが、今度は単純にセルAddressが入力されているわけではありません

「Cells.Rows.Count」というのは、このシートに存在するセル範囲の行数を数えています
その数字の前にAが入力されているので、A列の最終行が指定されます
画像の通りで行数値は「1048576」となりA1048576セルが選択状態になっています
ここが終点セルとなります、要は以降全部ってことですね

Range(Range("A2"), Range("A" & Cells.Rows.Count)).Select
範囲選択された状態

この2つを始点と終点としてセル範囲指定をすることで、A2からA1048576までが範囲選択された状態となります
画像の様に2行目以降がすべて選択されていることが確認できます

Range(Range("A2"), Range("A" & Cells.Rows.Count)).EntireRow.Select
対象範囲が選択された状態

ここまでの対象範囲の取得ではA列だけになってしまいます
そこでこの対象範囲を最終列まで広げていきます

EntireRowプロパティは、A列だけの範囲を列方向に広げてくれます
これを使用することで画像の通りに最終列まで範囲を広げることが出来ます

Range(Range("A2"), Range("A" & Cells.Rows.Count)).EntireRow.Delete

最後にDeleteメソッドを使用して、その範囲を削除します
上記までで指定行範囲を取得できているので、そこに対して削除を実行します

この範囲を別のシートで関数等で参照している場合、セル参照がエラー値（#REF!）になってしまいます
その場合はDeleteメソッドではなくClearメソッドを使用することでセルの初期化を行うことが出来ます

それが例コードの2つ目のものです
単純にメソッドをClearメソッドに変更しただけですが、これを行うと書式含めすべてが初期化されるので、見た目的には削除とほぼ同等の効果です

上記に記載していますが、このメソッドではセルが削除されないため、関数などの参照を行っている場合に自動的に範囲が修正されたり「#REF!」エラーになることを防ぐことが出来ます

作り込みの凄まじい関数ファイルなどで処理を行う場合はこちらを利用しておいた方が無難です
なんせ作り込まれた関数ファイルはVBAより遥かに難解ですので

なおブックのサイズにもよりますが、こういった削除処理を行った際は処理最後にでもブックの上書き保存を行っておいた方が良いです

なぜなら、削除したセル範囲のLastCellなどはそこでしか更新されないためです
またこれを行うことで、スクロールバーの可動域も新しいデータ範囲に調整されますので、あまりにも削除前とのデータ範囲に差がある場合にはユーザーにとっても意味のある保存と言えます

ただ保存はサーバー上であったり、ブックサイズが大きいと時間がかかってしまい
待ち時間がユーザーにとってのストレスになることもあることは認識しておきましょう

個人用マクロブックを読み込まない

個人用マクロブックが読み込みされなくなった時の対処について

ファイルタブを選択

まずは落ち着いてExcelを起動してください
そしてファイルタブを選択します

オプションを選択

次に左側メニューの一番下のオプションをワンクリックします
焦ってダブルクリックしてしまっても問題無いので落ち着いてクリックしましょう

アドインを選択

開いたウィンドウのExcelのオプションのメニューからアドインを選択します
手の震えを無理に抑えなくても…（しつこい）

オプションを選択

表示された画面の下部にある、管理項目を確認します
ここの選択肢から画像の「使用できないアイテム」を選択して、設定ボタンをクリックします

設定の確認画面

ここに「PERSONAL.XLSB」があれば、選択して下にある有効にするボタンをクリックしてください

これで再起動すると読み込みが正常に行われるようになります
ここにない場合は、マクロブックが存在していないかもしれません
こうなったらお手上げですね…この操作で復活出来ることを祈っております

先日発生した話

個人用マクロブックとは、そのPCにおいてExcelを起動すると自動的に読み込みされる
マクロ保存用のブックの事です。
個人用と名前の通りで、個人で利用するには非常に便利な機能です。

個人用のファイルは特定の個所に保存され、初期設定の状態でマクロセキュリティでは安全な場所として登録されているため起動時のマクロセキュリティ確認も必要ありません。
特に作成用のコードを保存しておくなど、作成に役立てる目的でも自分はよく使います。

そんな便利なファイルですが、いきなり使用が不能となりました。

これはかなり愕然としました
上記にあるように自分の仕事PCの個人用マクロブックはまさにこのサイトにあるような自分の知識の蓄え場でもあるため、結構な量のコードやフォームが存在しています。
日々仕事の中で作成した処理などで閃いたことや後に残しておきたいものがそこには貯め込まれています。

これを一気に失ったのです。

何か月もかけて作ったExcelVBA処理の比では無く
マジでくじけそうになるほどの衝撃でした。
「からっぽやないかい…」
「ゴミ箱も何も入ってへんやないかい…」

正直なところ、作成したExcelVBA処理ならあきらめもつきます
所詮はこのマクロブックからコピペなどして作成したモノですから

それほどの重要なファイルが消失したと思っていました

と、いうのも
このExcelの起動時読み込みには実際にはユーザー・Excel・Officeという３つの種類があります
これのうち、利用されるのが最初のユーザーになります

それも認識が無かったため、Excelの起動フォルダを確認しに行って
ファイルが存在していないと勘違いをしていました

実際にはユーザーの読み込みフォルダ内にファイルが存在することは確認できました

ところが、ところがです
この記事のタイトルの現象に気づいたのです
そこにファイルが間違いなく存在しているのに、なぜかExcel起動で読み込みが行われない

個人用マクロブックをダブルクリックで開いてあげると、VBAが実行できます

なので、もう一生こうして自動的に読み込まれていたファイルを手動で毎回起動しないといけないんだな、と半ば諦めかけていました
なぜならファイルが無くなっていなかったのでVBAコードが残っていた事に安堵していたからです
もうとにかく生きていてさえくれれば、それでええ…
そんな気持ちでした

ですが、ふと思い出しました
Google先生に聞いたら分かるんちゃうか、と

実際すぐに解決しました
とはいっても、画像のあるサイトが無かったので焦っていた時にはそのページを見ても
ちゃう、これが原因ちゃう
と、すぐに別のことを確認してしまっていました

結果無駄な時間を過ごすことになってしまいました

とにかく、ファイルが消失してなくて良かった～
VBA処理を作ったファイルはバックアップ取っていたけど、個人用マクロブックのバックアップは取っていませんでした
これを機に個人用マクロブックもバックアップを取るようにします
みなさんも個人用マクロブックを大切にして下さい