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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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.


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

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Jan Karel

www.bmsltd.co.uk/mvp is no longer working.

Please change it to

www.bmsltd.ie/mvp/

Best regards

Wolf


-----Original Message-----
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.


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

.

Hi Wolf,

www.bmsltd.co.uk/mvp is no longer working.

Please change it to

www.bmsltd.ie/mvp/


I know, I forgot to check my answer. Thanks.

Regards,

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

Hi Simon,

I did not try your tool to compare
times though I do not doubt it is much quicker hence my question


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

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]


Simon Livings wrote:
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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Simon,

This method also allows comparisons using Like to ensure that only
valid uses of the name are allowed.


I would be interested to see the code!

Regards,

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

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Simon,

Here is the relevant portion of the code that I used.


Thanks!

I'll have a look.

Regards,

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