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

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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.

Code:
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 :biggrin: (hope someone will)

chrees to all
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,615
Messages
5,832,713
Members
430,156
Latest member
Amosjack1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top