Newbie problem :(
 

Hello people,

im new to using VBA, and im having a rather stupid problem. I am tryin
to write a function that i could use inside the worksheet, e.g. A
=Foo(). I tried
Code
-------------------
Function Foo()
Foo = 42
End Functio
-------------------
and several variations on that theme, but the only effect i'm gettin
is #NAME errors. Does a function have to be registered in some wa
before being used on a sheet? or do i have to use CALL in some way?

Thanks for any help,

Liosa

--
Liosa
-----------------------------------------------------------------------
Liosan's Profile: http://www.excelforum.com/member.php...fo&userid=2629
View this thread: http://www.excelforum.com/showthread.php?threadid=39576

Newbie problem :(
 

Good afternoon Liosan

No, nothing like that. The code you provided returned 42 (the secret
of the Universe, apparently) without problem. Where are you placing
the code? If it is placed in a blank module within the VBE (Insert
Module) the formula =Foo() should work fine. Try it again and if you
still have a problem post back and I'll upload a spreadsheet containing
your code.

HTH

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=395765

Newbie problem :(
 

For newbies, no "stupid" questions - we all had to learn!
You need to make the Function Public (and put it in a code module you
insert, not in ThisWorkbook or the worksheet's code module):

Public Function Foo()
Foo = 42
End Function

Functions can only be used within their own code module unless you make them
Public.
--
- K Dales


"Liosan" wrote:


Hello people,

im new to using VBA, and im having a rather stupid problem. I am trying
to write a function that i could use inside the worksheet, e.g. A1
=Foo(). I tried
Code:
--------------------
Function Foo()
Foo = 42
End Function
--------------------
and several variations on that theme, but the only effect i'm getting
is #NAME errors. Does a function have to be registered in some way
before being used on a sheet? or do i have to use CALL in some way?

Thanks for any help,

Liosan


--
Liosan
------------------------------------------------------------------------
Liosan's Profile: http://www.excelforum.com/member.php...o&userid=26296
View this thread: http://www.excelforum.com/showthread...hreadid=395765

Newbie problem :(
 

Put your function in a General module (insert module in the VBE) rather than
a sheet module or the Thisworkbook module.

--
Regards,
Tom Ogilvy


"Liosan" wrote in
message ...

Hello people,

im new to using VBA, and im having a rather stupid problem. I am trying
to write a function that i could use inside the worksheet, e.g. A1
=Foo(). I tried
Code:
--------------------
Function Foo()
Foo = 42
End Function
--------------------
and several variations on that theme, but the only effect i'm getting
is #NAME errors. Does a function have to be registered in some way
before being used on a sheet? or do i have to use CALL in some way?

Thanks for any help,

Liosan


--
Liosan
------------------------------------------------------------------------
Liosan's Profile:
http://www.excelforum.com/member.php...o&userid=26296
View this thread: http://www.excelforum.com/showthread...hreadid=395765

Newbie problem :(
 

Thanks dominicb, I just dug it up in some tutorial 2 minutes ago :) Tha
seems to solve most of my VBA problems for the time.

Thanks for the help,

Liosa

--
Liosa
-----------------------------------------------------------------------
Liosan's Profile: http://www.excelforum.com/member.php...fo&userid=2629
View this thread: http://www.excelforum.com/showthread.php?threadid=39576

Newbie problem :(
 

Function is Public by default, as is Sub.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"K Dales" wrote in message
...
For newbies, no "stupid" questions - we all had to learn!
You need to make the Function Public (and put it in a code module you
insert, not in ThisWorkbook or the worksheet's code module):

Public Function Foo()
Foo = 42
End Function

Functions can only be used within their own code module unless you make
them
Public.
--
- K Dales


"Liosan" wrote:


Hello people,

im new to using VBA, and im having a rather stupid problem. I am trying
to write a function that i could use inside the worksheet, e.g. A1
=Foo(). I tried
Code:
--------------------
Function Foo()
Foo = 42
End Function
--------------------
and several variations on that theme, but the only effect i'm getting
is #NAME errors. Does a function have to be registered in some way
before being used on a sheet? or do i have to use CALL in some way?

Thanks for any help,

Liosan


--
Liosan
------------------------------------------------------------------------
Liosan's Profile:
http://www.excelforum.com/member.php...o&userid=26296
View this thread:
http://www.excelforum.com/showthread...hreadid=395765

Newbie problem :(
 

Liosan wrote:
Hello people,

im new to using VBA, and im having a rather stupid problem. I am trying
to write a function that i could use inside the worksheet, e.g. A1
=Foo(). I tried
Code:
--------------------
Function Foo()
Foo = 42
End Function
--------------------
and several variations on that theme, but the only effect i'm getting
is #NAME errors. Does a function have to be registered in some way
before being used on a sheet? or do i have to use CALL in some way?

Thanks for any help,

Liosan


--
Liosan
------------------------------------------------------------------------
Liosan's Profile: http://www.excelforum.com/member.php...o&userid=26296
View this thread: http://www.excelforum.com/showthread...hreadid=395765

Start a new blank workbook
From the Visual Basic Editor, right click VBAProject(Book1.xls)
Go to: Insert - Module
When the new module window opens add your code. This is (General)
(Declarations)
This should now work, you will see it switch to (General) (Foo)

Here is my first simple function I did awhile back. It is for
converting meters to feet. Enter a value in meters in cell A1, say 100.
Then in cell B1 type: =feet(A1)
It will then convert A1 from meters to feet.

Public Function Feet(meters) As Double
Feet = meters * 3.2808399
End Function