Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Footnote wizard linked to cell content | Excel Worksheet Functions | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) | |||
Help Pls re macro cell address | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |