Stop a Function from Running

[dflak]

I have a subroutine, SnapshotList that should get a list of files from a directory. However its DIR() statement is "hijacked" by a function called FileExist. Here is the code:

Please Login or Register  to view this content.

SnapshotList runs OK up to this line: Shtu.cells(RowNumS, "D") = FileName.

At that point execution jumps to FileExist and since it also uses DIR(), the FileName = DIR() statement in SnapshotList returns "" and execution stops.

What is calling FileExist? How do I stop FileExist from running?

I would post the workbook, but there is way too much proprietary information to sanitize it.

[rorya]

Is the FileExist function being used as a formula in a cell that refers to Shtu.cells(RowNumS, "D")? I'd suggest setting calculation to manual while you run the SnapshotList code.

[dflak]

FileExist does is not used in the range referred to in other macro. However, it exists on shtU. That's probably enough to get it going. I think I will run a test with it on another sheet.

I tried your recommendation and it worked.

However, I used a different logic to get around it. I use snapshot list to generate a list, for my users to see, of file snapshots available. I use data validation against this list to make the selection of a file name so they can only pick a file that exists. I also back this up with a ISNUMBER(MATCH()) on the selected file name and check that flag in case they override the validation.

Interesting situation. You gave me the clue as to what may have caused it and how to fix it, which is good in case I see a similar situation in the future.