VBA for making a list to extract Unique Cell References from an entire Workbook - REGEXP

[actjfc]

The following code extracts all references one cell at a time with a function. Somehow it works fine for my project. However, I need almost the same but applied to ALL cells within a whole workbook. So, the code should add a new sheet and make a list with one row per unique reference found showing the sheet where the formula is located, the cell address, the actual formula, and the cell reference itself. If a formula has several references will be repeated in several rows, but the if a reference is several times within the same formula should only be in the list one time per cell/formula.

For example: if the cell B5 has this formula sum(A1, A1*A2, A3) in the sheet1, should be listed as:

Sheet1 B5 sum(A1,A1*A2,A3) A1
Sheet1 B5 sum(A1,A1*A2,A3) A2
Sheet1 B5 sum(A1,A1*A2,A3) A3

The current formula output is just: A1, A1, A2, A3

Can somebody modify this VBA code to produce this workbook reference list? "No matches" should be not listed. Thanks!

Function ExtractCellRefs(Rg As Range) As String
'Updateby Extendoffice
Dim xRetList As Object
Dim xRegEx As Object
Dim I As Long
Dim xRet As String
Application.Volatile
Set xRegEx = CreateObject("VBSCRIPT.REGEXP")
With xRegEx
.Pattern = "('?[a-zA-Z0-9\s\[\]\.]{1,99})?'?!?\$?[A-Z]{1,3}\$?[0-9]{1,7}\$?[A-Z]{1,3}\$?[0-9]{1,7})?"
.Global = True
.MultiLine = True
.IgnoreCase = False
End With
Set xRetList = xRegEx.Execute(Rg.Formula)
If xRetList.Count > 0 Then
For I = 0 To xRetList.Count - 1
xRet = xRet & xRetList.Item(I) & ", "
Next
ExtractCellRefs = Left(xRet, Len(xRet) - 2)
Else
ExtractCellRefs = "No Matches"
End If
End Function

Source: 'https://www.extendoffice.com/documents/excel/4555-excel-extract-cell-reference-from-formula.html

 

[Peter_SSs]

One possible problem for you to look out for with the code Peter posted in Message #2 ...

You are right Rick and your examples are further examples to add to the two I gave in that earlier post.
However, I had pointed out that I was not addressing the RegExp pattern, but the questions the OP actually asked:
- to apply the code to a whole workbook, not just the active sheet, and
- ensure that cell references used multiple times in a single formula were only reported once.

I have not attempted to 'proofread' the RegExp pattern and have accepted that you are happy (enough) with it.

 

[Rick Rothstein]

You are right Rick and your examples are further examples to add to the two I gave in that earlier post.
However, I had pointed out that I was not addressing the RegExp pattern, but the questions the OP actually asked:
- to apply the code to a whole workbook, not just the active sheet, and
- ensure that cell references used multiple times in a single formula were only reported once.

I wasn't criticizing the code you posted... I understood you were not addressing the RegExp pattern, rather, I was just pointing out to the OP that if he used the code, he needed to be aware of more problems than the two you had already pointed out.