Running Worksheet Function in VBA

thereuare

Board Regular
Joined
May 28, 2002
Messages
232
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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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
 
Upvote 0
Hi !

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

Thanks
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top