Extracting ALL formulas from Excel

not4google · Mar 2, 2009

Hi all,

I have a spreadsheet with multiple formulas and wanted to create a report to list out these formulas. Is there a way of extracting all formulas from a sheet?

Thanks for any info,

VoG · Mar 2, 2009

Hello and welcome to MrExcel.

If you press CTRL + ` the formulas will be displayed. CTRL + ` again to revert to the normal display.

Krishnakumar · Mar 2, 2009

Hi,

To extract all formulas,

Code:

Sub kTest()
Dim r As Range, c As Range, w(), n As Long, ws As Worksheet

Set r = Nothing
On Error Resume Next
Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not r Is Nothing Then
    For Each c In r
        n = n + 1
        ReDim Preserve w(1 To n)
        w(n) = c.Formula
    Next
    On Error Resume Next
    Set ws = Sheets("ListFormulas")
    On Error GoTo 0
    If ws Is Nothing Then
        Set ws = Sheets.Add: ws.Name = "ListFormulas"
    End If
    With ws.Range("a1")
        .Value = "Formulas"
        .Offset(1).Resize(n).NumberFormat = "@"
        .Offset(1).Resize(n).Value = Application.Transpose(w)
    End With
End If
End Sub

HTH

not4google · Mar 3, 2009

Hi thanks for this, please tell me where the output for this would be...I am not familiar with Excel and am a Java developer. Any help would be appreciated,

not4google · Mar 3, 2009

Krishnakumar said:

Hi,

To extract all formulas,

Code:

Sub kTest()
Dim r As Range, c As Range, w(), n As Long, ws As Worksheet

Set r = Nothing
On Error Resume Next
Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not r Is Nothing Then
    For Each c In r
        n = n + 1
        ReDim Preserve w(1 To n)
        w(n) = c.Formula
    Next
    On Error Resume Next
    Set ws = Sheets("ListFormulas")
    On Error GoTo 0
    If ws Is Nothing Then
        Set ws = Sheets.Add: ws.Name = "ListFormulas"
    End If
    With ws.Range("a1")
        .Value = "Formulas"
        .Offset(1).Resize(n).NumberFormat = "@"
        .Offset(1).Resize(n).Value = Application.Transpose(w)
    End With
End If
End Sub

HTH

Hi thanks for this, please tell me where the output for this would be...I am not familiar with Excel and am a Java developer. Any help would be appreciated,

Krishnakumar · Mar 3, 2009

Hit Alt+F11 > Goto Insert > Module and paste the code there.
Close VBE window (Alt+Q) > Goto Tools > Macro > Macros and run kTest.

not4google · Mar 3, 2009

Thanks for the update, is possible to filter by a String for the function name. For example if I wanted to extract all formulas for all sheets where the formula had a name starting with testFunction?

not4google · Mar 3, 2009

ersonal; mso-style-noshow:yes; mso-style-unhide:no; mso-ansi-font-size:11.0pt; mso-bidi-font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; color:#1F497D; mso-themecolor:dark2;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-hansi-font-family:Calibri; mso-bidi-font-family:Calibri; mso-fareast-language:EN-US;} .MsoPapDefault {mso-style-type:export-only;} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 72.0pt 72.0pt 72.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style>[FONT=&quot]In fact if I could get all formula from all sheets excluding any index, lookup, offset and match formulas that would be ideal,

Thanks again
[/FONT]

not4google · Mar 3, 2009

If you are able to give me a very brief summary of the variables and functions in this code I can prob write this myself. Coming from a Java role this is foreign to me - any guidance on the kTest() would be appreciated,

Krishnakumar · Mar 4, 2009

Hi,

I think it needs some REGULAR EXPRESSION coding to get all the formula names. I hope pgc01 or Jindon or Richard might help you if they follow this thread.

Extracting ALL formulas from Excel

not4google

New Member

Excel Facts

VoG

Legend

Krishnakumar

Well-known Member

not4google

New Member

not4google

New Member

Krishnakumar

Well-known Member

not4google

New Member

not4google

New Member

not4google

New Member

Krishnakumar

Well-known Member

Similar threads

Forum statistics

Share this page

Extracting ALL formulas from Excel

New Member

Excel Facts

Legend

Well-known Member

New Member

New Member

Well-known Member

New Member

New Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock