running VBA Functions in spreadsheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DesCF - 17 Apr 2007 17:59 GMT

I created a straight forward VBA function in the VBA Editor that sums
miscellaneous values in my spreadsheet. I tested it by using a message
box to display the answer and it works fine. How do I get it to run in my
spreadsheet? I tried just putting it in a cell prefixed with an equal
sign but that doesn't seem to work. Also I used to be able (in Access) to
run public functions in the Immediate window by prefixing the function
with a question mark and just hitting the return key, but that just
produces the message: 'complie error, sub or function not defined' - is
there some other way?

Signature

Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

Reply to this Message

Don Guillett - 17 Apr 2007 18:03 GMT

As always, you should post your function for comments. Is it in a REGULAR
module?

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>
> I created a straight forward VBA function in the VBA Editor that sums
[quoted text clipped - 6 lines]
> produces the message: 'complie error, sub or function not defined' - is
> there some other way?

Reply to this Message

DesCF - 18 Apr 2007 08:19 GMT

It is located in a module attached to the spreadsheet, i.e. in when I
opened the vba editor there was spreadsheets 1, 2, & 3 - I double-clicked
1 to open the module. I don't have the code in fornt of me at the momment,
but for all intents and purposes it might as well be this:

Public Function DOG() as string

DOG = "DOG"

End Function

How do I use this function in a spreadsheet just to display the 'DOG' for
example ?

> As always, you should post your function for comments. Is it in a
> REGULAR module?

Signature

Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

Reply to this Message

JE McGimpsey - 18 Apr 2007 12:47 GMT

> How do I use this function in a spreadsheet just to display the 'DOG' for
> example ?

You do what Don suggested and use a standard module, that you get by
choosing Insert/Module from the VBE menu.

See http://www.mcgimpsey.com/excel/modules.html

Reply to this Message

Gary''s Student - 17 Apr 2007 18:28 GMT

Make sure it in the "right" place:

Functions are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

Signature

Gary''s Student - gsnu200715

> I created a straight forward VBA function in the VBA Editor that sums
> miscellaneous values in my spreadsheet. I tested it by using a message
[quoted text clipped - 5 lines]
> produces the message: 'complie error, sub or function not defined' - is
> there some other way?

Reply to this Message