[Example Code] Formula Nesting/Embeding / whatever ...

[bobsan42]

i needed something to put multiple formulas or values in another formula so here is the result.
select a non-blank cell
when start inserting a new formula use xxx to indicate where the old one should stay.
it is possible to make a simple equation also but first (sorry about this) a function must be selected then edit directly in the Formula bar as you like.

Sub WrapInFormula()
'Allows you to select a new formula. The old one is placed where you put "xxx" in the new one.
' This action is applied to each cell in the selected range
Dim oldxxxrng As String
oldxxxrng = IfNamedRangeExistsAddress("xxx")
If oldxxxrng = "" Then
    Application.Names.Add "xxx", "=" & ActiveSheet.Name & "!$A$1"
Else
    ActiveWorkbook.Names("xxx").RefersTo = "=" & ActiveSheet.Name & "!$A$1"
End If

Dim oldformula As String, oldformula1 As String 'Ins As Variant,
Dim newformula As String, newformula_cell As String
Dim rngcell As Range ', rngsel As Range

oldformula1 = ActiveCell.Formula
If InStr(1, oldformula1, "=") = 1 Then
    oldformula = "(" & Mid(oldformula1, 2) & ")"
Else
    oldformula = oldformula1
End If
ActiveCell.ClearContents
Application.DisplayFormulaBar = False
Application.DisplayFormulaBar = True
ActiveCell.FunctionWizard
If Not ActiveCell.Formula = "" Then
    newformula = ActiveCell.Formula
    ActiveCell.Formula = oldformula1
Else
    ActiveCell.Formula = oldformula1
    If oldxxxrng = "" Then
        Application.Names.Item("xxx").Delete
    Else
        ActiveWorkbook.Names("xxx").RefersTo = oldxxxrng
    End If
    Exit Sub
End If
i = 1

For Each rngcell In Selection.Cells
    oldformula1 = rngcell.Formula
    If oldformula1 = "" Then GoTo ThisIsBlankSoSkipIt
    If InStr(1, oldformula1, "=") = 1 Then
        oldformula = "(" & Mid(oldformula1, 2) & ")"
    Else
        oldformula = oldformula1
    End If
    newformula_cell = Replace(newformula, "xxx", oldformula)
    rngcell.Formula = newformula_cell
ThisIsBlankSoSkipIt:
i = i + 1
Next rngcell
If oldxxxrng = "" Then
    Application.Names.Item("xxx").Delete
Else
    ActiveWorkbook.Names("xxx").RefersTo = oldxxxrng
End If
End Sub

it turned out it can be useful to build complex formulas, so i tried to make it as generic as i could.

any comments are appreciated.
all questions will be answered.
feel free to use it if you find it useful (hope someone will)

chrees to all