• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

How do I fix this 'subscript out of range error' ?

I have a workbook before close macro that produces a 'subscript out of range' error when it gets to the code below. As soon as I click out of the error, both books close. But other than resorting to 'On Error Resume Next', how do I write it so that it closes both books without the error?

Thanks,
John
Windows("AllConfigs.xls").Close Saved = True
ThisWorkbook.Close Saved = True

Open in new window

0
John Carney
Asked:
John Carney
4 Solutions
 
Ardhendu SarangiSr. Project ManagerCommented:
check the name of the file. it might be incorrect...

if not, check this code..

Workbooks("AllConfigs.xls").Close Saved = True
ThisWorkbook.Close Saved = True

see if this works.

- Ardhendu
0
 
cyberkiwiCommented:
Use Workbooks not Windows.

If the file is new, it will not have an extension, like Workbooks("Book1")
If it is an existing file, the title changes - Workbooks("Book1.xls")
0
 
jklmnCommented:
Workbooks("AllConfigs.xls").Close True
ThisWorkbook.Close True
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Thanks for pointing out my "Windows" typo.  
Maybe there's something wrong with my computer but as long as I have the code 'ThisWorkook.Close True' anywhere in the macro, I get the error. If you take that line away, my calling workbook still closes, but i get the "Do you want to save ..." alert, which i would like to avoid. So I tried using "Appilcation.DisplayAlerts = False," but i still got the alert. Is there some other line of code that will suppress the alert without the line 'ThisWorkook.Close True'? If there is, then that will do it.
 Please create a file named 'AllConfigs.xls' , and try closing it from your 'Book1.xls' and let me know if you get the same results.
Thanks,
John
0
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Okay, I just discovered that 'On Error Resume Next' solves the problem. But that always seems like kind of a cop out. Is there any other way to fix it?
Thanks,
John
0
 
jklmnCommented:
The error 'subscript out of range error' should happen in ThisWorkbook.Close True because there is no subscript at all here.
0
 
jklmnCommented:
Sorry I meant
The error 'subscript out of range' should not happen in 'ThisWorkbook.Close True' because there is no subscript at all here.
0
 
Rory ArchibaldCommented:
Do you have a BeforeSave event? What if you use:
ThisWorkbook.Save
ThisWorkbook.Close False
0
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
I'm good now, thanks!

- John
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now