Macro suddenly stops mid process through an array

M

macmeldrum

Dear All,

I have the following array (variant): Row,FileName,Path,Use,Done with
about 5,000 rows in it.

I firstly fill the array columns Use and Done with the string "blank"
where there are any "empty" elements.

I then loop through each row, concatenate path & file and open the
file in read only that it corresponds to (there to ensure it exists).
It then reads a cell, writes it to another opened file and then close
the file.

My problem is that the macro suddenly stops (no debug error warning)
and random times, sometimes on file 35 or sometimes on file 435.

Any ideas why this would happen would be greatly appreciated.

Cheers

Mac
 
S

ShaneDevenshire

Hi,

You're going to have to post the code if you want anyone to figure out
what's wrong.
 
J

Joel

I have two suggestions.

1) Comment out all ON Error statements so the code will stop on the error.
2) change you setting in the VBA Tools Menu

Tools - Option - General - Stop on All Errors
 
B

Barb Reinhardt

I'd also add to put Option Explicit at the before the Sub MySub() line.
This will force you to declare all of your variables. I've also seen this
happen when I open multiple workbooks programmatically and I'm doing
something eise in another application and I just happen to press the SHIFT
key when a workbook is being opened.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.
 
B

Barb Reinhardt

If the shift key is pressed, you can use something like this

---
'Declare API
Declare Function GetKeyState Lib "User32" _
(ByVal vKey As Integer) As Integer
Const SHIFT_KEY = 16
Function ShiftPressed() As Boolean
'Returns True if shift key is pressed
ShiftPressed = GetKeyState(SHIFT_KEY) < 0
End Function
---

I'm not sure where I got this, but I'm sure it was from someone on this board.

I then put something like this in my code right before the workbook open

---
Do While ShiftPressed()
MsgBox ("The shift key is pressed. Please release it and press
OK.")
DoEvents
Loop
Set oWB = XLApp.Workbooks.Open(oldPath, UpdateLinks:=False,
ReadOnly:=True)
 
Top