Home > Excel, UDF > Who’s da mastah?…….Sho’nuff!

Who’s da mastah?…….Sho’nuff!

May 17th, 2011 admin

June 1st, 2008

Last Friday I was making dinner for a company of 12 at my grandmother’s house and happened to be sitting next to the CTO of the major social networking site. He told me a story about a question he asked every programmer he interviewed for a job. The question was how to program the Fibonacci sequence. Just because he left me a scrap in which he makes fun of me I’m going to post a little array UDF for this problem.

Function FIBONACCI(n As Long) As Variant

   Dim sFibonacci() As Variant
   Dim CallerSize As Long
   Dim i As Long: Dim j As Long: Dim k As Long

   Const sqrt5 = 2.23606797749979

   With Application.Caller
      ReDim sFibonacci(1 To .Rows.Count, 1 To .Columns.Count)
      CallerSize = .Cells.Count
      For i = 1 To .Rows.Count
         For j = 1 To .Columns.Count
            k = (i - 1) * .Columns.Count + j + n
            sFibonacci(i, j) = Round(((1+sqrt5)^k - (1-sqrt5)^k) / (2^k*sqrt5),0)
         Next j
      Next i
   End With

   FIBONACCI = sFibonacci

End Function

p.s. I’m going to win a case of champagne and a public apology soon…..

Categories: Excel, UDF Tags: ,
Comments are closed.