Show Formula Contest Automatically

[iv63]

Let’s say variables a and b are defined in cells B1 & B2. Cell B3 has a formula 2a+b/3 (=2*B1+B2/3). I want in cell C3 contest of this formula (2*1+2/3) not (=2*B1+B2/3). This can be done manually like this ="(2*"&TEXT(B1,0)&"+"&TEXT(B2,0)&"/3)".

The following Sub shows formula contest automatically.

Sub GetRefs()
Dim MyRange As Range, strFormula As String, strVal As String
With ActiveCell
strFormula = .Formula
For Each MyRange In .Precedents.Cells
With MyRange
strVal = """" & Range(.Address).Value & """"
strFormula = Replace(strFormula, .Address, strVal)
strFormula = Replace(strFormula, .Address(RowAbsolute:=False), strVal)
strFormula = Replace(strFormula, .Address(ColumnAbsolute:=False), strVal)
strFormula = Replace(strFormula, .Address(RowAbsolute:=False, ColumnAbsolute:=False), strVal)
End With
Next
MsgBox .Formula & vbCr & strFormula
End With
End Sub

I prefer Function instead of Sub. Is it hard to modify this code? Thank you,
iv

 

[PHV]

Replace this:
Sub GetRefs()
with this:
Function GetRefs()

and this:
MsgBox .Formula & vbCr & strFormula
with this:
GetRefs = .Formula & vbCr & strFormula

and finally this:
End Sub
with this:
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[iv63]

PHV:
Thanks for quick response. It seems it is not working - have you tried it?
Regards,
iv

 

[combo]

Add input argument to the function:

Function GetRefs(RefCell As Range)
Dim MyRange As Range, strFormula As String, strVal As String
With RefCell
' etc.

combo

 

[macropod]

The sub seems to be some code I posted somewhere. For a function, try:

Function GetRefs(Rng As Range) As String
Dim MyRange As Range, strFormula As String, strVal As String
With Rng
  strFormula = .Formula
  If InStr(strFormula, "=") = 0 Then
NoPrecedents:
    GetRefs = "No Precedents"
    Exit Function
  End If
  On Error GoTo NoPrecedents
  For Each MyRange In .Precedents.Cells
    With MyRange
      strVal = Range(.Address).Value
      strFormula = Replace(strFormula, .Address, strVal)
      strFormula = Replace(strFormula, .Address(RowAbsolute:=False), strVal)
      strFormula = Replace(strFormula, .Address(ColumnAbsolute:=False), strVal)
      strFormula = Replace(strFormula, .Address(RowAbsolute:=False, ColumnAbsolute:=False), strVal)
    End With
  Next
  GetRefs = strFormula
End With
End Function

which you can call with code like:

Sub Test()
MsgBox GetRefs(ActiveSheet.Range("A1"))
End Sub

Notes:
1. The original code errors-out if no precedents are found. The revised code does not;
2. Neither version of the code work as a UDF called from a worksheet (all you'll get back is the formula);
3. Named ranges are not handled in either version; and
4. Both versions of the code only work for references on the same worksheet as the passed cell's address.

Cheers
Paul Edstein
[MS MVP - Word]