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!