How to assign range to Double array?

J

Joe User

How can I assign the value of a range to a Double array?

The best I can do is a For Each loop. For example:

Function myIRR(myVal As Range, _
Optional myGuess As Double = 0.1)
Dim dVal() As Double, n as Long
ReDim dVal(1 To myVal.Rows.Count)
n = 0
For Each cell In myVal: n = n + 1: dVal(n) = cell: Next
myIRR = IRR(dVal, myGuess)
End Function

If dVal() were Variant, I could copy the range simply as follows:

Function myIRR(myVal As Range, _
Optional myGuess As Double = 0.1)
Dim dVal()
dVal = myVal
myIRR = IRR(dVal, myGuess)
End Function

But that results in a Type Mismatch error because the VB IRR function wants
a Double array.

PS: I know that I can use WorksheetFunction.IRR instead for this example.
I am using IRR just for demonstration purposes.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top