Use native excel FindAll in VBA

Question

Hello,

I need to find all cells that contain references (in cells' formulas) to other sheets in a workbook using VBA.

In Excel, one can accomplish this by selecting the target sheet pressing Ctrl+F and use FindAll to search for "!" character in formulas (see the image below). This works very fast and one gets the list of results almost instantly no matter how many formulas there are on the sheet.

However, FindAll is not available in VBA. So if I need to find all cells that contain "!" I need to use .Find() method to find the first occurrence of the string and then loop through all cells in the active range using .FindNext() method. This is inefficient and takes long time if one has many formulas on the sheet.

Is there a way to call FindAll() from VBA using some native MS Excel dll or some xll ?

I mean a situation where I import the dll like specified here https://docs.microsoft.com/en-us/office/client-developer/excel/how-to-access-dlls-in-excel ?

Thanks in advance!

Answer 1

However, this doesn't answer my question if it is possible to use native Excel dll from VBA to perform FindAll.

The answer is: You can't, there's no exported routine you can call for that.

Andreas.

Answer 2

Private Sub Example_FindAll()
Dim y As Long, R As Range, All As Range
'Create an example with some data
For y = 1 To 7 Step 2
Cells(y, 1) = 1
Cells(y + 1, 1) = 2
Next

'How to use:
Set All = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
Debug.Print All.Address
Set R = FindAll(All, 1, SearchDirection:=xlNext)
Debug.Print R.Address
Set R = FindAll(All, 1, SearchDirection:=xlPrevious)
Debug.Print R.Address

'To find "*" "?" or "~" use "~*" "~?" or "~~"
End Sub

Function FindAll(ByVal Where As Range, ByVal What, _
Optional ByVal After As Variant, _
Optional ByVal LookIn As XlFindLookIn = xlValues, _
Optional ByVal LookAt As XlLookAt = xlWhole, _
Optional ByVal SearchOrder As XlSearchOrder = xlByRows, _
Optional ByVal SearchDirection As XlSearchDirection = xlNext, _
Optional ByVal MatchCase As Boolean = False, _
Optional ByVal SearchFormat As Boolean = False) As Range
'Find all occurrences of What in Where (Windows version)
Dim FirstAddress As String
Dim C As Range
'From FastUnion:
Dim Stack As New Collection
Dim Temp() As Range, Item
Dim i As Long, j As Long

If Where Is Nothing Then Exit Function
If SearchDirection = xlNext And IsMissing(After) Then
'Set After to the last cell in Where to return the first cell in Where in front if _
it match What
Set C = Where.Areas(Where.Areas.Count)
'BUG in XL2010: Cells.Count produces a RTE 6 if C is the whole sheet
'Set After = C.Cells(C.Cells.Count)
Set After = C.Cells(C.Rows.Count * CDec(C.Columns.Count))
End If

Set C = Where.Find(What, After, LookIn, LookAt, SearchOrder, _
SearchDirection, MatchCase, SearchFormat:=SearchFormat)
If C Is Nothing Then Exit Function

FirstAddress = C.Address
Do
Stack.Add C
If SearchFormat Then
'If you call this function from an UDF and _
you find only the first cell use this instead
Set C = Where.Find(What, C, LookIn, LookAt, SearchOrder, _
SearchDirection, MatchCase, SearchFormat:=SearchFormat)
Else
If SearchDirection = xlNext Then
Set C = Where.FindNext(C)
Else
Set C = Where.FindPrevious(C)
End If
End If
'Can happen if we have merged cells
If C Is Nothing Then Exit Do
Loop Until FirstAddress = C.Address

'FastUnion algorithm © Andreas Killer, 2011:
'Get all cells as fragments
ReDim Temp(0 To Stack.Count - 1)
i = 0
For Each Item In Stack
Set Temp(i) = Item
i = i + 1
Next
'Combine each fragment with the next one
j = 1
Do
For i = 0 To UBound(Temp) - j Step j * 2
Set Temp(i) = Union(Temp(i), Temp(i + j))
Next
j = j * 2
Loop Until j > UBound(Temp)
'At this point we have all cells in the first fragment
Set FindAll = Temp(0)
End Function

Answer 3

@Andreas Killer

Thanks for the feedback. I will try your example out.

However, this doesn't answer my question if it is possible to use native Excel dll from VBA to perform FindAll.

Your code still completely relies on VBA.