Weird VBA problem
 

The code below works.

It is just a simple loop that lists files in my "C:\Less\In\"
directory.

Notice that the "Calculate" keyword is memo only.
Now if I activate calculate by removing the "'" I get a "Run-time
error 5 " message on the first run of the Sub. On subsequent runs it
is ok ?!
(The "str_FileName = Dir " line is highlighted on Debug)

If I adjust its position on the line (not within the code itself) I
get the message on the first Sub run again!

How can this be?





Sub L()

Dim str_FileName As String 'Name of file in AllJnls folder.
Dim str_MyPath As String 'Path to the AllJnls folder.

' Loop to get all LESS IN Files.
str_MyPath = "C:\Less\In\" ' Set the path.
str_FileName = Dir(str_MyPath) ' Retrieve the first
entry.

Do While str_FileName < "" 'Loop for each file.

'Calculate

Debug.Print str_FileName

str_FileName = Dir ' Get next file.

Loop
End Sub

Weird VBA problem
 

you need to specify which object the calculate method is to work on be that
the application, or a range.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob" wrote in message
om...
The code below works.

It is just a simple loop that lists files in my "C:\Less\In\"
directory.

Notice that the "Calculate" keyword is memo only.
Now if I activate calculate by removing the "'" I get a "Run-time
error 5 " message on the first run of the Sub. On subsequent runs it
is ok ?!
(The "str_FileName = Dir " line is highlighted on Debug)

If I adjust its position on the line (not within the code itself) I
get the message on the first Sub run again!

How can this be?





Sub L()

Dim str_FileName As String 'Name of file in AllJnls folder.
Dim str_MyPath As String 'Path to the AllJnls folder.

' Loop to get all LESS IN Files.
str_MyPath = "C:\Less\In\" ' Set the path.
str_FileName = Dir(str_MyPath) ' Retrieve the first
entry.

Do While str_FileName < "" 'Loop for each file.

'Calculate

Debug.Print str_FileName

str_FileName = Dir ' Get next file.

Loop
End Sub

Weird VBA problem
 

you need to specify which object the calculate method is to work on be that
the application, or a range.


I can put in "Calculate", "Range("A1:F10").Calculate" or a fully
qualified path the problem is the same.

The page being calculated is nothing special.

My original loop contained a lot more code most of which works fine.
I have have traced the problem to this calculate keyword although I
don't think this is the root of the problem as I have a call to
another sub that gives the same error.

Is anyone aware of any Excel bugs that sometimes cause a DIR to fail?

Thanks.

Weird VBA problem
 

Could this possibly be a corrupt Excel workbook ?