Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code No Longer Works in 2007
Excel 2007. I have a well developed workbook with lots of modules of VBA
behind it. It worked fine and for years under Excel 2003. Now after installing (upgrading) to Office 2007, I am getting inexplicable errors. One for instance occurs when trying to add a Name (label a cell) as follows: WbNew.Names.Add Name:="MIA" + Left(WbNew.ActiveSheet.Name, 3), RefersToR1C1:= _ "='" + WbNew.ActiveSheet.Name + "'!R" + Trim(Str(TCL + 5)) + "C2" This resolves to: WbNew.Names.Add Name:="MIA092", RefersToR1C1:= "='092-International Ministries'!R10C2" The error message I get is: Runtime error "1004" The name that you entered is not valid. Reasons for this can include: -The name does not begin with a letter or an underscore -The name contains a space or other invalid character -The name conflicts with an Excel built-in name or the name of another object in the workbook The first reason is not the problem, nor is the second. So why am I now getting this error in Excel 2007 when it has worked for years in Excel 2003? Is there anything short of going back to Office 2003 that I can do? -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code No Longer Works in 2007
Doug,
Remember that Excel 2007 has lots of rows (1,000,000 +) and lots of columns (through 4 letters). Names cannot resolve to valid cell addresses, so you will need to change your code to _not_ give you MIA092, which is a valid cell address in XL2007. Add a letter at the end, or another two letters at the start, or throw in an underscore or two, or.... HTH, Bernie MS Excel MVP "Chaplain Doug" wrote in message ... Excel 2007. I have a well developed workbook with lots of modules of VBA behind it. It worked fine and for years under Excel 2003. Now after installing (upgrading) to Office 2007, I am getting inexplicable errors. One for instance occurs when trying to add a Name (label a cell) as follows: WbNew.Names.Add Name:="MIA" + Left(WbNew.ActiveSheet.Name, 3), RefersToR1C1:= _ "='" + WbNew.ActiveSheet.Name + "'!R" + Trim(Str(TCL + 5)) + "C2" This resolves to: WbNew.Names.Add Name:="MIA092", RefersToR1C1:= "='092-International Ministries'!R10C2" The error message I get is: Runtime error "1004" The name that you entered is not valid. Reasons for this can include: -The name does not begin with a letter or an underscore -The name contains a space or other invalid character -The name conflicts with an Excel built-in name or the name of another object in the workbook The first reason is not the problem, nor is the second. So why am I now getting this error in Excel 2007 when it has worked for years in Excel 2003? Is there anything short of going back to Office 2003 that I can do? -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code No Longer Works in 2007
While it's not your specific problem, you might try to get in the habit of
using & for string concatenation instead of +. Sure, + seems to work, but if you aren't careful, you might get an unpleasant surprise: A & 1 = A1 A + 1 = A1 (sometimes type mismatch error) 1 & 1 = 11 1 + 1 = 2 - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Chaplain Doug" wrote in message ... Excel 2007. I have a well developed workbook with lots of modules of VBA behind it. It worked fine and for years under Excel 2003. Now after installing (upgrading) to Office 2007, I am getting inexplicable errors. One for instance occurs when trying to add a Name (label a cell) as follows: WbNew.Names.Add Name:="MIA" + Left(WbNew.ActiveSheet.Name, 3), RefersToR1C1:= _ "='" + WbNew.ActiveSheet.Name + "'!R" + Trim(Str(TCL + 5)) + "C2" This resolves to: WbNew.Names.Add Name:="MIA092", RefersToR1C1:= "='092-International Ministries'!R10C2" The error message I get is: Runtime error "1004" The name that you entered is not valid. Reasons for this can include: -The name does not begin with a letter or an underscore -The name contains a space or other invalid character -The name conflicts with an Excel built-in name or the name of another object in the workbook The first reason is not the problem, nor is the second. So why am I now getting this error in Excel 2007 when it has worked for years in Excel 2003? Is there anything short of going back to Office 2003 that I can do? -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ALT+ENTER no longer works | Excel Discussion (Misc queries) | |||
New Computer HPVAL no longer works | Excel Worksheet Functions | |||
folderexists code no longer works after moving to excel 2003 | Excel Programming | |||
VB6 Excel 97 code no longer works if Win2K SP3/4 applied. | Excel Programming | |||
Macro no longer works in XP | Excel Programming |