I have a situation where I need to insert formulas containing
sheetnames, and test for situations where the sheetname may not exist
in the workbook. I had some complex code that would crash every time,
and ultimately I narrowed down the cause of the crash to the use of
the GetOpenFilename line. This simple procedure below will
demonstrate the problem. The first line works as expected. The
GetOpenFilename works as expected. But then the third line (even
though identical) returns the error Method Formula of object Range
fails. Excel then becomes unstable and will crash when I run other
procedures.

Sub TestOpen()

Range("a1").Formula = "=badsheet!a21"
strOpenName = Application.GetOpenFilename("Excel files, *.xls")
Range("a2").Formula = "=badsheet!a22"

End Sub

Im using Excel 2003. I couldn't find any information on this in the
knowledgebase. Is this a known bug, and does anyone know of a
workaround I could try? I do need to use GetOpenFileName to get the
file with the formulas, and then I do need to test if any of the
formulas contain references to sheetnames which don't exist.

Thanks for any help on this,

Chuck Waterfield

I couldn't reproduce the behavior in Excel 2002. Are you sure there are not
other macros (perhaps event macros) that are interacting unpredictably?

Are you sure a sheet called "badsheet" exists?

Are you actually opening a file using GetOpenFileName or is this the code in
its entirety?

--

Vasant


"Chuck Waterfield" wrote in message
om...
I have a situation where I need to insert formulas containing
sheetnames, and test for situations where the sheetname may not exist
in the workbook. I had some complex code that would crash every time,
and ultimately I narrowed down the cause of the crash to the use of
the GetOpenFilename line. This simple procedure below will
demonstrate the problem. The first line works as expected. The
GetOpenFilename works as expected. But then the third line (even
though identical) returns the error Method Formula of object Range
fails. Excel then becomes unstable and will crash when I run other
procedures.

Sub TestOpen()

Range("a1").Formula = "=badsheet!a21"
strOpenName = Application.GetOpenFilename("Excel files, *.xls")
Range("a2").Formula = "=badsheet!a22"

End Sub

Im using Excel 2003. I couldn't find any information on this in the
knowledgebase. Is this a known bug, and does anyone know of a
workaround I could try? I do need to use GetOpenFileName to get the
file with the formulas, and then I do need to test if any of the
formulas contain references to sheetnames which don't exist.

Thanks for any help on this,

Chuck Waterfield

Chuck Waterfield wrote:
I have a situation where I need to insert formulas containing
sheetnames, and test for situations where the sheetname may not exist
in the workbook.

Take a look at the IsSheet function published by Jake in this newsgroup back
in 1998.
Works great for me.
http://groups.google.com/groups?q=is...elm=35F53650.D
61F0218%40wwa.com&rnum=4
In case the link above doesn't work, here's the function:

Public Function IsSheet(strSheetName As String)
IsSheet = False
For Each ws In Worksheets
If ws.Name = strSheetName Then
IsSheet = True
Exit For
End If
Next ws
End Function


Markus

Hi,

I can replicate this on two computers, one with Excel 2003, the other
with Excel 2002. I can paste the three-line macro into an empty
workbook and the behavior is the same. The code is actually clearer
and runs more smoothly as follows:

Sub TestOpen()

SendKeys ("{ESC}")
Range("a1").Formula = "=badsheet!a21"
strOpenName = Application.GetOpenFilename("Excel files, *.xls")
SendKeys ("{ESC}")
Range("a2").Formula = "=badsheet!a22"

End Sub

The sheet "badsheet" does NOT exist in the workbook, so what happens
is Excel pops up an OpenFileName dialogue. I cancel (or the SENDKEY
line cancels) and cell A21 shows a #REF. My complete code then checks
the cell and concludes if the worksheet name exists or not based on an
ISERROR test.

After the GetOpenFileName I don't need to open the workbook. I get
the same behavior in my longer code if I continue on open the workbook
or not.

My theory of what is happening is that the same dialogue gets opened
when I paste a formula that references a non-existant sheet or if I
call GetOpenFileName. Something happens when I actually call
GetOpenFileName (some lingering behavior) that causes Line 3 to bomb.

And Excel really becomes unstable. When I run a number of my other
macros (in my real notebook) Excel invariably crashes.

Thanks for your help with this!

Chuck Waterfield

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ...
I couldn't reproduce the behavior in Excel 2002. Are you sure there are not
other macros (perhaps event macros) that are interacting unpredictably?

Are you sure a sheet called "badsheet" exists?

Are you actually opening a file using GetOpenFileName or is this the code in
its entirety?

--

Vasant


"Chuck Waterfield" wrote in message
om...
I have a situation where I need to insert formulas containing
sheetnames, and test for situations where the sheetname may not exist
in the workbook. I had some complex code that would crash every time,
and ultimately I narrowed down the cause of the crash to the use of
the GetOpenFilename line. This simple procedure below will
demonstrate the problem. The first line works as expected. The
GetOpenFilename works as expected. But then the third line (even
though identical) returns the error Method Formula of object Range
fails. Excel then becomes unstable and will crash when I run other
procedures.

Sub TestOpen()

Range("a1").Formula = "=badsheet!a21"
strOpenName = Application.GetOpenFilename("Excel files, *.xls")
Range("a2").Formula = "=badsheet!a22"

End Sub

Im using Excel 2003. I couldn't find any information on this in the
knowledgebase. Is this a known bug, and does anyone know of a
workaround I could try? I do need to use GetOpenFileName to get the
file with the formulas, and then I do need to test if any of the
formulas contain references to sheetnames which don't exist.

Thanks for any help on this,

Chuck Waterfield

Hi Markus,

Yes, I use this same routine in my workbook when I know the sheetname.
The issue here is that I'm importing a list of formulas that I've
exported from another workbook. I don't know how to do a routine to
go through any formula and identify and isolate any possible worksheet
names so that I can test them with this procedure. (I expect some
highly complex series of string searches would do so, but I think it
would take me forever... maybe a routine exists?)

So instead, I use a different routine I've found on this newsgroup,
that pastes the formula in a cell, tests if it gives a #REF error. If
so, it assumes the worksheet doesn't exist. If it doesn't then it
clearly does.

Thanks for your assistance,

Chuck Waterfield

"Markus L" wrote in message ...
Chuck Waterfield wrote:
I have a situation where I need to insert formulas containing
sheetnames, and test for situations where the sheetname may not exist
in the workbook.

Take a look at the IsSheet function published by Jake in this newsgroup back
in 1998.
Works great for me.
http://groups.google.com/groups?q=is...elm=35F53650.D
61F0218%40wwa.com&rnum=4
In case the link above doesn't work, here's the function:

Public Function IsSheet(strSheetName As String)
IsSheet = False
For Each ws In Worksheets
If ws.Name = strSheetName Then
IsSheet = True
Exit For
End If
Next ws
End Function


Markus

tro find out if a sheet is unique, iterate through the Worksheets
collection:

Function SheetIsUnique(ByVal SheetName As String)
On Error Resume Next
Dim iSheetCount
bSheetNameIsUnique = True
For iSheetCount = 1 To Sheets.Count
If LCase(Sheets(iSheetCount).Name) =
LCase(SheetName) Then
bSheetNameIsUnique = False
Exit For
End If
Next
SheetIsUnique = bSheetNameIsUnique

End Function

to search for a value:

sub findsomething(somethingtofind as string)
dim CurRow
CurRow=1
With ActiveSheet.UsedRange
Do Until IsEmpty(Cells(CurRow, 1))
If Cells(CurRow, 1).Value < "" Then
If Cells(CurRow, 2).Value = somethingtofind
Then
dosomething
end if
end if
currow=currow+1
Loop
end with
end sub
"Chuck Waterfield" wrote in message
om...
Hi Markus,

Yes, I use this same routine in my workbook when I know the sheetname.
The issue here is that I'm importing a list of formulas that I've
exported from another workbook. I don't know how to do a routine to
go through any formula and identify and isolate any possible worksheet
names so that I can test them with this procedure. (I expect some
highly complex series of string searches would do so, but I think it
would take me forever... maybe a routine exists?)

So instead, I use a different routine I've found on this newsgroup,
that pastes the formula in a cell, tests if it gives a #REF error. If
so, it assumes the worksheet doesn't exist. If it doesn't then it
clearly does.

Thanks for your assistance,

Chuck Waterfield

"Markus L" wrote in message
...
Chuck Waterfield wrote:
I have a situation where I need to insert formulas containing
sheetnames, and test for situations where the sheetname may not exist
in the workbook.

Take a look at the IsSheet function published by Jake in this newsgroup
back
in 1998.
Works great for me.

http://groups.google.com/groups?q=is...elm=35F53650.D
61F0218%40wwa.com&rnum=4
In case the link above doesn't work, here's the function:

Public Function IsSheet(strSheetName As String)
IsSheet = False
For Each ws In Worksheets
If ws.Name = strSheetName Then
IsSheet = True
Exit For
End If
Next ws
End Function


Markus