1

I have the following function that when I run it says #value! error.

I would appreciate any help.

Function Bootstrap(S As Object, Z As Object, L As Double)

    Dim j As Integer
    Dim a() As Double
    Dim b() As Double
    Dim n As Integer
    Dim Q() As Double
    Dim sum As Double
    Dim P As Double

    ReDim a(1 To n)
    ReDim b(1 To n)
    ReDim Q(1 To n)

    dt = 1
    sum = 0
    Q(0) = 0

    For j = 1 To n - 1
        S.Cells(j, 1).Value = a(j)
        Z.Cells(j, 2).Value = b(j)
        P = Z(j) * (L * Q(j-1) - (L + dt * a(n) * Q(j))
        sum = sum + P
    Next j

    Bootstrap = sum

End Function

Bootstrapping function calculates the following value In fact I am trying to calculate this formula Q(t,Tn)=(∑(j=1)to(n-1) Z(t,Tj)[LQ(t,Tj-1)-(L+dtSn)Q(t,Tj)]/[Z(t,Tn)(L+dt*Sn)] +(Q(t,Tn-1)L)/(L+dtSn)

Inputs given are[S1 ,S2,….Sn ],[Z(t,T1),Z(t,T2)…..Z(t,Tn)]and and L=0.4

Improve this question
2
  • Please explain step by step the calculation that you are trying to do, because this is very very dark, you can edit your post to include this and help us help you... Because right now, I'm really shooting in the dark! Commented Nov 27, 2015 at 10:15
  • @R3uK. I have editted and given the formula for Bootstrapping function. I hope its not dark now. Commented Nov 27, 2015 at 10:52

2 Answers 2

Reset to default
2

Try this code : entered as =Bootstrap(A1:B1,A2:B2,0.4)
I have corrected the following
- Assigning the ranges to variants
- defining dt as double
- Dim Q() as 0 to n
- using A() and b() in the formula
- the input ranges are rows not columns

Function Bootstrap(S As Range, Z As Range, L As Double) As Double
Dim j As Integer
Dim a As Variant
Dim b As Variant
Dim n As Integer
Dim Q() As Double
Dim sum As Double
Dim P As Double
Dim dt As Double

n = Application.WorksheetFunction.Max(S.Columns.Count, Z.Columns.Count)
a = S.Value
b = Z.Value
dt = 1
sum = 0
ReDim Q(0 To n)
Q(0) = 0

For j = 1 To n - 1
    P = b(1, j) * (L * Q(j - 1)) - (L + dt * a(1, j) * Q(j - 1))
    sum = sum + P
    Q(j) = sum
Next j

Bootstrap = sum
End Function
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks Charles. This gives some output values. It seems to be correct. I am checking. But what if Inputs are in Columns not Rows ? Then simply changing the Application .WorkshhetFunction.Max(S.Rows.Count,Z.Rows.Count) ?
Then use Rows.Count instead of Columns.Count and reverse the indices in references to a() and b()
2

Take the habit to format and increment your code, especially before posting it!

  1. You need to type the output of the function (on the line of the function name)
  2. A parenthesis is missing from the line P = Z(j) * (L*Q(j-1)-(L+ dt * a(n) * Q(j))
  3. n is empty (and so are a, b and Q) when you try to redim your arrays, so you need to define them!
  4. Z(j) will also give you an error, because it is a Range, you need Z.Cells(i,j)

Try this :

Function Bootstrap(S As Range, Z As Range, L As Double) As Double
Dim j As Integer
Dim a() As Double
Dim b() As Double
Dim n As Integer
Dim Q() As Double
Dim sum As Double
Dim P As Double

n = Application.WorksheetFunction.Max(S.Columns.count, Z.Columns.count)
a = S.Value
b = Z.Value
dt = 1
sum = 0
ReDim Q(1 To n)
Q(0) = 0
'Q(1) = "??"


For j = 1 To n - 1
    P = b(1, j) * (L * Q(j - 1)) - (L + dt * a(1, j) * Q(j - 1))
    sum = sum + P
    Q(j) = sum
Next j

Bootstrap = sum
End Function
Improve this answer

13 Comments

I again tried running the code by putting n=2 ,but it shows the same #value! error.
In the Excel Spreadhseet output cell D1, I type =Bootstrap(A1:A2,B1:B2,0.4) ,which generates the #value! error.
The issue is your calculation and the fact that you use empty variables (here, a, b, Q are empty so you just erase the content of the cells that you select and calculate P with empty variables, so in fact the calcul is P = 0 - L = - L. See also point 4
Can you describe what you are trying to do? Or correct your algorithm for the calcul in the loop, because the reel problem is here! ;)
I have replaced Z(j) by Z.Cells(j,2).Value but no effect and my input is Z column matrix which I have stored the the values in Z to b matrix and similarly S to a() . So, a & b should NOT be empty .The inital value of Q is 0 i.e. Q(0) but for values say j= 2 ,Q(2) will be required ,which is NOT there. I am correcting the the calculation part of Q(j) and checking.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.