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.

 

[Nervatos]

Hello NoSparks

UT = V
IB = I
P = P
R = R

UT cell = B5
IB cell = B6
P cell = F5
I have not made it for R, cause I don't need it into voltage drop calculates.
Thank you.

 

[Alex Blakenburg]

Any reason you don't want to just do this ?

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
    ElseIf IsNumeric(ws.Range("B6").Value) And ws.Range("B6").Value <> "" And _
            IsNumeric(ws.Range("F5").Value) And ws.Range("F5").Value <> "" Then
        UT = Round(ws.Range("F5").Value / ws.Range("B6").Value, RN)
    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
    ElseIf IsNumeric(ws.Range("B5").Value) And ws.Range("B5").Value <> "" And _
            IsNumeric(ws.Range("F5").Value) And ws.Range("F5").Value <> "" Then
        IB = Round(ws.Range("F5").Value / ws.Range("B5").Value, RN)
    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
    ElseIf IsNumeric(ws.Range("B5").Value) And ws.Range("B5").Value <> "" And _
            IsNumeric(ws.Range("B6").Value) And ws.Range("B6").Value <> "" Then
        P = Round(ws.Range("B5").Value * ws.Range("B6").Value, RN)
    End If
    
End Function

 

[Nervatos]

Hello Alex

It actually solved the problem, but the only thing is - that I think it was easier to return functions instead.

If I now have these values that need to be calculated and I need to avoid circular reference. Here, not all fields are filled in, I have to find the results myself - with calculations. Any idea to fix it?
It must, of course, return the correct formula first.

Thanks for the help, really appreciate your kindness!

 

[Alex Blakenburg]

I was hoping @NoSparks might respond since I think he is might be more familiar with what you are trying to do.
I would need multiple examples using your format above of what you are trying to do.

I am not sure a Function would work in the sense of that your can't put a function in multiple boxes if some are manually input numbers.
I would think you would need a button to say calculate now, since you always need to enter at least say 2 values.

Also if you treat the 3 version of E = IR as one formula, how many formulas does your grid actually require.
Is each row related to one formula ?

 

[Nervatos]

Hello Alex

Sorry for the late reply, but I think I'm trying to do it in a new way and then I have to ask if there are any problems along the way.
Thanks for the help and happy new year.

 

[Alex Blakenburg]

Sounds good. Thank you for the update and Happy New Year to you too.