Named Ranges vs. Constant Addresses, sheet protection, Guidance.

[Neal Zimm]

Hi All,
I am learning about named ranges, so please bear with me.
I have an Addin holding template sheets copied to the user's workbook.

I'm looking for the 'best' way to go forward as I'm having unanticipated
problems with the named ranges, and am at a point in time where
I have to pick one way to go.

1. Can you use named ranges to copy values from locked cells on a
protected sheet? I'm getting intermittent errors.

I just realized that the Insert Define Name menu path is grayed out
on a protected sheet. (Shoulda been a 'heads up' to me, but wasn't)


Up 'til now I've been using public constants in the addin such as:

Public Const sTotRng = "D27" ' users workbook is referenced to the addin.

and then using code like: varname = UserWs.range(sTotRng).value

I thought there was value in establishing a named range, versus changing
a lot of constants for cell ranges, but see below.

MdlWs is an addin template sheet.

Mdlws.range(sTotRng).Copy Destination:=UserWs.range(sTotRng)

has been working when refreshing the user's sheet for formats, etc.
The above also worked fine when copying from a protected sheet in the
addin.


2. What can you tell me about why the commented line in the ExpenseAdd:
routine below did not work ?

As a workaround, I wrote the function below it to give me the
equivalent of my public constant method, it worked, but then, why go thru all
the trouble ? Maybe my "constant" method is the way to go.

3. Over time, if the layout of the MdlWs templates changes, changing
the constants for cell addresses does not seem too different from
changing the named ranges.

Your thoughts ??

Thanks,
Neal


ExpenseAdd: 'Copy from model. money cell + literal.

' line below gets 1004 error, the value of gWktExpenLitRng is the
' named range. Lines below it were the workaround.

' MdlWs.Range(gWktExpenLitRng).Copy _
Destination:=UserWs.Range(gWktExpenLitRng)

CellAdr = sCellAdr_vsRefToF(Wbk.Names(gWktExpenLitRng).RefersTo)
'CellAdr dimmed as string

MdlWs.Range(CellAdr).Copy Destination:=UserWs.Range(CellAdr)
Return


Public Function sCellAdr_vsRefToF(sRefersTo As String) As String

'Return plain address portion from "=!$a$1" .Names(xxxx).RefersTo
' also: "=!$a$1:$b$2,!$c$3:$d$4"

Dim Text As String, iByte As Integer

iByte = InStr(sRefersTo, "!")
Text = Right(sRefersTo, Len(sRefersTo) - iByte)
Text = Replace(Text, "!", "")
sCellAdr_vsRefToF = Text

End Function

 

[broro183]

hi Neal

1. Can you use named ranges to copy values from locked cells on a
protected sheet? I'm getting intermittent errors.

Yes, you can copy from locked cells on a protected sheet (may depend on
the protection settings). If you use a macro, which is reasonable within
an addin, you can unlock the sheet within the code & relock it or even
protect with "userinterfaceonly:= true". You can copy from a protected
sheet but can't necessarily paste into one, where are you pasting the
info to?

I just realized that the Insert Define Name menu path is grayed out on
a protected sheet. (Shoulda been a 'heads up' to me, but wasn't)

You must unprotect before Inserting a defined Name (then you can
reprotect), but once you have created the Names once as part of the
addin/template development you shouldn't need to do it again.

I would use named ranges & copy the *values* (& whatever else is
wanted) from the addin sheet (rather than copying "everything").

Code:
--------------------
UserWs.range(sTotRng).value = Mdlws.range(sTotRng).value
--------------------


Are you rolling out a new template as a one off & modifying existing
formatting?
If not, I suggest developing & providing the complete template &
potentially copying the info from the user's file into a copy of the
standardised template. This ensures one consistent format &
minimises/removes risks of wrong values being transferred.

2. What can you tell me about why the commented line in the ExpenseAdd:
routine below did not work ?)

I would use the above approach once you get the naming convention right
to identify the 2 different files (sorry I haven't delved into this).

3. Over time, if the layout of the MdlWs templates changes, changing the
constants for cell addresses does not seem too different from changing
the named ranges.

Try & standardise your template as much as possible before release by
consulting end users & using best practice principles, such as those in
the below link, to minimise/eradicate changes in the layout. If you have
to move cells that are defined as constants/within Names, use [ctrl + x]
to cut the cell & paste it into the new location. This results in the
named range (for static names) being moved to the new location.

Spreadsheet design pdf, http://www.eusprig.org/smbp.pdf, sourced from:
'Recommended Spreadsheet (Excel) web sites, tools, groups, best
practice, financial modelling, modeling'
(http://www.sysmod.com/sslinks.htm)

hth
Rob

 

[Neal Zimm]

Thanks much, your answers are good food for thought.

After I posted the questions, and wrote a couple of macros dealing with the
the .Names collection, I'm going with the following summary approach that I
think strikes a "middle ground".

1. addin workbook named ranges will be like:
"range name" = "=sheetname!cell address[,!cell address]

2. a macro, during addin development will update the names collection and
remove the sheetname from the range formula:
"range name" = "=!cell address[,!cell address]

This makes the addin named ranges generic to any worksheet.

3. At user workbook open event, (user books are referenced to the addin),
a macro will update the user book's name collection from the addin
.Names collection.

If the App undergoes any change, the changes to the addin sheets, both
the sheets themselves and their named ranges, will "flow" to the user books.

I did not put the following fact in the original posting since it
really was not germaine to the "problem" you did not have time to look into,
but one
addin sheet, can be copied to many sheets in the user's book, so the cell
addresses must be the same in each user sheet.

Thanks again
Neal


--
Neal Z

hi Neal

1. Can you use named ranges to copy values from locked cells on a
protected sheet? I'm getting intermittent errors.

Yes, you can copy from locked cells on a protected sheet (may depend on
the protection settings). If you use a macro, which is reasonable within
an addin, you can unlock the sheet within the code & relock it or even
protect with "userinterfaceonly:= true". You can copy from a protected
sheet but can't necessarily paste into one, where are you pasting the
info to?

I just realized that the Insert Define Name menu path is grayed out on
a protected sheet. (Shoulda been a 'heads up' to me, but wasn't)

You must unprotect before Inserting a defined Name (then you can
reprotect), but once you have created the Names once as part of the
addin/template development you shouldn't need to do it again.

I would use named ranges & copy the *values* (& whatever else is
wanted) from the addin sheet (rather than copying "everything").

Code:
--------------------
UserWs.range(sTotRng).value = Mdlws.range(sTotRng).value
--------------------


Are you rolling out a new template as a one off & modifying existing
formatting?
If not, I suggest developing & providing the complete template &
potentially copying the info from the user's file into a copy of the
standardised template. This ensures one consistent format &
minimises/removes risks of wrong values being transferred.

2. What can you tell me about why the commented line in the ExpenseAdd:
routine below did not work ?)

I would use the above approach once you get the naming convention right
to identify the 2 different files (sorry I haven't delved into this).

3. Over time, if the layout of the MdlWs templates changes, changing the
constants for cell addresses does not seem too different from changing
the named ranges.

Try & standardise your template as much as possible before release by
consulting end users & using best practice principles, such as those in
the below link, to minimise/eradicate changes in the layout. If you have
to move cells that are defined as constants/within Names, use [ctrl + x]
to cut the cell & paste it into the new location. This results in the
named range (for static names) being moved to the new location.

Spreadsheet design pdf, http://www.eusprig.org/smbp.pdf, sourced from:
'Recommended Spreadsheet (Excel) web sites, tools, groups, best
practice, financial modelling, modeling'
(http://www.sysmod.com/sslinks.htm)

hth
Rob


--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...