protecting formulas with named ranges
 

I hope someone can help me.
Several employees at my company use an excel workbook that has various
modules and is both formula and macro-intensive. One of the things we do when
creating a new workbook is to bring in some existing worksheets that have
many formulas referencing named ranges. In order to do this without creating
links, we add a | before the formulas (so they come into the workbook as
text, like |=formulahere ) and then once the worksheet is copied into the
new workbook we remove the | to make it a formula again. This is done via a
macro (we call it 'protecting the formula', not to be confused with Excels
protection).

It works pretty speedy in Office 2000, but in Office 2003 seems to really
slow down. I have tried changing calculation to manual and turning off
EnableEvents and ScreenUpdating, to no avail. Those don't seem to make a
difference.

If anyone has ideas for how I could speed this up in Office 2003, I'd really
appreciate it. Thanks!

Here is the basic code:
__________________________________________

Private Sub UnprotectASheet(wrksht As Worksheet)
'
Dim rng As Range
Dim cel As Range

On Error GoTo Err_Handler
Set rng = wrksht.Cells.SpecialCells(xlCellTypeConstants, 23)

On Error Resume Next
For Each cel In rng
If Left$(cel.Formula, 1) = "|" Then
cel.Formula = Mid$(cel.Formula, 2)
End If
Next cel
Exit Sub

Err_Handler:
Err.Clear

End Sub

protecting formulas with named ranges
 

I think I'd just try changing |= to =

If you need a macro, you could record one when you
Edit|replace
what: |=
with: =
replace all.

If that string isn't unique enough, you could make it more unique:
||||||=

Or use the specialcells to find just the text values.

Option Explicit
Private Sub UnprotectASheet(wrksht As Worksheet)

Dim rng As Range

Set rng = Nothing
On Error Resume Next
Set rng = wrksht.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0

If rng Is Nothing Then
'do nothing
Else
rng.Replace what:="|=", replacement:="=", lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False
End If

End Sub


Maybe you could have gotten it faster by not use 23 in:
Set rng = wrksht.Cells.SpecialCells(xlCellTypeConstants, 23)
Since you only had to inspect the Text cells (no errors, no numbers, no
logicals).

Robin wrote:

I hope someone can help me.
Several employees at my company use an excel workbook that has various
modules and is both formula and macro-intensive. One of the things we do when
creating a new workbook is to bring in some existing worksheets that have
many formulas referencing named ranges. In order to do this without creating
links, we add a | before the formulas (so they come into the workbook as
text, like |=formulahere ) and then once the worksheet is copied into the
new workbook we remove the | to make it a formula again. This is done via a
macro (we call it 'protecting the formula', not to be confused with Excels
protection).

It works pretty speedy in Office 2000, but in Office 2003 seems to really
slow down. I have tried changing calculation to manual and turning off
EnableEvents and ScreenUpdating, to no avail. Those don't seem to make a
difference.

If anyone has ideas for how I could speed this up in Office 2003, I'd really
appreciate it. Thanks!

Here is the basic code:
__________________________________________

Private Sub UnprotectASheet(wrksht As Worksheet)
'
Dim rng As Range
Dim cel As Range

On Error GoTo Err_Handler
Set rng = wrksht.Cells.SpecialCells(xlCellTypeConstants, 23)

On Error Resume Next
For Each cel In rng
If Left$(cel.Formula, 1) = "|" Then
cel.Formula = Mid$(cel.Formula, 2)
End If
Next cel
Exit Sub

Err_Handler:
Err.Clear

End Sub

--

Dave Peterson

protecting formulas with named ranges
 

Try doing an edit replace on |= to = across the whole workbook

cheers
simon


--
Simon Murphy
------------------------------------------------------------------------
Simon Murphy's Profile: http://www.excelforum.com/member.php...o&userid=26538
View this thread: http://www.excelforum.com/showthread...hreadid=401840

protecting formulas with named ranges
 

Thank you!

"Dave Peterson" wrote:

I think I'd just try changing |= to =

If you need a macro, you could record one when you
Edit|replace
what: |=
with: =
replace all.

If that string isn't unique enough, you could make it more unique:
||||||=

Or use the specialcells to find just the text values.

Option Explicit
Private Sub UnprotectASheet(wrksht As Worksheet)

Dim rng As Range

Set rng = Nothing
On Error Resume Next
Set rng = wrksht.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0

If rng Is Nothing Then
'do nothing
Else
rng.Replace what:="|=", replacement:="=", lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False
End If

End Sub


Maybe you could have gotten it faster by not use 23 in:
Set rng = wrksht.Cells.SpecialCells(xlCellTypeConstants, 23)
Since you only had to inspect the Text cells (no errors, no numbers, no
logicals).

Robin wrote:

I hope someone can help me.
Several employees at my company use an excel workbook that has various
modules and is both formula and macro-intensive. One of the things we do when
creating a new workbook is to bring in some existing worksheets that have
many formulas referencing named ranges. In order to do this without creating
links, we add a | before the formulas (so they come into the workbook as
text, like |=formulahere ) and then once the worksheet is copied into the
new workbook we remove the | to make it a formula again. This is done via a
macro (we call it 'protecting the formula', not to be confused with Excels
protection).

It works pretty speedy in Office 2000, but in Office 2003 seems to really
slow down. I have tried changing calculation to manual and turning off
EnableEvents and ScreenUpdating, to no avail. Those don't seem to make a
difference.

If anyone has ideas for how I could speed this up in Office 2003, I'd really
appreciate it. Thanks!

Here is the basic code:
__________________________________________

Private Sub UnprotectASheet(wrksht As Worksheet)
'
Dim rng As Range
Dim cel As Range

On Error GoTo Err_Handler
Set rng = wrksht.Cells.SpecialCells(xlCellTypeConstants, 23)

On Error Resume Next
For Each cel In rng
If Left$(cel.Formula, 1) = "|" Then
cel.Formula = Mid$(cel.Formula, 2)
End If
Next cel
Exit Sub

Err_Handler:
Err.Clear

End Sub

--

Dave Peterson