# VBForums CodeBank > CodeBank - Other >  Lambert W function for Excel, work on real and complex number

## manandpc

The Lambert W function (very efficient, fast)

See, LambertW.bas to get the VB module (attach)

Input: real or complex values from -infinity to +infinity
You can verify the results with

=IMSUM(IMPRODUCT(LambertW(COMPLEX(-100,0)),IMEXP(LambertWc(-100,0))),-1)
return:
-100.999999999999+8.5265128291212E-14i

OR

=IMPRODUCT(LambertW(-0.1),IMEXP(LambertW(-0.1)))
return:
-0.1

Lots of testing, no error found  :Big Grin:

----------


## Onewheeler

Thank you! That has saved me a lot of work!

 :big yellow:  :big yellow:  :big yellow: 

Martin/

----------


## Chipmunker

Thank you also.

One minor quibble: there seems to be an issue with the results that come from input values between 0 and -0.0212. As soon as it hits -0.0212 it's like hitting a wall, beyond that the results are wildly off:

-0.0218 > -0.02229
-0.0217 > -0.02219
-0.0216 > -0.02208
-0.0215 > -0.02198
-0.0214 > -0.02187
-0.0213 > -0.02177
-0.0212 > *-5.51714*
-0.0211 > *-5.57716*
...and so on, all input values from there to zero are similarly off.


Can anyone see why? Unfortunately I just lack the coding skills to pick it apart - gave it a go, but just no experience there whatsoever :-(

Any help much appreciated.

----------


## manandpc

See: http://www.had2know.com/academics/la...alculator.html

"If x is within the sub interval (-1/e, 0), the calculator returns two values."

And for x = -0.0211, the calculator returns -5.577162 and -0.02156, both values are valid.
In Excel, the function return only 1 of them, I will check if i can return the more suitable one.

----------


## Chipmunker

Aha, that all makes sense. Thank you.

Please don't spend any more time on my account - now the dual correct answers are apparent I can work with that.

Thanks again.

----------


## BobJordanB

I searched high and low for access top a vba routine for Lambert W.

This was the best - BUT it sometimes gives the -ve branch solutions instead of the +ve.

It is also complex which i do not need.

For others I will post my solution here.

It returns the +ve branch solution only, is accurate to 9dp for most of the useful space and is reasoanbly faast

Here it is and hope others find it useful.

Bob J.



```
Public Function myLambertW(x As Double) As Double
       '
       '      Function provided on the web  seems to have starting problems and uses complex Nos where I only need real
       '      This version solves for the upper branch of the solution only
       '
       '      It uses standard Newton iteration
       '
       '      The starting value is log(x+1) which is within 40% of target from -0.33 < x < 10^100
       '
       '      Results are accurate to about 9dp from -0.3678 < x < 10^100
       '
       '      Values for second Branch are not computed ie -0.36788... < x < 0, W(x) < -1
       '
       '      There is some potential to reduce iterations for smaller +ve x values
       '      but may need to increase as x approaches -1
       '
       '                           RB Jordan 3/2/2015
       '
    
       Dim xTry As Double
       Dim Iter As Integer
       
       If x < -Exp(-1) Then
              myLambert = CVErr(xlErrValue)
              Exit Function
       End If
    
       xTry = Log(1 + x)
       For Iter = 1 To 9
           xTry = xTry - (xTry - x / Exp(xTry)) / (1 + xTry)
       Next Iter
       
       myLambertW = xTry
    
End Function
```

----------


## Thomas McCormick

All:

There is a very nice presentation of the Lambert's W function (W0) and a method to calculate it based upon Halley's method in MATLAB at http://blogs.mathworks.com/cleve/201...e-1773193df571. (Google: The Lambert W Function Cleve Moler if link inactive).

Adapted VBA Code is attached

----------


## x I'm tc

This is a very old thread, but there's some great code in it!  However, all these formulae apparently work on the principal branch.  I need a formula that will work on the -1 branch (as well as the principal branch).  Is there such a thing?

----------

