Fast, efficient VBA to check if sheet exists

[ShoYnn]

I have a workbook that is a repository for information. Basically, you put the required data in the blocks, then click a submit button. The coding will then create a new sheet for this information with a unique name. To avoid duplicate entries, the code also verifies that a sheet with that unique name does not already exist. This sheet is used by multiple users across multiple computers on our work server. The issue I am running into is that not everyone's computer is as powerful as mine, and as the repository grows it is taking a long time/freezing up on the slower computers.

I know there are plenty of methods to verify that a sheet exists or not, but I need one that can run smoothly on weak machines when there are hundreds of sheets in the workbook to check against. Thanks in advance!

 

[Norie]

Are you sure checking that a sheet exists is what's slowing things down?

What method are you currently using?

 

[ShoYnn]

Are you sure checking that a sheet exists is what's slowing things down?

What method are you currently using?

I am absolutely sure it is what is slowing things down. When the book was fresh everything ran fast, now even on my computer that is beefier than everyone else's (apparently) it is running a little more slowly. Also, the first thing that happens upon clicking the submit button is the check function.

As for the method I am currently using, I will have to report back tomorrow. I am not at work right now and cannot recall off the top of my head. What I do remember is that the actual checking is done via a sub function named doessheetexist(xx) where xx is a variable based off a cell value that is the intended sheet name.

 

[ShoYnn]

Are you sure checking that a sheet exists is what's slowing things down?

What method are you currently using?

Currently, my code is:

Dim sht1 as String
sht1 = Sheets("Data entry").Range("O5").Value 'cell with unique sheet name
If doessheetexist(sht1) then

And then as a separate function

Function doessheetexist(Sh as String) as Boolean
Dim ws as Worksheet
On error resume next
Set ws This Workbook.Sheets(Sh)
On error goto 0
If not ws is nothing then does sheet exist = True
End function


This worked perfectly for a while, but we are currently over 300 sheets for it to dig through, so if there was a less processor hungry method for verifying, that would be amazing. Thanks!

 

[Fluff]

That code should not take long, but another way which may or maynot be quicker

sht1 = Sheets("Data entry").Range("O5").Value 'cell with unique sheet name
If Not Evaluate("isref('" & sht1 & "'!A1)") Then

 

[ShoYnn]

That code should not take long, but another way which may or maynot be quicker

sht1 = Sheets("Data entry").Range("O5").Value 'cell with unique sheet name
If Not Evaluate("isref('" & sht1 & "'!A1)") Then

Thanks Fluff, that totally saved the day!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Babynod]

That code should not take long, but another way which may or maynot be quicker

sht1 = Sheets("Data entry").Range("O5").Value 'cell with unique sheet name
If Not Evaluate("isref('" & sht1 & "'!A1)") Then

Will this work for cell values? My current duplicate validation is giving me issues

 

[Fluff]

You already have a thread for this question, so please stick to it. Thanks

 

[Babynod]

You already have a thread for this question, so please stick to it. Thanks

Sorry