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

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

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