Archive

Posts Tagged ‘UDF’

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

May 17th, 2011 admin No comments

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

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

Categories: Excel, UDF Tags: ,

Standard Deviation of Counts

May 17th, 2011 admin No comments

May 20th, 2008

After reading and commenting on the Standard Deviation of Counts at Methods in Excel I decided to award the topic a post on my own blog ( with the blessing of Ross )
The problem is to get the Standard Deviation for a number of counts of scores. You can achieve this by coding an UDF or by a combination of worksheet functions. Each with the obvious drawbacks.

The test example consists out of the following data:

The formula for the Standard Deviation I used was:

I named the two main ranges “values” and “scores”, defined:
N as SUM(counts),
the first summation as SUM(MMULT(values^2,TRANSPOSE(counts))),
and the second summation as SUM(MMULT(values,TRANSPOSE(counts))))^2
The combination of these parts resulted in:
{=SQRT((SUM(counts)*SUM(MMULT(values^2,TRANSPOSE(counts)))-((SUM(MMULT(values,TRANSPOSE(counts))))^2))/(SUM(counts)*(SUM(counts)-1)))}
If the scores represent the entire population instead of a sample you should change it to
{=SQRT((SUM(counts)*SUM(MMULT(values^2,TRANSPOSE(counts)))-((SUM(MMULT(values,TRANSPOSE(counts))))^2)))/SUM(counts)}

My choice for coding a UDF would be:

Function STDEVCOUNTS(Counts As Range, Values As Range, bPartial As Boolean)

   Dim N As Long: Dim i As Long
   Dim dSum As Double: Dim SValues() As Double

   ReDim SValues(1 To Values.Cells.Count)

   For i = 1 To Values.Cells.Count

      SValues(i) = Values(1, i) * Values(1, i)

   Next i

   With WorksheetFunction

      N = .Sum(Counts)
      dSum = N * .Sum(.MMult(SValues, .Transpose(Counts)))
      dSum = dSum - .Sum(.MMult(Values, .Transpose(Counts))) ^ 2
      dSum = dSum / (N * (N + bPartial))

   End With

   STDEVCOUNTS = dSum ^ 0.5

End Function

Slightly slower ( about 5% ) but less code is my second attempt with a little help from Bob Phillips from the Excel User Group.

Function STDEVCOUNTS(Counts As Range, Values As Range, bPartial As Boolean)

   Dim N As Long: Dim i As Long: Dim dSum As Double

   With WorksheetFunction

      N = .Sum(Counts)
      dSum = N * Evaluate(”SUM(MMULT(” & Values.Address(, , , True) & “^2,TRANSPOSE(” & Counts.Address(, , , True) & “)))”)
      dSum = dSum - .Sum(.MMult(Values, .Transpose(Counts))) ^ 2
      dSum = dSum / (N * (N + bPartial))

   End With

   STDEVCOUNTS = dSum ^ 0.5

End Function

Thoughts on this code or on the functions….?

Cheers

Jelle-Jeroen

Categories: Excel, UDF Tags: ,

How Excel solves all my problems

May 17th, 2011 admin No comments

May 8th, 2008

Last week I was on holliday and we had to divide 3 bottles of wine among ten friends.
We could have written our names on little papers and draw 3 names from the lot,
but why do something like that when you have a computer with excel.
So I wrote a array function that returns a permutation from a set of data.

I put the 10 names in A1:A10 and then selected C1:C3 and inserted =PERMUTATION(A1:A10)
finally I pressed shift ctrl enter

and voila ( sorry for the french ) a random permutation of 3 names…….

the source for this small miracle is below

Function PERMUTATION(ByRef rSource As Range) As Variant

   Dim PermCol As Collection: Dim Cell As Range: Dim Result() As Variant
   Dim iIndex As Long: Dim i As Long: Dim j As Long

   ReDim Result(1 To Application.Caller.Rows.Count, 1 To Application.Caller.Columns.Count)

   Set PermCol = New Collection

   i = 1

   For Each Cell In rSource

      PermCol.Add CStr(Trim(Cell.Value)), CStr(i): i = i + 1

   Next Cell

   For i = 1 To Application.Caller.Rows.Count

      For j = 1 To Application.Caller.Columns.Count

         iIndex = WorksheetFunction.RandBetween(1, PermCol.Count)
         Result(i, j) = PermCol(iIndex)
         PermCol.Remove (iIndex)

      Next j

   Next i

   PERMUTATION = Result

End Function

I don’t know any real life use for this function besides the dividing of winebotles I told you about.
But if somebody has any use for this beautiful function please let me know…

cheers

Jelle-Jeroen

link to this post on the excel user group with comments and other solution

 

Categories: Excel, UDF Tags: ,

Modified Dietz Method UDF

May 17th, 2011 admin No comments

April 15th, 2008

A while back when there was no xlns.lamkamp.nl I wrote and posted code for a Modified Dietz Method
UDF on wikipedia. For those of you in the financial industry it might be usefull.

Here is the link to that page on wikipedia.

 

Categories: Excel, Financial, UDF Tags: , ,

Extended VLOOKUP UDF Version 2.0 Beta

May 17th, 2011 admin No comments

April 9th, 2008

Thinking about Rick Williams remarks I rewrote the VLOOKPLUS function to perform better.
For an even better performance I shall have to rewrite the function in C/C++ or VB.Net.
Here is the result for now.

( It’s only good for exact matches when working with a negative column_index ).

Public Function VLOOKUPPLUS(l_v, t_a As Range, c_i As Long, Optional r_l) As Variant

   Dim i As Long: Dim l_r As Range

   With Application.WorksheetFunction

      If c_i < 0 Then

         If t_a.Columns.Count + c_i < 0 Then VLOOKUPPLUS = CVErr(xlErrRef): Exit Function

         Set l_r = t_a.Offset(0, t_a.Columns.Count - 1).Resize(t_a.Rows.Count, 1)

         VLOOKUPPLUS = .Index(l_r.Offset(0, c_i + 1), .Match(l_v, l_r, 0))

      ElseIf c_i > 0 Then

         VLOOKUPPLUS = .VLookup(l_v, t_a, c_i, r_l)

      Else

         VLOOKUPPLUS = CVErr(xlErrNA)

      End If

   End With

End Function
Categories: Excel, UDF Tags: ,

Writing Custom Excel Worksheet Functions in C#

May 17th, 2011 admin No comments

April 9th, 2008

Gahban berry wrote a great blog on writing custom Excel Worksheet Functions in C#.

Here is the link

 

Categories: Excel, UDF Tags: , ,

Alternative for my Extended VLOOKUP UDF

May 17th, 2011 admin No comments

April 8th, 2008

Rick Williams had a interesting comment on my Extended VLOOKUP UDF post.
He uses a index(match()) combination for these purposes.
I will look in to the performance comparison.

Here is the link to his reply on the Excel User Group.

 

Categories: Excel, UDF Tags: ,

Extended VLOOKUP UDF

May 17th, 2011 admin No comments

April 8th, 2008

In my daily job we use a lot of VLOOKUP on external datasheets.
We don ‘t want to change these sheets but often we want to do a sort of VLOOKUP but
with a negative column_index parameter, so we can look up to the left of the lookup_value.
I wrote a UDF to do just that.
Can some of you guys comment on my code…….. thnx


Function VLOOKUPPLUS(l_v, t_a As Range, c_i As Long, Optional r_l) As Variant

   Dim i As Long

   If c_i &lt; 0 Then
      If t_a.Columns.Count + c_i &lt; 0 Then
         VLOOKUPPLUS = CVErr(xlErrRef)
      Exit Function
   End If

   If r_l = 0 Then
      For i = 1 To t_a.Rows.Count
         If t_a.Cells(i, t_a.Columns.Count).Value = l_v Then
            VLOOKUPPLUS = t_a.Cells(i, (t_a.Columns.Count + 1 + c_i)).Value
            Exit Function
         End If
         VLOOKUPPLUS = CVErr(xlErrValue)
         Exit Function
      Next i
   Else
      For i = 1 To t_a.Rows.Count
         If t_a.Cells(i, t_a.Columns.Count).Value &lt;= l_v Then
            VLOOKUPPLUS = t_a.Cells(i, (t_a.Columns.Count + 1 + c_i)).Value
         End If
      Next i
   End If
   ElseIf c_i &gt; 0 Then
      VLOOKUPPLUS = Application.WorksheetFunction.VLookup(l_v, t_a, c_i, r_l)
   Else
      VLOOKUPPLUS = CVErr(xlErrNA)
   End If

End Function
Categories: Excel, UDF Tags: ,

ISIN Code UDF

May 17th, 2011 admin No comments

April 2nd, 2008

In my daily job validating security codes is a must.
So I wrote a UDF for validating ISINCodes.
There are no comments in the code, sorry for that…. maybe later…. enjoy..


Public Function ISINCODE(ByVal sISINCode As String) As Boolean

   Dim i As Integer: Dim iTotalScore As Integer
   Dim s As String: Dim sDigits As String

   sISINCode = UCase(Trim(sISINCode))

   If Len(sISINCode) &lt;&gt; 12 Then Exit Function

   If MID(sISINCode,1,1) < "A" Or MID(sISINCode,1,1) > "Z" Then Exit Function
   If MID(sISINCode,2,1) < "A" Or MID(sISINCode,2,1) > "Z" Then Exit Function

   sDigits = ""

   For i = 1 To 11
      s = Mid(sISINCode, i, 1)
      If s &gt;= "0" And s &lt;= "9" Then
         sDigits = sDigits &amp; s
      ElseIf s &gt;= "A" And s &lt;= "Z" Then
         sDigits = sDigits &amp; CStr(Asc(s) - 55)
      Else
         Exit Function
      End If
   Next i

   sDigits = StrReverse(sDigits)

   iTotalScore = 0

   For i = 1 To Len(sDigits)
      iTotalScore = iTotalScore + CInt(Mid(sDigits, i, 1))
      If i Mod 2 = 1 Then
         iTotalScore = iTotalScore + CInt(Mid(sDigits, i, 1))
         If CInt(Mid(sDigits, i, 1)) &gt; 4 Then
            iTotalScore = iTotalScore - 9
         End If
      End If
   Next i

   If (10 - (iTotalScore Mod 10)) Mod 10 = CInt(Mid(sISINCode, 12, 1)) Then
      ISINCODE = True
   End if

End Function
Categories: Excel, Financial, UDF Tags: , ,