Excelで数式が設定されているセルを探すには
数式が設定されているセルは「=SUM(A1:A10)」のように必ず「=」から始まります。
そのため、Excelの検索ダイアログで「検索する文字列」に「=」を入力して検索を行うと数式セルを探すことは出来ます。
ただし、文章などの一部として「=」が設定されているセルも検索対象となってしまいます。
結果として、Excelの検索ダイアログを使った場合では１セルずつ数式かどうかの判別しながら対応が必要になるため、時間が掛かることになります。
なお、セルに数式を表示したい場合はDisplayFormulasプロパティを使って表示することも出来ます。DisplayFormulasプロパティの詳細は「VBAでセルに数式を表示する」をご参照ください。
VBAで数式セルかどうかを判定する方法
VBAを使って数式セルを探す場合、RangeオブジェクトのHasFormulaプロパティを利用します。セルが数式であればTrueを返し、そうでなければFalseを返します。
他の判定方法としては、セルの数式を表すRangeオブジェクトのFormulaプロパティを利用する方法もできなくはありません。
ただ、Formulaプロパティだけでは本当に関数などの数式かどうかは判定できません。なぜかというと、セルの表示形式によって数式ではない場合があるからです。
セルの表示形式が文字列の場合はFormulaプロパティで取得した値は数式のように見えますが実際には文字列です。そのためFormulaプロパティとセルの値であるValueプロパティの値は同じになります。
それらを考慮すると、Formulaプロパティの一番左の文字が「=」の場合で、かつ、Valueプロパティと異なる場合は数式とみなすことが出来ます。
HasFormulaプロパティを使うのが簡単ですが、Formulaプロパティでの考え方も知っておいて損はないので、両方のコードを紹介します。
HasFormulaプロパティで数式が設定されているセルを探すコード
アクティブシートで利用されているセル範囲を１セルずつループして、HasFormulaプロパティで数式セルの場合にセル座標をメッセージボックスに出力します。
ループのActiveSheet.UsedRangeはシートで利用されているセル範囲を指します。そのため不要なセルを検索しないため高速に処理が行われます。
イミディエイトウィンドウとメッセージボックスの２通りで出力していますが、イミディエイトウィンドウだけでいい場合はメッセージボックスの出力はコメントアウトするなどで編集してください。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Sub SearchFormula() Dim r As Range '// 1セル Dim adrs '// セル位置 Dim msg '// 出力メッセージ '// 入力セル範囲をループ For Each r In ActiveSheet.UsedRange '// 数式セルの場合 If r.HasFormula = True Then adrs = r.Address(False, False) msg = msg & adrs & vbCr End If Next '// メッセージボックスに出力 Call MsgBox(msg) End Sub |
Formulaプロパティで数式が設定されているセルを探すコード
アクティブシートで利用されているセル範囲を１セルずつループして、Formulaプロパティの一番左が「=」で、かつ、Valueプロパティと不一致の場合は数式セルとみなしています。
あとは上のHasFormulaプロパティを使ったコードと考え方は同じです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Sub SearchFormula() Dim r As Range '// 1セル Dim f '// Formulaプロパティ値 Dim v '// Valueプロパティ値 Dim adrs '// セル位置 Dim msg '// 出力メッセージ '// 入力セル範囲をループ For Each r In ActiveSheet.UsedRange '// FormulaプロパティとValueプロパティを取得 f = r.Formula v = r.Value '// 一番左が「=」で、かつ、FormulaプロパティとValueプロパティが異なる場合 If Left(f, 1) = "=" And f <> v Then adrs = r.Address(False, False) msg = msg & adrs & vbCr End If Next '// メッセージボックスに出力 Call MsgBox(msg) End Sub |
使い方
数式を検索したいシートをアクティブにして、上のSearchFormula関数を実行します。２つありますがどっちでも結果は同じです。
実行後、VBA画面のイミディエイトウィンドウとメッセージボックスに数式セルの座標が出力されます。
結合セルに数式が設定されている場合は、結合セルの中で一番左上のセルの座標が出力されます。