|

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.

Similar Posts