Excelマクロで、セルの数式に別シート参照があるか調べる方法

Excelマクロの素人です。

セルの内容(数式)に別シート参照があるか調べる方法を調べています。

数式かどうかはHasFormulaで判定できますが、
数式で別シート(他のシート)を参照しているかどうかを調べたいです。

無理やりやろうとすれば「='シート1!'A1」などの
「!'」が含まれているかなどで判定することもできますが。。。
一般的にはどのように判定するものでしょうか。

別シートを参照していれば、値で貼り付けて、
そのシートからシート参照を無くしたいです。

No.2 ベストアンサー 回答者： WindFaller 回答日時： 2017/02/04 01:19

こんばんは。

>別シートを参照していれば、値で貼り付けて、
>そのシートからシート参照を無くしたいです。
値を貼り付けるのは別にして、私も困っていましたので、作ってみました。

試してみてください。なお、私の方は、[個人用マクロブック]の中に入れることにします。
クイックアクセスツールバーなどに入れる便利かもしれません。
ただし、SearchRefFormula　の名前は、「リンク検索」とかにしてください。探しやすいからです。

>一般的にはどのように判定するものでしょうか。
雑誌などで有名な人のマクロでは、Find メソッドを使っていましたが、ご質問者さんのHasFormula を活かすためには、以下のようなSpecialCells のほうがよいかと思いました。
*の部分は、私個人のマクロの部分です。なんとなく想像が付くと思います。

'//標準モジュールのみ
Sub SearchRefFormula()
　Dim wb As Workbook
　Dim sh As Worksheet
　Dim Rng As Variant
　Dim c As Variant, i As Long
　 Dim n As Long 'Static n As Long '*
　n = 1
　Const sFIND As String = "!"
　Const sFIND2 As String = "*.xls?]*"
'　If n = 1 Then
　　Set wb = ActiveWorkbook
'　End If
　For i = n To wb.Worksheets.Count
　　On Error Resume Next
　　Set Rng = Worksheets(i).Cells.SpecialCells(xlCellTypeFormulas, 23)
　　On Error GoTo 0
　　If TypeName(Rng) = "Range" Then
　　　Application.ScreenUpdating = False
　　　For Each c In Rng.Cells
　　　　If InStr(2, c.Formula, sFIND) > 0 Or _
　　　　　c.Formula Like sFIND2 Then
　　　　　c.Value = c.Value
　　　　　Beep
　　　　　'*
　　　　End If
　　　Next c
　　End If
　　Application.ScreenUpdating = True
　Next i
　n = 0
　If n = 0 Then
　MsgBox "完全に終了しました。", vbInformation
　End If
End Sub

'--------------
私のためのマクロは
　''c.Interior.ColorIndex = 38　'色を付ける場合
　''wb.Worksheets(i).Select 'シートを開く
　''Beep　'音が出る
　''End　　'止める
'-----------------

この回答へのお礼

ご回答、ありがとうございました。
何とか実装が完了しました。
とても参考になりました。
ありがとうございました。

お礼日時：2017/02/05 16:42

No.3 回答者： ママチャリ 回答日時： 2017/02/04 11:04

ご質問の内容自体が一般的でないため、当然、一般的な判定方法も無いと思います。

よって、_shuichi_さんの判定方法が正解と考え良いのではないでしょうか。

ちなみに、=A1+Sheet2!A1のように、ひとつの数式に自シートと他シートを同時に使用していた場合は、値で張り付けですか？
そうなると、シート内での整合性が保証できなくなります。
結果的に、すべて値張り付けする必要があるような気がするのですが、いかがでしょう？

この回答へのお礼

ご回答、ありがとうございました。
そうですね。「=A1+Sheet2!A1」のようなケースは想定していませんでした。
でも、幸いにそのようなケースは無かったので大丈夫でした。
大変参考になりました。ありがとうございました。

お礼日時：2017/02/05 16:45

No.1 回答者： くんこば 回答日時： 2017/02/03 23:51

「データ」タブ⇒「接続」のリンクの編集

で、できないですか？

この回答へのお礼

早速のご回答、ありがとうございました。
マクロで実行したかったので。。。
参考にさせていただきます。

お礼日時：2017/02/05 16:40