Excel

Determine if a formula is a UDF

Ease of Use

Easy

Version tested with

2000, 2002 

Submitted by:

Justinlabenne

Description:

Use this code to distinguish if the formula on your worksheet are user-defined-functions or native to Excel. 

Discussion:

Before copying a worksheet to a new workbook, it would be relevant to know if the formulas contained within are able to function on their own, or if some of them are udf's, requiring code to be transferred along with the sheet. This check helps me to ensure I know what kinds of formulas are contained on a sheet. 

Code:

instructions for use 

			
Option Explicit 
 '===================================================
 '{Credit to JoeWas and Right-Click from the
 '{MrExcel.com messageboard for help with this code
 '===================================================
Private cNativeFunctions As New Collection 
 
Dim rRng As Range 
Dim mszFunc As String 
 
 
Sub CheckFormula_Native_or_UDF() 
    Dim nm As Name 
    Dim sHasErr As String 
     
     '   Make sure our collection is filled:
    If cNativeFunctions.Count = 0 Then BuildCollection 
     
     
     '   Limit evaluation to formula cells only:
    On Error GoTo ErrExit 
    For Each rRng In Cells.SpecialCells(xlCellTypeFormulas) 
         
         
         '       Evaluate formula for returned errors:
        sHasErr = Empty 
        If Application.WorksheetFunction.IsError(rRng) = True Then _ 
        sHasErr = vbNewLine & "and has an Error!" 
         
         
         '       Determine if the formula is a UDF or a Native Excel function
        If IsUDF(rRng) Then 
             
             
             '           This message for a UDF
            MsgBox "Cell: " & rRng.Address & vbNewLine & _ 
            "Function: " & mszFunc & vbNewLine & vbNewLine & _ 
            "Is a User Defined Function," & sHasErr 
             
             
             
        Else 
             
             
             '           This is the message for a native formula
            MsgBox "Cell: " & rRng.Address & vbNewLine & _ 
            "Function: " & mszFunc & vbNewLine & vbNewLine & _ 
            "Is a Native Excel Function," & sHasErr 
             
             
        End If 
         
         
    Next rRng 
    Exit Sub 
     
ErrExit: 
    MsgBox "No formulas found on this sheet", 64 
End Sub 
 
 
Private Function IsUDF(rRng As Range) As Boolean 
    Dim lret As Long 
     
     '   Make sure our collection is filled:
    If cNativeFunctions.Count = 0 Then BuildCollection 
     
     
     '   Build a replacement temp string out of the cells formula
     '   by cutting the {=} sign off
    mszFunc = Replace(rRng.FormulaLocal, "=", "") 
     
     
     '   =================================================================
     '   We have a collection of built with all the native Excel
     '   formulas, but this is in case we encounter and
     '   {A1+B1} Formulas. Not a udf, and also not in our collection
     '   so we exit the function because it is still a legit native
     '   function.  IsUDF = False
    If InStr(mszFunc, "(") = 0 Then Exit Function 
     '   =================================================================
     
     
     '   Cut the formula up so it can be evaluated against our
     '   native collection
    mszFunc = Left(mszFunc, InStr(mszFunc, "(") - 1) 
     
     '   We stored all the native functions in the colleciotn as uppercase
     '   So make sure our temp string is cinverted to match
    mszFunc = Trim(UCase(mszFunc)) 
     
     
     '   If we get an error, it must be a Udf...
    On Error GoTo ErrHandle 
    lret = cNativeFunctions(mszFunc) 
    Exit Function 
     
ErrHandle: 
    IsUDF = True 
End Function 
 
 
Private Sub BuildCollection() 
     '   Collection of Native Excel formulas
    cNativeFunctions.Add 1, "ABS" 
    cNativeFunctions.Add 1, "ACCRINT" 
    cNativeFunctions.Add 1, "ACCRINTM" 
    cNativeFunctions.Add 1, "ACOS" 
    cNativeFunctions.Add 1, "ACOSH" 
    cNativeFunctions.Add 1, "ADDRESS" 
    cNativeFunctions.Add 1, "AMORDEGRC" 
    cNativeFunctions.Add 1, "AMORLINC" 
    cNativeFunctions.Add 1, "AND" 
    cNativeFunctions.Add 1, "AREAS" 
    cNativeFunctions.Add 1, "ASC" 
    cNativeFunctions.Add 1, "ASIN" 
    cNativeFunctions.Add 1, "ASINH" 
    cNativeFunctions.Add 1, "ATAN" 
    cNativeFunctions.Add 1, "ATAN2" 
    cNativeFunctions.Add 1, "ATANH" 
    cNativeFunctions.Add 1, "AVEDEV" 
    cNativeFunctions.Add 1, "AVERAGE" 
    cNativeFunctions.Add 1, "AVERAGEA" 
    cNativeFunctions.Add 1, "BAHTTEXT" 
    cNativeFunctions.Add 1, "BESSELI" 
    cNativeFunctions.Add 1, "BESSELJ" 
    cNativeFunctions.Add 1, "BESSELK" 
    cNativeFunctions.Add 1, "BESSELY" 
    cNativeFunctions.Add 1, "BETADIST" 
    cNativeFunctions.Add 1, "BETAINV" 
    cNativeFunctions.Add 1, "BIN2DEC" 
    cNativeFunctions.Add 1, "BIN2HEX" 
    cNativeFunctions.Add 1, "BIN2OCT" 
    cNativeFunctions.Add 1, "BINOMDIST" 
    cNativeFunctions.Add 1, "CEILING" 
    cNativeFunctions.Add 1, "CELL" 
    cNativeFunctions.Add 1, "CHAR" 
    cNativeFunctions.Add 1, "CHIDIST" 
    cNativeFunctions.Add 1, "CHIINV" 
    cNativeFunctions.Add 1, "CHITEST" 
    cNativeFunctions.Add 1, "CHOOSE" 
    cNativeFunctions.Add 1, "CLEAN" 
    cNativeFunctions.Add 1, "CODE" 
    cNativeFunctions.Add 1, "COLUMN" 
    cNativeFunctions.Add 1, "COLUMNS" 
    cNativeFunctions.Add 1, "COMBIN" 
    cNativeFunctions.Add 1, "COMPLEX" 
    cNativeFunctions.Add 1, "CONCATENATE" 
    cNativeFunctions.Add 1, "CONFIDENCE" 
    cNativeFunctions.Add 1, "CONVERT" 
    cNativeFunctions.Add 1, "CORREL" 
    cNativeFunctions.Add 1, "COS" 
    cNativeFunctions.Add 1, "COSH" 
    cNativeFunctions.Add 1, "COUNT" 
    cNativeFunctions.Add 1, "COUNTA" 
    cNativeFunctions.Add 1, "COUNTBLANK" 
    cNativeFunctions.Add 1, "COUNTIF" 
    cNativeFunctions.Add 1, "COUPDAYBS" 
    cNativeFunctions.Add 1, "COUPDAYS" 
    cNativeFunctions.Add 1, "COUPDAYSNC" 
    cNativeFunctions.Add 1, "COUPNCD" 
    cNativeFunctions.Add 1, "COUPNUM" 
    cNativeFunctions.Add 1, "COUPPCD" 
    cNativeFunctions.Add 1, "COVAR" 
    cNativeFunctions.Add 1, "CRITBINOM" 
    cNativeFunctions.Add 1, "CUMIPMT" 
    cNativeFunctions.Add 1, "CUMPRINC" 
    cNativeFunctions.Add 1, "DATE" 
    cNativeFunctions.Add 1, "DATEVALUE" 
    cNativeFunctions.Add 1, "DAVERAGE" 
    cNativeFunctions.Add 1, "DAY" 
    cNativeFunctions.Add 1, "DAYS360" 
    cNativeFunctions.Add 1, "DB" 
    cNativeFunctions.Add 1, "DCOUNT" 
    cNativeFunctions.Add 1, "DCOUNTA" 
    cNativeFunctions.Add 1, "DDB" 
    cNativeFunctions.Add 1, "DEC2BIN" 
    cNativeFunctions.Add 1, "DEC2HEX" 
    cNativeFunctions.Add 1, "DEC2OCT" 
    cNativeFunctions.Add 1, "DEGREES" 
    cNativeFunctions.Add 1, "DELTA" 
    cNativeFunctions.Add 1, "DEVSQ" 
    cNativeFunctions.Add 1, "DGET" 
    cNativeFunctions.Add 1, "DISC" 
    cNativeFunctions.Add 1, "DMAX" 
    cNativeFunctions.Add 1, "DMIN" 
    cNativeFunctions.Add 1, "DOLLAR" 
    cNativeFunctions.Add 1, "DOLLARDE" 
    cNativeFunctions.Add 1, "DOLLARFR" 
    cNativeFunctions.Add 1, "DPRODUCT" 
    cNativeFunctions.Add 1, "DSTDEV" 
    cNativeFunctions.Add 1, "DSTDEVP" 
    cNativeFunctions.Add 1, "DSUM" 
    cNativeFunctions.Add 1, "DURATION" 
    cNativeFunctions.Add 1, "DVAR" 
    cNativeFunctions.Add 1, "DVARP" 
    cNativeFunctions.Add 1, "EDATE" 
    cNativeFunctions.Add 1, "EFFECT" 
    cNativeFunctions.Add 1, "EOMONTH" 
    cNativeFunctions.Add 1, "ERF" 
    cNativeFunctions.Add 1, "ERFC" 
    cNativeFunctions.Add 1, "ERROR.TYPE" 
    cNativeFunctions.Add 1, "EUROCONVERT" 
    cNativeFunctions.Add 1, "EVEN" 
    cNativeFunctions.Add 1, "EXACT" 
    cNativeFunctions.Add 1, "EXP" 
    cNativeFunctions.Add 1, "EXPONDIST" 
    cNativeFunctions.Add 1, "FACT" 
    cNativeFunctions.Add 1, "FACTDOUBLE" 
    cNativeFunctions.Add 1, "FALSE" 
    cNativeFunctions.Add 1, "FDIST" 
    cNativeFunctions.Add 1, "FIND" 
    cNativeFunctions.Add 1, "FINV" 
    cNativeFunctions.Add 1, "FISHER" 
    cNativeFunctions.Add 1, "FISHERINV" 
    cNativeFunctions.Add 1, "FIXED" 
    cNativeFunctions.Add 1, "FLOOR" 
    cNativeFunctions.Add 1, "FORECAST" 
    cNativeFunctions.Add 1, "FREQUENCY" 
    cNativeFunctions.Add 1, "FTEST" 
    cNativeFunctions.Add 1, "FV" 
    cNativeFunctions.Add 1, "FVSCHEDULE" 
    cNativeFunctions.Add 1, "GAMMADIST" 
    cNativeFunctions.Add 1, "GAMMAINV" 
    cNativeFunctions.Add 1, "GAMMALN" 
    cNativeFunctions.Add 1, "GCD" 
    cNativeFunctions.Add 1, "GEOMEAN" 
    cNativeFunctions.Add 1, "GESTEP" 
    cNativeFunctions.Add 1, "GETPIVOTDATA" 
    cNativeFunctions.Add 1, "GROWTH" 
    cNativeFunctions.Add 1, "HARMEAN" 
    cNativeFunctions.Add 1, "HEX2BIN" 
    cNativeFunctions.Add 1, "HEX2DEC" 
    cNativeFunctions.Add 1, "HEX2OCT" 
    cNativeFunctions.Add 1, "HLOOKUP" 
    cNativeFunctions.Add 1, "HOUR" 
    cNativeFunctions.Add 1, "HYPERLINK" 
    cNativeFunctions.Add 1, "HYPGEOMDIST" 
    cNativeFunctions.Add 1, "IF" 
    cNativeFunctions.Add 1, "IMABS" 
    cNativeFunctions.Add 1, "IMAGINARY" 
    cNativeFunctions.Add 1, "IMARGUMENT" 
    cNativeFunctions.Add 1, "IMCONJUGATE" 
    cNativeFunctions.Add 1, "IMCOS" 
    cNativeFunctions.Add 1, "IMDIV" 
    cNativeFunctions.Add 1, "IMEXP" 
    cNativeFunctions.Add 1, "IMLN" 
    cNativeFunctions.Add 1, "IMLOG10" 
    cNativeFunctions.Add 1, "IMLOG2" 
    cNativeFunctions.Add 1, "IMPOWER" 
    cNativeFunctions.Add 1, "IMPRODUCT" 
    cNativeFunctions.Add 1, "IMREAL" 
    cNativeFunctions.Add 1, "IMSIN" 
    cNativeFunctions.Add 1, "IMSQRT" 
    cNativeFunctions.Add 1, "IMSUB" 
    cNativeFunctions.Add 1, "IMSUM" 
    cNativeFunctions.Add 1, "INDEX" 
    cNativeFunctions.Add 1, "INDIRECT" 
    cNativeFunctions.Add 1, "INFO" 
    cNativeFunctions.Add 1, "INT" 
    cNativeFunctions.Add 1, "INTERCEPT" 
    cNativeFunctions.Add 1, "INTRATE" 
    cNativeFunctions.Add 1, "IPMT" 
    cNativeFunctions.Add 1, "IRR" 
    cNativeFunctions.Add 1, "ISBLANK" 
    cNativeFunctions.Add 1, "ISERR" 
    cNativeFunctions.Add 1, "ISERROR" 
    cNativeFunctions.Add 1, "ISEVEN" 
    cNativeFunctions.Add 1, "ISLOGICAL" 
    cNativeFunctions.Add 1, "ISNA" 
    cNativeFunctions.Add 1, "ISNONTEXT" 
    cNativeFunctions.Add 1, "ISNUMBER" 
    cNativeFunctions.Add 1, "ISODD" 
    cNativeFunctions.Add 1, "ISPMT" 
    cNativeFunctions.Add 1, "ISREF" 
    cNativeFunctions.Add 1, "ISTEXT" 
    cNativeFunctions.Add 1, "JIS" 
    cNativeFunctions.Add 1, "KURT" 
    cNativeFunctions.Add 1, "LARGE" 
    cNativeFunctions.Add 1, "LCM" 
    cNativeFunctions.Add 1, "LEFT" 
    cNativeFunctions.Add 1, "LEN" 
    cNativeFunctions.Add 1, "LINEST" 
    cNativeFunctions.Add 1, "LN" 
    cNativeFunctions.Add 1, "LOG" 
    cNativeFunctions.Add 1, "LOG10" 
    cNativeFunctions.Add 1, "LOGEST" 
    cNativeFunctions.Add 1, "LOGINV" 
    cNativeFunctions.Add 1, "LOGNORMDIST" 
    cNativeFunctions.Add 1, "LOOKUP" 
    cNativeFunctions.Add 1, "LOWER" 
    cNativeFunctions.Add 1, "MATCH" 
    cNativeFunctions.Add 1, "MAX" 
    cNativeFunctions.Add 1, "MAXA" 
    cNativeFunctions.Add 1, "MDETERM" 
    cNativeFunctions.Add 1, "MDURATION" 
    cNativeFunctions.Add 1, "MEDIAN" 
    cNativeFunctions.Add 1, "MID" 
    cNativeFunctions.Add 1, "MIN" 
    cNativeFunctions.Add 1, "MINA" 
    cNativeFunctions.Add 1, "MINUTE" 
    cNativeFunctions.Add 1, "MINVERSE" 
    cNativeFunctions.Add 1, "MIRR" 
    cNativeFunctions.Add 1, "MMULT" 
    cNativeFunctions.Add 1, "MOD" 
    cNativeFunctions.Add 1, "MODE" 
    cNativeFunctions.Add 1, "MONTH" 
    cNativeFunctions.Add 1, "MROUND" 
    cNativeFunctions.Add 1, "MULTINOMIAL" 
    cNativeFunctions.Add 1, "N" 
    cNativeFunctions.Add 1, "NA" 
    cNativeFunctions.Add 1, "NEGBINOMDIST" 
    cNativeFunctions.Add 1, "NETWORKDAYS" 
    cNativeFunctions.Add 1, "NOMINAL" 
    cNativeFunctions.Add 1, "NORMDIST" 
    cNativeFunctions.Add 1, "NORMINV" 
    cNativeFunctions.Add 1, "NORMSDIST" 
    cNativeFunctions.Add 1, "NORMSINV" 
    cNativeFunctions.Add 1, "NOT" 
    cNativeFunctions.Add 1, "NOW" 
    cNativeFunctions.Add 1, "NPER" 
    cNativeFunctions.Add 1, "NPV" 
    cNativeFunctions.Add 1, "OCT2BIN" 
    cNativeFunctions.Add 1, "OCT2DEC" 
    cNativeFunctions.Add 1, "OCT2HEX" 
    cNativeFunctions.Add 1, "ODD" 
    cNativeFunctions.Add 1, "ODDFPRICE" 
    cNativeFunctions.Add 1, "ODDFYIELD" 
    cNativeFunctions.Add 1, "ODDLPRICE" 
    cNativeFunctions.Add 1, "ODDLYIELD" 
    cNativeFunctions.Add 1, "OFFSET" 
    cNativeFunctions.Add 1, "OR" 
    cNativeFunctions.Add 1, "PEARSON" 
    cNativeFunctions.Add 1, "PERCENTILE" 
    cNativeFunctions.Add 1, "PERCENTRANK" 
    cNativeFunctions.Add 1, "PERMUT" 
    cNativeFunctions.Add 1, "PHONETIC" 
    cNativeFunctions.Add 1, "PI" 
    cNativeFunctions.Add 1, "PMT" 
    cNativeFunctions.Add 1, "POISSON" 
    cNativeFunctions.Add 1, "POWER" 
    cNativeFunctions.Add 1, "PPMT" 
    cNativeFunctions.Add 1, "PRICE" 
    cNativeFunctions.Add 1, "PRICEDISC" 
    cNativeFunctions.Add 1, "PRICEMAT" 
    cNativeFunctions.Add 1, "PROB" 
    cNativeFunctions.Add 1, "PRODUCT" 
    cNativeFunctions.Add 1, "PROPER" 
    cNativeFunctions.Add 1, "PV" 
    cNativeFunctions.Add 1, "QUARTILE" 
    cNativeFunctions.Add 1, "QUOTIENT" 
    cNativeFunctions.Add 1, "RADIANS" 
    cNativeFunctions.Add 1, "RAND" 
    cNativeFunctions.Add 1, "RANDBETWEEN" 
    cNativeFunctions.Add 1, "RANK" 
    cNativeFunctions.Add 1, "RATE" 
    cNativeFunctions.Add 1, "RECEIVED" 
    cNativeFunctions.Add 1, "REPLACE" 
    cNativeFunctions.Add 1, "REPT" 
    cNativeFunctions.Add 1, "RIGHT" 
    cNativeFunctions.Add 1, "ROMAN" 
    cNativeFunctions.Add 1, "ROUND" 
    cNativeFunctions.Add 1, "ROUNDDOWN" 
    cNativeFunctions.Add 1, "ROUNDUP" 
    cNativeFunctions.Add 1, "ROW" 
    cNativeFunctions.Add 1, "ROWS" 
    cNativeFunctions.Add 1, "RSQ" 
    cNativeFunctions.Add 1, "RTD" 
    cNativeFunctions.Add 1, "SEARCH" 
    cNativeFunctions.Add 1, "SECOND" 
    cNativeFunctions.Add 1, "SERIESSUM" 
    cNativeFunctions.Add 1, "SIGN" 
    cNativeFunctions.Add 1, "SIN" 
    cNativeFunctions.Add 1, "SINH" 
    cNativeFunctions.Add 1, "SKEW" 
    cNativeFunctions.Add 1, "SLN" 
    cNativeFunctions.Add 1, "SLOPE" 
    cNativeFunctions.Add 1, "SMALL" 
    cNativeFunctions.Add 1, "SQL.REQUEST" 
    cNativeFunctions.Add 1, "SQRT" 
    cNativeFunctions.Add 1, "SQRTPI" 
    cNativeFunctions.Add 1, "STANDARDIZE" 
    cNativeFunctions.Add 1, "STDEV" 
    cNativeFunctions.Add 1, "STDEVA" 
    cNativeFunctions.Add 1, "STDEVP" 
    cNativeFunctions.Add 1, "STDEVPA" 
    cNativeFunctions.Add 1, "STEYX" 
    cNativeFunctions.Add 1, "SUBSTITUTE" 
    cNativeFunctions.Add 1, "SUBTOTAL" 
    cNativeFunctions.Add 1, "SUM" 
    cNativeFunctions.Add 1, "SUMIF" 
    cNativeFunctions.Add 1, "SUMPRODUCT" 
    cNativeFunctions.Add 1, "SUMSQ" 
    cNativeFunctions.Add 1, "SUMX2MY2" 
    cNativeFunctions.Add 1, "SUMX2PY2" 
    cNativeFunctions.Add 1, "SUMXMY2" 
    cNativeFunctions.Add 1, "SYD" 
    cNativeFunctions.Add 1, "T" 
    cNativeFunctions.Add 1, "TAN" 
    cNativeFunctions.Add 1, "TANH" 
    cNativeFunctions.Add 1, "TBILLEQ" 
    cNativeFunctions.Add 1, "TBILLPRICE" 
    cNativeFunctions.Add 1, "TBILLYIELD" 
    cNativeFunctions.Add 1, "TDIST" 
    cNativeFunctions.Add 1, "TEXT" 
    cNativeFunctions.Add 1, "These" 
    cNativeFunctions.Add 1, "TIME" 
    cNativeFunctions.Add 1, "TIMEVALUE" 
    cNativeFunctions.Add 1, "TINV" 
    cNativeFunctions.Add 1, "TODAY" 
    cNativeFunctions.Add 1, "TRANSPOSE" 
    cNativeFunctions.Add 1, "TREND" 
    cNativeFunctions.Add 1, "TRIM" 
    cNativeFunctions.Add 1, "TRIMMEAN" 
    cNativeFunctions.Add 1, "TRUE" 
    cNativeFunctions.Add 1, "TRUNC" 
    cNativeFunctions.Add 1, "TTEST" 
    cNativeFunctions.Add 1, "TYPE" 
    cNativeFunctions.Add 1, "UPPER" 
    cNativeFunctions.Add 1, "VALUE" 
    cNativeFunctions.Add 1, "VAR" 
    cNativeFunctions.Add 1, "VARA" 
    cNativeFunctions.Add 1, "VARP" 
    cNativeFunctions.Add 1, "VARPA" 
    cNativeFunctions.Add 1, "VDB" 
    cNativeFunctions.Add 1, "VLOOKUP" 
    cNativeFunctions.Add 1, "WEEKDAY" 
    cNativeFunctions.Add 1, "WEEKNUM" 
    cNativeFunctions.Add 1, "WEIBULL" 
    cNativeFunctions.Add 1, "WORKDAY" 
    cNativeFunctions.Add 1, "XIRR" 
    cNativeFunctions.Add 1, "XNPV" 
    cNativeFunctions.Add 1, "YEAR" 
    cNativeFunctions.Add 1, "YEARFRAC" 
    cNativeFunctions.Add 1, "YIELD" 
    cNativeFunctions.Add 1, "YIELDDISC" 
    cNativeFunctions.Add 1, "YIELDMAT" 
    cNativeFunctions.Add 1, "ZTEST" 
End Sub

How to use:

  1. Open an Excel Workbook
  2. Copy the code
  3. Press Alt + F11 to open the Visual Basic Editor (VBE)
  4. Select INSERT > MODULE from the menubar
  5. Paste code into the right pane
  6. Press Alt+Q to return to Excel
  7. Save workbook before any other changes
 

Test the code:

  1. Put some formulas on your worksheet (Native and Udf's)
  2. Go to TOOLS > MACRO > MACROS
  3. When the dialog appears, select {CheckFormula_Native_or_UDF}
  4. Press Run
 

Sample File:

IsUdf.zip 22.33KB 

Approved by mdmackillop


This entry has been viewed 127 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express