Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Riko Wichmann
 
Posts: n/a
Default Macro on cell content

Dear all,

this must be a common problem, and I thought I found the solution, but
it doesn't work ... I'm probably missing something very simple.

I want to execute a simple VB function in one cell, taking another cell
as Parameter. Both, parameter cell and function result are strings. For
example:

A1: "this is content of cell A1"
A2: =Convert(A1)

and Convert is a VB function

Function Convert(content As string) as String

' do something depending on content
Convert = "result-string"
End Function


However, I always get a #NAME? Error in cell A2.

The idea is of course, that I don't want to explicitly execute a macro
by hand, but have something similar to A2 = A1*2, just for a function all.

How do I do this kind of exercise properly?

Thanks a lot,

Riko
  #2   Report Post  
JE McGimpsey
 
Posts: n/a
Default

You probably put the function in the worksheet or workbook code module.
Functions should normally go into regular code modules (choose
Insert/Module in the Visual Basic Editor).

If you want to leave the function in the worksheet code module, you'll
need to qualify the function call:

=Sheet1.CONVERT(A1)

Note that Convert is also the name of a function in the Analysis Toolpak
Add-in.


In article ,
Riko Wichmann wrote:

Dear all,

this must be a common problem, and I thought I found the solution, but
it doesn't work ... I'm probably missing something very simple.

I want to execute a simple VB function in one cell, taking another cell
as Parameter. Both, parameter cell and function result are strings. For
example:

A1: "this is content of cell A1"
A2: =Convert(A1)

and Convert is a VB function

Function Convert(content As string) as String

' do something depending on content
Convert = "result-string"
End Function


However, I always get a #NAME? Error in cell A2.

The idea is of course, that I don't want to explicitly execute a macro
by hand, but have something similar to A2 = A1*2, just for a function all.

How do I do this kind of exercise properly?

Thanks a lot,

Riko

  #3   Report Post  
Riko Wichmann
 
Posts: n/a
Default

Aha, it seems like the problem was buried in the fact, that I had named
the module the same as the function.
After renaming the module, the function was excecuted just fine ... took
me some time to figure that out, though!

Cheers,

Riko


JE McGimpsey wrote:
You probably put the function in the worksheet or workbook code module.
Functions should normally go into regular code modules (choose
Insert/Module in the Visual Basic Editor).

If you want to leave the function in the worksheet code module, you'll
need to qualify the function call:

=Sheet1.CONVERT(A1)

Note that Convert is also the name of a function in the Analysis Toolpak
Add-in.


In article ,
Riko Wichmann wrote:


Dear all,

this must be a common problem, and I thought I found the solution, but
it doesn't work ... I'm probably missing something very simple.

I want to execute a simple VB function in one cell, taking another cell
as Parameter. Both, parameter cell and function result are strings. For
example:

A1: "this is content of cell A1"
A2: =Convert(A1)

and Convert is a VB function

Function Convert(content As string) as String

' do something depending on content
Convert = "result-string"
End Function


However, I always get a #NAME? Error in cell A2.

The idea is of course, that I don't want to explicitly execute a macro
by hand, but have something similar to A2 = A1*2, just for a function all.

How do I do this kind of exercise properly?

Thanks a lot,

Riko

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Footnote wizard linked to cell content MVAND Excel Worksheet Functions 0 February 14th 05 07:11 PM
Cell Formula reference to cell Based On third Cell Content Gabriel Excel Discussion (Misc queries) 1 February 11th 05 07:36 AM
Cell Formula reference to cell Based On third Cell Content Gabriel Excel Discussion (Misc queries) 0 February 11th 05 06:35 AM
Help Pls re macro cell address Hugh Jago Excel Discussion (Misc queries) 2 January 5th 05 10:01 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 11:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"