Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Work-around for lack of " ' " before/after sheet names with no spaces
2003/2007
Have a number of procedures that rely upon sheet names being encapsulated like 'Sheet Name'!Cellref . Currently, if a sheetname has no spaces then SheetName!CellRef. My proceedures rely upon finding the " ' " and/or the " '! " Because of the (recent?) change, I have thought of temporairly concatenating a "space" + some Char to the end of each sheetname to force the issue then removing the "space" + some Char later in the code. Of course, there is the issue of the sheetname currently being at a limit length of 31 and what to do then? Any thoughts of other workarounds?????? EagleOne. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Work-around for lack of " ' " before/after sheet names with no spa
Just look for the !
" wrote: 2003/2007 Have a number of procedures that rely upon sheet names being encapsulated like 'Sheet Name'!Cellref . Currently, if a sheetname has no spaces then SheetName!CellRef. My proceedures rely upon finding the " ' " and/or the " '! " Because of the (recent?) change, I have thought of temporairly concatenating a "space" + some Char to the end of each sheetname to force the issue then removing the "space" + some Char later in the code. Of course, there is the issue of the sheetname currently being at a limit length of 31 and what to do then? Any thoughts of other workarounds?????? EagleOne. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Work-around for lack of " ' " before/after sheet names with no spaces
You could search each formula for the varios sheet names - if found then
check if they are surrounded by '' - if not then do a replace to replace the sheetname with "'" & sheetname & "'" Tim wrote in message ... To be more clear, What the procedures use is essentially ActiveCell.Formula. Of course, the formulas may have a link to within-the-same sheet, to amother sheet same Wb, or to another Wb. To change the sheet names would be overkill and fraught with potential problems. What I may need to do is stuff the string myString = ActiveCell.Formula.with " ' " What is the best approach? EagleOne wrote: 2003/2007 Have a number of procedures that rely upon sheet names being encapsulated like 'Sheet Name'!Cellref . Currently, if a sheetname has no spaces then SheetName!CellRef. My proceedures rely upon finding the " ' " and/or the " '! " Because of the (recent?) change, I have thought of temporairly concatenating a "space" + some Char to the end of each sheetname to force the issue then removing the "space" + some Char later in the code. Of course, there is the issue of the sheetname currently being at a limit length of 31 and what to do then? Any thoughts of other workarounds?????? EagleOne. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Work-around for lack of " ' " before/after sheet names with no spaces
Tim, thanks for the input.
The challenge is to isolate just the SheetNames in an ActiveCell.Formula and then surround them with " ' " if the SheetName does not already have them. Getting the SheetName is the trick. My assumption is that I may need RegEx to do the job. I have just enough VBA experience to be dangerous. My RexEx is minimal as its beginning learning curve is almost vertical out of the box. EagleOne "Tim" <tim j williams at gmail dot com wrote: You could search each formula for the varios sheet names - if found then check if they are surrounded by '' - if not then do a replace to replace the sheetname with "'" & sheetname & "'" Tim wrote in message .. . To be more clear, What the procedures use is essentially ActiveCell.Formula. Of course, the formulas may have a link to within-the-same sheet, to amother sheet same Wb, or to another Wb. To change the sheet names would be overkill and fraught with potential problems. What I may need to do is stuff the string myString = ActiveCell.Formula.with " ' " What is the best approach? EagleOne wrote: 2003/2007 Have a number of procedures that rely upon sheet names being encapsulated like 'Sheet Name'!Cellref . Currently, if a sheetname has no spaces then SheetName!CellRef. My proceedures rely upon finding the " ' " and/or the " '! " Because of the (recent?) change, I have thought of temporairly concatenating a "space" + some Char to the end of each sheetname to force the issue then removing the "space" + some Char later in the code. Of course, there is the issue of the sheetname currently being at a limit length of 31 and what to do then? Any thoughts of other workarounds?????? EagleOne. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Work-around for lack of " ' " before/after sheet names with no
You are already adding the "!" someplace in the code. Just change to "'!"
(there is a single quote in there). the sheet name is easy to stip off. mysheetname = left(cellname,len(instr(cellname,"!") - 1)) " wrote: Tim, thanks for the input. The challenge is to isolate just the SheetNames in an ActiveCell.Formula and then surround them with " ' " if the SheetName does not already have them. Getting the SheetName is the trick. My assumption is that I may need RegEx to do the job. I have just enough VBA experience to be dangerous. My RexEx is minimal as its beginning learning curve is almost vertical out of the box. EagleOne "Tim" <tim j williams at gmail dot com wrote: You could search each formula for the varios sheet names - if found then check if they are surrounded by '' - if not then do a replace to replace the sheetname with "'" & sheetname & "'" Tim wrote in message .. . To be more clear, What the procedures use is essentially ActiveCell.Formula. Of course, the formulas may have a link to within-the-same sheet, to amother sheet same Wb, or to another Wb. To change the sheet names would be overkill and fraught with potential problems. What I may need to do is stuff the string myString = ActiveCell.Formula.with " ' " What is the best approach? EagleOne wrote: 2003/2007 Have a number of procedures that rely upon sheet names being encapsulated like 'Sheet Name'!Cellref . Currently, if a sheetname has no spaces then SheetName!CellRef. My proceedures rely upon finding the " ' " and/or the " '! " Because of the (recent?) change, I have thought of temporairly concatenating a "space" + some Char to the end of each sheetname to force the issue then removing the "space" + some Char later in the code. Of course, there is the issue of the sheetname currently being at a limit length of 31 and what to do then? Any thoughts of other workarounds?????? EagleOne. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Work-around for lack of " ' " before/after sheet names with no spaces
If I manually enter the ' ' around a "non-space" sheet name in a formula
then Excel just removes them. Isn't this going to be a problem? If you only need to adjust the formulas in memory and have a finite list of possible sheetnames then if instr(sFormula, sName & "!")0 then sFormula=replace(sFormula, sName & "!", "'" & sName & "'!") end if or something like that should work as long as you don't have sheetnames which might be substrings of other sheetnames... Tim wrote in message ... Tim, thanks for the input. The challenge is to isolate just the SheetNames in an ActiveCell.Formula and then surround them with " ' " if the SheetName does not already have them. Getting the SheetName is the trick. My assumption is that I may need RegEx to do the job. I have just enough VBA experience to be dangerous. My RexEx is minimal as its beginning learning curve is almost vertical out of the box. EagleOne "Tim" <tim j williams at gmail dot com wrote: You could search each formula for the varios sheet names - if found then check if they are surrounded by '' - if not then do a replace to replace the sheetname with "'" & sheetname & "'" Tim wrote in message . .. To be more clear, What the procedures use is essentially ActiveCell.Formula. Of course, the formulas may have a link to within-the-same sheet, to amother sheet same Wb, or to another Wb. To change the sheet names would be overkill and fraught with potential problems. What I may need to do is stuff the string myString = ActiveCell.Formula.with " ' " What is the best approach? EagleOne wrote: 2003/2007 Have a number of procedures that rely upon sheet names being encapsulated like 'Sheet Name'!Cellref . Currently, if a sheetname has no spaces then SheetName!CellRef. My proceedures rely upon finding the " ' " and/or the " '! " Because of the (recent?) change, I have thought of temporairly concatenating a "space" + some Char to the end of each sheetname to force the issue then removing the "space" + some Char later in the code. Of course, there is the issue of the sheetname currently being at a limit length of 31 and what to do then? Any thoughts of other workarounds?????? EagleOne. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Work-around for lack of " ' " before/after sheet names with nospaces
You can build the formula in code and include the apostrophes.
If excel decides it doesn't need them, then it'll remove them. So if you include them, it's no harm, no foul. Excel works the same way if you do it manually. Create a test workbook with two worksheets--Sheet1 and Sheet2. In A1 of Sheet2, type this formula: ='Sheet1'!a1 and hit enter. Note that excel removed the apostrophes after you hit enter. Excel is very smart. And as an aside, if you ever needed a worksheet formula that used =indirect() and pointed to a cell that contained a sheet name, you could write the formula like: =indirect("'" & a1 & "'!A1") If the apostrophes aren't required, then it won't hurt. ================ ps. Another way to not care how to write the address in a formula is to let excel's vba do the work. Dim myCell as range dim myFormulaCell as range Set myformulacell = worksheets("Sheet1").range("a1") set mycell = worksheets("Sheet2").range("x9") myformulacell.formula = "=" & mycell.address(external:=true) or to use it in an =sum() function: Dim myFormulaCell as range dim myRng as range Set myformulacell = worksheets("Sheet1").range("a1") set myRng = worksheets("sheet2").range("a1:z99") myformulacell.formula = "=sum(" & myrng.address(external:=true) & ")" Tim wrote: If I manually enter the ' ' around a "non-space" sheet name in a formula then Excel just removes them. Isn't this going to be a problem? If you only need to adjust the formulas in memory and have a finite list of possible sheetnames then if instr(sFormula, sName & "!")0 then sFormula=replace(sFormula, sName & "!", "'" & sName & "'!") end if or something like that should work as long as you don't have sheetnames which might be substrings of other sheetnames... Tim wrote in message ... Tim, thanks for the input. The challenge is to isolate just the SheetNames in an ActiveCell.Formula and then surround them with " ' " if the SheetName does not already have them. Getting the SheetName is the trick. My assumption is that I may need RegEx to do the job. I have just enough VBA experience to be dangerous. My RexEx is minimal as its beginning learning curve is almost vertical out of the box. EagleOne "Tim" <tim j williams at gmail dot com wrote: You could search each formula for the varios sheet names - if found then check if they are surrounded by '' - if not then do a replace to replace the sheetname with "'" & sheetname & "'" Tim wrote in message . .. To be more clear, What the procedures use is essentially ActiveCell.Formula. Of course, the formulas may have a link to within-the-same sheet, to amother sheet same Wb, or to another Wb. To change the sheet names would be overkill and fraught with potential problems. What I may need to do is stuff the string myString = ActiveCell.Formula.with " ' " What is the best approach? EagleOne wrote: 2003/2007 Have a number of procedures that rely upon sheet names being encapsulated like 'Sheet Name'!Cellref . Currently, if a sheetname has no spaces then SheetName!CellRef. My proceedures rely upon finding the " ' " and/or the " '! " Because of the (recent?) change, I have thought of temporairly concatenating a "space" + some Char to the end of each sheetname to force the issue then removing the "space" + some Char later in the code. Of course, there is the issue of the sheetname currently being at a limit length of 31 and what to do then? Any thoughts of other workarounds?????? EagleOne. -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Work-around for lack of " ' " before/after sheet names with no spaces
Thanks so much Dave!
Dave Peterson wrote: You can build the formula in code and include the apostrophes. If excel decides it doesn't need them, then it'll remove them. So if you include them, it's no harm, no foul. Excel works the same way if you do it manually. Create a test workbook with two worksheets--Sheet1 and Sheet2. In A1 of Sheet2, type this formula: ='Sheet1'!a1 and hit enter. Note that excel removed the apostrophes after you hit enter. Excel is very smart. And as an aside, if you ever needed a worksheet formula that used =indirect() and pointed to a cell that contained a sheet name, you could write the formula like: =indirect("'" & a1 & "'!A1") If the apostrophes aren't required, then it won't hurt. ================ ps. Another way to not care how to write the address in a formula is to let excel's vba do the work. Dim myCell as range dim myFormulaCell as range Set myformulacell = worksheets("Sheet1").range("a1") set mycell = worksheets("Sheet2").range("x9") myformulacell.formula = "=" & mycell.address(external:=true) or to use it in an =sum() function: Dim myFormulaCell as range dim myRng as range Set myformulacell = worksheets("Sheet1").range("a1") set myRng = worksheets("sheet2").range("a1:z99") myformulacell.formula = "=sum(" & myrng.address(external:=true) & ")" Tim wrote: If I manually enter the ' ' around a "non-space" sheet name in a formula then Excel just removes them. Isn't this going to be a problem? If you only need to adjust the formulas in memory and have a finite list of possible sheetnames then if instr(sFormula, sName & "!")0 then sFormula=replace(sFormula, sName & "!", "'" & sName & "'!") end if or something like that should work as long as you don't have sheetnames which might be substrings of other sheetnames... Tim wrote in message ... Tim, thanks for the input. The challenge is to isolate just the SheetNames in an ActiveCell.Formula and then surround them with " ' " if the SheetName does not already have them. Getting the SheetName is the trick. My assumption is that I may need RegEx to do the job. I have just enough VBA experience to be dangerous. My RexEx is minimal as its beginning learning curve is almost vertical out of the box. EagleOne "Tim" <tim j williams at gmail dot com wrote: You could search each formula for the varios sheet names - if found then check if they are surrounded by '' - if not then do a replace to replace the sheetname with "'" & sheetname & "'" Tim wrote in message . .. To be more clear, What the procedures use is essentially ActiveCell.Formula. Of course, the formulas may have a link to within-the-same sheet, to amother sheet same Wb, or to another Wb. To change the sheet names would be overkill and fraught with potential problems. What I may need to do is stuff the string myString = ActiveCell.Formula.with " ' " What is the best approach? EagleOne wrote: 2003/2007 Have a number of procedures that rely upon sheet names being encapsulated like 'Sheet Name'!Cellref . Currently, if a sheetname has no spaces then SheetName!CellRef. My proceedures rely upon finding the " ' " and/or the " '! " Because of the (recent?) change, I have thought of temporairly concatenating a "space" + some Char to the end of each sheetname to force the issue then removing the "space" + some Char later in the code. Of course, there is the issue of the sheetname currently being at a limit length of 31 and what to do then? Any thoughts of other workarounds?????? EagleOne. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allow "List" to work in a locked sheet | Excel Worksheet Functions | |||
Stop the "Personal" sheet from popping up every time I open a work | Excel Discussion (Misc queries) | |||
Sub to strip away "Sheet" prefix from names | Excel Programming | |||
pictures to work with "data" "sort" option | Excel Discussion (Misc queries) | |||
Backup to specific folder if workbook names begins with "NSR" or "MAC" | Excel Programming |