Show Formula Contest Automatically

[iv63]

For example let’s have variables a and b 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)" (basically I want the VBA function that takes an active cell formula and substitutes values for the cell addresses, returning a text string). Macropod's Sub from thread707-1591952 worked "like a charm"

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

However I prefer Function instead of Sub. Is it hard to modify this code? Thank you for your time.
iv

 

[jges]

Why do you prefer Function instead of Sub?

 

[iv63]

BTW I have "improved" Macropod's Sub by replacing
MsgBox .Formula & vbCr & strFormula
with
ActiveCell.Offset(0, 1) = "'" & strFormula
I think with Sub you have to run Macro every time you change the formula's parameters. Correct? With function in cell C3 I would have =myFunction(B3) which will be updated automatically.
Regards,
iv

 

[PHV]

[!]Function[/!] GetRefs([!]theCell As Range[/!])
Dim MyRange As Range, strFormula As String, strVal As String
With [!]theCell[/!]
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
[!]GetRefs = strFormula[/!]
End With
End [!]Function[/!]

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

 

[iv63]

Thanks PHV for the quick response. Your function returns cell formula =2*B1+B2/3 instead of =2*1+2/3 (with values of cells B1 and B2).
Regards,
iv

 

[mintjulep]

PVH,

You'd think wouldn't you.

But that doesn't work.

If you invoke .precedents in a cell from a function it returns the precedents for the cell that the function is in.

 

[PHV]

So, in C3, you have [!]=GetRefs(B3)[/!] ?

 

[mintjulep]

Try this:

Function GetRefs (TheCell as Range)
GetRefs = TheCell.precedents.cells
End Function

Point it at any other cell with a formula in it.

 

[mintjulep]

oops.

Function GetRefs (TheCell as Range)
GetRefs = TheCell.precedents.cells.address
End Function

 

[SkipVought]

hi,

What would be the business case for such a requirement?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[electricpete]

It is something about the function that creates the limitation/maloperation of precedents. You get different results (the expected results) when you invoke precedents directly from immediate window or from subroutine....uUnless the subroutine is called from function or the immediate window occurs during function stop command... in which case you get the same limitation as when called from function.

 

[electricpete]

Skip - I can see where it might be helpful for showing where a result comes from. You could put an adjacent cell containing the formulas (with named ranges or addresses) and another adjacent cell showing values plugged into the formula (but not evaluated down to a final result)

 

[SkipVought]

Would it not be more enlightening to use Named Ranges, than merely numbers? It's more or less as close to 'self documenting' as you can get in some cases.

What is more instructive?
[tt]
=45*.25

or

=Rate*Time
[/tt]
No brainer!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[electricpete]

If only one I'd use named ranges (and I use them a lot).
But they are not mutually exclusive. What I described was above was using both (in separate cells).