View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 27,285
Default Any quicker ideas?

Why not set a static boolean that flags whether the check has been done or
not.

Public Function DoWork()
Static bChecked
Static rng as Range

if not bChecked then
On Error resume Next
set rng = Range("RangeName")
On Error goto 0
bChecked = True
End if

if not rng is nothing then
' do lookup
End if
DoWork = bchecked
End function

--
Regards,
Tom Ogilvy


"big t" wrote in message
...
Bob & keepITcool,

Thanks for your help. I am pretty much only checking for one range name,
which doesn't exist in my workbook, but might in another user's, so I

guess
my original 14 second time corresponds to Bob's 12000 milliseconds.

The reason for checking the range so often is that the RangeExists

function
is called from another function which is used ~600 times in my workbook,

and
is in a loop for n = 1 to 81 - ie a possible 48600 times.

I just included the range name in my workbook and the time dropped to 1.1
seconds which is a fantastic improvement! However, now that the range

exisits
the calling function tries to do a lookup in that range and it's time has
gone from 14.6 seconds up to 27.5 seconds!!!!

"Just as I'm about to get out...they pull me back in"!!!

Thanks again for all your help,
big t


"Bob Phillips" wrote:

That depends if the range exists or not.

On my machine I get

401 and 180 ms if foo exists

and

12658 and 12718 (sic!) if it doesn't.

The test is not robust, previous time yours was over a second faster

when
the name didn't exist, but the difference is significant depending on
existence.

--

HTH

RP

"keepITcool" wrote in message
...
Try:

Public Function RangeCheck(rangeName As String) As Boolean
On Error Resume Next
RangeCheck = ObjPtr(Range(rangeName)) < 0
End Function

Sub TimeIt()
Dim t1&, t2&, n&, b As Boolean
Const max = 30000
t1 = Timer * 1000
For n = 1 To max
b = RangeExists("foo")
Next
t1 = Timer * 1000 - t1

t2 = Timer * 1000
For n = 1 To max
b = RangeCheck("foo")
Next
t2 = Timer * 1000 - t2

MsgBox t1 & vbLf & t2

End Sub

my small test shows it's twice as fast (200ms on my laptop).
HOWEVER even your code runs in 380 milliseconds for 30000 iterations.
i think the bottleneck is somewhere else
OR are you checking EXTERNAL range names?




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"?B?YmlnIHQ=?=" wrote:

Hi everyone,

I'm using the following code to check a range exists on a worksheet:

Public Function RangeExists(rangeName As String) As Boolean
Dim var As Variant

RangeExists = False
On Error GoTo BadRange
If rangeName < "" Then
var = Range(rangeName)
RangeExists = True 'will raise error where range does not
exist
'therefore to get here, it must
Exit Function
End If

BadRange:
End Function

and it works fine. The trouble is I call this function about 34000
times and it takes approx 14 seconds to run.

Any ideas for a faster way to check a range exists?

TIA
big t