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

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

cheers
simon

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

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
>