Simple VBA Module functions do not work on the worksheet!

[Guest]

In both Excel 2000 and 2007 when I enter the following functions on a
worksheet module (or the Excel obect page 'behind' the worksheet), I am
unable to execute (or call) on the worksheet. I get the error, [ #NAME? ].

Sub ShowSum()
Sum = 1 + 1
MsgBox "The answer is " & Sum
End Sub

Function CubeRoot() ' returns the cube root of a number
CubeRoot = num ^ (1 / 3)
End Function

 

[Juan Pablo González]

That is correct. You need to put that code in standard modules.

 

[Bob Phillips]

Put them in a standard code module.

And where is Num in the function defined?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Not worksheet code
use a standard module.

ShowSum will work as coded. CubeRoot:


Function CubeRoot(num As Double) As Double ' returns the cube root of a number
CubeRoot = num ^ (1 / 3)
End Function


needs an argument

 

[JE McGimpsey]

While you should probably put your function in a regular code module,
you CAN call it like this (assuming it's in the Sheet1 code module):

=Sheet1.Cuberoot(x)

Note that I'm assuming you meant

Public Function CubeRoot(num As Double) As Double
CubeRoot = num ^ (1 / 3)
End Function

 

[Guest]

Obviously I'm a neophyte. What is a 'standard' module? I've been entering
these into VBAProject\MS Excel Objects\Sheet1 (Sheet1) and into
VBAProject\Modules\Module1.

Not worksheet code
use a standard module.

ShowSum will work as coded. CubeRoot:


Function CubeRoot(num As Double) As Double ' returns the cube root of a number
CubeRoot = num ^ (1 / 3)
End Function


needs an argument
--
Gary's Student
gsnu200703

In both Excel 2000 and 2007 when I enter the following functions on a
worksheet module (or the Excel obect page 'behind' the worksheet), I am
unable to execute (or call) on the worksheet. I get the error, [ #NAME? ].

Sub ShowSum()
Sum = 1 + 1
MsgBox "The answer is " & Sum
End Sub

Function CubeRoot() ' returns the cube root of a number
CubeRoot = num ^ (1 / 3)
End Function

 

[Guest]

What is a 'regular' code module. For that matter, what is an 'irregular'
code module. Yes, I'm just getting started studying VBA, but I sure got hung
up in a hurry! And how do I go about rating the responses I've been getting?
Sites like this are a gold mine! It's difficult for me to accept that so
many people will attempt to help so many who need it! Tks.

While you should probably put your function in a regular code module,
you CAN call it like this (assuming it's in the Sheet1 code module):

=Sheet1.Cuberoot(x)

Note that I'm assuming you meant

Public Function CubeRoot(num As Double) As Double
CubeRoot = num ^ (1 / 3)
End Function

In both Excel 2000 and 2007 when I enter the following functions on a
worksheet module (or the Excel obect page 'behind' the worksheet), I am
unable to execute (or call) on the worksheet. I get the error, [ #NAME? ].

Sub ShowSum()
Sum = 1 + 1
MsgBox "The answer is " & Sum
End Sub

Function CubeRoot() ' returns the cube root of a number
CubeRoot = num ^ (1 / 3)
End Function

 

[George Nicholson]

A "standard", "regular" or "general" module is one that isn't attached to an
Excel object (i.e., not attached to a Form, Worksheet or the Workbook
object). These are created either by using the macro recorder or by
Insert>Module in the vbEditor.

I've been entering
these into VBAProject\MS Excel Objects\Sheet1 Nope.

VBAProject\Modules\Module1.

That should do it.

There are also Class Modules, but you don't need to worry about those at the
moment other than to know you don't need/want to use them for this.

HTH,

Obviously I'm a neophyte. What is a 'standard' module? I've been
entering
these into VBAProject\MS Excel Objects\Sheet1 (Sheet1) and into
VBAProject\Modules\Module1.

Not worksheet code
use a standard module.

ShowSum will work as coded. CubeRoot:


Function CubeRoot(num As Double) As Double ' returns the cube root of a
number
CubeRoot = num ^ (1 / 3)
End Function


needs an argument
--
Gary's Student
gsnu200703

In both Excel 2000 and 2007 when I enter the following functions on a
worksheet module (or the Excel obect page 'behind' the worksheet), I am
unable to execute (or call) on the worksheet. I get the error, [
#NAME? ].

Sub ShowSum()
Sum = 1 + 1
MsgBox "The answer is " & Sum
End Sub

Function CubeRoot() ' returns the cube root of a number
CubeRoot = num ^ (1 / 3)
End Function

 

[Bob Phillips]

A regular code module is one that isn't a userform, isn't Thisworkbook, and
isn't a sheet code module. As mentioned before, that is the one from
Insert>Module menu, the one that you called VBAProject\Modules\Module1
(although it could be Module2, 3, etc. as you add more.

As for rating, most of the responses you get are posted on the Usenet
newsgroups, so even if you do rate it, it won't show up here.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

What is a 'regular' code module. For that matter, what is an 'irregular'
code module. Yes, I'm just getting started studying VBA, but I sure got
hung
up in a hurry! And how do I go about rating the responses I've been
getting?
Sites like this are a gold mine! It's difficult for me to accept that so
many people will attempt to help so many who need it! Tks.

While you should probably put your function in a regular code module,
you CAN call it like this (assuming it's in the Sheet1 code module):

=Sheet1.Cuberoot(x)

Note that I'm assuming you meant

Public Function CubeRoot(num As Double) As Double
CubeRoot = num ^ (1 / 3)
End Function

In both Excel 2000 and 2007 when I enter the following functions on a
worksheet module (or the Excel obect page 'behind' the worksheet), I am
unable to execute (or call) on the worksheet. I get the error, [
#NAME? ].

Sub ShowSum()
Sum = 1 + 1
MsgBox "The answer is " & Sum
End Sub

Function CubeRoot() ' returns the cube root of a number
CubeRoot = num ^ (1 / 3)
End Function

 

[Tom Ogilvy]

General/Standard module:

VBAProject\Modules\Module1

the one you get when you do Insert=>Module

--
Regards,
Tom Ogilvy

Obviously I'm a neophyte. What is a 'standard' module? I've been
entering
these into VBAProject\MS Excel Objects\Sheet1 (Sheet1) and into
VBAProject\Modules\Module1.

Not worksheet code
use a standard module.

ShowSum will work as coded. CubeRoot:


Function CubeRoot(num As Double) As Double ' returns the cube root of a
number
CubeRoot = num ^ (1 / 3)
End Function


needs an argument
--
Gary's Student
gsnu200703

In both Excel 2000 and 2007 when I enter the following functions on a
worksheet module (or the Excel obect page 'behind' the worksheet), I am
unable to execute (or call) on the worksheet. I get the error, [
#NAME? ].

Sub ShowSum()
Sum = 1 + 1
MsgBox "The answer is " & Sum
End Sub

Function CubeRoot() ' returns the cube root of a number
CubeRoot = num ^ (1 / 3)
End Function