Difference between assigning an array to another, and not assigning

Chronicles84

New Member
Joined
Apr 8, 2017
Messages
7
Hi,

New to VBA. I'm trying to understand the difference between assigning an array to another and not - the difference syntax and advantages/disadvantages etc

I have two lots of code, as seen below. The first seems to work correctly, and does not rely on assigning parameters to an array:

Code:
Function MyPVArray(R As Double, CFS As Range)
Dim p As Double
Dim n As Long
n = CFS.Cells.Count



For i = 1 To n
    p = p + CFS(i) / (1 + R) ^ n
    Next i
MyPVArray = p


End Function

As far as I can tell this works correctly in determining the present value of a range of cash flows.

The next code tries to replicate this but explicitly assigns the cash flows to an array.

Code:
Function MyPVArray2(R As Double, CFS As Range)


Dim p As Double
Dim n As Long
Dim z As Range
Set z = Range("CFS")
n = z.Cells.Count



For i = 1 To n
    p = p + z(i) / (1 + R) ^ n
    Next i
MyPVArray2 = p




End Function

This second function does not work successfully - returning a value error. I have set ranges before using the set z = ranges("name here") format before, and it hs worked correctly. So, I'm not sure if the problem is here, but this is the only difference between them, and the value error suggests to me that it is not assigning the .value correctly.

Any advice/feedback would be greatly appreciated.

Regards
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Watch MrExcel Video

Forum statistics

Threads
1,118,415
Messages
5,571,977
Members
412,430
Latest member
Huuktkt
Top