Worksheet functions in VBA macros using VBA in Microsoft Excel

by  About
       

Almost all of the built-in worksheet functions can be used in macros.
If you have a non-English version of Excel you have to remember to use the English function names,
and that the separator sign between the function arguments always is a comma (,).
If you don’t know the English function name, record a macro while you enter the function the usual way in a cell,
in the registered macro the function name will be translated to English.
When you are going to use a worksheetfunction in a macro you also have to include the Application-object.

In Excel97 you use the object Application.WorksheetFunction. Examples:

MyTotal = Application.Sum(Range("A1:A100"))
' returns the total of the values in A1:A100 in the active worksheet.

MyTotal = Application.WorksheetFunction.Sum(Range("A1:A100"))
' the same formula for Excel 97.


9 thoughts on “Worksheet functions in VBA macros using VBA in Microsoft Excel

  1. I´ve tried to use some date functions, such as networkdays, date and year, on the vba enviroment but i had no success, do you have any tip for this kind of functions.

  2. “If you want to use functions from the Analysis Tool Pack addin in your own macros:
    - Open the VBE (Alt+F11).
    - Activate the project where you want to use the function(s).
    - Select Tools, References… and check the option atpvbaen.xls.
    - click the OK-button to close the References-dialog.

    The macros in the workbook where you added the reference to the atpvbaen.xls library can now use the functions like this:

    workdaycount = networkdays(Date, Date + 14)

    Or like this to avoid conflict with other user defined functions with the same name:

    workdaycount = [atpvbaen.xls].networkdays(Date, Date + 14)

    It is not necessary to install the Analysis Tool Pack addin from the menu Tools, Add-Ins…

    The DATE() and YEAR() worksheet functions are not necessary in VBA where you use the built-in functions Date and Year:

    Today = Date
    Tomorrow = Date + 1
    CurrentYear = Year(Date)
    NextYear = Year(Date) + 1 “

  3. “when I use worksheet functions, arguments in functions are separeted by semicolon otherwise they dont work . for instance if I separate them with comma.
    i.e.
    OFFSET(Sheet1!$E$8,COUNT(Sheet1!$E:$E)-1,0) dosent work.
    OFFSET(Sheet1!$E$8;COUNT(Sheet1!$E:$E)-1;0) works”

  4. “I think this is an OS setting – could be Excel in a version I am not familar with though.

    What OS and version of Excel are you using? “

  5. I´ve tried to use some date functions, such as networkdays, date and year, on the vba enviroment but i had no success, do you have any tip for this kind of functions.

  6. “If you want to use functions from the Analysis Tool Pack addin in your own macros:
    - Open the VBE (Alt+F11).
    - Activate the project where you want to use the function(s).
    - Select Tools, References… and check the option atpvbaen.xls.
    - click the OK-button to close the References-dialog.

    The macros in the workbook where you added the reference to the atpvbaen.xls library can now use the functions like this:

    workdaycount = networkdays(Date, Date + 14)

    Or like this to avoid conflict with other user defined functions with the same name:

    workdaycount = [atpvbaen.xls].networkdays(Date, Date + 14)

    It is not necessary to install the Analysis Tool Pack addin from the menu Tools, Add-Ins…

    The DATE() and YEAR() worksheet functions are not necessary in VBA where you use the built-in functions Date and Year:

    Today = Date
    Tomorrow = Date + 1
    CurrentYear = Year(Date)
    NextYear = Year(Date) + 1 “

  7. “when I use worksheet functions, arguments in functions are separeted by semicolon otherwise they dont work . for instance if I separate them with comma.
    i.e.
    OFFSET(Sheet1!$E$8,COUNT(Sheet1!$E:$E)-1,0) dosent work.
    OFFSET(Sheet1!$E$8;COUNT(Sheet1!$E:$E)-1;0) works”

  8. “I think this is an OS setting – could be Excel in a version I am not familar with though.

    What OS and version of Excel are you using? “

  9. Hello, My name is Juan Macedo, I have some problems with Spanish Excel functions, that could be interesting for You:
    I found these in Office different versions (last in Office 2013)
    The Excel Address function is translated to DIRECCION, the Excel Offset function is translated to DESREF, well in Excell worksheet is possible to use the spanish function, but in VBA MACROS it is impossible to use any of both (English or Spanish versions), for some of the functions it is possible to use the english version when the spanish function is not available, but at least the two mentioned do not work.

    Thanks

    Juan Macedo
    [email protected]

Leave a Reply

Your email address will not be published. Required fields are marked *


8 + = sixteen

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>