Function with circular reference - VBA

[nitzbar]

I'm writing a function in VBA used to calculate prices based on given data. I am applying this function through an entire column on cells. I would like to fine tune the function such that for each cell, it considers the range of input data plus the calculations performed on previous cells. However, every time i try this, I get a circular reference error. Any suggestions ???:banghead:

[malik641]

Not sure. It will be easier if you could post the code.

[nitzbar]

not sure if this will help... let me know if you can make sense of it... have just included the relevant part

l = 1
k = 1
i = 1
m = 1
For i = 1 To 389
'IF FUTURE IS IN FIRST LEG OF SPREAD
If (futsym = spreaddata.Cells(i, 1).Value And spreaddata.Cells(i, 5).Value <> "-" And spreaddata.Cells(i, 5).Value > 0) Then
leg2 = spreaddata.Cells(i, 2).Value

For j = 1 To 70
If (leg2 = futdata.Cells(j, 1).Value And futdata.Cells(j, 2).Value <> "-" And futdata.Cells(j, 2).Value > 0) Then
ibidmult1(k) = futdata.Cells(j, 2).Value + spreaddata.Cells(i, 5).Value
k = k + 1

End If
'USING IMPLIED PRICES TO FURTHER IMPLY PRICES ( HIGHER GENERATION)
If j > 2 Then

For l = 1 To j - 1
If (leg2 = impfutdata.Cells(l, 1).Value And impfutdata.Cells(l, 2).Value <> "-" And impfutdata.Cells(l, 2).Value > 0 And impfutdata.Cells(l, 2).Value <> "") Then
ibidmult11(m) = impfutdata.Cells(l, 2).Value + spreaddata.Cells(i, 5).Value
m = m + 1
End If
Next l

End If



Next j

[malik641]

This function wouldn't happen to be a worksheet function would it? Because then I could see an issue with this code. Otherwise I would expect a circular reference error to arise from setting a 2+ formulas incorrectly...which I don't see you're doing in your code.

You can post the whole code if you want (it's not THAT big, is it?). And when you run into the error, on what line does the VBE highlight when you choose "Debug" from the error message window?

[nitzbar]

I plan to go beyond this, but i just stopped coding at this point to see what kind of result I got. Let me know what you think... Thanks....


'IMPLIED BIDPRICE FUNCTION

Public Function impbid(futsym As String, spreaddata As Range, futdata As Range, impfutdata As Range) As Variant

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim m As Integer

Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim d As Integer

Dim leg2 As String

Dim leg1 As String

Dim ibidmult1(1000) As Variant
Dim ibidmult2(1000) As Variant
Dim ibidmult11(1000) As Variant
Dim ibimult22(1000) As Variant

l = 1
k = 1
i = 1
m = 1
For i = 1 To 389
'IF FUTURE IS IN FIRST LEG OF SPREAD
If (futsym = spreaddata.Cells(i, 1).Value And spreaddata.Cells(i, 5).Value <> "-" And spreaddata.Cells(i, 5).Value > 0) Then
leg2 = spreaddata.Cells(i, 2).Value

For j = 1 To 70
If (leg2 = futdata.Cells(j, 1).Value And futdata.Cells(j, 2).Value <> "-" And futdata.Cells(j, 2).Value > 0) Then
ibidmult1(k) = futdata.Cells(j, 2).Value + spreaddata.Cells(i, 5).Value
k = k + 1

End If
'USING IMPLIED PRICES TO FURTHER IMPLY PRICES ( HIGHER GENERATION)
If j > 2 Then

For l = 1 To j - 1
If (leg2 = impfutdata.Cells(l, 1).Value And impfutdata.Cells(l, 2).Value <> "-" And impfutdata.Cells(l, 2).Value > 0 And impfutdata.Cells(l, 2).Value <> "") Then
ibidmult11(m) = impfutdata.Cells(l, 2).Value + spreaddata.Cells(i, 5).Value
m = m + 1
End If
Next l

End If

Next j

End If
Next i

impbid = ibidmult11(1)
End Function

[malik641]

Ok. One more question for you: Can you post the formula that gave you the error (the one you used on your worksheet) and in WHICH cell it was in?

[nitzbar]

i just called the function 'impbid' in a cell.... and that gave an error...

[malik641]

Ok...Which cell? Any cell? Any cell in column A? Any cell in row 3?

[nitzbar]

the error was in the cell where I called the function...

[malik641]

Ok. Either you're being a wise guy or you don't understand me.

If you don't understand me, I want to know which cell ADDRESS you called the function. A1? B3? Something like that.

If you're being a wise guy then I'll just stop posting in this thread.

[nitzbar]

i'm sorry.. I misunderstood.... i called the function in column j, in every cell from j 13 to j 83. the inputs for the function i.e. 'futsym' changed in each case while the range inputs remained constant. the 'impfutdata' range included the one where i was calling the function. I guess this is what is causing the problem. However, i attempted to loop things in a way that the function when called would only use that data from the third range for which the function had already calculated data when being called in the previous cell. i thought that this would avoid the circular reference, but i guess that didn't work.


Does this help ?

[malik641]

Yes, it does. Thanks nitzbar.

It's pretty tough to say. Is there a chance you can post the workbook? It will be the easiest way for me to solve this issue.

[nitzbar]

I changed the code and continued working on the file. Planned on returning to it. Will send the file once I write up this piece of code again. Thanks.