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

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!