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.

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.

Work-around for lack of " ' " before/after sheet names with no spaces
 

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.

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.

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.

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.

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.

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

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.