Any way to use "standard" Excel functions inside VBA functions

I am new to this group, so don't mind if I ask somthing that is too easy for 
you. My question is whether
I may use all "standard" Excel 2007 functions. I would like to find interval 
in which some function returns
"#NUM!" although it must not, because all parameters are inside "permited 
ranges". My idea is to start
with some interval [A, B] for which A is "acceptable" argument and B is 
"unacceptable" argument, and,
by halving it (C=A+(B-A)/2.0) and asking : IF C is "unacceptable" argument 
THEN B=C ELSE A=C
WHILE (B-A>(A+B)/2^32) and my User Defined Function returns A (or B). But 
when I use somthing
like (inside my User Defined Function):

Dim X as Double ' and some more variables, not necessary for explanation

and, after that declaration, some assignments ... and :

X=Application.WorksheetFunction.IFERROR(Application.WorksheetFunction.<_someFunc>(<parameters>); 
 -8.0)

(because of my "Local settings" I use semicolumn as arguments' delimiter) in 
order to assign to X value of that function
(it is between 0.0 and 1.0 for all values of parameters), or -8.0 if the 
function returns "#NUM!", after that I would "ask"
whether X is less than 0.0 (this means that Excel function returns "#NUM!"), 
and so on,...

But when I "walk through" my User Defined Function (F8, F8, ... F8) and see 
that values of some other variables are
being changed as I expect, when the flow goes to the "sentence" a few rows 
above program "crashes" as if the it exited
my UDF.

Please help !!!
Thanks !!! 

0
Nenad
5/6/2010 5:31:50 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
810 Views

Similar Articles

[PageSpeed] 50

Some worksheet functions can be called with application.worksheetfunction....
Some can't.

But you can check for errors with:

if iserror(...) then
directly in VBA.  No need to use application.worksheetfunction.iserror().

You may want to share the code for the UDF to get more helpful answers.

Nenad Tosic wrote:
> 
> I am new to this group, so don't mind if I ask somthing that is too easy for
> you. My question is whether
> I may use all "standard" Excel 2007 functions. I would like to find interval
> in which some function returns
> "#NUM!" although it must not, because all parameters are inside "permited
> ranges". My idea is to start
> with some interval [A, B] for which A is "acceptable" argument and B is
> "unacceptable" argument, and,
> by halving it (C=A+(B-A)/2.0) and asking : IF C is "unacceptable" argument
> THEN B=C ELSE A=C
> WHILE (B-A>(A+B)/2^32) and my User Defined Function returns A (or B). But
> when I use somthing
> like (inside my User Defined Function):
> 
> Dim X as Double ' and some more variables, not necessary for explanation
> 
> and, after that declaration, some assignments ... and :
> 
> X=Application.WorksheetFunction.IFERROR(Application.WorksheetFunction.<_someFunc>(<parameters>);
>  -8.0)
> 
> (because of my "Local settings" I use semicolumn as arguments' delimiter) in
> order to assign to X value of that function
> (it is between 0.0 and 1.0 for all values of parameters), or -8.0 if the
> function returns "#NUM!", after that I would "ask"
> whether X is less than 0.0 (this means that Excel function returns "#NUM!"),
> and so on,...
> 
> But when I "walk through" my User Defined Function (F8, F8, ... F8) and see
> that values of some other variables are
> being changed as I expect, when the flow goes to the "sentence" a few rows
> above program "crashes" as if the it exited
> my UDF.
> 
> Please help !!!
> Thanks !!!

-- 

Dave Peterson
0
Dave
5/6/2010 8:01:21 PM
You can call worksheet functions from VBA code (except for those
functions, such as Month, that have a native VBA equivalent). There
are two ways to do this. First is to go through
Application.WorksheetFunction. For example,

Dim D As Double
On Error Resume Next
Err.Clear
D = Application.WorksheetFunction.VLookup( _
    "a", Range("A1:B5"), 2, False)
If Err.Number <> 0 Then
    Debug.Print "error"
Else
    Debug.Print D
End If

In this code, the Err.Number value will be a value other than 0 if the
VLookup function fails. You need to test Err.Number to see if the
function call succeeded. 

The other way is to omit the WorksheetFunction qualifier and go
directly through Application.

Dim D As Variant
D = Application.VLookup( _
    "a", Range("A1:B5"), 2, False)
If IsError(D) = True Then
    Debug.Print "error"
Else
    Debug.Print D
End If

Here, if VLookup fails, no Error is raised but the function returns an
Error typed Variant. The IsError functions tests the variable to
determine whether it contains an error. In this method, the result
variable  (D in this example) must be declared as a Variant. If it is
not a Variant, you'll get an error 13, Type Mismatch, because VBA
attempts to assign an Error to a variable type other than Variant, and
such assignment is not allowed.

If you need to determine the exact type of error (e.g., DIV/0, NAME,
etc), you can use CVErr to create an Error type variant and test the
error value against the result of CVErr. E.g., 

Dim V As Variant
' code to set value of V goes here
If IsError(V) Then
    If V = CVErr(xlErrValue) Then
        Debug.Print "#value"
    ElseIf V = CVErr(xlErrNA) Then
        Debug.Print "#n/a"
    ElseIf V = CVErr(xlErrName) Then
        Debug.Print "#name"
    ' and so on
    End If
End If

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]







On Thu, 6 May 2010 19:31:50 +0200, "Nenad Tosic"
<tosicnenad@ikomline.net> wrote:

>I am new to this group, so don't mind if I ask somthing that is too easy for 
>you. My question is whether
>I may use all "standard" Excel 2007 functions. I would like to find interval 
>in which some function returns
>"#NUM!" although it must not, because all parameters are inside "permited 
>ranges". My idea is to start
>with some interval [A, B] for which A is "acceptable" argument and B is 
>"unacceptable" argument, and,
>by halving it (C=A+(B-A)/2.0) and asking : IF C is "unacceptable" argument 
>THEN B=C ELSE A=C
>WHILE (B-A>(A+B)/2^32) and my User Defined Function returns A (or B). But 
>when I use somthing
>like (inside my User Defined Function):
>
>Dim X as Double ' and some more variables, not necessary for explanation
>
>and, after that declaration, some assignments ... and :
>
>X=Application.WorksheetFunction.IFERROR(Application.WorksheetFunction.<_someFunc>(<parameters>); 
> -8.0)
>
>(because of my "Local settings" I use semicolumn as arguments' delimiter) in 
>order to assign to X value of that function
>(it is between 0.0 and 1.0 for all values of parameters), or -8.0 if the 
>function returns "#NUM!", after that I would "ask"
>whether X is less than 0.0 (this means that Excel function returns "#NUM!"), 
>and so on,...
>
>But when I "walk through" my User Defined Function (F8, F8, ... F8) and see 
>that values of some other variables are
>being changed as I expect, when the flow goes to the "sentence" a few rows 
>above program "crashes" as if the it exited
>my UDF.
>
>Please help !!!
>Thanks !!! 
0
Chip
5/6/2010 8:16:25 PM
Thanks, Mr Dave, and I am sorry for my impatience, because I did not see 
today early in the moning
that you and Mr Chip Pearson had already answered me, so I asked once more. 
I made two UDFs.
Logics is the same. They don't do anything too useful, just find the 
interval of X for which Excel function
CHIDIST(X, N) returns "#NUM!", although I was convinced that it must not (N 
is Degrees_Of_Freedom,
for N>775 there is some interval ...).

Nenad

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:4BE32011.9727FD34@verizonXSPAM.net...
> Some worksheet functions can be called with 
> application.worksheetfunction....
> Some can't.
>
> But you can check for errors with:
>
> if iserror(...) then
> directly in VBA.  No need to use application.worksheetfunction.iserror().
>
> You may want to share the code for the UDF to get more helpful answers.
>
> Nenad Tosic wrote:
>> ... 

0
Nenad
5/7/2010 8:06:33 PM
Thanks, Mr Chip, and I am sorry for my impatience, because I did not see 
today early in the moning
that you and Mr Dave Peterson had already answered me, so I asked once more. 
I made two UDFs.
Logics is the same. They don't do anything too useful, just find the 
interval of X for which Excel function
CHIDIST(X, N) returns "#NUM!", although I was convinced that it must not (N 
is Degrees_Of_Freedom,
for N>775 there is some interval ...). I just wanted to say THANKS, so I 
erased yours answer ...

Nenad

"Chip Pearson" <chip@cpearson.com> wrote in message 
news:u586u5l0im3201mo75l1u472lt929ohiad@4ax.com...
>
> ... 

0
Nenad
5/7/2010 8:11:11 PM
Reply:

Similar Artilces:

trial enterprise to standard
hello all, i got a real big problem and hope you can help. my client has a server with SQL 2008 enterprise evaluation edition. This SQL is all setup for his programs and working fine. the problem is now that the company has not the money to buy the SQL enteprise version. this company already haves a SQL 2008 standard edition from a previous server that is not online anymore. is there a way to insert the standard key into the enteprise edition without a new install? kind regards, stefan reugebrink Netherlands According to http://msdn.microsoft.com/en-us/library...

Get Standard CSV File
Hello, I am trying to save some data from Excel to a CSV file to import into an application I am working on. The problem is that I get a file with Mime: application/vnd.ms-excel Instead of: text/csv How can I create a standard text/csv file? Thank You, Miguel "shapper" <mdmoura@gmail.com> schrieb im Newsbeitrag news:9039d650-026e-4b02-abc6-38dead5bf155@j9g2000vbp.googlegroups.com... > Hello, > > I am trying to save some data from Excel to a CSV file to import into > an application I am working on. Do you mean, you are programming this a...

Crystal Report 9.2.2
Good afternoon, I did lots of research but seems i can't get any good informations on those websites. I will try here and hope to have an answer. We own CRM 1.2, we have been suggested by people on this forum to use Crystal Report 9.2.2 - Standard Edition ( 1 license ). To change and edit the reports. Does anyone have a clue for pricing ? Thanks in advance. scan the internet using google - there will be many different prices which change all the time. Any price i quote you will be out of date -- John O'Donnell Microsoft CRM MVP http://www.microsoft.com/BusinessSolutions/Co...

Standardized spelling checker interface?
I want to suggest to dictionary companies that make dictionary software that they also provide a callable interface. All I need is two functions: 1. Spell check this word 2. Spell guess (used when word is misspelled) I see Microsoft has a "Microsoft Common Speller Application Programming Interface (CSAPI)", which they conveniently won't give unless I request it, and appears to be much more than is needed. Is there a standardized interface for "Spell check this word" and "Spell guess"? If this is to work at all, various dictionary companies will need a stand...

Visio 2003 standard Servicepack1 problems
Hi. I am trying to update my Visio 2003 standard Dutch via the Microsoft update service. At the end of the upgrade to SP1, there is a message that the update did not work OK, but without any extra explanation why. When I download the SP1 update manually and try to install it, the update software claims that (in Dutch) "the software is already installed, of is a part of another update that is installed". However, the version in my Visio 2003 is still a non SP1 version. Does anyone have a clue for me about how to actually upgrade to SP1? Thanks, Arnold ...

How do I print onto a non-standard size notecard?
I am trying to print onto a notecard (about 5.2" X 7.2"). I've put the dimension in the page set up and specified 'Main tray' for papersource, but when I try to print the error message says that either the wrong type of size paper has been selected. Some printers don't handle custom styles well. In such cases your best bet is to select the next-larger built-in paper size and make up the difference with margins. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "bessylou" <bessyl...

Upgrading exchange 2003 standard to Enterprise version
Is this more or less a straight forward procedure? On Wed, 4 May 2005 23:05:17 -0700, "Randy Stultz" <betnetworking.nospam@cox.net> wrote: >Is this more or less a straight forward procedure? > As long as you backup and test then the installation is a "re-install" and re-apply of the service packs & hotfixes. Backup immediately afterwards as well (a couple of times) and then restore the single store the the RSG to make sure it's good. If you have the Site Replication Service installed, you need to remove it first. I did a standard to enterprise upg...

Calculating Standard Cost in an Actual Cost System
I am looking for an application or modification that will calculate and rollup the standard cost of an item in an actual cost system. We are using GP Manufacturing. This data would be useful in quoting Jobs/items. Do anyone know of such an application? -- Chip Reed Nextec Group, Cleveland, Oh On Oct 23, 8:45 am, Chip Reed <ChipR...@discussions.microsoft.com> wrote: > I am looking for an application or modification that will calculate and > rollup the standard cost of an item in an actual cost system. We are using GP > Manufacturing. > > This data would be useful in ...

Credit Detail Issue with 2005 Standard
There is a issue with Money 2005 standard - you can not access or change the credit details on your credit card accounts (interest rate, credit limit, etc.) The solution is to spend more on the Deluxe or Premium or to return the product - not really great solutions in my opinion! Okay, that was one of my future items to deal with. I agree it is pretty lame of them to lock out the fields (even if they don't allow you to do anything with it in the Standard Version). ...

Exchange Standard 16gb size limit monitoring
I hit the 16gb limit the other day in my Exchange Standard installation. I figured out how to fix the problem (dismount and use ESutil), but I don't want to hit that size limitation again. Is there a free script or tool out there to notify me when the size hits 15gb? Where can I find out the size of the Storage group? "JohnS" <JohnSPAM@hotmail.com> wrote in message news:ud07%23WkjFHA.3300@TK2MSFTNGP15.phx.gbl... >I hit the 16gb limit the other day in my Exchange Standard installation. I >figured out how to fix the problem (dismount and use ESutil), but ...

Money 2000 Standard version 8
Is MS Money 2000 Standard compatable with XP Home Edition? Because after installing it on XP it seems to do strange things. Like some odd error message about a file name when selecting a 'tab' on 'options' menu! In microsoft.public.money, Dimwit wrote: >Is MS Money 2000 Standard compatable with XP Home Edition? Because after >installing it on XP it seems to do strange things. Like some odd error >message about a file name when selecting a 'tab' on 'options' menu! You need to both install and run Money 2000 as an administrative user. If you alre...

How do I download the standard 2003 "games" which I have lost.
The standard games (solitaire etc) which come as part of the "Windows" or "Office" installation have gone missing from my computer, ie the "Games" folder is empty. How do I get them back? Is it possible to download them? I've searched the Microsoft web site but can't find anything. No standard games come with Office. patbpon wrote: > The standard games (solitaire etc) which come as part of the "Windows" or > "Office" installation have gone missing from my computer, ie the "Games" > folder is empty....

Getting totals in standard reports
Is there a way to get column totals for one of the canned reports? Specifically the work order summary? Thanks for any help! You can edit the built in reports in a text editor like notepad. Best to back it up first, though, or better yet, Memorize a copy of the report you want to change and edit that (it appears as "Memorized-ReportName.qrp" in the directory). The reports are .qrp files in the Reports folder of the RMS folder in Windows by default. The report is structured in the .qrp file like this. //--- Report Summary --- // - contains the general options and SQL que...

Money 2004 standard #4
Installs but "meets error" and doesn't run. ...

Exchange 2K3 Standard Versus Enterprise
Is the Standard Edition 6Gb limit an absolute maximum for all information stores? i.e. Is the limit 16Gb per store, or all stores together? TIA Maurice 16 GB for the mailbox store, and 16 GB for the public store. The mailbox store consists of the priv1.edb and the priv1.stm, added together...public store consists of pub.edb and pub.stm, added together... "Maurice Bishop" <Office@NoSpamHerePleaseThankyou.MJBishop.com> wrote in message news:%23Sr7vN%23qEHA.2796@TK2MSFTNGP10.phx.gbl... > Is the Standard Edition 6Gb limit an absolute maximum for all information > st...

Exchange SP2
Hi, I'm just looking for confirmation that when SP2 for Exchange is released it will change the mailbox storage size from 16 GB to 75 GB in Standard Edition. If so, that is great! Thanks, Glen http://www.microsoft.com/exchange/downloads/2003/sp2/overview.mspx http://www.exchange-forums.com/phpBB2/viewtopic.php?t=59 Will "Glen" <Glen@discussions.microsoft.com> wrote in message news:54442026-58E6-4F09-9BE0-8EB215117E57@microsoft.com... > Hi, > > I'm just looking for confirmation that when SP2 for Exchange is released > it > will change the mailbo...

Can Activities be linked to opportunities (Sales standard)?
Apologies if this seems like a stupid question but we are considering purchasing the product and I feel that this is necessary functionality. I would like to be able to see activities (phone, letter, fax, etc.) associated or linked against sales opportunities rather than contacts. Is this possible in CRm sales standard? I hope the experts on this list will be able to help me with this query. TIA. I am running the CRM Profesional version and you can associate activities to opportunities. I assume that it is the same for the standard version as I don't know why they would use this ...

Disabling Standard Icons
I am running Office 2003 and I want to assign a error message to the SAVE icon on the StandardToolbar to disable the standard save function to get the user to use another save button that I have set up on the spreadsheet. I know it can be done because I have it on a spreadsheet someone else set up but I cannot find where they set it up. It needs to be done in such a way that when I send the document (spreadsheet) to other Pc's it will still be setup. Any help would be most apprciated. Look in the macros on the example workbook. Learning wrote: > I am running Offic...

Defrag SBS2003 Standard
I have a SBS2003 Standard server running Exchange 2003. Server and exchange are installed on RAID 5 Array across 3 physical disks. I would like to run Windows Disk Defrag on the server to restructure data for some increased performance. What is the recommended approach in proceeding? Stop Exchange and other services (AV, Content filtering, etc) before running defrag? Run defrag in safe mode? Thanks in advance. Hi Mark, Thanks for your post. I would like to suggest you take the server offline during the maintenance time, disable anti-virus, anti-spam etc monitoring s...

Exchage Server 2000 Upgrade
I attempted to do a system upgrade from Exchange Server 2000 Standard to Exchange Server 2000 Enterprise Edition. During the upgrade process, the install hung....each time the message on the screen was "...creating registry entries for the SMTP service". I let the system sit in that state for over an hour during several ttempts at this and never was able to get passed the process with having to restart the server with the message still displayed and hung on the console. I really need to do an in place upgrade of the Exchange server, rather than a clean install and migration ...

Installing Dynamics GP 9 on Windows XP + SQL Server 2005 Standard
Hi, I installed Dynamics GP 9.0 on Windows XP with SQL Server 2005 Standard without problems during installation. But, when I open GP Utilities the "Server" field is blank and the combo box listing there are no servers to choose from. Or, in the other words, GP Utilities doesn´t show any server. Or again, seems that GP Utilities doesn´t care about the database... And I can´t go ahead. I´d be very glad for any help. Thanks. Marcos, You need to create an ODBC data source so that Utilities knows what server to point to - take a look at page 29 of the GP 9...

saving a local standard rate for an enterprise resource
We are using MSP 2003 Server. We have a mix of employees and contractors in the ERP. The employees have a fully loaded standard rate but we do not capture the contractor rate in the ERP. Is there a way for my users to save locally in their project file a standard rate value for the contractors? I have been trying to use the cost rate tables but after I save and close the project and reopen, the rates are gone. any help would be appreciated. If your contractor resources are local, you can keep separate rates. All Enterprise Resource Rates will always be reset when you open ...

Moving from Office 2007 Standard to Enterprise Editions
I've just purchased a license and media for office 2007 enterprise edition. I've currently got the standard edition running on my desktop and laptop (both running Vista). Can I chose to just install the components from the Enterprise edition that are not part of Standard, and leave those I've already got alone? Or do I need them all running under the same license (I legally own both)? If I have to uninstall the standard edition prior to installing the enterprise edition, I'm wondering if there is any way to do that while preserving all (or some) of my settings a...

Visual C++ .NET Standard Edition
Hello, I am considering purchasing a "Visual C++ .NET 2003 Standard Edition". I've downloaded FTPtree.sln solution examples but it's been suggested that I need a retail version to run it with all the MFCs and libraries and DLLs. If I purchase "Visual C++ .NET 2003 Standard Edition" will I be able to develop FTPtree-type applications? Thank you, Victor. Hey Victor, The answer to that question is yes and if you are serious about developing applications or even if you want to start out with Windows programming I would snatch it too soon because Microsoft is ...

SBS vs Standard
Hi all! All of my experience with Exchange has been with the "Full/Standard" versions of Exchange & Server. Now, I'm thinking of filling a small clients needs with an SBS setup (cheaper), but I'm a little hazy on the differences. They have about 30 users, and will want to use OWA. Anything special I need to be aware of with the SBS versions? Thanks! Mark The only thing you need be aware of is that SBS Exchange must reside on the SBS Server, you CANNOT install it on another server in the SBS Domain. Other than that it runs the same as Standard Version of Excha...