ExcelVBA – VBAの部品庫

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

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

'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出力に対しても有効な点は注意が必要です

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

特定のワークシートの関数を再計算させるコード

'アクティブシートを再計算
ActiveSheet.Calculate

'特定のシートを再計算
Worksheets("Sheet1").Calculate

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

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

ワークシートオブジェクトを任意に指定することで、特定のシートだけを再計算を行うことが可能です
2つ目の例コードはワークシート名を指定することで特定のシートを対象としています

また3つ目のコードの様に、ブックから指定することで別のブックのシートを再計算させることも可能です
これを応用することで、特定のブックの全シートの再計算を行うことが出来ます
以下の記事にて解説しています

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

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

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

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

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

複数のブックを開いた状態で特定のブックのみを再計算するコード

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

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

再計算を行うには対象のオブジェクトに対して「Calculate」メソッドを使用します
しかし、Workbookオブジェクトにはこのメソッドがありません
そのため、複数のブックを開いた状態で1つだけのブックを再計算するには少し工夫が必要となります
それがこの記事コードになります

このコードを利用することで、複数のブックを開いた状態であっても特定のブックのみを再計算することが可能となるので極端に時間のかかる再計算の不要なブックを除外することが可能になります

コード解説

コードの流れ自体は非常に簡単なものです
Worksheetオブジェクトをループで再計算を実行していくだけです

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

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

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

次にループ処理の個所です
ForEachループによって特定のWorkbookに含まれるWorksheetを全てループします

ここで大切なこととして、Excelのシートにはオブジェクトが複数あります
主にはWorksheetとSheetです
特に後者のSheetオブジェクトはマクロの記録でよく出てきます

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

このコードの様にWorksheetオブジェクトでは無く、 Sheetオブジェクトとしてもほとんどの場合問題なく動作します

このSheetオブジェクトにはグラフシートやマクロシートなどの通常Excelで使用する方眼紙シート以外のものも含まれます
そのため再計算を行う目的であれば、Worksheetオブジェクトを指定する方が良いです

そして、「Workbooks(“Book1.xlsx”)」の引数の文字列を対象とするブックの名前に変更すれば任意のブックを指定することが出来ます

zz対象Ws.Calculate

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

この様にして複数のブックを開いた状態で特定のブックのみの全シートを再計算させることが出来ます
使いどころが限定的ですが、知っていれば処理時間を短くすることも可能なので有用だと思います

全ブックまとめて再計算するほうがええねん、という方は以下の記事を確認してください

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

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

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

'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処理を作ったファイルはバックアップ取っていたけど、個人用マクロブックのバックアップは取っていませんでした
これを機に個人用マクロブックもバックアップを取るようにします
みなさんも個人用マクロブックを大切にして下さい

値の入力されたセルだけをクリアする

SpecialCellsメソッドの引数xlCellTypeConstantsを利用して値入力されたセルだけをクリアする方法について

'指定範囲内のみを対象とする
Range("A1:C3").SpecialCells(xlCellTypeConstants).ClearContents

'すべての使用済みセル範囲を対象とする
Cells.SpecialCells(xlCellTypeConstants).ClearContents

セル範囲の中で値の入力されたセルのみをクリアするには「SpecialCells」メソッドの引数「xlCellTypeConstants」を使用します

すでに関数によってある程度フォーマットが完成したシートに対して、入力値の部分だけを初期化する場合に利用します
単純にセル範囲全てをクリアしてしまうと、関数も消去されてしまうためです

なお、セル自体が数式が入力されているかを判定する方法は以下の記事で解説しています
ただ今回のような範囲に一括で処理を実行したい場合にはあまり向いていません

セル内容が数式かどうかを判定する

しかし、このメソッドもUsedRangeに影響を受けるため、既存のデータから上書き保存が許されないような処理の場合は記事コードはうまく動作しない場合がある点に注意が必要です
そのあたりに関しては以下の記事で解説していますので確認しておいてください

数式の入力されたセルだけを取得する

コード解説

画像の動きを確認してください
前提としてC列はA列＋B列の数式が入力されています
なので値入力された範囲はA2～B7となります

主にすでにあるフォーマットから入力範囲のデータを消去する場合に利用する場面が多いため記事コードでも前者の方が利用頻度は高いと思われます

'指定範囲内のみを対象とする
Range("A1:C3").SpecialCells(xlCellTypeConstants).ClearContents

このコードではA1～C3という範囲指定を行った上で、値のセルに対してClearContentsメソッドを実行して入力値を消去しています
画像ではA2～B3がその範囲に該当するため、そこが消去されています

こうして指定することでせっかく作った関数を消去せずに入力フォーマットを初期化できます

'すべての使用済みセル範囲を対象とする
Cells.SpecialCells(xlCellTypeConstants).ClearContents

こちらではシート全体のうち、使用済みセル範囲であるUsedRangeプロパティで取得される範囲の全ての値セルがクリアされます
この方法だと見出しや題目等の部分まで消去されるので、あまり利用頻度は無いかもしれません

ちなみにセル内に「=”見出し名”」というような入力を行えば、そのセルは数式と認定されるようになるので全ての範囲から値セルを消去しても問題なくなります
ただ全てを振り替えたり、修正があった場合に非常に面倒なので現実的な利用方法ではありません

この方法でセル範囲を限定すれば、ClearContentsメソッドを他のものに変更するだけで、値セルにだけ背景色を設定したりすることも簡単に行うことが出来ますので、アイデア次第で広がる汎用性のあるものだと思います
ただただUsedRangeに引っ張られる点を除けば、ですが・・・

数式の入力されたセルだけを取得する

SpecialCellsメソッドの引数xlCellTypeFormulasを利用して数式セルが入力されたセルだけを取得する

Debug.Print Cells.SpecialCells(xlCellTypeFormulas).Address

数式が入力されたセルを取得するには「SpecialCells」メソッドに引数「xlCellTypeFormulas」を使用します
この引数設定では数式が入力されたセル範囲を取得できます

このメソッドには利用時に注意点があります
それはUsedRangeプロパティが基準とされる点です
コードの解説とともに解説します

コード解説

記事コード含め合計4行を実行しています
前提として、シートの選択範囲が数式が入ったセルです
それ以外は空白セルは空白、数値が入っている部分とC列が数式です

記事コードが実行されると選択範囲と同じ範囲が取得されています
C2～C7のセル範囲が取得されます

ここで1つ注意したいのが、Cellsオブジェクトはワークシート全体のセル全てを指定しますが、内部的には自動的にUsedRangeプロパティに変換されます
ただこの引数設定ではあまり影響は無いかもしれません

ActiveCell.SpecialCells(xlCellTypeFormulas).Address

Range("A1").SpecialCells(xlCellTypeFormulas).Address

→→→　$C$2:$C$7

これら2行の指定でも同じくC列の入力範囲が取得されています
指定Rangeオブジェクトが単一の場合にはCellsオブジェクトと同様にUsedRangeプロパティに変換されて取得されます

ほとんどそういった利用は無いとは思いますが、数式かどうかの判定には利用できないですね

Range("A1:C3").SpecialCells(xlCellTypeFormulas).Address

→→→　$C$2:$C$3

ここではRangeオブジェクトで範囲指定を行っています
この場合はその範囲内で条件を満たすものが取得されます

A1～C3までのセル範囲のうち数式が入力されたセルはC2とC3となるため、その範囲が取得されています

この様に全ての範囲から取得を行うのではなく、限定された範囲から取得する場合はその範囲を指定してあげれば良いだけです

ただ今回の引数の際にはあまり影響は無いのですが、やはりUsedRangeプロパティの影響を受けています
指定範囲内であってもUsedRangeプロパティ外は検索対象に入りません

つまりIntersectメソッドを使ったときの様に、指定範囲とUsedRangeプロパティの範囲の重複する部分のみが対象となってしまいます

このメソッドを利用する場合にはとにかくUsedRangeプロパティへの注意が必要なため、以下の記事を参考にしてこのプロパティの動きをしっかり理解しておいてください

シートの使用済みセルを範囲選択

セル内容が数式かどうかを判定する

セルの入力内容が数式かどうかを判定するためのプロパティについて

'セルが数式かどうかを判定する（Trueなら数式）
Debug.Print Range("A1").HasFormula

セル内容が数式かどうかを判定するには「HasFormula」プロパティを使用します
Trueならセル内容は数式が入力されています、値等の数式以外であればFalseが返されます

セルの入力値には大きく分けて数式と値が存在しますが、それを判定するためのプロパティです
これはIs～～関数等のものではなく、Rangeオブジェクトのプロパティである点に認識が混同しないように注意が必要です

ちなみに数式と関数はこのプロパティ上は同一で判断します

コード解説

画像の動きを確認してください

まず前提としてB1セルにはA1セルに表示された数式が入力されています
ただ空白を返しているだけの数式になります
B2セルには何も入力されていません

なので空白かどうかの判定に関しては出力されるのは「True」が出力されています

これは空白チェック時の要注意点の1つなのですが、数式の結果が空白を返している場合は、セルが空白と混同して判定されてしまいます
この場合は空白かどうかを判定するだけでは不十分です

Debug.Print Range("B1").HasFormula

→→→　True

次にプロパティを利用した判定を取得してみます
この場合はB1には数式が入力されているため、Trueが返されます

ここでさらに空白チェックを複合的に条件を立てることにより、数式で空白が返されたものは除外することが出来ます

Debug.Print Range("B2").HasFormula

→→→　False

B2に関しては何も入力されていないため数式では無いのでFalseが返されます
ここは数式でなければ入力値があったとしても値であればFalseとなります

このプロパティを利用することで、指定セルから数式の空白を含めた空白を探すような特殊な場面で活用できます