VBA function run-time error 28

[Nervatos]

Hello there

I have tried to get this to work, but it comes with this error: Run-time error '28': Out of stack space

Private Function UT() As Double
    Application.Volatile
    Dim ws As Worksheet
    Set ws = Worksheets(Sh)

    If IsNumeric(ws.Range("B5").Value) And ws.Range("B5").Value <> "" Then
        UT = ws.Range("B5").Value
        Exit Function
    ElseIf P() > 0 And IB() > 0 Then
        UT = Round(P() / IB(), RN)
        Exit Function
    End If
End Function

Private Function IB() As Double
    Application.Volatile
    On Error GoTo 0
    Dim ws As Worksheet
    Set ws = Worksheets(Sh)

    If IsNumeric(ws.Range("B6").Value) And ws.Range("B6").Value <> "" Then
        IB = ws.Range("B6").Value
        Exit Function
    ElseIf P() > 0 And UT() > 0 Then
        IB = Round(P() / UT(), RN)
        Exit Function
    End If
End Function

Private Function P() As Double
    Application.Volatile
    Dim ws As Worksheet
    Set ws = Worksheets(Sh)

    If IsNumeric(ws.Range("F5").Value) And ws.Range("F5").Value <> "" Then
        P = ws.Range("F5").Value
        Exit Function
     ElseIf UT() > 0 And IB() > 0 Then
        P = Round(UT() * IB(), RN)
        Exit Function
    End If
End Function

My error comes in this line: ElseIf P() > 0 And IB() > 0 Then
Coming in Function UT.

The error is comes then some value is missing. I have tried to search for the error, but all I found is Application.EnableEvents = False and Application.EnableEvents = True. I have tried to put it in this Functions, but still same error. So hope You guys can handle it, cause I can't.
Thank you.

 

[Alex Blakenburg]

I am not sure how you are using this or what you are trying to do.
But the functions are effectively a circular reference.

If the function your start with eg IB is called and IB has a value it only run once, even if both UT & P are text.

If IB is text then it recursively runs the other 2.
If both the other 2 are text then it will give you your error.
If only 1 of the the other 2 are text it seems to be depend on the order with one being recursively running a certain a number of times and the other doing a runaway recursion and also giving you, your error.

 

[Nervatos]

Hello Alex

I'm trying to make Ohms Law, so it's only numbers.

Do you know how to solve this kind of circular reference and still given the values their owns function?
Thanks for your reply.

 

[Alex Blakenburg]

How are you calling the function(s) ? Are you just calling one of them ?
Each function has a line similar to this one in function P: If IsNumeric(ws.Range("F5").Value) And ws.Range("F5").Value <> "" Then
What is the calculation meant to be if this line fails and it moves on to the ElseIf ?
Is there any validation on B5, B6 & F5 to force them to be Numeric ?

 

[Nervatos]

Hello Alex

I calling it like this:

Public Function SFUT() As String
    Application.Volatile
    Call UT
    SFUT = UT
End Function

I have no validation for the cells, only check it's a number and the cell has value.

I need to get two values, to return the last value. So if B5 (UT) and F6 (P) has value, then I can find B6 (IB). Same if F6 (P) and B6 (IB) then I can get B5 (UT). I have other calculations, so then I just use UT(), IB() and P() to get other values.
If there is no value for some of them, it should just return 0

Hope you can understand my bad English

 

[Alex Blakenburg]

So specifically in Call UT,
if UT has a value it will return that.
If UT does not have a value then you need both IB & P to be populated to return a value else return 0.
Is that correct ?

If it is then I think the UT function needs to check B6 & F5 directly not by calling their function.

 

[Nervatos]

Hello Alex

Yes that's right, and same for both IB & P, they also need two values to return value.

 

[Alex Blakenburg]

It is 1 am here in Sydney, I will have a look tomorrow (well tomorrow for you)

 

[Nervatos]

Hello Alex

That's so fine, thank you so much for your help and time.

 

[NoSparks]

Which is UT, Which is IB, Which is P, and which is in what cell ?