Excel
VBA
ExcelVBA

数式中のセル参照を参照先のセルの値に置換し、セルの数式を文字列として返すExcel Worksheet関数

More than 3 years have passed since last update.

およびセルの数式を文字列として返すExcel Worksheet関数を作りました。

何言ってるのかよくわからないと思いますがこういうことです。

example.png

Excelで計算書などを作る場合にセルの中の数式が見えていないとヤダ!と言われるケースが多々あるので作りました。

機械的に置換しているだけで大した例外処理などは行っていません。特に範囲を参照している場合に対応していないことに注意です。

ちなみにExcel 2013からはセルの数式を返すFORMULATEXT()が実装されています。

' セルの数式を文字列として返す。

' rcにtrueを渡すとR1C1形式で返す。
Function CellFormula(cell As Range, Optional rc As Boolean) As String
If rc Then
CellFormula = cell.FormulaR1C1Local
Else
CellFormula = cell.FormulaLocal
End If
End Function

' 数式中のセル参照を参照先のセルの値に置換し、セルの数式を文字列として返す。
' 範囲を参照している場合は考慮していないので注意。
' 定義された名前は考慮していないので注意。
' 数式の中に参照のような文字列が存在する場合も考慮していないので注意。
Function CellIntermediateFormula(cell As Range) As String
Application.Volatile

Dim regExp As Object
Dim matches As Object
Dim match As Object

Dim i As Long
Dim strEvaled As String
Dim strTarget As String

strTarget = cell.FormulaLocal

Set regExp = CreateObject("VBScript.RegExp")
regExp.Pattern = "\$?[A-Z]+\$?[1-9]\d*"
regExp.IgnoreCase = True
regExp.Global = True

If regExp.Test(strTarget) Then
Set matches = regExp.Execute(strTarget)
For i = 0 To matches.Count - 1
Set match = matches(i)
strEvaled = cell.Worksheet.Range(match.Value).Text
strTarget = Replace(strTarget, match.Value, strEvaled)
Next i
End If

CellIntermediateFormula = strTarget
End Function