Excel 97 macro proble.

[darronb]

I am creating a macro to check if a particular worksheet exists.

for example.

if worksheet = "Name" then

I can not find the syntax

can anyone help.
Thanks.

 

[Loomah]

Hi
Try this

Function SheetExist(sh As String) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = Worksheets(sh)
    If Not ws Is Nothing Then SheetExist = True
End Function

Sub a()
MsgBox SheetExist("sheet1")
End Sub

Happy Friday

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[xlbo]

please remember to post in the VBA forum for code questions forum707

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[darronb]

Sorry call me a bit thick, but i am a novice at this and can not understand how this works???????

 

[xlbo]

Do you know how to create a module?
if so, just paste it in and run the sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Loomah]

Just to expand a little on what I've given you.

The fist little segment of code is a function that, by relying on a known error, will return TRUE if a sheet you specify exists and FALSE if it doesn't.

The second snippet is and example of how the function can be called. You have to pass an argument to the function of the sheet name you are checking.

However, this function will only check the active workbook and, as it stands, will not accept a workbook name as an argument. he code shouldn't need to be in the active book but it will still only check the active book!

I'm assuming that you do know how to create a module? If not, post back.

Hippy Friday

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[darronb]

I have created a macro and i just can not understand this code.

I have 4 worksheets (Extracted Data, North, South, & scotland). I have edited the code below to check to check if the north worksheet exists.

As you can probably see it is not working.

What variable do i need to pass back to the call statment.
Greatful for the help.

Call sheetExist(north)
.
.
.
.
Function SheetExist(northSheet As String) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = Worksheets(north)
If Not ws Is Nothing Then SheetExist = True
End Function

 

[xlbo]

Call sheetExist("north")

or

Call sheetExist(activesheet.name)



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Call sheetExist("north")

HOWEVER, if you sheet is named North, it will return FALSE because "north" <> "North"

Skip,

_[red][/red]

 

[Loomah]

It must just be me but there is something about this that makes me smile

Call sheetExist(activesheet.name)

How many times could this be false?!

Anyway, the problem at hand.

In addition to the fact you need to name the sheet you want to test for as a string you also need to ensure that the same variable name is used in the function. That is a real Monday morning explanation!

In more detail:-
When you do this

Call sheetExist("north")

you are passing the string "north" to the function's parameter "sh" (using my original code) as a string. This string is then used to identify the the worksheet we are trying to assign to the variable ws.

Thet means that you MUST use the same variable name within the function; ie set ws = worksheets(sh)

What you have done in the code you have created is to change the names so you have

Function SheetExist([b]northSheet[/b] As String) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = Worksheets([b]north[/b])
    If Not ws Is Nothing Then SheetExist = True
End Function

The bold bits must be the same. It doesn't matter what you call them as long as you use the same name!

As for Skip's comments - not necessarily so. The code I posted was done using a default workbook and tested as is. In otherwords I was testing for "sheet1" in a workbook containing only "Sheet1". This returns TRUE. Not sure if later versions of Excel became more picky (I'm using xl97 at the mo) as it's a while since I did anything with later versions!!

What will definately cause you problems is if you have trailing or leading spaces in your sheet names.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[darronb]

Thanks for that I have got it working now.

A little knowledge is dangerous

 

[xlbo]

Loomah - lol - yes - I realised that after posting - brought a smile to my face as well !!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MeGustaXL]

<Just a bit off topic>
[Tongue in cheek as well]
The answer to Loomah's TFTD:

If a man says something and there are no women there to hear him, is he still wrong?

Must be NO, because (a) Everything a man says is always correct, and (b) Women never listen to a word a man says, whether they're present or not!

[/Tongue in cheek as well]
</Just a bit off topic>


Chris

Beer is God's way of telling us He loves us and wants us to be happy - Benjamin Franklin