Log In
Register
Home
October 21, 2020
Offline
I am trying to pass a named range to a function. I receive a #VALUE! error in the cell when I try the following code. The code was working before I tried to pass the table in.
I have attached the spreadsheet.
Public Function Annuity(age, defAge, ArrayMortality() As Variant, Seg_1, Seg_2, Seg_3)
'Dim ArrayMortality As Variant
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
'Range("Up84MALE").Select
Application.Goto Workbooks("VBA mortality attempt backup.xlsm").Sheets("Qx").Range("ArrayMortality()")
'Dim Seg_1 As Variant
'Dim Seg_2 As Variant
'Dim Seg_3 As Variant
'Dim age As Variant
'Dim defAge As Variant
'Dim Annuity As Variant
'Seg_1 = 0.03
'Seg_2 = 0.04
'Seg_3 = 0.05
'age = 15
'defAge = 15
'ArrayMortality = Range("mortality").Value
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)))
'Debug.Print ArrayL(i)
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
'For o = 1 To 121
'Debug.Print Annuity
'Next o
End Function
See Answer
Trusted Members
Moderators
November 1, 2018
Offline
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.
October 21, 2020
Offline
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.
October 21, 2020
Offline
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?
December 29, 2020
Offline
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)
Trusted Members
Moderators
November 1, 2018
Offline
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.
October 21, 2020
Offline
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.
December 29, 2020
Offline
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... )
October 21, 2020
Offline
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.
| 1 | GAM83Male |
| 2 | GAM94SQ1 |
| 3 | GAM94SQ2 |
| 4 | IAM71Female |
December 29, 2020
Offline
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
Answers Post
Trusted Members
Moderators
November 1, 2018
Offline
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.
October 21, 2020
Offline
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.
December 29, 2020
Offline
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
October 21, 2020
Offline
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?
December 29, 2020
Offline
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
October 21, 2020
Offline
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.
December 29, 2020
Offline
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
October 21, 2020
Offline
I added the file.
December 29, 2020
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
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
1 Guest(s)
