Distinguish between udf - Native Function

Justinlabenne · Jun 25th, 2005, 07:41 AM

Not sure if I am just not thinking straight, but is it possible to determine via code if a formula is "built-in" or if it is a udf?

I understand the code below is invalid, and does not work, but I post it to help you identify the type of determination I am trying to make

Code:

Sub CheckFormulaType()
    Dim r As Range
    
    For Each r In Cells.SpecialCells(xlCellTypeFormulas)
        If Not r.Formula Is Application.WorksheetFunction Then
            r.Interior.ColorIndex = 3
        End If
    Next r
End Sub

Tom Schreiner · Jun 26th, 2005, 08:41 AM

Well...

Here is a list of every Excel function (2003). It would not be difficult to load these items into a collection and then check them against:

r.FormulaLocal

Code:

ABS 
ACCRINT 
ACCRINTM 
ACOS 
ACOSH 
ADDRESS 
AMORDEGRC 
AMORLINC 
AND 
AREAS 
ASC 
ASIN 
ASINH 
ATAN 
ATAN2 
ATANH 
AVEDEV 
AVERAGE 
AVERAGEA 
BAHTTEXT 
BESSELI 
BESSELJ 
BESSELK 
BESSELY 
BETADIST 
BETAINV 
BIN2DEC 
BIN2HEX 
BIN2OCT 
BINOMDIST 
CEILING 
CELL 
CHAR 
CHIDIST 
CHIINV 
CHITEST 
CHOOSE 
CLEAN 
CODE 
COLUMN 
COLUMNS 
COMBIN 
COMPLEX 
CONCATENATE 
CONFIDENCE 
CONVERT 
CORREL 
COS 
COSH 
COUNT 
COUNTA 
COUNTBLANK 
COUNTIF 
COUPDAYBS 
COUPDAYS 
COUPDAYSNC 
COUPNCD 
COUPNUM 
COUPPCD 
COVAR 
CRITBINOM 
CUMIPMT 
CUMPRINC 
DATE 
DATEVALUE 
DAVERAGE 
DAY 
DAYS360 
DB 
DCOUNT 
DCOUNTA 
DDB 
DEC2BIN 
DEC2HEX 
DEC2OCT 
DEGREES 
DELTA 
DEVSQ 
DGET 
DISC 
DMAX 
DMIN 
DOLLAR 
DOLLARDE 
DOLLARFR 
DPRODUCT 
DSTDEV 
DSTDEVP 
DSUM 
DURATION 
DVAR 
DVARP 
EDATE 
EFFECT 
EOMONTH 
ERF 
ERFC 
ERROR.TYPE 
EUROCONVERT 
EVEN 
EXACT 
EXP 
EXPONDIST 
FACT 
FACTDOUBLE 
FALSE 
FDIST 
FIND 
FINV 
FISHER 
FISHERINV 
FIXED 
FLOOR 
FORECAST 
FREQUENCY 
FTEST 
FV 
FVSCHEDULE 
GAMMADIST 
GAMMAINV 
GAMMALN 
GCD 
GEOMEAN 
GESTEP 
GETPIVOTDATA 
GROWTH 
HARMEAN 
HEX2BIN 
HEX2DEC 
HEX2OCT 
HLOOKUP 
HOUR 
HYPERLINK 
HYPGEOMDIST 
IF 
IMABS 
IMAGINARY 
IMARGUMENT 
IMCONJUGATE 
IMCOS 
IMDIV 
IMEXP 
IMLN 
IMLOG10 
IMLOG2 
IMPOWER 
IMPRODUCT 
IMREAL 
IMSIN 
IMSQRT 
IMSUB 
IMSUM 
INDEX 
INDIRECT 
INFO 
INT 
INTERCEPT 
INTRATE 
IPMT 
IRR 
ISBLANK 
ISERR 
ISERROR 
ISEVEN 
ISLOGICAL 
ISNA 
ISNONTEXT 
ISNUMBER 
ISODD 
ISPMT 
ISREF 
ISTEXT 
JIS 
KURT 
LARGE 
LCM 
LEFT 
LEN 
LINEST 
LN 
LOG 
LOG10 
LOGEST 
LOGINV 
LOGNORMDIST 
LOOKUP 
LOWER 
MATCH 
MAX 
MAXA 
MDETERM 
MDURATION 
MEDIAN 
MID 
MIN 
MINA 
MINUTE 
MINVERSE 
MIRR 
MMULT 
MOD 
MODE 
MONTH 
MROUND 
MULTINOMIAL 
N 
NA 
NEGBINOMDIST 
NETWORKDAYS 
NOMINAL 
NORMDIST 
NORMINV 
NORMSDIST 
NORMSINV 
NOT 
NOW 
NPER 
NPV 
OCT2BIN 
OCT2DEC 
OCT2HEX 
ODD 
ODDFPRICE 
ODDFYIELD 
ODDLPRICE 
ODDLYIELD 
OFFSET 
OR 
PEARSON 
PERCENTILE 
PERCENTRANK 
PERMUT 
PHONETIC 
PI 
PMT 
POISSON 
POWER 
PPMT 
PRICE 
PRICEDISC 
PRICEMAT 
PROB 
PRODUCT 
PROPER 
PV 
QUARTILE 
QUOTIENT 
RADIANS 
RAND 
RANDBETWEEN 
RANK 
RATE 
RECEIVED 
REPLACE 
REPT 
RIGHT 
ROMAN 
ROUND 
ROUNDDOWN 
ROUNDUP 
ROW 
ROWS 
RSQ 
RTD 
SEARCH 
SECOND 
SERIESSUM 
SIGN 
SIN 
SINH 
SKEW 
SLN 
SLOPE 
SMALL 
SQL.REQUEST 
SQRT 
SQRTPI 
STANDARDIZE 
STDEV 
STDEVA 
STDEVP 
STDEVPA 
STEYX 
SUBSTITUTE 
SUBTOTAL 
SUM 
SUMIF 
SUMPRODUCT 
SUMSQ 
SUMX2MY2 
SUMX2PY2 
SUMXMY2 
SYD 
T 
TAN 
TANH 
TBILLEQ 
TBILLPRICE 
TBILLYIELD 
TDIST 
TEXT 
These 
TIME 
TIMEVALUE 
TINV 
TODAY 
TRANSPOSE 
TREND 
TRIM 
TRIMMEAN 
TRUE 
TRUNC 
TTEST 
TYPE 
UPPER 
VALUE 
VAR 
VARA 
VARP 
VARPA 
VDB 
VLOOKUP 
WEEKDAY 
WEEKNUM 
WEIBULL 
WORKDAY 
XIRR 
XNPV 
YEAR 
YEARFRAC 
YIELD 
YIELDDISC 
YIELDMAT 
ZTEST

Tom Schreiner · Jun 26th, 2005, 08:59 AM

Copy functions from previous post into a1:a334
Run LoadCollection
Run CheckFormulaType

Code:

Dim c As New Collection


Sub LoadCollection()
    Dim r As Range
    
    For Each r In [a1:a334]
        c.Add r.Row, Trim(UCase(r))
    Next
    
End Sub

Sub CheckFormulaType()
    Dim r As Range
    
    For Each r In Cells.SpecialCells(xlCellTypeFormulas)
        If IsUDF(r) Then
            MsgBox "UDF"
        Else
            MsgBox "Native Excel Function"
        End If
    Next r
    
End Sub

Function IsUDF(r As Range) As Boolean
    Dim b As Long, fStr As String
    
    
    fStr = Replace(r.FormulaLocal, "=", "")
    fStr = Left(fStr, InStr(fStr, "(") - 1)
    fStr = Trim(UCase(fStr))
    On Error GoTo Err_
    b = c(fStr)

Exit Function
Err_:
IsUDF = True
End Function

Joe Was · Jun 26th, 2005, 10:18 AM

Right-Click, using your code and adding some window dressing, see if you like your code better now?

Private c As New Collection, fStr As String, p As Range

Sub CheckFormulaType()
Dim f As Range

If c.Count = 0 Then
For Each f In [a1:a334]
c.Add f.Row, Trim(UCase(f))
Next f
End If

For Each p In Cells.SpecialCells(xlCellTypeFormulas)
If IsUDF(p) Then
MsgBox "Cell: " & p.Address & vbCr & _
"Function: " & fStr & vbCr & _
"Is a User Defined Function"
Else
MsgBox "Cell: " & p.Address & vbCr & _
"Function: " & fStr & vbCr & _
"Is a Native Excel Function!"
End If
Next p
End Sub

Function IsUDF(p As Range) As Boolean
Dim b As Long

fStr = Replace(p.FormulaLocal, "=", "")
fStr = Left(fStr, InStr(fStr, "(") - 1)
fStr = Trim(UCase(fStr))
On Error GoTo Err_
b = c(fStr)

Exit Function
Err_:
IsUDF = True
End Function

Tom Schreiner · Jun 26th, 2005, 10:35 AM

Much Better!

:o :x

:-> :-> :->

Justinlabenne · Jun 26th, 2005, 11:13 AM

Very nice, only issue I have is a simple formula like this: =D3/E3 causes an error or if the formula is returning an error value. I will have to see what I can modify to resolve, thanks for the start from both of you, couldn't get my head around this one so I appreciate you time and effort,

Thanks again,

Joe Was · Jun 26th, 2005, 11:25 AM

Easy fix:

Private c As New Collection, fStr As String, p As Range

Sub CheckFormulaType()
Dim f As Range

If c.Count = 0 Then
For Each f In [a1:a334]
c.Add f.Row, Trim(UCase(f))
Next f
End If

For Each p In Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo myErr

If IsUDF(p) Then
MsgBox "Cell: " & p.Address & vbCr & _
"Function: " & fStr & vbCr & _
"Is a User Defined Function"
Else
MsgBox "Cell: " & p.Address & vbCr & _
"Function: " & fStr & vbCr & _
"Is a Native Excel Function!"
End If

GoTo myNext

myErr:
If p.HasFormula = True Then MsgBox "Cell: " & _
p.Address & vbCr & _
"Contains a formula!"

myNext:
Next p
End Sub

Function IsUDF(p As Range) As Boolean
Dim b As Long

fStr = Replace(p.FormulaLocal, "=", "")
fStr = Left(fStr, InStr(fStr, "(") - 1)
fStr = Trim(UCase(fStr))
On Error GoTo Err_
b = c(fStr)

Exit Function
Err_:
IsUDF = True
End Function

Tom Schreiner · Jun 26th, 2005, 11:39 AM

Any UDF will have parenthesis. Check for those. Here is a modification of Joe's modification to allow for your example "=D3/E3" It also accounts for workbook names that will also appear as function...

You may also use a UDF to determine if other cells contain UDF's such as:

=IsUdf(A1)

Code:

Option Explicit

Private c As New Collection, fStr As String, p As Range

Sub CheckFormulaType()
Dim f As Range, n As Name

If c.Count = 0 Then BuildCollection

For Each p In Cells.SpecialCells(xlCellTypeFormulas)
    If IsUDF(p) Then
        MsgBox "Cell: " & p.Address & vbCr & _
        "Function: " & fStr & vbCr & _
        "Is a User Defined Function"
    Else
        On Error Resume Next
        Set n = Names(Replace(p.FormulaLocal, "=", ""))
        On Error GoTo 0
        If Not n Is Nothing Then
            MsgBox "Cell: " & p.Address & vbCr & _
            "Function: " & fStr & vbCr & _
            "Is a Workbook Name!"
            Set n = Nothing
        Else
            MsgBox "Cell: " & p.Address & vbCr & _
            "Function: " & fStr & vbCr & _
            "Is a Native Excel Function!"
        End If
    End If
Next p

End Sub



Function IsUDF(p As Range) As Boolean
    Dim b As Long
    
    If c.Count = 0 Then BuildCollection
    
    fStr = Replace(p.FormulaLocal, "=", "")
    If InStr(fStr, "(") = 0 Then Exit Function
    fStr = Left(fStr, InStr(fStr, "(") - 1)
    fStr = Trim(UCase(fStr))
    On Error GoTo Err_
    b = c(fStr)
    
    Exit Function
Err_:
    IsUDF = True
End Function



Sub BuildCollection()
    c.Add 1, "ABS"
    c.Add 1, "ACCRINT"
    c.Add 1, "ACCRINTM"
    c.Add 1, "ACOS"
    c.Add 1, "ACOSH"
    c.Add 1, "ADDRESS"
    c.Add 1, "AMORDEGRC"
    c.Add 1, "AMORLINC"
    c.Add 1, "AND"
    c.Add 1, "AREAS"
    c.Add 1, "ASC"
    c.Add 1, "ASIN"
    c.Add 1, "ASINH"
    c.Add 1, "ATAN"
    c.Add 1, "ATAN2"
    c.Add 1, "ATANH"
    c.Add 1, "AVEDEV"
    c.Add 1, "AVERAGE"
    c.Add 1, "AVERAGEA"
    c.Add 1, "BAHTTEXT"
    c.Add 1, "BESSELI"
    c.Add 1, "BESSELJ"
    c.Add 1, "BESSELK"
    c.Add 1, "BESSELY"
    c.Add 1, "BETADIST"
    c.Add 1, "BETAINV"
    c.Add 1, "BIN2DEC"
    c.Add 1, "BIN2HEX"
    c.Add 1, "BIN2OCT"
    c.Add 1, "BINOMDIST"
    c.Add 1, "CEILING"
    c.Add 1, "CELL"
    c.Add 1, "CHAR"
    c.Add 1, "CHIDIST"
    c.Add 1, "CHIINV"
    c.Add 1, "CHITEST"
    c.Add 1, "CHOOSE"
    c.Add 1, "CLEAN"
    c.Add 1, "CODE"
    c.Add 1, "COLUMN"
    c.Add 1, "COLUMNS"
    c.Add 1, "COMBIN"
    c.Add 1, "COMPLEX"
    c.Add 1, "CONCATENATE"
    c.Add 1, "CONFIDENCE"
    c.Add 1, "CONVERT"
    c.Add 1, "CORREL"
    c.Add 1, "COS"
    c.Add 1, "COSH"
    c.Add 1, "COUNT"
    c.Add 1, "COUNTA"
    c.Add 1, "COUNTBLANK"
    c.Add 1, "COUNTIF"
    c.Add 1, "COUPDAYBS"
    c.Add 1, "COUPDAYS"
    c.Add 1, "COUPDAYSNC"
    c.Add 1, "COUPNCD"
    c.Add 1, "COUPNUM"
    c.Add 1, "COUPPCD"
    c.Add 1, "COVAR"
    c.Add 1, "CRITBINOM"
    c.Add 1, "CUMIPMT"
    c.Add 1, "CUMPRINC"
    c.Add 1, "DATE"
    c.Add 1, "DATEVALUE"
    c.Add 1, "DAVERAGE"
    c.Add 1, "DAY"
    c.Add 1, "DAYS360"
    c.Add 1, "DB"
    c.Add 1, "DCOUNT"
    c.Add 1, "DCOUNTA"
    c.Add 1, "DDB"
    c.Add 1, "DEC2BIN"
    c.Add 1, "DEC2HEX"
    c.Add 1, "DEC2OCT"
    c.Add 1, "DEGREES"
    c.Add 1, "DELTA"
    c.Add 1, "DEVSQ"
    c.Add 1, "DGET"
    c.Add 1, "DISC"
    c.Add 1, "DMAX"
    c.Add 1, "DMIN"
    c.Add 1, "DOLLAR"
    c.Add 1, "DOLLARDE"
    c.Add 1, "DOLLARFR"
    c.Add 1, "DPRODUCT"
    c.Add 1, "DSTDEV"
    c.Add 1, "DSTDEVP"
    c.Add 1, "DSUM"
    c.Add 1, "DURATION"
    c.Add 1, "DVAR"
    c.Add 1, "DVARP"
    c.Add 1, "EDATE"
    c.Add 1, "EFFECT"
    c.Add 1, "EOMONTH"
    c.Add 1, "ERF"
    c.Add 1, "ERFC"
    c.Add 1, "ERROR.TYPE"
    c.Add 1, "EUROCONVERT"
    c.Add 1, "EVEN"
    c.Add 1, "EXACT"
    c.Add 1, "EXP"
    c.Add 1, "EXPONDIST"
    c.Add 1, "FACT"
    c.Add 1, "FACTDOUBLE"
    c.Add 1, "FALSE"
    c.Add 1, "FDIST"
    c.Add 1, "FIND"
    c.Add 1, "FINV"
    c.Add 1, "FISHER"
    c.Add 1, "FISHERINV"
    c.Add 1, "FIXED"
    c.Add 1, "FLOOR"
    c.Add 1, "FORECAST"
    c.Add 1, "FREQUENCY"
    c.Add 1, "FTEST"
    c.Add 1, "FV"
    c.Add 1, "FVSCHEDULE"
    c.Add 1, "GAMMADIST"
    c.Add 1, "GAMMAINV"
    c.Add 1, "GAMMALN"
    c.Add 1, "GCD"
    c.Add 1, "GEOMEAN"
    c.Add 1, "GESTEP"
    c.Add 1, "GETPIVOTDATA"
    c.Add 1, "GROWTH"
    c.Add 1, "HARMEAN"
    c.Add 1, "HEX2BIN"
    c.Add 1, "HEX2DEC"
    c.Add 1, "HEX2OCT"
    c.Add 1, "HLOOKUP"
    c.Add 1, "HOUR"
    c.Add 1, "HYPERLINK"
    c.Add 1, "HYPGEOMDIST"
    c.Add 1, "IF"
    c.Add 1, "IMABS"
    c.Add 1, "IMAGINARY"
    c.Add 1, "IMARGUMENT"
    c.Add 1, "IMCONJUGATE"
    c.Add 1, "IMCOS"
    c.Add 1, "IMDIV"
    c.Add 1, "IMEXP"
    c.Add 1, "IMLN"
    c.Add 1, "IMLOG10"
    c.Add 1, "IMLOG2"
    c.Add 1, "IMPOWER"
    c.Add 1, "IMPRODUCT"
    c.Add 1, "IMREAL"
    c.Add 1, "IMSIN"
    c.Add 1, "IMSQRT"
    c.Add 1, "IMSUB"
    c.Add 1, "IMSUM"
    c.Add 1, "INDEX"
    c.Add 1, "INDIRECT"
    c.Add 1, "INFO"
    c.Add 1, "INT"
    c.Add 1, "INTERCEPT"
    c.Add 1, "INTRATE"
    c.Add 1, "IPMT"
    c.Add 1, "IRR"
    c.Add 1, "ISBLANK"
    c.Add 1, "ISERR"
    c.Add 1, "ISERROR"
    c.Add 1, "ISEVEN"
    c.Add 1, "ISLOGICAL"
    c.Add 1, "ISNA"
    c.Add 1, "ISNONTEXT"
    c.Add 1, "ISNUMBER"
    c.Add 1, "ISODD"
    c.Add 1, "ISPMT"
    c.Add 1, "ISREF"
    c.Add 1, "ISTEXT"
    c.Add 1, "JIS"
    c.Add 1, "KURT"
    c.Add 1, "LARGE"
    c.Add 1, "LCM"
    c.Add 1, "LEFT"
    c.Add 1, "LEN"
    c.Add 1, "LINEST"
    c.Add 1, "LN"
    c.Add 1, "LOG"
    c.Add 1, "LOG10"
    c.Add 1, "LOGEST"
    c.Add 1, "LOGINV"
    c.Add 1, "LOGNORMDIST"
    c.Add 1, "LOOKUP"
    c.Add 1, "LOWER"
    c.Add 1, "MATCH"
    c.Add 1, "MAX"
    c.Add 1, "MAXA"
    c.Add 1, "MDETERM"
    c.Add 1, "MDURATION"
    c.Add 1, "MEDIAN"
    c.Add 1, "MID"
    c.Add 1, "MIN"
    c.Add 1, "MINA"
    c.Add 1, "MINUTE"
    c.Add 1, "MINVERSE"
    c.Add 1, "MIRR"
    c.Add 1, "MMULT"
    c.Add 1, "MOD"
    c.Add 1, "MODE"
    c.Add 1, "MONTH"
    c.Add 1, "MROUND"
    c.Add 1, "MULTINOMIAL"
    c.Add 1, "N"
    c.Add 1, "NA"
    c.Add 1, "NEGBINOMDIST"
    c.Add 1, "NETWORKDAYS"
    c.Add 1, "NOMINAL"
    c.Add 1, "NORMDIST"
    c.Add 1, "NORMINV"
    c.Add 1, "NORMSDIST"
    c.Add 1, "NORMSINV"
    c.Add 1, "NOT"
    c.Add 1, "NOW"
    c.Add 1, "NPER"
    c.Add 1, "NPV"
    c.Add 1, "OCT2BIN"
    c.Add 1, "OCT2DEC"
    c.Add 1, "OCT2HEX"
    c.Add 1, "ODD"
    c.Add 1, "ODDFPRICE"
    c.Add 1, "ODDFYIELD"
    c.Add 1, "ODDLPRICE"
    c.Add 1, "ODDLYIELD"
    c.Add 1, "OFFSET"
    c.Add 1, "OR"
    c.Add 1, "PEARSON"
    c.Add 1, "PERCENTILE"
    c.Add 1, "PERCENTRANK"
    c.Add 1, "PERMUT"
    c.Add 1, "PHONETIC"
    c.Add 1, "PI"
    c.Add 1, "PMT"
    c.Add 1, "POISSON"
    c.Add 1, "POWER"
    c.Add 1, "PPMT"
    c.Add 1, "PRICE"
    c.Add 1, "PRICEDISC"
    c.Add 1, "PRICEMAT"
    c.Add 1, "PROB"
    c.Add 1, "PRODUCT"
    c.Add 1, "PROPER"
    c.Add 1, "PV"
    c.Add 1, "QUARTILE"
    c.Add 1, "QUOTIENT"
    c.Add 1, "RADIANS"
    c.Add 1, "RAND"
    c.Add 1, "RANDBETWEEN"
    c.Add 1, "RANK"
    c.Add 1, "RATE"
    c.Add 1, "RECEIVED"
    c.Add 1, "REPLACE"
    c.Add 1, "REPT"
    c.Add 1, "RIGHT"
    c.Add 1, "ROMAN"
    c.Add 1, "ROUND"
    c.Add 1, "ROUNDDOWN"
    c.Add 1, "ROUNDUP"
    c.Add 1, "ROW"
    c.Add 1, "ROWS"
    c.Add 1, "RSQ"
    c.Add 1, "RTD"
    c.Add 1, "SEARCH"
    c.Add 1, "SECOND"
    c.Add 1, "SERIESSUM"
    c.Add 1, "SIGN"
    c.Add 1, "SIN"
    c.Add 1, "SINH"
    c.Add 1, "SKEW"
    c.Add 1, "SLN"
    c.Add 1, "SLOPE"
    c.Add 1, "SMALL"
    c.Add 1, "SQL.REQUEST"
    c.Add 1, "SQRT"
    c.Add 1, "SQRTPI"
    c.Add 1, "STANDARDIZE"
    c.Add 1, "STDEV"
    c.Add 1, "STDEVA"
    c.Add 1, "STDEVP"
    c.Add 1, "STDEVPA"
    c.Add 1, "STEYX"
    c.Add 1, "SUBSTITUTE"
    c.Add 1, "SUBTOTAL"
    c.Add 1, "SUM"
    c.Add 1, "SUMIF"
    c.Add 1, "SUMPRODUCT"
    c.Add 1, "SUMSQ"
    c.Add 1, "SUMX2MY2"
    c.Add 1, "SUMX2PY2"
    c.Add 1, "SUMXMY2"
    c.Add 1, "SYD"
    c.Add 1, "T"
    c.Add 1, "TAN"
    c.Add 1, "TANH"
    c.Add 1, "TBILLEQ"
    c.Add 1, "TBILLPRICE"
    c.Add 1, "TBILLYIELD"
    c.Add 1, "TDIST"
    c.Add 1, "TEXT"
    c.Add 1, "These"
    c.Add 1, "TIME"
    c.Add 1, "TIMEVALUE"
    c.Add 1, "TINV"
    c.Add 1, "TODAY"
    c.Add 1, "TRANSPOSE"
    c.Add 1, "TREND"
    c.Add 1, "TRIM"
    c.Add 1, "TRIMMEAN"
    c.Add 1, "TRUE"
    c.Add 1, "TRUNC"
    c.Add 1, "TTEST"
    c.Add 1, "TYPE"
    c.Add 1, "UPPER"
    c.Add 1, "VALUE"
    c.Add 1, "VAR"
    c.Add 1, "VARA"
    c.Add 1, "VARP"
    c.Add 1, "VARPA"
    c.Add 1, "VDB"
    c.Add 1, "VLOOKUP"
    c.Add 1, "WEEKDAY"
    c.Add 1, "WEEKNUM"
    c.Add 1, "WEIBULL"
    c.Add 1, "WORKDAY"
    c.Add 1, "XIRR"
    c.Add 1, "XNPV"
    c.Add 1, "YEAR"
    c.Add 1, "YEARFRAC"
    c.Add 1, "YIELD"
    c.Add 1, "YIELDDISC"
    c.Add 1, "YIELDMAT"
    c.Add 1, "ZTEST"
End Sub

Justinlabenne · Jun 26th, 2005, 11:59 AM

I just used your code Right-Click, and everything appears spot on.

WIth all the modifications to each others code I just grabbed the bottom one, it worked terrific, hopefully this is solved and I thank you for your help

I am gonna get some sleep, so I will test in depth later, looks good to go so far,

Thanks again to Joe Was and Right-Click,

Joe Was · Jun 26th, 2005, 10:50 PM

This little improvement checks to see if the Formula/Function results in an error, if it does it adds an error message to the other Cell Information in the MsgBox.

Option Explicit

Private c As New Collection, fStr As String, p As Range

Sub CheckFormulaType()
Dim f As Range, n As Name, myF$

If c.Count = 0 Then BuildCollection

For Each p In Cells.SpecialCells(xlCellTypeFormulas)
myF = ""
If Application.WorksheetFunction.IsError(p) = True Then myF = vbCr & "and has an Error!"

If IsUDF(p) Then
MsgBox "Cell: " & p.Address & vbCr & _
"Function: " & fStr & vbCr & vbCr & _
"Is a User Defined Function" & myF
Else

On Error Resume Next
Set n = Names(Replace(p.FormulaLocal, "=", ""))
On Error GoTo 0

If Not n Is Nothing Then
MsgBox "Cell: " & p.Address & vbCr & _
"Function: " & fStr & vbCr & vbCr & _
"Is a Workbook Name!"
Set n = Nothing
Else
MsgBox "Cell: " & p.Address & vbCr & _
"Function: " & fStr & vbCr & vbCr & _
"Is a Native Excel Function," & myF
End If
End If
Next p
End Sub

Function IsUDF(p As Range) As Boolean
Dim b As Long

If c.Count = 0 Then BuildCollection

fStr = Replace(p.FormulaLocal, "=", "")
If InStr(fStr, "(") = 0 Then Exit Function
fStr = Left(fStr, InStr(fStr, "(") - 1)
fStr = Trim(UCase(fStr))

On Error GoTo Err_
b = c(fStr)
Exit Function

Err_:
IsUDF = True
End Function

Sub BuildCollection()
c.Add 1, "ABS"
c.Add 1, "ACCRINT"
c.Add 1, "ACCRINTM"
c.Add 1, "ACOS"
c.Add 1, "ACOSH"
c.Add 1, "ADDRESS"
c.Add 1, "AMORDEGRC"
c.Add 1, "AMORLINC"
c.Add 1, "AND"
c.Add 1, "AREAS"
c.Add 1, "ASC"
c.Add 1, "ASIN"
c.Add 1, "ASINH"
c.Add 1, "ATAN"
c.Add 1, "ATAN2"
c.Add 1, "ATANH"
c.Add 1, "AVEDEV"
c.Add 1, "AVERAGE"
c.Add 1, "AVERAGEA"
c.Add 1, "BAHTTEXT"
c.Add 1, "BESSELI"
c.Add 1, "BESSELJ"
c.Add 1, "BESSELK"
c.Add 1, "BESSELY"
c.Add 1, "BETADIST"
c.Add 1, "BETAINV"
c.Add 1, "BIN2DEC"
c.Add 1, "BIN2HEX"
c.Add 1, "BIN2OCT"
c.Add 1, "BINOMDIST"
c.Add 1, "CEILING"
c.Add 1, "CELL"
c.Add 1, "CHAR"
c.Add 1, "CHIDIST"
c.Add 1, "CHIINV"
c.Add 1, "CHITEST"
c.Add 1, "CHOOSE"
c.Add 1, "CLEAN"
c.Add 1, "CODE"
c.Add 1, "COLUMN"
c.Add 1, "COLUMNS"
c.Add 1, "COMBIN"
c.Add 1, "COMPLEX"
c.Add 1, "CONCATENATE"
c.Add 1, "CONFIDENCE"
c.Add 1, "CONVERT"
c.Add 1, "CORREL"
c.Add 1, "COS"
c.Add 1, "COSH"
c.Add 1, "COUNT"
c.Add 1, "COUNTA"
c.Add 1, "COUNTBLANK"
c.Add 1, "COUNTIF"
c.Add 1, "COUPDAYBS"
c.Add 1, "COUPDAYS"
c.Add 1, "COUPDAYSNC"
c.Add 1, "COUPNCD"
c.Add 1, "COUPNUM"
c.Add 1, "COUPPCD"
c.Add 1, "COVAR"
c.Add 1, "CRITBINOM"
c.Add 1, "CUMIPMT"
c.Add 1, "CUMPRINC"
c.Add 1, "DATE"
c.Add 1, "DATEVALUE"
c.Add 1, "DAVERAGE"
c.Add 1, "DAY"
c.Add 1, "DAYS360"
c.Add 1, "DB"
c.Add 1, "DCOUNT"
c.Add 1, "DCOUNTA"
c.Add 1, "DDB"
c.Add 1, "DEC2BIN"
c.Add 1, "DEC2HEX"
c.Add 1, "DEC2OCT"
c.Add 1, "DEGREES"
c.Add 1, "DELTA"
c.Add 1, "DEVSQ"
c.Add 1, "DGET"
c.Add 1, "DISC"
c.Add 1, "DMAX"
c.Add 1, "DMIN"
c.Add 1, "DOLLAR"
c.Add 1, "DOLLARDE"
c.Add 1, "DOLLARFR"
c.Add 1, "DPRODUCT"
c.Add 1, "DSTDEV"
c.Add 1, "DSTDEVP"
c.Add 1, "DSUM"
c.Add 1, "DURATION"
c.Add 1, "DVAR"
c.Add 1, "DVARP"
c.Add 1, "EDATE"
c.Add 1, "EFFECT"
c.Add 1, "EOMONTH"
c.Add 1, "ERF"
c.Add 1, "ERFC"
c.Add 1, "ERROR.TYPE"
c.Add 1, "EUROCONVERT"
c.Add 1, "EVEN"
c.Add 1, "EXACT"
c.Add 1, "EXP"
c.Add 1, "EXPONDIST"
c.Add 1, "FACT"
c.Add 1, "FACTDOUBLE"
c.Add 1, "FALSE"
c.Add 1, "FDIST"
c.Add 1, "FIND"
c.Add 1, "FINV"
c.Add 1, "FISHER"
c.Add 1, "FISHERINV"
c.Add 1, "FIXED"
c.Add 1, "FLOOR"
c.Add 1, "FORECAST"
c.Add 1, "FREQUENCY"
c.Add 1, "FTEST"
c.Add 1, "FV"
c.Add 1, "FVSCHEDULE"
c.Add 1, "GAMMADIST"
c.Add 1, "GAMMAINV"
c.Add 1, "GAMMALN"
c.Add 1, "GCD"
c.Add 1, "GEOMEAN"
c.Add 1, "GESTEP"
c.Add 1, "GETPIVOTDATA"
c.Add 1, "GROWTH"
c.Add 1, "HARMEAN"
c.Add 1, "HEX2BIN"
c.Add 1, "HEX2DEC"
c.Add 1, "HEX2OCT"
c.Add 1, "HLOOKUP"
c.Add 1, "HOUR"
c.Add 1, "HYPERLINK"
c.Add 1, "HYPGEOMDIST"
c.Add 1, "IF"
c.Add 1, "IMABS"
c.Add 1, "IMAGINARY"
c.Add 1, "IMARGUMENT"
c.Add 1, "IMCONJUGATE"
c.Add 1, "IMCOS"
c.Add 1, "IMDIV"
c.Add 1, "IMEXP"
c.Add 1, "IMLN"
c.Add 1, "IMLOG10"
c.Add 1, "IMLOG2"
c.Add 1, "IMPOWER"
c.Add 1, "IMPRODUCT"
c.Add 1, "IMREAL"
c.Add 1, "IMSIN"
c.Add 1, "IMSQRT"
c.Add 1, "IMSUB"
c.Add 1, "IMSUM"
c.Add 1, "INDEX"
c.Add 1, "INDIRECT"
c.Add 1, "INFO"
c.Add 1, "INT"
c.Add 1, "INTERCEPT"
c.Add 1, "INTRATE"
c.Add 1, "IPMT"
c.Add 1, "IRR"
c.Add 1, "ISBLANK"
c.Add 1, "ISERR"
c.Add 1, "ISERROR"
c.Add 1, "ISEVEN"
c.Add 1, "ISLOGICAL"
c.Add 1, "ISNA"
c.Add 1, "ISNONTEXT"
c.Add 1, "ISNUMBER"
c.Add 1, "ISODD"
c.Add 1, "ISPMT"
c.Add 1, "ISREF"
c.Add 1, "ISTEXT"
c.Add 1, "JIS"
c.Add 1, "KURT"
c.Add 1, "LARGE"
c.Add 1, "LCM"
c.Add 1, "LEFT"
c.Add 1, "LEN"
c.Add 1, "LINEST"
c.Add 1, "LN"
c.Add 1, "LOG"
c.Add 1, "LOG10"
c.Add 1, "LOGEST"
c.Add 1, "LOGINV"
c.Add 1, "LOGNORMDIST"
c.Add 1, "LOOKUP"
c.Add 1, "LOWER"
c.Add 1, "MATCH"
c.Add 1, "MAX"
c.Add 1, "MAXA"
c.Add 1, "MDETERM"
c.Add 1, "MDURATION"
c.Add 1, "MEDIAN"
c.Add 1, "MID"
c.Add 1, "MIN"
c.Add 1, "MINA"
c.Add 1, "MINUTE"
c.Add 1, "MINVERSE"
c.Add 1, "MIRR"
c.Add 1, "MMULT"
c.Add 1, "MOD"
c.Add 1, "MODE"
c.Add 1, "MONTH"
c.Add 1, "MROUND"
c.Add 1, "MULTINOMIAL"
c.Add 1, "N"
c.Add 1, "NA"
c.Add 1, "NEGBINOMDIST"
c.Add 1, "NETWORKDAYS"
c.Add 1, "NOMINAL"
c.Add 1, "NORMDIST"
c.Add 1, "NORMINV"
c.Add 1, "NORMSDIST"
c.Add 1, "NORMSINV"
c.Add 1, "NOT"
c.Add 1, "NOW"
c.Add 1, "NPER"
c.Add 1, "NPV"
c.Add 1, "OCT2BIN"
c.Add 1, "OCT2DEC"
c.Add 1, "OCT2HEX"
c.Add 1, "ODD"
c.Add 1, "ODDFPRICE"
c.Add 1, "ODDFYIELD"
c.Add 1, "ODDLPRICE"
c.Add 1, "ODDLYIELD"
c.Add 1, "OFFSET"
c.Add 1, "OR"
c.Add 1, "PEARSON"
c.Add 1, "PERCENTILE"
c.Add 1, "PERCENTRANK"
c.Add 1, "PERMUT"
c.Add 1, "PHONETIC"
c.Add 1, "PI"
c.Add 1, "PMT"
c.Add 1, "POISSON"
c.Add 1, "POWER"
c.Add 1, "PPMT"
c.Add 1, "PRICE"
c.Add 1, "PRICEDISC"
c.Add 1, "PRICEMAT"
c.Add 1, "PROB"
c.Add 1, "PRODUCT"
c.Add 1, "PROPER"
c.Add 1, "PV"
c.Add 1, "QUARTILE"
c.Add 1, "QUOTIENT"
c.Add 1, "RADIANS"
c.Add 1, "RAND"
c.Add 1, "RANDBETWEEN"
c.Add 1, "RANK"
c.Add 1, "RATE"
c.Add 1, "RECEIVED"
c.Add 1, "REPLACE"
c.Add 1, "REPT"
c.Add 1, "RIGHT"
c.Add 1, "ROMAN"
c.Add 1, "ROUND"
c.Add 1, "ROUNDDOWN"
c.Add 1, "ROUNDUP"
c.Add 1, "ROW"
c.Add 1, "ROWS"
c.Add 1, "RSQ"
c.Add 1, "RTD"
c.Add 1, "SEARCH"
c.Add 1, "SECOND"
c.Add 1, "SERIESSUM"
c.Add 1, "SIGN"
c.Add 1, "SIN"
c.Add 1, "SINH"
c.Add 1, "SKEW"
c.Add 1, "SLN"
c.Add 1, "SLOPE"
c.Add 1, "SMALL"
c.Add 1, "SQL.REQUEST"
c.Add 1, "SQRT"
c.Add 1, "SQRTPI"
c.Add 1, "STANDARDIZE"
c.Add 1, "STDEV"
c.Add 1, "STDEVA"
c.Add 1, "STDEVP"
c.Add 1, "STDEVPA"
c.Add 1, "STEYX"
c.Add 1, "SUBSTITUTE"
c.Add 1, "SUBTOTAL"
c.Add 1, "SUM"
c.Add 1, "SUMIF"
c.Add 1, "SUMPRODUCT"
c.Add 1, "SUMSQ"
c.Add 1, "SUMX2MY2"
c.Add 1, "SUMX2PY2"
c.Add 1, "SUMXMY2"
c.Add 1, "SYD"
c.Add 1, "T"
c.Add 1, "TAN"
c.Add 1, "TANH"
c.Add 1, "TBILLEQ"
c.Add 1, "TBILLPRICE"
c.Add 1, "TBILLYIELD"
c.Add 1, "TDIST"
c.Add 1, "TEXT"
c.Add 1, "These"
c.Add 1, "TIME"
c.Add 1, "TIMEVALUE"
c.Add 1, "TINV"
c.Add 1, "TODAY"
c.Add 1, "TRANSPOSE"
c.Add 1, "TREND"
c.Add 1, "TRIM"
c.Add 1, "TRIMMEAN"
c.Add 1, "TRUE"
c.Add 1, "TRUNC"
c.Add 1, "TTEST"
c.Add 1, "TYPE"
c.Add 1, "UPPER"
c.Add 1, "VALUE"
c.Add 1, "VAR"
c.Add 1, "VARA"
c.Add 1, "VARP"
c.Add 1, "VARPA"
c.Add 1, "VDB"
c.Add 1, "VLOOKUP"
c.Add 1, "WEEKDAY"
c.Add 1, "WEEKNUM"
c.Add 1, "WEIBULL"
c.Add 1, "WORKDAY"
c.Add 1, "XIRR"
c.Add 1, "XNPV"
c.Add 1, "YEAR"
c.Add 1, "YEARFRAC"
c.Add 1, "YIELD"
c.Add 1, "YIELDDISC"
c.Add 1, "YIELDMAT"
c.Add 1, "ZTEST"
End Sub

Jun 25th, 2005, 07:41 AM	#1
Justinlabenne Join Date: Jun 2004 Location: Ohio Posts: 959	Distinguish between udf - Native Function Not sure if I am just not thinking straight, but is it possible to determine via code if a formula is "built-in" or if it is a udf? I understand the code below is invalid, and does not work, but I post it to help you identify the type of determination I am trying to make Code: Sub CheckFormulaType() Dim r As Range For Each r In Cells.SpecialCells(xlCellTypeFormulas) If Not r.Formula Is Application.WorksheetFunction Then r.Interior.ColorIndex = 3 End If Next r End Sub __________________ Justin Labenne www.jlxl.net

Jun 26th, 2005, 10:18 AM	#4
Joe Was MrExcel MVP Join Date: Feb 2002 Location: Central Florida, USA Posts: 7,529	Right-Click, using your code and adding some window dressing, see if you like your code better now? Private c As New Collection, fStr As String, p As Range Sub CheckFormulaType() Dim f As Range If c.Count = 0 Then For Each f In [a1:a334] c.Add f.Row, Trim(UCase(f)) Next f End If For Each p In Cells.SpecialCells(xlCellTypeFormulas) If IsUDF(p) Then MsgBox "Cell: " & p.Address & vbCr & _ "Function: " & fStr & vbCr & _ "Is a User Defined Function" Else MsgBox "Cell: " & p.Address & vbCr & _ "Function: " & fStr & vbCr & _ "Is a Native Excel Function!" End If Next p End Sub Function IsUDF(p As Range) As Boolean Dim b As Long fStr = Replace(p.FormulaLocal, "=", "") fStr = Left(fStr, InStr(fStr, "(") - 1) fStr = Trim(UCase(fStr)) On Error GoTo Err_ b = c(fStr) Exit Function Err_: IsUDF = True End Function __________________ JSW: Try and try again: "The way of the Coder!"

Jun 26th, 2005, 11:13 AM	#6
Justinlabenne Join Date: Jun 2004 Location: Ohio Posts: 959	Very nice, only issue I have is a simple formula like this: =D3/E3 causes an error or if the formula is returning an error value. I will have to see what I can modify to resolve, thanks for the start from both of you, couldn't get my head around this one so I appreciate you time and effort, Thanks again, __________________ Justin Labenne www.jlxl.net

Jun 26th, 2005, 11:25 AM	#7
Joe Was MrExcel MVP Join Date: Feb 2002 Location: Central Florida, USA Posts: 7,529	Easy fix: Private c As New Collection, fStr As String, p As Range Sub CheckFormulaType() Dim f As Range If c.Count = 0 Then For Each f In [a1:a334] c.Add f.Row, Trim(UCase(f)) Next f End If For Each p In Cells.SpecialCells(xlCellTypeFormulas) On Error GoTo myErr If IsUDF(p) Then MsgBox "Cell: " & p.Address & vbCr & _ "Function: " & fStr & vbCr & _ "Is a User Defined Function" Else MsgBox "Cell: " & p.Address & vbCr & _ "Function: " & fStr & vbCr & _ "Is a Native Excel Function!" End If GoTo myNext myErr: If p.HasFormula = True Then MsgBox "Cell: " & _ p.Address & vbCr & _ "Contains a formula!" myNext: Next p End Sub Function IsUDF(p As Range) As Boolean Dim b As Long fStr = Replace(p.FormulaLocal, "=", "") fStr = Left(fStr, InStr(fStr, "(") - 1) fStr = Trim(UCase(fStr)) On Error GoTo Err_ b = c(fStr) Exit Function Err_: IsUDF = True End Function __________________ JSW: Try and try again: "The way of the Coder!"

Jun 26th, 2005, 11:59 AM	#9
Justinlabenne Join Date: Jun 2004 Location: Ohio Posts: 959	I just used your code Right-Click, and everything appears spot on. WIth all the modifications to each others code I just grabbed the bottom one, it worked terrific, hopefully this is solved and I thank you for your help I am gonna get some sleep, so I will test in depth later, looks good to go so far, Thanks again to Joe Was and Right-Click, __________________ Justin Labenne www.jlxl.net

Jun 26th, 2005, 10:35 AM	#5
Tom Schreiner Join Date: Mar 2002 Posts: 6,205	Much Better! :o :x :-> :-> :->

Jun 26th, 2005, 10:50 PM	#10
Joe Was MrExcel MVP Join Date: Feb 2002 Location: Central Florida, USA Posts: 7,529	This little improvement checks to see if the Formula/Function results in an error, if it does it adds an error message to the other Cell Information in the MsgBox. Option Explicit Private c As New Collection, fStr As String, p As Range Sub CheckFormulaType() Dim f As Range, n As Name, myF$ If c.Count = 0 Then BuildCollection For Each p In Cells.SpecialCells(xlCellTypeFormulas) myF = "" If Application.WorksheetFunction.IsError(p) = True Then myF = vbCr & "and has an Error!" If IsUDF(p) Then MsgBox "Cell: " & p.Address & vbCr & _ "Function: " & fStr & vbCr & vbCr & _ "Is a User Defined Function" & myF Else On Error Resume Next Set n = Names(Replace(p.FormulaLocal, "=", "")) On Error GoTo 0 If Not n Is Nothing Then MsgBox "Cell: " & p.Address & vbCr & _ "Function: " & fStr & vbCr & vbCr & _ "Is a Workbook Name!" Set n = Nothing Else MsgBox "Cell: " & p.Address & vbCr & _ "Function: " & fStr & vbCr & vbCr & _ "Is a Native Excel Function," & myF End If End If Next p End Sub Function IsUDF(p As Range) As Boolean Dim b As Long If c.Count = 0 Then BuildCollection fStr = Replace(p.FormulaLocal, "=", "") If InStr(fStr, "(") = 0 Then Exit Function fStr = Left(fStr, InStr(fStr, "(") - 1) fStr = Trim(UCase(fStr)) On Error GoTo Err_ b = c(fStr) Exit Function Err_: IsUDF = True End Function Sub BuildCollection() c.Add 1, "ABS" c.Add 1, "ACCRINT" c.Add 1, "ACCRINTM" c.Add 1, "ACOS" c.Add 1, "ACOSH" c.Add 1, "ADDRESS" c.Add 1, "AMORDEGRC" c.Add 1, "AMORLINC" c.Add 1, "AND" c.Add 1, "AREAS" c.Add 1, "ASC" c.Add 1, "ASIN" c.Add 1, "ASINH" c.Add 1, "ATAN" c.Add 1, "ATAN2" c.Add 1, "ATANH" c.Add 1, "AVEDEV" c.Add 1, "AVERAGE" c.Add 1, "AVERAGEA" c.Add 1, "BAHTTEXT" c.Add 1, "BESSELI" c.Add 1, "BESSELJ" c.Add 1, "BESSELK" c.Add 1, "BESSELY" c.Add 1, "BETADIST" c.Add 1, "BETAINV" c.Add 1, "BIN2DEC" c.Add 1, "BIN2HEX" c.Add 1, "BIN2OCT" c.Add 1, "BINOMDIST" c.Add 1, "CEILING" c.Add 1, "CELL" c.Add 1, "CHAR" c.Add 1, "CHIDIST" c.Add 1, "CHIINV" c.Add 1, "CHITEST" c.Add 1, "CHOOSE" c.Add 1, "CLEAN" c.Add 1, "CODE" c.Add 1, "COLUMN" c.Add 1, "COLUMNS" c.Add 1, "COMBIN" c.Add 1, "COMPLEX" c.Add 1, "CONCATENATE" c.Add 1, "CONFIDENCE" c.Add 1, "CONVERT" c.Add 1, "CORREL" c.Add 1, "COS" c.Add 1, "COSH" c.Add 1, "COUNT" c.Add 1, "COUNTA" c.Add 1, "COUNTBLANK" c.Add 1, "COUNTIF" c.Add 1, "COUPDAYBS" c.Add 1, "COUPDAYS" c.Add 1, "COUPDAYSNC" c.Add 1, "COUPNCD" c.Add 1, "COUPNUM" c.Add 1, "COUPPCD" c.Add 1, "COVAR" c.Add 1, "CRITBINOM" c.Add 1, "CUMIPMT" c.Add 1, "CUMPRINC" c.Add 1, "DATE" c.Add 1, "DATEVALUE" c.Add 1, "DAVERAGE" c.Add 1, "DAY" c.Add 1, "DAYS360" c.Add 1, "DB" c.Add 1, "DCOUNT" c.Add 1, "DCOUNTA" c.Add 1, "DDB" c.Add 1, "DEC2BIN" c.Add 1, "DEC2HEX" c.Add 1, "DEC2OCT" c.Add 1, "DEGREES" c.Add 1, "DELTA" c.Add 1, "DEVSQ" c.Add 1, "DGET" c.Add 1, "DISC" c.Add 1, "DMAX" c.Add 1, "DMIN" c.Add 1, "DOLLAR" c.Add 1, "DOLLARDE" c.Add 1, "DOLLARFR" c.Add 1, "DPRODUCT" c.Add 1, "DSTDEV" c.Add 1, "DSTDEVP" c.Add 1, "DSUM" c.Add 1, "DURATION" c.Add 1, "DVAR" c.Add 1, "DVARP" c.Add 1, "EDATE" c.Add 1, "EFFECT" c.Add 1, "EOMONTH" c.Add 1, "ERF" c.Add 1, "ERFC" c.Add 1, "ERROR.TYPE" c.Add 1, "EUROCONVERT" c.Add 1, "EVEN" c.Add 1, "EXACT" c.Add 1, "EXP" c.Add 1, "EXPONDIST" c.Add 1, "FACT" c.Add 1, "FACTDOUBLE" c.Add 1, "FALSE" c.Add 1, "FDIST" c.Add 1, "FIND" c.Add 1, "FINV" c.Add 1, "FISHER" c.Add 1, "FISHERINV" c.Add 1, "FIXED" c.Add 1, "FLOOR" c.Add 1, "FORECAST" c.Add 1, "FREQUENCY" c.Add 1, "FTEST" c.Add 1, "FV" c.Add 1, "FVSCHEDULE" c.Add 1, "GAMMADIST" c.Add 1, "GAMMAINV" c.Add 1, "GAMMALN" c.Add 1, "GCD" c.Add 1, "GEOMEAN" c.Add 1, "GESTEP" c.Add 1, "GETPIVOTDATA" c.Add 1, "GROWTH" c.Add 1, "HARMEAN" c.Add 1, "HEX2BIN" c.Add 1, "HEX2DEC" c.Add 1, "HEX2OCT" c.Add 1, "HLOOKUP" c.Add 1, "HOUR" c.Add 1, "HYPERLINK" c.Add 1, "HYPGEOMDIST" c.Add 1, "IF" c.Add 1, "IMABS" c.Add 1, "IMAGINARY" c.Add 1, "IMARGUMENT" c.Add 1, "IMCONJUGATE" c.Add 1, "IMCOS" c.Add 1, "IMDIV" c.Add 1, "IMEXP" c.Add 1, "IMLN" c.Add 1, "IMLOG10" c.Add 1, "IMLOG2" c.Add 1, "IMPOWER" c.Add 1, "IMPRODUCT" c.Add 1, "IMREAL" c.Add 1, "IMSIN" c.Add 1, "IMSQRT" c.Add 1, "IMSUB" c.Add 1, "IMSUM" c.Add 1, "INDEX" c.Add 1, "INDIRECT" c.Add 1, "INFO" c.Add 1, "INT" c.Add 1, "INTERCEPT" c.Add 1, "INTRATE" c.Add 1, "IPMT" c.Add 1, "IRR" c.Add 1, "ISBLANK" c.Add 1, "ISERR" c.Add 1, "ISERROR" c.Add 1, "ISEVEN" c.Add 1, "ISLOGICAL" c.Add 1, "ISNA" c.Add 1, "ISNONTEXT" c.Add 1, "ISNUMBER" c.Add 1, "ISODD" c.Add 1, "ISPMT" c.Add 1, "ISREF" c.Add 1, "ISTEXT" c.Add 1, "JIS" c.Add 1, "KURT" c.Add 1, "LARGE" c.Add 1, "LCM" c.Add 1, "LEFT" c.Add 1, "LEN" c.Add 1, "LINEST" c.Add 1, "LN" c.Add 1, "LOG" c.Add 1, "LOG10" c.Add 1, "LOGEST" c.Add 1, "LOGINV" c.Add 1, "LOGNORMDIST" c.Add 1, "LOOKUP" c.Add 1, "LOWER" c.Add 1, "MATCH" c.Add 1, "MAX" c.Add 1, "MAXA" c.Add 1, "MDETERM" c.Add 1, "MDURATION" c.Add 1, "MEDIAN" c.Add 1, "MID" c.Add 1, "MIN" c.Add 1, "MINA" c.Add 1, "MINUTE" c.Add 1, "MINVERSE" c.Add 1, "MIRR" c.Add 1, "MMULT" c.Add 1, "MOD" c.Add 1, "MODE" c.Add 1, "MONTH" c.Add 1, "MROUND" c.Add 1, "MULTINOMIAL" c.Add 1, "N" c.Add 1, "NA" c.Add 1, "NEGBINOMDIST" c.Add 1, "NETWORKDAYS" c.Add 1, "NOMINAL" c.Add 1, "NORMDIST" c.Add 1, "NORMINV" c.Add 1, "NORMSDIST" c.Add 1, "NORMSINV" c.Add 1, "NOT" c.Add 1, "NOW" c.Add 1, "NPER" c.Add 1, "NPV" c.Add 1, "OCT2BIN" c.Add 1, "OCT2DEC" c.Add 1, "OCT2HEX" c.Add 1, "ODD" c.Add 1, "ODDFPRICE" c.Add 1, "ODDFYIELD" c.Add 1, "ODDLPRICE" c.Add 1, "ODDLYIELD" c.Add 1, "OFFSET" c.Add 1, "OR" c.Add 1, "PEARSON" c.Add 1, "PERCENTILE" c.Add 1, "PERCENTRANK" c.Add 1, "PERMUT" c.Add 1, "PHONETIC" c.Add 1, "PI" c.Add 1, "PMT" c.Add 1, "POISSON" c.Add 1, "POWER" c.Add 1, "PPMT" c.Add 1, "PRICE" c.Add 1, "PRICEDISC" c.Add 1, "PRICEMAT" c.Add 1, "PROB" c.Add 1, "PRODUCT" c.Add 1, "PROPER" c.Add 1, "PV" c.Add 1, "QUARTILE" c.Add 1, "QUOTIENT" c.Add 1, "RADIANS" c.Add 1, "RAND" c.Add 1, "RANDBETWEEN" c.Add 1, "RANK" c.Add 1, "RATE" c.Add 1, "RECEIVED" c.Add 1, "REPLACE" c.Add 1, "REPT" c.Add 1, "RIGHT" c.Add 1, "ROMAN" c.Add 1, "ROUND" c.Add 1, "ROUNDDOWN" c.Add 1, "ROUNDUP" c.Add 1, "ROW" c.Add 1, "ROWS" c.Add 1, "RSQ" c.Add 1, "RTD" c.Add 1, "SEARCH" c.Add 1, "SECOND" c.Add 1, "SERIESSUM" c.Add 1, "SIGN" c.Add 1, "SIN" c.Add 1, "SINH" c.Add 1, "SKEW" c.Add 1, "SLN" c.Add 1, "SLOPE" c.Add 1, "SMALL" c.Add 1, "SQL.REQUEST" c.Add 1, "SQRT" c.Add 1, "SQRTPI" c.Add 1, "STANDARDIZE" c.Add 1, "STDEV" c.Add 1, "STDEVA" c.Add 1, "STDEVP" c.Add 1, "STDEVPA" c.Add 1, "STEYX" c.Add 1, "SUBSTITUTE" c.Add 1, "SUBTOTAL" c.Add 1, "SUM" c.Add 1, "SUMIF" c.Add 1, "SUMPRODUCT" c.Add 1, "SUMSQ" c.Add 1, "SUMX2MY2" c.Add 1, "SUMX2PY2" c.Add 1, "SUMXMY2" c.Add 1, "SYD" c.Add 1, "T" c.Add 1, "TAN" c.Add 1, "TANH" c.Add 1, "TBILLEQ" c.Add 1, "TBILLPRICE" c.Add 1, "TBILLYIELD" c.Add 1, "TDIST" c.Add 1, "TEXT" c.Add 1, "These" c.Add 1, "TIME" c.Add 1, "TIMEVALUE" c.Add 1, "TINV" c.Add 1, "TODAY" c.Add 1, "TRANSPOSE" c.Add 1, "TREND" c.Add 1, "TRIM" c.Add 1, "TRIMMEAN" c.Add 1, "TRUE" c.Add 1, "TRUNC" c.Add 1, "TTEST" c.Add 1, "TYPE" c.Add 1, "UPPER" c.Add 1, "VALUE" c.Add 1, "VAR" c.Add 1, "VARA" c.Add 1, "VARP" c.Add 1, "VARPA" c.Add 1, "VDB" c.Add 1, "VLOOKUP" c.Add 1, "WEEKDAY" c.Add 1, "WEEKNUM" c.Add 1, "WEIBULL" c.Add 1, "WORKDAY" c.Add 1, "XIRR" c.Add 1, "XNPV" c.Add 1, "YEAR" c.Add 1, "YEARFRAC" c.Add 1, "YIELD" c.Add 1, "YIELDDISC" c.Add 1, "YIELDMAT" c.Add 1, "ZTEST" End Sub __________________ JSW: Try and try again: "The way of the Coder!"

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode