I need to solve an implicit equation in VBA

Question

I want to give the other parameters that are mentioned in the function, and get a solution for a (the angle), but I get error: "invalid procedure call or argument" Run-time error 5. I need to call the function in excel worksheet. It is a pretty long equation. Also, it could be that I enter a infinite loop but I don't know how to avoid that.

 Function calculateangle(r, h, C, g, d, m, t, x, y As Single) As Single

 Dim a As Single
 a = 0

 While y <> (d + r - r * Cos(a) + (x - (t - r + r * Sin(a))) * Tan(a) - (g 
 / (2 * ((((C * m * (2 * g * (h - (d + r - r * Cos(a)))) ^
 (1 / 2)) + m * (2 * g * (h - (d + r - r * Cos(a)))) ^ (1 / 2)) / (m +
 0.04593)) ^ 2) * (Cos(a)) ^ 2)) * (x - (t - r + r * Sin(a))) ^ 2)

     a = a + 0.01
 Wend

 MsgBox Round(a, 2)

 End Function

One note: `r, h, C, g, d, m, t, x` are all variants and not single. The only single is `y` — Scott Craner, Mar 23 '18 at 22:09
@ScottCraner Thanks! I declared them all as single, and now I get the error: "Expression too complex". Is there anything I can do? — blend, Mar 23 '18 at 22:12
@ScottCraner yep indeed, but the thing is I can't break the formula into pieces since the unknown (a) is found in many places. — blend, Mar 23 '18 at 22:19
1. If y is already above or below (don't know whether a = a + 0.01 progresses the result down or up) then a result will never be reached. 2. You might have a problem with a = a + 0.01 progressing past a truly *equals* value, possibly due to 15 digit precision. Better to round the result to the significant digits of y and increment a so you are not skipping any possible matching result. 3. Even if you found a truly equals equation you are still a = a + 0.01 before exiting the loop so a is the first step past the equality. — , Mar 23 '18 at 22:34
@Jeeped 1. Y is always positive, and a should also be positive, so starting from 0 it increases for sure. 2. Yes I did round it to 2 digits, and also tried the code in python and it works, but in here i still get expression too complex. — blend, Mar 23 '18 at 22:38
@Jeeped Expression too complex again, but in python i still get a good result — blend, Mar 23 '18 at 22:46
Why not use `Double`? In any event, could you make this a [mcve]? How are you calling this function? — John Coleman, Mar 23 '18 at 23:27
Furthermore, checking floating point numbers for equality is almost always misguided. The `y <> ...` should probably be replaced by a comparison which involves a tolerance. — John Coleman, Mar 23 '18 at 23:31
@JohnColeman I found the solution using the Solver of Excel. Otherwise, I tried all your suggestions but I still get the Expression too complex error. Anyways, thanks everyone. — blend, Mar 23 '18 at 23:41
@blend Look [here](https://stackoverflow.com/a/49456115/6698332), please. Yesterday only I wrote and got -1 :))) and today you are the second already who needs in it. _"break the formula into smaller pieces. - [Scott Craner](https://stackoverflow.com/questions/49458897/i-need-to-solve-an-implicit-equation-in-vba#comment85920798_49458897)"_ — user6698332, Mar 24 '18 at 05:49

score 2 · Answer 1 · answered Mar 24 '18 at 07:21

One obvious issue is that you are using a Function but not returning a value.

This really is a complex piece of spaghetti! However, I suggest an approach like below which will help separate out various bits and thus make it easier to do debugging

Function calculateangle(<...all the bits ...>) As Double
Dim a As Double
Dim tTolerance as Double
dim f1 as Double  ' sub sections to help untangle the spaghetti
Dim f2 as Double
Dim f3 as Double
Dim fFinal as Double
Dim tWithinTolerance as Boolean
    tWithinTolerance = false
    a = 0
    tTolerance = 0.01
    While not tWithinTolerance 
        f1 = d + r - r * Cos(a)
        f2 = m*2*g*(h - f1)
        f3 = x - (t - r + r * Sin(a)) 
        fFinal = (f1 + f3 * Tan(a) - (g  / (2 * ((((C * f2) ^
 (1 / 2)) + f2 ^ (1 / 2)) / (m + 0.04593)) ^ 2) * (Cos(a)) ^ 2)) * f3 ^ 2)
     tWithinTolerance = (Abs(y - fFinal) < tTolerance)
        a = a + 0.01
    Wend
    Calculateangle = a ' note how this sets a return value for the function
End Function

I have left the rounding (which is a presentation issue) to the code that calls this function - this way you can display the answer to whatever level of detail you want!

(apologies if I have mangled any of the calculation on the way through - but you get the concept!)

score 0 · Answer 2 · answered Mar 24 '18 at 06:55

For the author and those who want to deal with his solitaire. I hope I did not confuse anything in parentheses and simplifications.

Do

    vCosA = Cos(a)
    vCosADR = d + r * (1 - vCosA) ' d + r - r * vCosA '
    vCosMGHADR = m * (2 * g * (h - vCosADR))
    vSinAXTR = (x - (t - r * (1 - Sin(a)))) ' - r + r * Sin(a)

   '((C * vCosMGHADR) + vCosMGHADR) == ((C + 1) * vCosMGHADR)

    If (y = _
            (vCosADR + vSinAXTR * Tan(a) - _
                (g / _
                    (2 * _
                        ( _
                            ( _
                                ((C + 1) * vCosMGHADR) / _
                                (m + 0.04593) _
                            ) ^ 2 _
                        ) * (vCosA ^ 2) _
                    ) _
                ) * vSinAXTR ^ 2 _
            )) Then Exit Do          ' *** EXIT DO ***

    a = a + 0.01

Loop

I need to solve an implicit equation in VBA

2 Answers2