Any quicker ideas?

[big t]

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

 

[Bob Phillips]

big t,

Not sure if this any quicker (don't have 3400 names to check), but it's a
heck shorter

Public Function RangeExists(rangeName As String) As Boolean
On Error Resume Next
RangeExists = Range(rangeName).Address <> ""
End Function

Is this the same test 3400 times, or a different name each time?

 

[keepITcool]

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 >

 

[keepITcool]

aha..

did some more testing by inserting 10000 names.
i tried to insert 30000 names and excel (almost) stalled...

it will get slow because of inherent weaknesses in the collection object.
All collections over say 5000 items will significantly slow things up.

Also the names collection contains 4 indexes that need to be
maintained....
Copying your sheets will explode your file size..
Moving your sheets to new workbooks is virtually impossible..


WHY do you need all those names?
wouldnt it be a lot more:
manageble
faster
smaller (filesize/memory)

if you'd just use range references?

However you'd have to dereference your formula's use of rangenames.. a
quite daunting task!



keepITcool

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


keepITcool <[email protected]> wrote:

 

[Bob Phillips]

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.

 

[big t]

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

 

[Tom Ogilvy]

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

 

[Bob Phillips]

Exactly where I was going when I asked the question <vbg>

Bob

 

[Rob van Gelder]

Public Function RangeExists(rangeName As String) As Boolean
On Error Resume Next
RangeExists = TypeOf Range(rangeName) Is Range
End Function