Changing Formula elements to values

[Co-op Bank]

Hello, is there a way to change a formula for example '=a1+a2+a3' to show its
constant values instead in the cell so it would show '=10+20+30' assuming
'a1' = 10 etc...

Would this be a VBA solution? Any help suggestions would be much appreciated.

Regards
Brian
Manchester, England

[Gary''s Student]

Yes, it can be done in VBA:

1. Either code or down-load a simple table-driven parser for worksheet
constructs.
2. Run the parser on any cell to develop a table of cell references,
constants, named objects, etc.
3. Use the EVALUATE method on each item in the list to get the numeric value
4. Replace each item in the original expression with its equivalent numeric
value
5. Store the modified expression elsewhere.
--
Gary's Student


"Co-op Bank" wrote:

> Hello, is there a way to change a formula for example '=a1+a2+a3' to show its
> constant values instead in the cell so it would show '=10+20+30' assuming
> 'a1' = 10 etc...
>
> Would this be a VBA solution? Any help suggestions would be much appreciated.
>
> Regards
> Brian
> Manchester, England

[Bernie Deitrick]

Brian,

Select the cell with the formula, and run the macro below. It will change most cell references to
values, with the exception of multi-cell ranges. That would be do-able, but would require a
re-write.

HTH,
Bernie
MS Excel MVP

Sub Convert()
'Converts cell references to values within the
'Activecell's formula
'Written by Bernie Deitrick Dec 15, 2005

Dim strForm As String
Dim strOrig As String
Dim Addr As Variant
Dim i As Integer
Dim myCell As Range
Const Operators As String = "=+-*/^()"

strForm = ActiveCell.Formula
strOrig = ActiveCell.Formula

For i = 1 To Len(Operators)
strForm = Replace(strForm, Mid(Operators, i, 1), "*")
Next i

Addr = Split(strForm, "*")
For i = LBound(Addr) To UBound(Addr)
On Error GoTo NotCell
Set myCell = Range(Addr(i))
strOrig = Replace(strOrig, Addr(i), myCell.Value)
NotCell:
Resume GoOn
GoOn:
Next i

ActiveCell.Formula = strOrig
End Sub






"Co-op Bank" <[email protected]> wrote in message
news:[email protected]...
> Hello, is there a way to change a formula for example '=a1+a2+a3' to show its
> constant values instead in the cell so it would show '=10+20+30' assuming
> 'a1' = 10 etc...
>
> Would this be a VBA solution? Any help suggestions would be much appreciated.
>
> Regards
> Brian
> Manchester, England

[Co-op Bank]

Excellent answer Bernie thankyou, this has saved alot of time.

Thanks
Brian

"Bernie Deitrick" wrote:

> Brian,
>
> Select the cell with the formula, and run the macro below. It will change most cell references to
> values, with the exception of multi-cell ranges. That would be do-able, but would require a
> re-write.
>
> HTH,
> Bernie
> MS Excel MVP
>
> Sub Convert()
> 'Converts cell references to values within the
> 'Activecell's formula
> 'Written by Bernie Deitrick Dec 15, 2005
>
> Dim strForm As String
> Dim strOrig As String
> Dim Addr As Variant
> Dim i As Integer
> Dim myCell As Range
> Const Operators As String = "=+-*/^()"
>
> strForm = ActiveCell.Formula
> strOrig = ActiveCell.Formula
>
> For i = 1 To Len(Operators)
> strForm = Replace(strForm, Mid(Operators, i, 1), "*")
> Next i
>
> Addr = Split(strForm, "*")
> For i = LBound(Addr) To UBound(Addr)
> On Error GoTo NotCell
> Set myCell = Range(Addr(i))
> strOrig = Replace(strOrig, Addr(i), myCell.Value)
> NotCell:
> Resume GoOn
> GoOn:
> Next i
>
> ActiveCell.Formula = strOrig
> End Sub
>
>
>
>
>
>
> "Co-op Bank" <[email protected]> wrote in message
> news:[email protected]...
> > Hello, is there a way to change a formula for example '=a1+a2+a3' to show its
> > constant values instead in the cell so it would show '=10+20+30' assuming
> > 'a1' = 10 etc...
> >
> > Would this be a VBA solution? Any help suggestions would be much appreciated.
> >
> > Regards
> > Brian
> > Manchester, England
>
>
>