Results 1 to 3 of 3

calling function in excel vba

This is a discussion on calling function in excel vba within the Excel Questions forums, part of the Question Forums category; Hi I noticed that i can not right sum() function alone like left(). what i mean by that, i have ...

  1. #1
    Board Regular
    Join Date
    Mar 2016
    Posts
    330

    Default calling function in excel vba

    Hi
    I noticed that i can not right sum() function alone like left(). what i mean by that, i have to use this syntax to call sun() function

    x = Excel.WorksheetFunction.Sum(Range("a11:a20"))

    however I can use left() function without using Excel.worksheetfucntion like the following

    Cells(20, 3) = Left(Range("a1"), 3)

    I tried to use sum() like the following

    x = Sum(Range("a11:a20"))

    but I got compile error msg - sub or function is not defined. Thanks

  2. #2
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    2,709

    Default Re: calling function in excel vba

    Some functions, like LEFT() are both Excel functions and VBA functions.

    Others, like Excel's SUM(), have no VBA equivalent, but can be called from VBA using WorksheetFunction.Sum or Application.Sum

    If you look at the Members of the WorksheetFunction Object, you'll see that many, but not all, Excel functions can be called from VBA.
    For example, you can't call Excel's MAX or MIN functions.

    Also, be careful, there are differences in the way some functions behave between Excel and VBA. Even though VBA has a Round function, I will often use Application.Round so that my results round the same way that Excel does, rather than using VBA's "Banker's rounding".

  3. #3
    Board Regular
    Join Date
    Sep 2009
    Location
    CT, USA
    Posts
    303

    Default Re: calling function in excel vba

    You have to use the WorksheetFunction or Application for functions that are not native to VBA. The SUM function is not native to VBA, therefore you have to tell VBA that that procedure is found in the application.

    Here's a good description of Worksheet functions.
    The WorksheetFunction Method – Daily Dose of Excel
    Good Luck!
    Bill

    Low intermediate VBA user - I know just enough to be dangerous. Trying to learn by doing.

    Use http://tableizer.journalistopia.com/ to show your worksheets in this forum.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com