Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Running Worksheet Function in VBA

This is a discussion on Running Worksheet Function in VBA within the Excel Questions forums, part of the Question Forums category; Is this a valid line of code? DivisionInteger = Application.WorksheetFunction.Quotient(CheckValue, BoxSize) My program creates an error, with the variable DivisionInteger ...

  1. #1
    Board Regular
    Join Date
    May 2002
    Posts
    232

    Default

    Is this a valid line of code?

    DivisionInteger = Application.WorksheetFunction.Quotient(CheckValue, BoxSize)

    My program creates an error, with the variable DivisionInteger being empty, and the variables CheckValue and BoxSize having values (these two variables should be enough to create a value for DivisionInteger)

    When i just try the Quotient Function in a cell with 2 numbers, it works fine.

    Thanks.

  2. #2
    Board Regular
    Join Date
    Aug 2002
    Posts
    305

    Default

    Excel-VBA help contains a "list of worksheet functions available to visual basic". Quotient is not one of them.

    However:

    Function MyQuotient(Num, DivBy)
    MyQuotient = Int(Num / DivBy)
    End Function


    [ This Message was edited by: Swamp Thing on 2002-08-24 20:14 ]

  3. #3
    New Member
    Join Date
    Aug 2002
    Location
    India
    Posts
    19

    Default

    Hi !

    If either Numerator or Denominator argument is nonnumeric, QUOTIENT returns the #VALUE! error value. Evaluate if there is an error their !!

    Thanks

  4. #4
    Board Regular
    Join Date
    May 2002
    Posts
    232

    Default

    Thanks to you both:

    1) I thought i could use worksheet functions (that don't have their own VBA equivalent) if you specify in your code Application.WorksheetFunction.

    2) I don't have an error with a negative number as both numbers are positive. The error i get is #NAME.

    I think i found a workaround by using the INT function, but i'd still like to know if i CAN call worksheet functions that aren't defined in VBA by using the code above.

  5. #5
    Board Regular
    Join Date
    Aug 2002
    Posts
    305

    Default

    Well, you can use some of the worksheet functions in VBA. As I mentioned, VBA-excel help contains a list of the functions that you are allowed to use in VB. Also, if you type "WorksheetFunction." then intellisense will show you the list. (Quotient is not in it, of course).

  6. #6
    Board Regular
    Join Date
    May 2002
    Posts
    232

    Default

    Thanks for the explaination, i thought all functions in a worksheet became available, not just some of them.

    I've been learning from Walker's "Dummies" book (which is EXCELLENT), but it states:

    "Although VBA offers a decent assortment of built-in functions, you might not always find exactly what you need. Fortunately, you can also use Excel's worksheet functions in your VBA procedures. The only worksheet functions that you cannot use are those that have an equivalent VBA function."

    So is the above incorrect, or am i interpretting it wrong?

    Thanks again.

  7. #7
    Board Regular
    Join Date
    Aug 2002
    Posts
    305

    Default

    It turns out that Quotient comes from the Analysis Tool-Pak add-in, so it's not an intrinsic worksheet function anyway.
    So old Walker did get it right, and the list in VBA Help is also correct..

    [ This Message was edited by: Swamp Thing on 2002-08-24 21:22 ]

  8. #8
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209

    Default

    On 2002-08-24 20:57, thereuare wrote:
    Thanks for the explaination, i thought all functions in a worksheet became available, not just some of them.

    I've been learning from Walker's "Dummies" book (which is EXCELLENT), but it states:

    "Although VBA offers a decent assortment of built-in functions, you might not always find exactly what you need. Fortunately, you can also use Excel's worksheet functions in your VBA procedures. The only worksheet functions that you cannot use are those that have an equivalent VBA function."

    So is the above incorrect, or am i interpretting it wrong?

    Thanks again.
    Quotient is available via the Analysis Toolpak Addin (VBA), you just need to referenc it in you project.


    Kind Regards,
    Ivan F Moala From the City of Sails

  9. #9
    Board Regular
    Join Date
    May 2002
    Posts
    232

    Default

    I did load the add in, but do i have to reference the add-in as well?

    If so, how does one reference the add-in?

    Thanks.

  10. #10
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209

    Default

    On 2002-08-24 21:29, thereuare wrote:
    I did load the add in, but do i have to reference the add-in as well?

    If so, how does one reference the add-in?

    Thanks.
    Yes, you have to reference it in your Project
    NB: You must load in the VBA version

    By code thats;

    AddIns("Analysis ToolPak - VBA").Installed = True

    Or manually - I'm sure you know the steps..just select the VBA version.

    Your code then should look something like;



    Variable = QUOTIENT(x, y)


    Kind Regards,
    Ivan F Moala From the City of Sails

Page 1 of 2 12 LastLast

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