• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
You are here: Home
Lost password?
sp_Search
Advanced Search|Last Search Results
Advanced Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search

Please confirm you want to mark all posts read

Mark all topics read

sp_MobileMenu Actions
Actions
sp_LogInOut
Log In
sp_Search

Search Forums

sp_RankInfo
Ranks Information
Avatar

New/Updated Topics

General Excel Questions & Answers

  Sick leave periods in one cell

  Amortization

  removing pesky hidden xml sheets in a workbook

  Customer Order From Facebook Page.

  Grey out master list when selected in dropdown

Dashboards & Charts

  Connecting 2 separate pivot charts or data sets to calculate…

VBA & Macros

  Send Email Code Error

  Direccionar de un rango a una celda especifica

  how can i add windows media player in excel 2016

  Changing shape colours

Power Query

  Unpivoting data

Power Query

  pq_7.01_parameter_tables

Power BI

  How identify the URL to connect power bi with Project online

Excel Expert

  new and deleted entries

  Excel Dashboard

Select Forum

  Rules and Guides

Forum Rules and Guides

  Public Forums - For Registered Users

General Excel Questions & Answers

Dashboards & Charts

VBA & Macros

Power Query

Power Pivot

  Course Members Only

Excel Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Power BI

Excel

Word

Outlook

Excel Expert

Excel for Customer Service Professionals

Excel Analysis Toolpak

Excel Tables

Excel for Operations Management

Financial Modelling

Advanced Excel Formulas

Pivot Tables Quick Start

ForumsVBA & Macros
sp_TopicIcon
Pass Named Range to VBA Function
12Jump to page
Avatar
David Davala
Posts: 26
Level 0
January 4, 2021 - 8:22 am

1

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

sp_AnswersTopicSeeAnswer
Avatar
Velouria
London or thereabouts
Posts: 613

Level 4
January 4, 2021 - 7:43 pm

2

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.

Avatar
David Davala
Posts: 26
Level 0
January 5, 2021 - 11:13 pm

3

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.

Avatar
David Davala
Posts: 26
Level 0
January 6, 2021 - 12:25 am

4

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?

Avatar
Alan Elston
Out of here
Posts: 21
Level 0
January 7, 2021 - 4:09 am

5

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)

Avatar
Velouria
London or thereabouts
Posts: 613

Level 4
January 7, 2021 - 8:21 pm

6

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.

Avatar
David Davala
Posts: 26
Level 0
January 8, 2021 - 12:17 am

7

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.

Avatar
Alan Elston
Out of here
Posts: 21
Level 0
January 8, 2021 - 6:37 am

8

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...    )

Avatar
David Davala
Posts: 26
Level 0
January 9, 2021 - 1:51 am

9

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
Avatar
Alan Elston
Out of here
Posts: 21
Level 0
January 9, 2021 - 3:01 am

10

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

sp_AnswersTopicAnswer
Avatar
Velouria
London or thereabouts
Posts: 613

Level 4
January 9, 2021 - 8:57 pm

11

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.

Avatar
David Davala
Posts: 26
Level 0
January 25, 2021 - 6:02 am

12

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.  

Avatar
Alan Elston
Out of here
Posts: 21
Level 0
January 25, 2021 - 10:32 pm

13

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

ThatsWotIDidItAlready.JPGImage Enlarger

 

 

_.______________________-

 

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

sp_PlupAttachments Attachments
  • sp_PlupImage ThatsWotIDidItAlready.JPG (175 KB)
Avatar
David Davala
Posts: 26
Level 0
January 27, 2021 - 6:42 am

14

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?

Avatar
Alan Elston
Out of here
Posts: 21
Level 0
January 27, 2021 - 7:51 pm

15

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

Avatar
David Davala
Posts: 26
Level 0
January 28, 2021 - 12:30 am

16

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.

Avatar
Alan Elston
Out of here
Posts: 21
Level 0
January 28, 2021 - 5:05 am

17

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

Avatar
David Davala
Posts: 26
Level 0
January 28, 2021 - 12:20 pm

18

I added the file.  

Avatar
Alan Elston
Out of here
Posts: 21
Level 0
January 28, 2021 - 5:48 pm

19

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 )  

NRtTI2Q.jpgImage Enlarger

.

.

.

.

.

.

.

.

.

.

 

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:

NRtTI2Q.jpgImage Enlarger

 

.

.

.

.

.

.

.

.

.

.

 

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

NRtTI2Q.jpgImage Enlarger

 

.

.

.

.

.

.

:

 

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

Avatar
Velouria
London or thereabouts
Posts: 613

Level 4
January 28, 2021 - 10:19 pm

20

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

12Jump to page
Forum Timezone:
Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Chandler Davis, baber Tufail
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)

Devices in use: Desktop (9), Phone (3)

Forum Stats:
Groups: 3
Forums: 24
Topics: 6205
Posts: 27212
Member Stats:
Guest Posters: 49
Members: 31880
Moderators: 3
Admins: 4
© Simple:Press

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.