Running Worksheet Function in VBA

[thereuare]

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.

 

[Swamp Thing]

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

However:<pre>
Function MyQuotient(Num, DivBy)
MyQuotient = Int(Num / DivBy)
End Function</pre>
This message was edited by Swamp Thing on 2002-08-24 20:14

 

[JayMenon]

Hi !

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

Thanks

 

[thereuare]

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.<worksheetfunction u want to use goes here>

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.

 

[Swamp Thing]

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).

 

[thereuare]

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.

 

[Swamp Thing]

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

 

[Ivan F Moala]

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.

 

[thereuare]

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.

 

[Ivan F Moala]

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;

<pre/>

Variable = QUOTIENT(x, y)

</pre>