Use VBA to iteratively solve a equation variable

Question

I'm trying to solve "N Factor" of the following equation in VBA:

I have looked for other similar questions and I can't find any with suitable solutions.

I thought an iterative approach to obtain "N Factor" value from an initial value of N would work in VBA. For reference, the worksheet used is shown below:

When I try to do this programmatically in VBA incrementally from an initial value of N, it doesn't work. My code is below:

Function Fun_N(C, R, E, M, D As Double) As Double

With Application.WorksheetFunction

Dim D_Cal As Double

N = 0.001 'Arbitrary number to initialize the loop

D_Cal = ((1.5 * C) / ((4 * Atn(1)) * R)) * ((((0.0045 * E) ^ 3) / (N ^ 3)) * (1 - (1 / ((1 + (E / R) ^ 2) ^ 0.5))) + (1 / (M * ((1 + ((40000 * (N ^ 2)) / ((R ^ 2) * ((M) ^ (2 / 3))))) ^ 0.5))))   'Constant Pi  = 4 * Atn(1)

While D_Cal < D

    N = N + 0.000001
    D_Cal = ((1.5 * C) / ((4 * Atn(1)) * R)) * ((((0.0045 * E) ^ 3) / (N ^ 3)) * (1 - (1 / ((1 + (E / R) ^ 2) ^ 0.5))) + (1 / (M * ((1 + ((40000 * (N ^ 2)) / ((R ^ 2) * ((M) ^ (2 / 3))))) ^ 0.5))))   'Constant Pi  = 4 * Atn(1)
    
Wend

End With

Fun_N = N

End Function

I'm not know the issue (code, loop...)

Thanks in advance for help.

`C, R, E, M, D As Double` Only sets `D` as a `Double`. you will need to spell out each: `C As Double, R As Double, E As Double, M As Double, D As Double` — Scott Craner, Jun 25 '20 at 18:43
And the `With Application.WorksheetFunction` and `End With` are not needed as they are not used as prefix anywhere. — Scott Craner, Jun 25 '20 at 18:45
You are getting an overflow error. My guess is that you have `()` in the wrong place(s). — Scott Craner, Jun 25 '20 at 18:51
is this the value you are looking for :`0.946619` ? then you should try and debug your code. — Luuk, Jun 25 '20 at 19:01
I would break the portions of your equation out into their individual calculations and then combine them back at the end for simplicity/readability — urdearboy, Jun 25 '20 at 19:07
Yes Luuk, for the example values, N = 0.946619. I try debug the code but dont works. Please how can i do it. Thanks. — giovas, Jun 25 '20 at 20:52

score 0 · Answer 1 · answered Jun 25 '20 at 23:25

So I re-created your spread sheet and the first value I got for D-Cal was 13365959.6835436 using C = 9000, R = 6, E = 8, M = 3000, D = 0.115, N = 0.001 and copying the formulas as you had them. Your while loop will never execute as it says while D_Cal < D execute code. This is false at the start so the code inside the loop never executes. Changing this to:

While D_Cal > D

    N = N + 0.000001
    D_Cal = ((1.5 * C) / ((4 * Atn(1)) * R)) * ((((0.0045 * E) ^ 3) / (N ^ 3)) * (1 - (1 / ((1 + (E / R) ^ 2) ^ 0.5))) + (1 / (M * ((1 + ((40000 * (N ^ 2)) / ((R ^ 2) * ((M) ^ (2 / 3))))) ^ 0.5))))

Wend

Fun_N = N

gave me N = 0.946619000006383 as the answer so I think that is all you need to do.

Thank you @bigjoepro75, its works. Question: It is possible to use loops to optimize the execution time, the code takes a several seconds to execute one data entry, and I need to calculate data thousands. — giovas, Jun 26 '20 at 01:49

Use VBA to iteratively solve a equation variable

1 Answers1