Your Application.Goto line can't work since you can't use brackets in the name of a range - so it can't be called "ArrayMortality()" - and you can't select another cell in a UDF called from a cell anyway.

I took out the () after ArraryMortality and it still does not work.

The function worked when I input all variables except the mortality table.  I need to use the array input in the function argument.

I also changed the first line to:

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

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

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

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

If a cell reference will not work, how about a number which then refers to the range?

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)

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

Yes. You could use INDIRECT(cell_reference) in the function call, assuming that the named range is static and not dynamic. Or you could amend the code so that it takes the range name/address as a string and then refers to Application.Range(ArrayMortality).Value to get the values into an array.

Here is my current code that works.

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

Dim ArrayL(122) As Variant
Dim ArrayInt_Discount(121) As Variant
Dim ArraySurvival(121) As Variant
Dim ArrayAnnAmt(121) As Variant
Dim ArrayPresentValue(121) As Variant
Dim ArrayPayment_Freq_Adj(121) As Variant

Annuity = 0
ArrayL(1) = 1000000
ArrayInt_Discount(1) = 1
ArraySurvival(1) = 1

For i = 1 To 120
ArrayL(i + 1) = (ArrayL(i) * (1 - ArrayMortality(i, 1)))
Next i

For j = 2 To 120
If j < age Then
ArraySurvival(j) = 1
Else
ArraySurvival(j) = 1 - (ArrayL(age) - ArrayL(j)) / ArrayL(age)
End If
Next j

For k = 1 To 119

If k < age Then
ArrayInt_Discount(k) = 1
ArrayPayment_Freq_Adj(k) = 0
ElseIf k = age Then
ArrayInt_Discount(k) = 1
ArrayPayment_Freq_Adj(k) = (13 / 24) + (11 / 24) / (1 + Seg_1) * (1 - (ArrayL(k) - ArrayL(k + 1)) / ArrayL(k))
ElseIf k < (age + 5) Then
ArrayInt_Discount(k) = 1 / ((1 + Seg_1) ^ (k - age))
ArrayPayment_Freq_Adj(k) = (13 / 24) + (11 / 24) / (1 + Seg_1) * (1 - (ArrayL(k) - ArrayL(k + 1)) / ArrayL(k))
ElseIf k < (age + 20) Then
ArrayInt_Discount(k) = 1 / ((1 + Seg_2) ^ (k - age))
ArrayPayment_Freq_Adj(k) = (13 / 24) + (11 / 24) / (1 + Seg_2) * (1 - (ArrayL(k) - ArrayL(k + 1)) / ArrayL(k))
ElseIf ArraySurvival(k) = 0 Then
ArrayPayment_Freq_Adj(k) = 0
Else
ArrayInt_Discount(k) = 1 / ((1 + Seg_3) ^ (k - age))
ArrayPayment_Freq_Adj(k) = (13 / 24) + (11 / 24) / (1 + Seg_3) * (1 - (ArrayL(k) - ArrayL(k + 1)) / ArrayL(k))
End If
Next k

For m = 1 To 120
If m < defAge Then
ArrayAnnAmt(m) = 0
Else
ArrayAnnAmt(m) = 1
End If

Next m

For l = 1 To 121
ArrayPresentValue(l) = ArrayInt_Discount(l) * ArraySurvival(l) * ArrayPayment_Freq_Adj(l) * ArrayAnnAmt(l)
Next l

For p = 1 To 121
Annuity = ArrayPresentValue(p) + Annuity
Next p

End Function

 When I used the function in the spreadsheet, I had to specify the mortality table range name.  In this example, it is "GAM83MALE."  I want to be able to refer to a cell that has the name.  Even better would be entering a number that corresponds to the range.  I could then number the ranges.

I have never used INDIRECT before, but I tried what I think Veloria suggested, and what I think she said to do seemed to work…So in a cell you type like

=Annuity(N5,N6,INDIRECT(T8),N2,N3,N4)

( In your uploaded file T8 contains the text  GAM83Male )

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

The suggestion from Veloria seems to work, and I get the same results if I use

=Annuity(N5,N6,INDIRECT(T8),N2,N3,N4)

or

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

or

=Annuity(N5,N6,Qx!F9:Qx!G129,N2,N3,N4)

or

=Annuity(N5,N6,Qx!F9:G129,N2,N3,N4)

_._____________________

Note: In your coding, ArrayMortality, as you are using it,  is not an array. In your coding, ArrayMortality is a range.

To explain:-

When you use this in your coding,

ArrayMortality(i, 1)

then you are missing things: You are not using any known syntax. But Excel is usually designed to to make a good geuss at what it should be. In this case, that incorrect syntax is actually interpreted by Excel as this full correct syntax

ArrayMortality.Item(i, 1).Value

( Excel usually guesses that you meant to write ArrayMortality.Item(i, 1)  if you miss out the .Item but add something in the ( ).

In addition, usually ,  ( but not always ) ,  if you do not fully put in the correct coding when using a  range, as you are typically doing in your coding, then Excel guesses you meant to be using  the .Value Property. We often say that the default Property of a range object is the .Value property , which Excel  will almost always geuss as what was intended or wanted,  if it sees a range object used in a coding part that does not obviously require a reference to the range object itself.

If we want to be even more pedantic, then strictly speaking its even more complicated. Actually the code line seen by Excel will be like

ArrayMortality.Areas.Item(1).Item(i, 1).Value

As I said in your last Thread, range referencing is actually a very complicated subject and mostly you are lucky not to notice and Excel geusses when you don’t do it properly. )

 Those two code lines above are referring to the value in the cell in the range ArrayMortality at row number of i and column number 1 , but note those are the row number and column number relative to the top left of the range, not necerssarily the row number and column number of the worksheet. The Range Item Property , using two numbers in the ( ) effectively allows you to reference a specific cell in the range where the co ordinte (1, 1) will be top left of that range ..._

_...For example, if this is your function signature line

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

and this, ( or any of the 4 variations I showed ) ,  is used in a cell

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

then ArrayMortality becomes the range GAM83Male ( which is in your workbook as  the range F9:F129 in worksheet Qx )

So this

ArrayMortality(1, 1)

is

ArrayMortality.Item(1, 1).Value

is

Worksheets("Qx").Range("F9:G129”).Item(1, 1).Value

is

Worksheets("Qx").Range("F9”).Value

=0

( is also Application.Range("=Qx!F9:G129").Item(1, 1). Value

is  also Application.Range("=Qx!F9").Value

is also .... etc... ect...    )

With my current code, this function works.

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

I want this to work.

=Annuity(N5,N6,T8,N2,N3,N4)

Ideally, I would like to enter a number for the third argument of the function. (Or a cell reference containing a number.)  The number would then refer to the named range.  The following is an example of the numbering system.  I could then add ranges as needed.

You probably want to do something along the lines of what Veloria was referring to when she said " … you could amend the code so that it takes the range name/address as a string and then …."

_._______________________________

So , if you want to do this…_

=Annuity(N5,N6,T8,N2,N3,N4)

_.... then you could make this modification

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)

_.____________________________________

If you want to do something like this_...

=Annuity(N5,N6,1,N2,N3,N4)

_....then you need a modification something like this:

Public Function Annuity(age, defAge, RngItm As Long, Seg_1, Seg_2, Seg_3)

Dim ArrayMortality As Range

' Set ArrayMortality = Worksheets("Qx").Range("" & Worksheets("Sheet1").Range("T8:T72").Item(RngItm).Value & "")

' Or

 Set ArrayMortality = Application.Range("" & Worksheets("Sheet1").Range("T8:T72").Item(RngItm).Value & "")

In the last example, I use the Range.Item Property with single argument in (  ) . If you use a single argument for the Range.Item Property, VBA takes sequentially each column in a row, then goes down to the next row and takes each column in that  row, then goes down to the next row and and takes each column in that  row, then goes down to the next row and …. etc, so like for example in a three column range

1 2 3

4 5 6

7 .....etc

But as we are looking at just one column in this case ( Range("T8:T72") ) , then each item number gives us a row, like

1

2

3

4

5

... etc

Pseudo like, in Worksheet Sheet1

Range("T8:T72").Item(1).Value =  Range("T8").Value = “GAM83Male“

Range("T8:T72").Item(2).Value =  Range("T9").Value  = „GAM94SQ1“

Alan

If you want to use a number, you should either use some sort of lookup function within the formula that calls the UDF, or also pass the cells that contain the range names as a further argument to the UDF. Hard coding the address of those cells within your UDF code would be bad practice.

This works very well.

How would I do the following?:

Replace the cell reference with a number.

The number would reference a multi-column and row table where the first column is a number, 1,2,3, etc., the second column is the named range of the mortality table.  

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

You're correct.  It did work like that already.

I have a new problem.

I've added a Boolean argument to the function.

Public Function Annuity(age, defAge, MortTblName As Long, Seg_1, Seg_2, Seg_3, IntOnlyDef As Boolean)

If "IntOnlyDef" is true, I want the values in ArrayMortality to be 0 from 1 to defAge

I have the following: 

If IntOnlyDef Then
          For q = 1 To defAge
                ArrayMortality(q) = 0
           Next q
End If

This does not seem to be working.

I DO NOT want to change the values of the mortality table permanently.  

Is it because ArrayMortality is defined as a range?

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.myonlinetraininghub.com/excel-forum/vba-macros/pass-named-range-to-vba-function#p18319
https://www.myonlinetraininghub.com/excel-forum/vba-macros/pass-named-range-to-vba-function#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