Tuesday, November 4, 2014

Making VBA Functions into UDFs

The Challenge

Sometimes we need a function available only in VBA to work on a worksheet.  Sometimes that takes a lot of hard word, sometimes it's too simple to believe.  Here's the process I went through to create a User-Defined Function (UDF) of the InStrRev VBA function after two (more?) people mentioned in this thread on Allen Wyatt's site that it would be nice to have.

The Process

  1. Create the User-Defined Function For this exercise I named it "udf_InStrRev".
  2. The parameters of the UDF are the same as the VBA function's.
  3. The arguments for the VBA function are the parameters of the UDF
  4. The code is <UDF FunctionName> = <VBA Function>(<UDF Function Parameters>)
That's it!

The Code

Function udf_InStrRev(StringCheck As String, _
                      StringMatch As String, _
                      Optional Start As Long = -1, _
                      Optional Compare As VbCompareMethod) As String
    udf_InStrRev = InStrRev(StringCheck, StringMatch, Start, Compare)
End Function