+ Reply to Thread
Results 1 to 4 of 4

All Formula Extraction

  1. #1
    Registered User
    Join Date
    04-26-2016
    Location
    Minneapolis
    MS-Off Ver
    2013
    Posts
    21

    All Formula Extraction

    I found this great code that was working and am now getting an error on

    vFormulas = Application.Index(Array(dic.keys, dic.items, dic1.items), 0, 0)

    and can't figure out why.


    I realize there are other ones out there but this one is lighting and the only one that could handle a huge spreadsheet without Excel crashing. Any input would be appreciated.
    -What got me is it was working and I haven't made any changes to the sheet itself.

    Sub ListFormulas()
    Dim ws As Worksheet
    Dim FormulaCells As Range
    Dim Cell As Range
    Dim FormulaSheet As Worksheet
    Dim lRow As Long
    Dim dic As Object
    Dim vFormulas As Variant
    Dim vFormulas1 As Variant

    Application.ScreenUpdating = False

    lRow = 2

    Set dic = CreateObject("Scripting.Dictionary")
    Set dic1 = CreateObject("Scripting.Dictionary")

    For Each ws In ActiveWorkbook.Worksheets

    ' Create a Range object for all formula cells
    On Error Resume Next
    Set FormulaCells = ws.UsedRange.SpecialCells(xlFormulas)
    On Error GoTo 0
    ' Exit if no formulas are found
    If Not FormulaCells Is Nothing Then


    ' Process each formula
    For Each Cell In FormulaCells
    dic.Add ws.Name & "!" & Cell.Address(0, 0), "'" & Cell.Formula
    dic1.Add ws.Name & "!" & Cell.Address(0, 0), Cell.Value
    Next Cell
    End If
    Next ws

    If dic.Count <> 0 Then
    ' Add a new worksheet
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Formula list").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    Set FormulaSheet = ActiveWorkbook.Worksheets.Add
    FormulaSheet.Name = "Formula list"

    ' Set up the column headings
    FormulaSheet.Range("A1:B1") = Array("Address", Formula)
    vFormulas = Application.Index(Array(dic.keys, dic.items, dic1.items), 0, 0)
    FormulaSheet.Range("A2").Resize(UBound(vFormulas, 2), UBound(vFormulas, 1)).Value = Application.Transpose(vFormulas)
    ' Adjust column widths
    'FormulaSheet.Columns("A:C").AutoFit
    End If
    End Sub

    the original post:
    https://www.experts-exchange.com/que...answer40553334

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,040

    Re: All Formula Extraction

    Some of your formulas are too long. Change

    dic.Add ws.Name & "!" & Cell.Address(0, 0), "'" & Cell.Formula

    to

    dic.Add ws.Name & "!" & Cell.Address(0, 0), "'" & Left(Cell.Formula, 254)
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    04-26-2016
    Location
    Minneapolis
    MS-Off Ver
    2013
    Posts
    21

    Re: All Formula Extraction

    you have no idea how awesome it is you responded with a working solution. Thank you.

    - I am fairly new to VBA but have an above average SQL history, so I am understanding the content and language, however the syntax is all different. Would there be a resource you would suggest for learning that? preferable a book, but anything will do.
    - Mainly towards syntax and structure i.e. all keywords / procedures / functions glossary or index.
    - and what I can fields or variables I would put in for each "." delimiter -- if you are familiar with intellisense in SQL server, something like this does popup once in a while in VBA

    lastly if there some sort of 'like' button or indicator that you resolved my post, please let me know!

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,040

    Re: All Formula Extraction

    I always recommend John Walkenbach's "Power Programming with Excel XXXX VBA" - great books.

    VBA has intellisense - you need to explicitly dimension your variables and set them correctly, along the lines of

    Dim rngC As Range
    Set rngC =Selection
    rngC.

    Intellisense starts after you type the . after rngC if you have enabled it in VBA options.

    And since you stared the string, there are tools at the top that allow you to mark the thread as "Solved" and you can click the "* Add Reputaion" at the lower left of my post to give me cred.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. To avoid duplication of data extraction in array formula
    By ikm-99 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-04-2016, 07:23 PM
  2. Helppppp Text extraction formula
    By Deandre305 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-03-2016, 05:25 PM
  3. Formula for text extraction
    By viber52 in forum Excel Formulas & Functions
    Replies: 23
    Last Post: 07-24-2015, 11:53 AM
  4. [SOLVED] Adding A Second Criteria To A Data Extraction Formula
    By rlh06 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-29-2013, 10:35 AM
  5. Excel 2008 : Integer Extraction Formula
    By cnsader in forum Excel General
    Replies: 3
    Last Post: 01-09-2011, 05:49 PM
  6. Excel 2007 : Formula for extraction of text in bracket
    By bernard.x in forum Excel General
    Replies: 4
    Last Post: 12-08-2010, 02:20 AM
  7. Value Extraction Formula
    By ringrim in forum Excel General
    Replies: 4
    Last Post: 04-01-2009, 02:35 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1