Speeding up Loops 1

[ninja1980]

Hi all

I’m trying to solve an equation via an iterative method. The VB code below shows a simple loop I’ve created to solve for Nc. Stricly speaking, Nc is found when when “Term” is equal to 0.

I’m fairly new to VBA and would really like a few pointer on how to make this sort of routine more efficient.

Thanks

CODE:

******************************************************************
Function Type1_and_2_Area(inlet_P, back_P, Omega)

Dim Pc As Single
Dim Po As Single
Dim Pa As Single
Dim Nc As Single
Dim Term As Double

'===Conversions======

Po = inlet_P * 14.50377 '====Convert from bara to psia

'===Determine Critical/Non Critical Flow======

'First find Nc:

Nc = 0

Do
Nc = Nc + 0.00001
Term = (Nc ^ 2) + ((Omega ^ 2 - (2 * Omega)) * ((1 - Nc) ^ 2)) + (2 * (Omega ^ 2) * Log(Nc)) + ((2 * Omega ^ 2) * (1 - Nc))

Loop Until Term < 0.00001 ' Loop until terms become small

****Some code missing***

End Function

 

[Andrzejek]

Try to break this line:

 Term = (Nc ^ 2) + ((Omega ^ 2 - (2 * Omega)) * ((1 - Nc) ^ 2)) + (2 * (Omega ^ 2) * Log(Nc)) + ((2 * Omega ^ 2) * (1 - Nc))

into separate lines of calculations,

NcSqr = Nc ^ 2
OmegaSqr = Omega ^ 2
...

Term = NcSqr + (OmegaSqr - (2 * Omega) ....

and see if that will make any difference.

Also,

Function Type1_and_2_Area(inlet_P [red]As ???[/red], back_P [red]As ???[/red], Omega [red]As ???[/red]) [red]As ???[/red]

Unless they are all Variants

Have fun.

---- Andy

 

[TonyJollans]

The only real work being done in that code is the iterative calculation itself. Assuming the formula itself is expressed in its most efficient form, I don't think there is anything much you can really do to make the code more efficient.

That said [blue][tt](Omega * (Omega-2))[/tt][/blue] may be more efficient than [tt](Omega ^ 2 - (2 * Omega))[/tt]; you would need to experiment.

And [blue][tt](2 * Omega ^ 2 * (Log(Nc) + 1 - Nc))[/tt][/blue] may be more efficient than [tt](2 * (Omega ^ 2) * Log(Nc)) + ((2 * Omega ^ 2) * (1 - Nc))[/tt]; again, you would need to experiment.



Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[ettienne]

One method is to guess an upper limit for Nc, let's say 1.

Calculate Nc for 1, if 1 is too high then take the average of your starting value 0 and the upper value 1, which is 0.5 = (0+1)/2.
Calculate Nc for 0.5, if 0.5 is too high then try 0.25 = (0+0.5)/2.
If 0.5 is too low then try 0.75 = (0.5+1)/2.

This is a method we used in quantitative analysis, it gets you to a solution in less iterations than Nc = Nc + 0.00001

 

[ninja1980]

Thanks everyone, especially ettienne

Also, Andrzejek, what is the benefit of declaring the variables inside the function? Its just I’ve never even thought of doing it before now

Thanks

 

[PHV]

Can't you use use the builtin Solver.XLA ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ninja1980]

Also, ettienne, could you give me a starting point to translate this method into code

Thanks

 

[ninja1980]

Hi PHV, how do I go about using Solver.XLA????

Thanks

 

[ettienne]

For the solver, in Excel click Tools, Goal Seek.
Read the help, but basically it will iterate through values until it solves the equation.
If you do not declare variables with a type then the will be declared as Variant.
Variant is bad because your variables are not typed, and they do slow down processing.

 

[ettienne]

Try the solver first, that is a really good tool to use.

 

[ninja1980]

Doesn’t the solver link into Excel??? I’m trying to do this entirely in VB.

I’ve written the code below. The only problem is I want the value of Nc used to calculate “Term” to use later on. In my code Nc will be altered in the final If statement.

Any ideas?

CODE

******************************************************

Nc = 1
Delta = 1.0

Do
Term = (Nc ^ 2) + ((Omega ^ 2 - (2 * Omega)) * ((1 - Nc) ^ 2)) + (2 * (Omega ^ 2) * Log(Nc)) + ((2 * Omega ^ 2) * (1 - Nc))

If Term > 0.0001 Then
Nc = Nc - (Delta/2)
Else
Nc = Nc + (Delta/2)

Loop Until ABS(Term) < 0.00001 ' Loop until terms become small

 

[ettienne]

What is a typical input value for Omega?

 

[ettienne]

This is a simplified routine, but it will get you going.

    Dim dblLo As Double
    Dim dblHi As Double
    Dim Nc As Double
    Dim Term As Single
    Dim Omega As Single
    
    Omega = 1
    dblLo = 0  'Assuming solution is somewhere between 0 and 1
    dblHi = 2  'Start with 2 since (0+2)/2 = 1
    Do
        Nc = (dblLo + dblHi) / 2
        Term = (Nc ^ 2) + ((Omega ^ 2 - (2 * Omega)) * ((1 - Nc) ^ 2)) + (2 * (Omega ^ 2) * Log(Nc)) + ((2 * Omega ^ 2) * (1 - Nc))
        If Term > 0 Then
            dblHi = Nc
        Else
            dblLo = Nc
        End If
        
    Loop Until Abs(Term) < 0.00001

    MsgBox Nc

 

[ninja1980]

Thanks very much. I've come up with something similar but your coding is a bit shorter and more efficient.

Thanks again

 

[N1GHTEYES]

If you are still looking for a faster solution to this, you might just want to do some maths on it. It will take some experimentation to see how well behaved it is with the omega values you're using, but, if it is well behaved, you could probably do it almost analytically.

I assume you are solving for Nc for a given series of omega values yes? In which case, for each instance, omega is effectively a constant, so separate all the Nc and omega terms and precalculate the omega terms outside the loop.

Reduce the equation to terms in powers of Nc and log(Nc) with coefficients which are functions of omega. Calculate the coefficients outside the loop. Call the resultant function y.

Partially differentiate y wrt Nc.

Rearrange the above to get dNc as a function of Nc, omega and dy.

Now take the initial y equation. Assuming omega is a constant for a given requirement, it is a quadratic in Nc apart from a log (Nc) term. Make the assumption that the log(Nc) term is relatively insignificant and ignore it. Solve the quadratic to get a starting value for Nc.

Now do this loop:
Insert the Nc value in the y equation. Call the result dy. Use the relationship you derived from the partial differential to derive the dNc value required to get dy.

Subtract dNc from Nc and loop.

Like I said, you'll need to check out its behaviour in the omega region you're using, but it will probably iterate to a solution very quickly.

Hope this helps,

Tony

 

[ettienne]

My routine gives a solution in 22 iterations...

 

[HughLerwill]

Try using long multipication eg.Omega * Omega in preference to Omega ^ 2; use of the ^ operator has always been slower in VB and I guess the same may be true in VBA.