Run Time Error 9: Subscript out of Range

[DanManly]

Hi, I'm baffled and would appreciate any help I can get. So I've been using a macro for several weeks now that checks to see if a file already exists. If it does, it gets opened automatically. For some reason, it will not allow me to set wBook anymore. It had been working flawlessly and I have verified the file and directory names to be correct, and now I'm stuck! My IT guy was in today and may have done something to the network, but that's honestly grasping at straws. If anybody has any suggestions, I would greatly appreciate hearing them. Thanks.

-Dan

Please Login or Register  to view this content.

Moderator's Note: Welcome to the forum, You have to put code tags around code. Select the codes then hit the "#"

[bheanloh]

Something like this

Please Login or Register  to view this content.

[DanManly]

Thanks a ton! That seems to fix it and get me back up and running. Although for my own knowledge, does "On Error Resume Next and GoTo 0" just suppress the error msg? If the error is being caused by something, I just worry about a potential band aid fix that might come back to haunt me later. Or does "On Error Resume Next and GoTo 0" do something more that I'm not familiar with?

[bheanloh]

On Error Resume Next - ignore the error
On Error Goto 0 - the default error handler of VBA
Remember to switch back to On Error Goto 0 whenever you use On Error Resume Next, so that you won't allow any other unexpected errors to pass through as if nothing is happening. Hope I explained it well

[DanManly]

So it does just ignore the error message. I wonder what is causing the Subscript out of Range msg, considering it works fine when it is ignored? Thanks for the tip Boon, hopefully there isn't something more serious happening in the background which is triggering this error msg. Is it common for VBA to produce error messages out of the blue?

[bheanloh]

The reason you got an error is because you are referring to an object that is not there. I don't think they will produce error message out of the blue. There must be some human errors somewhere

[DanManly]

I have no doubt that there is some human error involved. But the object IS there, and it is proven so, because the immediate next line uses the object wBook and when the error msg is ignored, it works. So the object is being created and then used, that is why I am so confused as to why that specific error msg is popping up.

[bheanloh]

I'm not sure about your definition of 'object'. From my understanding, for this case, the object is referring to the workbook that you're trying to open. So, if the object (the workbook) is not opened, you can't assign a variable (for this case, wbook) to the object, and thus error occurs.
Do you mind explaining why the object is there? (I'm not computer science major, so I don't know the exact definition of 'object'. I deduce it myself )

Edit: added (for this case, wbook)

[DanManly]

It still doesn't make sense even if the 'object' refers to the closed workbook, because this macro has worked for weeks on other closed workbooks.

[bheanloh]

Maybe every time when you run the code, the workbooks are opened? I'm not sure.

[Norie]

Maybe it's the file extension causing the problem, try dropping it.

By the way what line of code uses wbook straight after?

[DanManly]

I just tried dropping the file extension and it gives the same error. As for wBook...

If wBook Is Nothing Then 'File Not open <---- this line checks to see if the file is not open, if it is False (ie. the file is open) and it alerts the user.
'do nothing
Else 'If it is open (ie. if it is Something... run these lines of code)
MsgBox ("The record EXISTS and is already OPEN!")

I've tested this scenario and it prompts me that it is open.

[Norie]

Is the workbook you are looking for actually open when you get the error?

What value is RecordName when you get the error.

If you are trying to find if a workbook open there is an alternative that avoids using On Error etc.

You could use a function like this.

Please Login or Register  to view this content.

[DanManly]

I get the error regardless if the file is open or not. The macro looks if a file exists, opened or closed. When the error occurs, RecordName is the exact same string as the file name.

[Norie]

If the workbook is open and you have the correct name then the code should not throw an error.

Where is RecordName declared and given a value.

Can you post the rest of the code?

[DanManly]

Sub CheckIfRecordExists()

Dim RecordName As Variant
Dim FilePathName As Variant
Dim Lot As String
Dim Project As String
Dim Customer As String
Dim wBook As Workbook

Application.EnableEvents = True

Lot = ActiveSheet.Range("L3").Text
Project = ActiveSheet.Range("C3").Text
Customer = ActiveSheet.Range("C2").Text

RecordName = Customer + " - " + Project + " - " + "Lot " + Lot
FilePathName = "Z:\Active Projects\Saved Records\" + RecordName + ".xlsm"

[Norie]

Can you post an example of the filenames?

PS & is the concatenate operator, + will work most of the time but can cause errors.

[DanManly]

Great Gulf - GC1 - Northmanor Estates - Lot 999.xlsm

Customer: Great Gulf
Project: Northmanor Estates
Lot: 999

[Norie]

What happens when you run this code?

Please Login or Register  to view this content.

[DanManly]

It does nothing. No Msgbox appears.

[Norie]

Are you running the code with workbook named 'Great Gulf - GC1 - Northmanor Estates - Lot 999.xlsm' open?

If you are then VBA just isn't finding it.

This may sound stupid but you've got a lot of space in the file name, have you checked if there's any misplaced/leading/trailing space?

[DanManly]

Oops, I ran it while the file was closed. It returns "Great Gulf - GC1 - Northmanor Estates - Lot 999.xlsm found."

[Norie]

Do you still get the error on the original code?

PS Did you try the function I posted to find out if a workbook was open?

[DanManly]

Yes the error occurs, but only if the file is closed. This sub worked for weeks with the file either open or closed. That's the purpose of the code. It already confirms when it is open, so no, I have no run your function to reconfirm that it's open.

[Norie]

Dan

So where are you now exactly?

Is the code working?

[DanManly]

The code works when I suppress the error using the original suggestion of bheanloh. Still don't know why the error is coming up in the first place, considering it never used to. I feel like I've been chasing my tail here.

On Error Resume Next
Set wbook = Workbooks(RecordName & ".xlsm")
On Error GoTo 0

[Norie]

Dan

If you add my code from post #13 does this code work?

Please Login or Register  to view this content.

[DanManly]

When this line of code ran when the file was closed, it gave the error.
Set wbook = Workbooks(RecordName & ".xlsm")

Once we established that the file existed and that it wasn't open, this line worked!
Set wBook = Workbooks.Open(FileName:=FilePathName)

Well thanks for the help in restructuring the code Norie. The only thing that still puzzles me, is why the code worked for an extended period of time, and then what seemed to be out of the blue, this error showed up. :S Anyways, time to move on. Thanks again.

[Norie]

Dan

The code I posted is meant to avoid any errors.

It calls the function IsWBOpen to see if the workbook is open.

If it is open then it runs this line of code.

Please Login or Register  to view this content.

If it's not open then it runs this line of code.

Please Login or Register  to view this content.

There is definitely something weird going on here and I'm not sure how to find out what it is.

Could you attach the file with the code and the file you are trying to find/open?

Remove any sensitive/confidential data.