Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of Range - CodeName
I'm getting a 'Subscript out of range' error on a line of code as follows
ThisWorkbook.VBProject.VBComponents(ActiveSheet.Co deName).Properties("_CodeName").Value = "Output" & Now * 10000000000# The odd thing is the fact that this only occurs the first time the code is run when the workbbok is opened and, when I select debug the error, all I simply do is press run (play button) and the code conitnues on perfectly with the new codename for the active sheet. I've also changed the 'NOW * 10000000000#' to '12' and I got the same error (I couldn't test it for the second use as a codename conflict would occur). Any thoughts as to why this happens and how to fix it would be greatly appreciated. I already have tried 'On Error Resume Next' but that didn't work. It still halted my procedure. -- Cheers! Richard Reye "Never argue with an idiot. They'll bring you down to their level then beat you with experience" - someone |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of Range - CodeName
Not sure if this helps - but i tried your code Excel 2002 SP-2 VB6.3 and
it works fine. I could not get it to throw an error either in open workbook event or a module, repeated use did not either ! -- Cheers Nigel "Richard Reye" wrote in message ... I'm getting a 'Subscript out of range' error on a line of code as follows ThisWorkbook.VBProject.VBComponents(ActiveSheet.Co deName).Properties("_CodeN ame").Value = "Output" & Now * 10000000000# The odd thing is the fact that this only occurs the first time the code is run when the workbbok is opened and, when I select debug the error, all I simply do is press run (play button) and the code conitnues on perfectly with the new codename for the active sheet. I've also changed the 'NOW * 10000000000#' to '12' and I got the same error (I couldn't test it for the second use as a codename conflict would occur). Any thoughts as to why this happens and how to fix it would be greatly appreciated. I already have tried 'On Error Resume Next' but that didn't work. It still halted my procedure. -- Cheers! Richard Reye "Never argue with an idiot. They'll bring you down to their level then beat you with experience" - someone |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of Range - CodeName
Your code worked for me just fine. You might try shortening it to
ThisWorkbook.VBProject.VBComponents(ActiveSheet.Co deName).Name = _ "Output" & Now * 10000000000# -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Richard Reye" wrote in message ... I'm getting a 'Subscript out of range' error on a line of code as follows ThisWorkbook.VBProject.VBComponents(ActiveSheet.Co deName).Properties("_CodeName").Value = "Output" & Now * 10000000000# The odd thing is the fact that this only occurs the first time the code is run when the workbbok is opened and, when I select debug the error, all I simply do is press run (play button) and the code conitnues on perfectly with the new codename for the active sheet. I've also changed the 'NOW * 10000000000#' to '12' and I got the same error (I couldn't test it for the second use as a codename conflict would occur). Any thoughts as to why this happens and how to fix it would be greatly appreciated. I already have tried 'On Error Resume Next' but that didn't work. It still halted my procedure. -- Cheers! Richard Reye "Never argue with an idiot. They'll bring you down to their level then beat you with experience" - someone |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of Range - CodeName
Are you doing this in the Workbook.Open event. I would try qualifying
Activesheet ThisWorkbook.VBProject.VBComponents(ThisWorkbook.A ctiveSheet.CodeName _ ).Properties("_CodeName").Value = "Output" & Now * 10000000000# -- Regards, Tom Ogilvy "Richard Reye" wrote in message ... I'm getting a 'Subscript out of range' error on a line of code as follows ThisWorkbook.VBProject.VBComponents(ActiveSheet.Co deName).Properties("_CodeN ame").Value = "Output" & Now * 10000000000# The odd thing is the fact that this only occurs the first time the code is run when the workbbok is opened and, when I select debug the error, all I simply do is press run (play button) and the code conitnues on perfectly with the new codename for the active sheet. I've also changed the 'NOW * 10000000000#' to '12' and I got the same error (I couldn't test it for the second use as a codename conflict would occur). Any thoughts as to why this happens and how to fix it would be greatly appreciated. I already have tried 'On Error Resume Next' but that didn't work. It still halted my procedure. -- Cheers! Richard Reye "Never argue with an idiot. They'll bring you down to their level then beat you with experience" - someone |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
what does 'Subscript Out of range' mean?? | Excel Worksheet Functions | |||
9: Subscript out of range | Excel Discussion (Misc queries) | |||
Subscript is out of Range | Excel Programming | |||
Subscript out of range | Excel Programming | |||
Subscript Out of Range | Excel Programming |