Thank you Alan.

I have attached my current spreadsheet.  

I am using Office 365.

I have a section commented out that does not work.  

Once the mortality table is loaded in, I want to change some of the values in ArrayMortality if the Boolean IntOnlyDef is True.  I want to change the values in positions 0 through defAge.

For example. (I'm making these numbers up)

Some of the values of ArrayMortality are as follows.

1   .002

2   .003

.....   (I'm skipping some for this example)

49   .025

50   .026

51   .027.

....   (Skipping the rest of the array for this example.)

If the Boolean IntOnlyDef is True and defAge = 50, then the ArrayMortality is changed to this.

1   .000

2   .000

....  (Change the ones I'm skipping to .000)

49  .000

50  .000

51 .027

... (Skipping the rest for demonstration.  They do not change.)

I do NOT want the values changed in the named range in the spreadsheet.

Hi
_1) I see no attached file
_2) ArrayMortality is a named range in the spreadsheet. So if you change ArrayMortality then you change the named range, ArrayMortality , in the spreadsheet.

ArrayMortality is not an array. It is never an array. ArrayMortality is a range in the spreadsheet. If you change it, then  it will change the values in the spreadsheet.

You cannot change it and not change it. That is nonsense

But,  I think I may understand what you are trying to say.
I think I may understand what you are trying to do.
When I see your file I will look again

Alan

I added the file.  

Hi David

First, something important to remember: You can call your variables anything you like. The word you chose is completely arbitrary.
For example, in your macro you use a range in your function. You can call it anything you like
All of these mean exactly the same range:
( just small sample for this explanation purposes only : Range H14 – H19 )  

.

.

.

.

.

.

.

.

.

.

Dim myRange As Range
 Set myRange = Range("H14:H19")
or
Dim ArrayMortality As Range
 Set ArrayMortality = Range("H14:H19")
or
Dim DavidsRange As Range
 Set DavidsRange = Range("H14:H19")
or
Dim Ode_to_a_Small_Lump_of_Green_Putty_I_Found_in_My_Armpit_One_Midsummer_Morning As Range
 Set Ode_to_a_Small_Lump_of_Green_Putty_I_Found_in_My_Armpit_One_Midsummer_Morning = Range("H14:H19")
or
Dim DavidsVerticalDataList As Range
 Set DavidsVerticalDataList = Range("H14:H19")

It makes no difference what you call it. They are always your spreadsheet range:

.

.

.

.

.

.

.

.

.

.

It is a spreadsheet range. If you change it , it will change that spreadsheet range

But an array is different.
Same example

Dim RangeMortality As Range
 Set RangeMortality = Range("H14:H19")
Dim ArrayMortality() As Variant
 Let ArrayMortality() = RangeMortality.Value

This is still range RangeMortality

.

.

.

.

.

.

:

It is a spreadsheet range. If you change it , it will change the spreadsheet range

RangeMortality.Item(1, 1).Value = 0.000342
Because this is just single column then also is RangeMortality.Item(1).Value = 0.000342
( Can be written wrongly incomplete like RangeMortality(1, 1) and Excel will guess what you mean if you are lucky)

This below is array ArrayMortality()
0.000342
0.000318
0.000302
0.000294
0.000292
0.000293

An array is just numbers in a variable inside computer memory. It is not a spreadsheet range. It is just a list of numbers stored somewhere in the computer's memory. We can't see those numbers. They are hidden from us.

If you change it, it will not change your spreadsheet range

We cannot see the numbers because they are not in any spreadsheet range, but we can access them in coding, like

ArrayMortality(1 , 1) = 0.000342

_.__________________

Array referrencing is easy to understand:-
ArrayMortality(1 , 1)
It can only be written as ArrayMortality(1 , 1) ' There is no other way to write this!

Range referrencing is difficult to understand:-
RangeMortality.Item(1, 1).Value
It can sometimes be written wrongly as RangeMortality(1, 1) , and, if you are lucky, Excel will guess what you mean
There are very many different ways to referrence a spreadsheet range.

_.________________________________

So your solution will be as follows, applying to  your last file: - Use an array in your function. Get your array at the start from your range. Then you can do anything you like to that array, and it won't have any effect on your ranges

Dim RngMortality As Range
 Set RngMortality = Application.Range("" & Worksheets("MortalityTables").Range("Mort_Tables_Names").Item(MortTblName).Value & "")
Dim ArrMortality() As Variant
 Let ArrMortality() = RngMortality.Value ' Array comes from range

' or just
Dim ArrMortality() As Variant
 Let ArrMortality() = Application.Range("" & Worksheets("MortalityTables").Range("Mort_Tables_Names").Item(MortTblName).Value & "").Value ' Array comes from range

Now you can change values in your  array , but it must be like ArrMortality(q , 1)
' ArrMortality(q) will not work!

Changing any array values will not effect any spreadsheet ranges.

_.-__

Alan

RangeMortality.Item(1, 1).Value
and

RangeMortality(1, 1).Value

are the same, as indeed are RangeMortality.Cells.Item(1, 1).Value and RangeMortality.Cells(1, 1).Value

RangeMortality(1, 1) is the incorrect way to use the Range Item Property like its proper way RangeMortality.Item(1, 1) , but I think you will usually get away with it.

Similarly in the last two, Cells.Item(1, 1) is the proper way to write Cells(1, 1)

Using Cells in this case is pointless I think since it returns a range object consisting of all the Cells of the object its applied to. So I think it returns what’s before it in this case.  Applying it to a worksheet seems to be the most valid use of it. 

I am not 100% sure on all that, but I thinks that’s basically it.

I’m using the word incorrect , proper , valid all very  loosely and probably these and related aspects of the Areas is open to pointless academic debate, since there’s no real authority on this. You can argue if you like that the Cells item Property doesn’t exist: You could say Cells returns a range object to which you apply the Range Item Property.
Microsoft will agree with you one day and contradict you the next. Say what you like, it doesn’t matter. Microsoft change there documentation on it seemingly randomly.

Similarly in the last two, Cells.Item(1, 1) is the proper way to write Cells(1, 1)

Why is that the proper way?
TypeName(Cells.Item(1, 1)) returns an object of Range type, like any version below :

Cells(1)

Cells(1,1)

The object returned has the same Range type, all variations are 100% correct, there is no "better range" concept.

Same for Collections or Dictionary objects:
They accept different syntaxes, all correct:

objDict.Item("ItemKey"), objDict("ItemKey"), objDict.Item(1), objDict(1)

objColl.Item("ItemKey"), objColl("ItemKey"), objColl.Item(1), objColl(1)

The Item method is the default method for an object. Therefore, all variations are equivalent.

Microsoft says:
The default member of Range forwards calls with parameters to the Item member. Thus, someRange(1) and someRange(1,1) are equivalent to someRange.Item(1) and someRange.Item(1,1), respectively.

I would avoid Item, as it might bring more confusion, for example:

Debug.Print Range("B2:D4").Item(2, "D").Address 'Prints "$E$3"

More, if you select let's say cell G1, this code will return P1:
activecell.item(1,10).address

If you select another cell, the code will return another address, based on selected cell.

In this scenario, Item acts like the offset method:
activecell.Offset(1-1,10-1).address will also return P1.

Most of David’s problems originally arose because he was unknowingly using range object item referencing  in this form
ArrayMortality(j, l)
, but he was mistakenly thinking he had an array.
He was using a range object and thinking it was an array, or visa versa.
So I put some emphasis on discussing Range Item and Range .Value ideas to try and help get across the difference between range objects and arrays.

I tend to be a bit more explicit when answering forum questions as I might be otherwise. I think it is easier to “go backwards” later, rather than starting to rely on implied defaults, and then trying to figure out what’s going on when things go wrong later. Just personal opinion , that’s all.
The word “proper” was a loose way of saying explicit, to some extent, I suppose.
Once again, I am using the term "go backwards” very loosely. It can be regarded as very professional to use the shortest, simplest coding making good use of all the implied defaults.

I don’t like these range item things that we are discussing myself.
My favourite way to range reference is the single argument Range(" ") way. But just personal choice again

Alan

Hi Alan,
You did a great job with detailed explanations, thank you for that, we all have preferences and habits.
Indeed, it is hard to see the difference between objects when reading data from sheets. For an object declared as a variant:

Dim MyData as Variant,
Set MyData =Range("A1:A10") makes MyData a Range
      MyData =Range("A1:A10") makes MyData a bidimensional Array

Set is the only difference between the 2 statements, so it should be used wisely when assigning objects, Set is used only for assigning object references to a variable.
Objects like workbooks, worksheets, ranges, names, applications, cannot be assigned to a variable without the Set statement. Also, Set cannot be used to assign elements that are not valid object references.
Hope it helps clarifying things.

Thank you.  This is what I needed.