How can I improve the find method?

S

Simon Livings

I am writing a program that counts how many times each range names in a
workbook is used in the formulae. At the moment I am looping through
each name and then looping through each sheet and performing the find
method on the cells.specialcells(xlcelltypeformulas). My problem is that
with 600 range names and 30 sheets, the process takes at least half an
hour to complete.

I am wondering if there is a better alternative to the find method that
would be quicker. I am considering setting up arrays to store all the
formulae on each sheet and to perform a search in these arrays, but
because the formulas or not contiguous the arrays themselves are not
easy to set up and so the benefit might not be there. I have also
played around with looping through the sheets first and then the names
afterwards, but this approach actually appears slower.

I am using xl2000, and the code used for finding is as follows:

Set rSearch = sh.Cells.SpecialCells(xlCellTypeFormulas)
Set rFound = rSearch.Find(What:=strFind, LookIn:=xlFormulas,
lookat:=xlPart, searchorder:=xlByColumns, searchdirection:=xlNext)

' If a match is found then
If Not rFound Is Nothing Then
' Record a record of the first cell address found (traps when the
search repeats)
strFirstAddress = rFound.Address

' Perform a search on the sheet for further references.
Do
' Count the occurrences
lCountNames = lCountNames + 1
' Find the next match
Set rFound = rSearch.FindNext(rFound)
' Loop only if we haven't already found all of the references.
Loop While Not rFound Is Nothing And rFound.Address <>
strFirstAddress

Any advice would be greatly received.

Many thanks in advance,
Simon Livings
 
J

Jan Karel Pieterse

Hi Simon,
I am writing a program that counts how many times each range names in a
workbook is used in the formulae. At the moment I am looping through
each name and then looping through each sheet and performing the find
method on the cells.specialcells(xlcelltypeformulas). My problem is that
with 600 range names and 30 sheets, the process takes at least half an
hour to complete.

I am wondering if there is a better alternative to the find method that
would be quicker. I am considering setting up arrays to store all the
formulae on each sheet and to perform a search in these arrays, but
because the formulas or not contiguous the arrays themselves are not
easy to set up and so the benefit might not be there. I have also
played around with looping through the sheets first and then the names
afterwards, but this approach actually appears slower.
Click to expand...

First of all, let me point you to our Name manager (by Charles Williams,
Matthew Henson and myself) from:

www.jkp-ads.com

or

www.bmsltd.co.uk/mvp

or from:

www.decisionmodels.com/downloads.htm

Secondly: how are you discerning between pieces of string that contain a
name's name but in fact might not really contain the name itself, e.g.
consider having the name "Name"

It will be correctly found in a cell with this formula:

=SUM(Name)

But also in this one (incorrect):

=IF(A1="","Name not entered",A1)

Also, it will find

=Sum(myName)

as containing your "Name".

You will have to parse out each and every case in which a name is detected
in a cell, to doublecheck whether it is actually a real use of that name,
or just a partial string. Our Name Manager does that, but alas only gives
you an indication whether or not a name has been used, not how many times
or where. Obviously, due to the parsing, the process of finding used names
takes (a lot) longer too.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
S

Simon Livings

Many thanks Jan,

I have had a look at your tool and am very impressed. However, as you
note it does not list out how many times the names are used, but can
only confirm whether they are used or not.

Forgetting the parsing issue (which I agree is very important and
admittedly I didn't really consider this) I return to my original
question: Is there a better/quicker method of searching than using the
Find method. Currently each name takes approximately 2.5 seconds to
search through 30 sheets (113k formulae in total) which in my current
model takes about 25 minutes to run. I did not try your tool to compare
times though I do not doubt it is much quicker hence my question.

Thus without asking you to divulge trade secrets - can you give me any
pointers on how to speed up the search method?

Many thanks for your comments,
Simon Livings
 
J

Jan Karel Pieterse

Hi Simon,
I did not try your tool to compare
times though I do not doubt it is much quicker hence my question
Click to expand...

My tool will no doubt be slower, as it parses every found occurrence to
check for validity. Hence the warning message the utility gives before
it starts checking. And it just uses the Find method.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
J

Jake Marx

Hi Simon,

The only thing I can think of that might help is the following:

1) make a copy of your workbook by using SaveAs
2) do all of the following steps on the copy
3) loop through your Names collection and populate
an array of strings with the Name property of each
Name object
4) delete all Names in the workbook
5) call the following function for each element in the
array, passing in the element value as an argument
to the function

I don't know how much faster this would be, but it may limit the number of
cells you are looking at.


Public Function glGetFormulaErrors(Optional rsName _
As String = vbNullString) As Long
Dim ws As Worksheet
Dim c As Range
Dim lNumErrs As Long

For Each ws In ThisWorkbook.Worksheets
For Each c In ws.UsedRange.SpecialCells( _
xlCellTypeFormulas).Cells
If Len(rsName) Then
lNumErrs = lNumErrs - ((c.Errors(xlEvaluateToError _
).Value = True) And _
(InStr(1, c.Formula, rsName, vbTextCompare) > 0))
Else
lNumErrs = lNumErrs - c.Errors( _
xlEvaluateToError).Value
End If
Next c
Next ws

glGetFormulaErrors = lNumErrs
End Function

Actually, now that I think about it more, you may be better off deleting the
Names one by one and calling the function after each deletion. That would
lead to the following code:

Sub GetNameRefCount()
Dim nm As Name
Dim lNumDesc As Long
Dim lNumNames As Long
Dim sName As String
Dim sRefersTo As String
Dim bVisible As Boolean
Dim lName As Long

Application.ScreenUpdating = False

lNumNames = ThisWorkbook.Names.Count

For lName = 1 To lNumNames
Set nm = ThisWorkbook.Names(lName)
With nm
sName = .Name
sRefersTo = .RefersTo
bVisible = .Visible
.Delete
End With
lNumDesc = mlGetFormulaErrors(sName)
Debug.Print sName & ": " & CStr(lNumDesc)
'/ add name back in
ThisWorkbook.Names.Add sName, sRefersTo, bVisible
Next lName

Set nm = Nothing
Application.ScreenUpdating = True
End Sub

Private Function mlGetFormulaErrors(Optional rsName _
As String = vbNullString) As Long
Dim ws As Worksheet
Dim c As Range
Dim lNumErrs As Long

For Each ws In ThisWorkbook.Worksheets
For Each c In ws.UsedRange.SpecialCells( _
xlCellTypeFormulas).Cells
If Len(rsName) Then
lNumErrs = lNumErrs - ((c.Errors(xlEvaluateToError _
).Value = True) And _
(InStr(1, c.Formula, rsName, vbTextCompare) > 0))
Else
lNumErrs = lNumErrs - c.Errors( _
xlEvaluateToError).Value
End If
Next c
Next ws

mlGetFormulaErrors = lNumErrs
End Function

This will still take quite awhile to run, but you may be able to cut the
time down to something more manageable. Worth a shot, anyway. <g>

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
S

Simon Livings

Hi Jake,

Firstly - many thanks for spending the time writing that code for me. I
have only just picked up the code (now Monday morning for me) and over
the weekend I was playing with different approaches.

What I actually went for in the end was this:

1. Load all formulae from the workbook into an array.
2. For each name in the workbook, count how many times the name occurs
in the array.
3. This method also allows comparisons using Like to ensure that only
valid uses of the name are allowed.

I am happy with this method as it cut the time down by a factor of 10,
although admittedly the process of loading all formulae may not be
suitable when only a few names are present (ie no time saving).

Although I like your approach, I would rather not play around with
deleting things as a method of trying to find them as I am bound to make
a mistake somewhere and the model becomes unusable! However, I did
appreciate your efforts and is certainly a useful technique for me to
consider in the future.

Many thanks again,
Simon Livings
 
S

Simon Livings

Here is the relevant portion of the code that I used. Please note that
it is still hot off the press so hasn't been fully tested yet, though
does appear to work. The reason why I included the two groups of
characters for the first Like test is to avoid double counting repeated
names (eg Date, Date1, Date11 etc)

Option Base 1

Private Function SearchFormulae()
Dim sh As Worksheet, c As Range, arFormulae() As String, nm As Name,
iName As Integer
Dim rngFormulae As Range, lCount As Long, i As Long, arNames() As
Integer
Dim strFormula As String, strTest1 As String, strTest2 As String

For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
Set rngFormulae = sh.Cells.SpecialCells(xlCellTypeFormulas)
If rngFormulae Is Nothing Then GoTo NextSheet

' Resize the array to incorporate the new cells
ReDim Preserve arFormulae(lCount + rngFormulae.Cells.Count)

For Each c In rngFormulae
lCount = lCount + 1
arFormulae(lCount) = c.Formula
Next c

NextSheet:
Next sh

' We now have our array of ALL formulae in the model with which to
search for names
ReDim arNames(Activeworkbook.Names.Count)
iName = 0
For Each nm In ActiveWorkbook.Names

iName = iName + 1

lCount = 0
strTest1 = "*[*/+^,<>=()&-]" & nm.Name & "[*/+^,<>=()&-]*"
strTest2 = "*[*/+^,<>=()&-]" & nm.Name

' Loop through the formulae to find the names
For i = 1 To UBound(arFormulae)
strFormula = arFormulae(i)

If InStr(1, strFormula, nm.Name) <> 0 Then
If strFormula Like strTest1 Or strFormula Like strTest2 Then
lCount = lCount + 1
End If
Next i

' Record the result of the test
arNames(iName) = lCount
Next nm

SearchFormulae = arNames
End Function


If you see any obvious errors or ways to speed this up even more then
please let me know.

Kind regards
Simon Livings
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with FindNext Method 1
Macro to find and copy to another sheet 4
Using the FIND function in a loop 2
Macro to find an occurrence and create blanks rows 3
Please help with this... 1
How do I get this macro to start the search in the second row 2
Code For Copy Copies Twice 3
Setting the Options of the Find method 6

Top