Rather than a formula entering userform, you could enter the formula normaly and use this UDF to extract the formula that you wanted.
FromulaReplace has optional RowOffset and ColumnOffset arguments.
It looks into a cell, reads the formula, and replace the cell references with (offset) values from those cells.
In the attached, =ReplaceFormula(D9, 0, -1)
takes =B3+B5+B7-(B2-B4-B6)
and returns "= b + d + f -( a - c - e )"
Function FormulaReplace(ByVal aCell As Range, Optional RowOffset As Long = 0, Optional ColumnOffset As Long = 0) As String
Const Space As String = " "
Dim formStr As String
Dim Operators As Variant, strOperator As Variant
Dim Terms As Variant, oneTerm As Variant
Dim oneCell As Range, i As Long
Application.Volatile
Operators = Array("+", "-", "*", "/", "^", "=", ":")
Set aCell = aCell.Cells(1, 1)
formStr = aCell.Formula
formStr = Application.ConvertFormula(formStr, xlA1, xlA1, xlAbsolute)
formStr = Replace(formStr, "(", "( ")
formStr = Replace(formStr, ")", " )")
For Each strOperator In Operators
formStr = Replace(formStr, strOperator, Space & strOperator & Space)
Next strOperator
Terms = Split(formStr, Space)
For i = 0 To UBound(Terms)
Set oneCell = Nothing
On Error Resume Next
Set oneCell = Range(Terms(i))
On Error GoTo 0
If Not oneCell Is Nothing Then
With oneCell
Terms(i) = Space & CStr(.Offset(RowOffset, ColumnOffset).Value) & Space
End With
End If
Next i
formStr = Join(Terms, vbNullString)
FormulaReplace = formStr
End Function
If you in VBA and looking for the precedents of a cell's formula, Range("D9").Precedents will return those cells as a range.
Bookmarks