Solved: excel 2007 macro error

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

calitila

Thread Starter
Joined
Oct 12, 2009
Messages
3
Hi,

Every macro enabled file that I try to use on my computer gives me the same error: Runtime error 9 subscript out of range.

I get this error on files that work on other computers, including files I wrote on my computer, they don't work anymore and the all give me the same error.

Even writing a very simple macro I get this error, for example:

Sub MyInfo()
Workbooks("CropTool").Worksheets("RawData").Range("A1").Cells(2,4) = "hi"
End Sub

I have checked about 10 times to make sure that the workbook is indeed called CropTool, and the worksheet is called RawData.

This error has nothing to do with what I write in the module....

Please help.
 
Joined
Jul 25, 2004
Messages
5,458
Hello, welcome to the board!

You shouldn't be mixing both the Range and Cells methods. Choose one, but it's just one or the other. Now, that being said, it should still work and the Range method will pretty much just be ignored. As to why this is erroring out is most likely due to what you've already thought - either the workbook or worksheet name is not valid. I would make sure the workbook is open, name is intact, worksheet is visible and unprotected with no spaces in the name, etc. The error you're receiving is for (basically) an object that doesn't exist which you're calling on in code.

HTH
 

calitila

Thread Starter
Joined
Oct 12, 2009
Messages
3
Thank you for the prompt reply, and thanks for the tip with Range & Cells.

I am very confused. If the problem is indeed with the name of the worksheet then why would a file that has worked in the past (with out being resaved or renamed) all of the sudden stop working.

Then again, I think the problem really is with the naming. To check to see if all macros gave me the same error I tried the most classic:

Sub HelloWorld()
MsgBox "Hello World"
End Sub

and this worked.

Unfortunately, even naming the file A and replacing the name in the code with A, I get the same error.

I think a way that this could be avoided is by simple calling up the active workbook (assuming the error is in the workbook name and not the worksheet name). However I am self taught and don't know how to do this.

Thanks again for all your help :)
 
Joined
Jul 25, 2004
Messages
5,458
Hmm. Well, if the workbook in question is the activeworkbook, try these steps. Open the VBE, press Ctrl + G to open the Immediate Window, type this and check the return...
Code:
?activeworkbook.name
Does it return the expected results?
 

calitila

Thread Starter
Joined
Oct 12, 2009
Messages
3
Thanks again!

It does return what I expect.

To give you more information I tried the following code:

MsgBox "1"
MsgBox ActiveWorkbook.Name
MsgBox "2"
MsgBox ActiveWorkbook.Path
MsgBox "3"
MsgBox Workbooks(ActiveWorkbook.Name).Path
MsgBox "4"
MsgBox Workbooks(ActiveWorkbook.Name).Name
MsgBox "5"
MsgBox Workbooks("CropTool.xlsm").Name
MsgBox "6"
MsgBox Workbooks("CropTool.xlsm").Worksheets("RawData").Name
MsgBox "7"

From this I think that I need to add the extention.... although I never have before.

This works:
MsgBox Workbooks("CropTool.xlsm").Name

While this doesn't:
MsgBox Workbooks("CropTool").Name

If you have any insight as to why I need to add the extension I would be grateful, I'd like to understand.

Thanks again :)
 
Joined
Jul 25, 2004
Messages
5,458
Did you make any recent changes? I've seen a few people complain about this too. I'm wondering if maybe it's not an update issue. Personally I think it's better to use the entire name. Of course it depends what you're doing. Calling code to find another workbook which is open is, IMHO, dangerous. If you're calling code for that particular workbook - and from within that workbook - it's better to use ThisWorkbook instead.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top