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

[EagleOne]

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.

 

[Guest]

Just look for the !

 

[EagleOne]

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

 

[Tim]

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

 

[EagleOne]

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

 

[Guest]

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))

 

[Tim]

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

 

[Dave Peterson]

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) & ")"

 

[EagleOne]

Thanks so much Dave!

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) & ")"