In the spreadsheet attached to this thread, the following function works.

=Annuity(N5,N6,GAM83Male,N2,N3,N4)…_

_...which function coding exactly worked with that

_.________________________________

Is it possible to replace "GAM83MALE" with a cell reference? …_

_...In your uploaded file , GAM83Male is the range F9:F129 in worksheet Qx

So , you can refer to that in a spreadsheet either as  

GAM83Male

or

Qx!F9:G129

or

Qx!F9:Qx!G129

_._________________________________

By the way, the () on a variable usually  indicates that the variable is an array.  When you do something like this

ArrayMortality() As Variant

you are  defining that all the elements of the array will be of Variant type  ( An array is like a lot of variables together organised in some grid arrangement –to a first approximation you could say it is like a spreadsheet inside VBA that you can’t see , and each of the invisible cells is an element of the array )

If you do this,

ArrayMortality As Variant

then you are defining ArrayMortality as a single variable of Variant type.

Variant type is usually the default type you get when you don’t define specifically  the type .

So all these next  three lines are the same

 Public Function Annuity(age, defAge, ArrayMortality As Variant, Seg_1, Seg_2, Seg_3)

Public Function Annuity(age, defAge, ArrayMortality, Seg_1, Seg_2, Seg_3)

Public Function Annuity(age As Variant, defAge As Variant, ArrayMortality As Variant, Seg_1 As Variant, Seg_2 As Variant, Seg_3 As Variant)

That is what I dided it already, I thoughted that is what you meant?

_.______________________-

Or/ And this is what I think Veloria she did meant it like this:

Public Function Annuity(age, defAge, CelRef As String, Seg_1, Seg_2, Seg_3)

Dim ArrayMortality As Range

 Set ArrayMortality = Worksheets("Qx").Range(CelRef)

' Or

 Set ArrayMortality = Application.Range(CelRef)

Then in cell like is this formula

=Annuity(N5,N6,VLOOKUP(1,Sheet1!S8:T72,2,FALSE),N2,N3,N4)

Alan

Hi David.
I don’t understand fully what you are on about. I am probably missing some information from you.

Please always supply a workbook and tell me exactly what you are doing, or trying to do.
Please always tell me what version of your function or coding you are using.
( Because we have been discussing many versions of your macro so I am confused now about which function and which of your coding you are using now.
Please remember you may know what you are doing but we don’t. Please try to explain your issues more fully
Thanks)
_.___________________________________________

Like I explained here:
https://www.myonlinetraininghu.....ion#p18319
https://www.myonlinetraininghu.....ion#p18329
you were using ArrayMortality some times as a range.

Previously we did have  …. ArrayMortality becomes the range GAM83Male ( which is in your workbook was the range F9:F129 in worksheet Qx …….. ) …..

So previously like…. For Example

          For q = 1 To 3
                ArrayMortality(q) = 0
           Next q

Is like then will be

ArrayMortality(1)
is ArrayMortality.Item(1)
is Worksheets("Qx").Range("F9:F129").Item(1)
is Worksheets("Qx").Range("F9")

Similarly
ArrayMortality(2) Is Worksheets("Qx").Range("F10")

And
ArrayMortality(3) Is Worksheets("Qx").Range("F11")

But I am not sure what is your issue currently

_.____

And please also tell me what version of Microsoft Office you are using, thanks

Alan

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

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