Newbie problem :(

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

Please Login or Register  to view this content.

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

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

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

Thanks for the help,

Liosan

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
>
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

Function is Public by default, as is Sub.

--

HTH

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


"K Dales" <[email protected]> wrote in message
news:[email protected]...
> 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
> >
> >

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