Should I use a VBA Macro Function or Call a Worksheet Function?
If the function you want to use exists in Excel VBA, use that. It will be faster and cleaner, and work in all Microsoft Office programs, not only in Excel. Here is a full list of Excel VBA Functions. If the function you want is not there, but exists in the Worksheet, you may be able to call a Worksheet function
How can you tell what functions are supported by VBA and what by Worksheet Functions?
In the Visual Basic Editor (Alt+F11), Press F2 to display the Object Browser
In the top drop-down, Select VBA. You will see all the functions available in VBA.
For Worksheet Functions, select Excel. Within Excel, select WorksheetFunction in the left pane to display Worksheet Functions.
But you do need some caution.
In an earlier post, we mentioned that functions in VBA can (and do) differ from those in the spreadsheet. There are also functions with the same names, yet behave differently or have different arguments.
This post lists some Excel Worksheet Functions not found in VBA. You can call these (and many others) using Application.WorksheetFunction.function() as shown in that earlier post. Some Worksheet Functions you can’t call from VBA, but there are alternatives.
Then, there are a few functions in VBA that do not have parallels in the Excel Worksheet.
To make it easier to tell which is which, in the lists below, Excel Worksheet Functions appear in UPPERCASE (as in the Worksheet). VBA Functions appear in Mixed Case (as they do in code).
Some Callable Excel Worksheet Functions Not Found in VBA
This is not an exhaustive list. There are many more functions in the Excel worksheet (over 450) than there are in Excel VBA (a bit over 100).
PROPER(text)
Database functions: DAVERAGE, DCOUNT
DATEDIF Calculates the number of days, months, or years between two dates
DAYS Returns the number of days between two dates (not needed: Just subtract the earlier date from the later one)
EDATE Returns the serial number of the date that is the indicated number of months before or after the start date
EOMONTH Returns the serial number of the last day of the month before or after a specified number of months
NETWORKDAYS Returns the number of whole workdays between two dates
WEEKDAY Converts a date serial number to a number representing the day of the week
WEEKNUM Converts a serial number to the number of the week in the year
WORKDAY Returns the serial number of the date before or after a specified number of workdays
COMPLEX(real_num, i_num) Converts real and imaginary coefficients into a complex number
IM* functions for working with complex numbers (IMABS, IMAGINARY, etc.).
CONVERT(number, from_unit, to_unit) Converts a number from one measurement system to another
PMT Returns the periodic payment for an annuity.
CELL Returns information about the formatting, location, or contents of a cell.
CHOOSE Chooses a value from a list of values
INDEX Uses an index to choose a value from a reference or array
MATCH Looks up values in a reference or array
INDIRECT Returns a reference indicated by a text value
LOOKUP, VLOOKUP, HLOOKUP Look up values in a vector or array
ASIN, ASINH, SINH etc. (For formulas to calculate these in VBA without WorksheetFunction see Derived Math Functions in VBA Help)
CEILING(number, multiple) Rounds a number up (larger) to the nearest multiple (down if both number and multiple are negative).
FLOOR(number, multiple) Rounds a number down (smaller) to the nearest multiple (up if both number and multiple are negative).
EVEN Rounds a number up to the nearest even integer
ODD Rounds a number up to the nearest odd integer
EXP Returns e raised to the power of a given number
FACT Returns the factorial of a number
GCD Returns the greatest common denominator
LCM Returns the lowest common multiple
DEGREES Converts radians to degrees
RADIANS Converts degrees to radians
ROUNDDOWN Rounds a number toward zero
ROUNDUP Rounds a number away from zero
SUMIF, SUMIFS, COUNTIF, COUNTIFS, etc.
The IS-FUNCTIONS (ISERROR, ISLOGICAL, ISNA, etc.) for checking the type of entry in a cell have a parallel in VBA TypeName(ActiveCell.Value). But the latter just gives “Error” for #NAME?, #REF!, #NUM!, etc. whereas the Worksheet functions are more specific.
Non-Callable Excel Worksheet Functions Not Found in VBA
These worksheet functions do not work with Application.WorksheetFunction.function(). We give some alternatives (this is not an exhaustive list either).
INDIRECT() (address a cell indirectly) use Range, Cells, Offset, or any other way of referencing cells.
N() (force numeric) use Val(string$)
T() (return text, or blank if not text) No equivalent.
NA() use ActiveCell.Value = “#N/A” to insert #N/A into the cell.
Some Excel VBA Functions Not Found in the Worksheet:
Randomize to reset the random number counter.
InStr() to find a string within another (FIND() and SEARCH() are similar).
InStrRev() find a string within a reversed string.
Chr()
Space$(spaces) has no direct equivalent, but you can use =REPT(“ “, num)
FormatDateTime$ and FormatNumber$ (along with Format$) are subsumed in TEXT().
Excel Functions that Differ Between the Worksheet and VBA:
FIND(find_text, within_text, [start_num]) Finds one text value within another (case-sensitive)
SEARCH(find_text,within_text,[start_num]) Finds one text value within another (not case-sensitive)
In VBA, use InStr([start], string1, string2, [compare]) where compare = vbBinaryCompare (0) case-sensitive or vbTextCompare (1) case-insensitive.
MID(text, start, num) and Mid$(text, start [, num])
REPLACE(old_text, start_num, num_chars, new_text) replaces a given number of characters at a specified position in a text string, with a different text string, once. In contrast, VBA Replace$(old_text, find, replace, [start, [count, [compare]]]) returns a string in which all occurrences (or a specified number) of a specified substring in old_text are replaced with another string. The analogue for this in the Worksheet is SUBSTITUTE(text, old_text, new_text, [instance_num])
LOWER() is equivalent to VBA function LCase
UPPER() is equivalent to VBA function UCase
VALUE() is equivalent to VBA function Val
TEXT() behaves like Format$()
TODAY() is equivalent to VBA function Date (Returns the serial number of today’s date)
DATE(year, month, day) is equivalent to VBA DateSerial(year, month, day)
EXACT() is equivalent to VBA function StrComp(String1, String2, [compare])
ISBLANK() is equivalent to VBA function IsEmpty(cell)
RAND() is equivalent to VBA function Rnd (random number).
SIGN() is equivalent to VBA function Sgn (Sign of the argument).
SQRT() is equivalent to VBA function Sqr (Square Root).
TRUNC() (truncate a number) is equivalent to VBA function Fix.
TYPE() (type of cell contents) is similar to VBA function TypeName (type of a variable).
ADDRESS(row_num, column_num) –use ActiveCell.Address
COLUMN(cell) (Returns the column number of a cell) –use ActiveCell.Column
ROW(cell) (Returns the row number of a cell) –use ActiveCell.Row.
FORMULATEXT(cell) (Returns the formula at the given reference as text) –use ActiveCell.Formula
OFFSET(cell, rows, cols) (Returns a cell offset from a given cell) –use ActiveCell.Offset(rows, cols).Select etc.
ROUND(number, num_digits) occurs in the Worksheet and as a VBA function. It rounds a number to a specified number of digits. Note that num_digits can be negative in the spreadsheet function, but not in VBA.
Notes
The Microsoft Excel worksheet function and the Visual Basic equivalent are not always calculated the same way! They may give different results, even when they have the same name.
What if Application.WorksheetFunction.function() does not work for you? There are two alternative methods for using worksheet functions in Visual Basic. They are the ExecuteExcel4Macro and Evaluate functions. Here is an example calling the ATAN worksheet function in Excel from Visual Basic:
x = Application.ExecuteExcel4Macro(“ATAN(12)”)
x = Application.Evaluate(“ATAN(12)”)
These notes are from Not all Excel worksheet functions are supported as methods of the Application object in Visual Basic for Applications.