I get this error: Sub or Function Not Defined

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I wrote a simple function in visual basic that uses Sqrt(), NormDist(), and
Power() functions. I can use these functions in any worksheet, but when I
include them in the Visual Basic function, it says "Sub or Function Not
Defined" for each of the above functions. However, it did not complain for
the Log() function though.

Help from Microsoft says that I should find WPS.EXE file, compile it from
some other computer, and then ... - totally over my head since I have no clue
what it is asking me to do. Wondering if you would have any simple and
reliable solution?

Regards,
Debu
 
HI Debu,

Check VBA's HELP for the syntax of the functions, like Sqr (not Sqrt).
There are more subtle differences.
You can often access the worksheet function version like with Application.Worksheetfunction.Sqrt , but even that is not always
true.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I wrote a simple function in visual basic that uses Sqrt(), NormDist(), and
| Power() functions. I can use these functions in any worksheet, but when I
| include them in the Visual Basic function, it says "Sub or Function Not
| Defined" for each of the above functions. However, it did not complain for
| the Log() function though.
|
| Help from Microsoft says that I should find WPS.EXE file, compile it from
| some other computer, and then ... - totally over my head since I have no clue
| what it is asking me to do. Wondering if you would have any simple and
| reliable solution?
|
| Regards,
| Debu
 
I gave exactly the wrong example of calling a worksheetfunction. Try Application.worksheetfunction.Power

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| HI Debu,
|
| Check VBA's HELP for the syntax of the functions, like Sqr (not Sqrt).
| There are more subtle differences.
| You can often access the worksheet function version like with Application.Worksheetfunction.Sqrt , but even that is not always
| true.
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
||I wrote a simple function in visual basic that uses Sqrt(), NormDist(), and
|| Power() functions. I can use these functions in any worksheet, but when I
|| include them in the Visual Basic function, it says "Sub or Function Not
|| Defined" for each of the above functions. However, it did not complain for
|| the Log() function though.
||
|| Help from Microsoft says that I should find WPS.EXE file, compile it from
|| some other computer, and then ... - totally over my head since I have no clue
|| what it is asking me to do. Wondering if you would have any simple and
|| reliable solution?
||
|| Regards,
|| Debu
|
|
 
Apart from changing Sqrt to Sqr, I also included Application.WorkseetFunction
in front of the worksheet functions and it worked perfect!! Thank you Niek
for your response.

Best regards,
Debu
 
Sorry, I forgot to mention that I did use Power in place of Sqrt (or Sqr)
function. Regards, Debu
 
VBA does not support use of the worksheet function.

VBA has its own square root function names Sqr

this requires no qualification with application or worksheetfunction or
application.worksheetfunction because it is a built in VBA function. You can
see what built in Excel functions are supported for use in VBA (with the
qualification) by looking in the object browser in the VBE and finding the
worksheetfuction object.
 
Thanks Tom, the list of these functions requiring no qualification is
certainly helpful and reduces some coding effort. Regards, Debu
 
I am trying to use the worksheet functions xirr and xnpv but get the same
error message as Debu. The syntax I am using is
Application.WorksheetFunction.XIRR(arguments). Both xirr and xnpv are listed
in the objects browser. What am I doing wrong?
 
I also get the error message: "Run-time error 1004, Unable to get the
property of the WorksheetFunction class". Any have a clue about what this
means and how to fix the problem?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to call module function or sub from the Add In? 3
Editing user defined functions 5
RoundUp Sub/Routine not defined 2
Sub or Function not defined 6
Sub or Function not defined 2
Fundamental problem with Indirect function 8
Cell reference in User Defined Function. 19
VLookup error - Sub or function not defined 4

Back
Top