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
0
3/1/2005 4:19:28 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
584 Views

Similar Articles

[PageSpeed] 47

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 <e544SqnHFHA.2976@TK2MSFTNGP15.phx.gbl>,
 Riko Wichmann <riko.wichmann@remove-this-to-reply.desy.de> 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
0
jemcgimpsey (6723)
3/1/2005 5:52:17 PM
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 <e544SqnHFHA.2976@TK2MSFTNGP15.phx.gbl>,
>  Riko Wichmann <riko.wichmann@remove-this-to-reply.desy.de> 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
0
3/3/2005 2:07:54 PM
Reply:

Similar Artilces:

How do I chart the same data cell on a range of worksheets?
I have the same row of cells on numerous worksheets that I want to chart or consolidate onto another worksheet ? Keith - You need to create a consolidated data range: http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Keith wrote: > I have the same row of cells on numerous worksheets that I want to chart or > consolidate onto another worksheet ? ...

Measuring cell width and height for printed paper
The width of cells is displayed in characters and pixels. The height of cells is displayed in points and pixels. How do I know how many inches or centimeters a certain number of pixels will be on printed paper? Hi For one square inch. Try 12.45 (144 Pixels) for the width and 72 (144 Pixels) for the height. On my Dell printer its exactly one inch. HTH John "Pekka Numminen" <p.numminen@suomi24.fi> wrote in message news:aa2c2e4b-1ab2-4aab-b4c8-80a4f30aafcf@q16g2000yqq.googlegroups.com... > The width of cells is displayed in characters and pixels. The height >...

Outlook Macro (VBA) programming
This is a wierd one (at least to me). I'm not a VBA expert by any means. I want to write a macro that, when Outlook 2003 executes it, causes Outlook to close. That's all. No other functions. Just a macro that says "Exit" or "Close Application" or "Alt-F4". You can imagine that this is a difficult macro to record (even if Outlook could record macros, how would you record THAT?) For a VBA macro running in Outlook the code would be quite easy: Sub KillOutlook() On Error Resume Next Application.Quit End Sub Clicking on the Close box at the top rig...

How to prevent access to hidden cells?
As you may know, it is very easy to view contents of hidden cells of a hidden worksheet of a structure protected workbook. You just need to enter a formula in any cell, referring to the hidden cell. for ex, to see content of hidden cell A1 of hidden sheet1, just enter formula in a cell "=sheet1!A1" to get the content! Is there a way to block this easy workaround? NB : I am referring to protected cells in an opened workbook. ...

sumproduct--counting--zero--blank cells
I'm using these formula to count, =SUMPRODUCT(($W$9:$W$272>=0)*($W$9:$W$272<10)) =SUMPRODUCT(($W$9:$W$272>=10)*($W$9:$W$272<20)) ........etc how do i get it so bank cells are excluded from the count. The way it is now, they are counted in the 0 to 10 range... Thanks Jeremy -- Message posted via http://www.officekb.com COUNTBLANK(range) "jeremy via OfficeKB.com" wrote: > I'm using these formula to count, > > =SUMPRODUCT(($W$9:$W$272>=0)*($W$9:$W$272<10)) > =SUMPRODUCT(($W$9:$W$272>=10)*($W$9:$W$272<20)) > ........etc > how do...

How can I insert a cell reference in a footer (eg for variable foo
Any ideas on how to do this? I'm trying to create a template with the doc reference number in the footer However, I'm trying to avoid users having to edit the footer (because this just wont get done). Hi only possible with VBA using an event procedure. e.g. put the following code in your workbook module for cell A1 Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wkSht As Worksheet For Each wkSht In Me.Worksheets With wkSht.PageSetup .CenterFooter = wksht.range("A1").value End With Next wkSht End Sub -- Regards Frank Kabel Frankfurt, Ger...

Can I Delete Symbol in Cell That Has A Formula?
I am using Excel 2004 for Mac and can't fiigur out how to get rid of the little green triangle in the upper left hand corner of a cell that contains a formula. Is there a way to accomplish this? I am not using my Mac at the moment but a guess would be excel>preferences>error checking and uncheck enambe background error checking or something similar on a pc it's under tools>options>error checking Regards, Peo Sjoblom "John" wrote: > I am using Excel 2004 for Mac and can't fiigur out how to get rid of the > little green triangle in the upper ...

References omit formatting and return cell address
In two cases of references between worksheets, the formatting from the original cell does not appear in the cell that it is referenced to. Case 1: Worksheet 1, A1 contains a currency formatted number - $2,000 Worksheet 2, A1 references the Workhseet 1, A1 cell using the = sign, yet it returns 2000 (unless I manually reformat the Workksheet 2 cell to Currency Case 2: Worksheet 3, A1 contains an apartment # - e.g. 4 Worksheet 4, A1 references this cell but returns the cell address - Worksheet2,!A1' - rather than the number 4. I tried different formats for the number 4,...

macro #2
I'm new at this so this might seem elementary but if you don't know you ask those that do. I've created a macro that is suppose to clear the input data on a worksheet when a command button is clicked. However when the msgbox appears asking them if they wish to continue and they click "yes" nothing happens.I've included the code that I've written. Any help would be appreciated. Private Sub cmdclear_Click() MsgBox "Caution: You are about to delete the information on this worksheet" & vbNewLine & vbNewLine & "Do you wish to continue?"...

Same cell name in multiple worksheets
I would like to name cells in multiple worksheets with the same name. Is there an easy way to do this? The only way I found to do this was to name the cells in a worksheet that is in a seperate workbook. Then move the worksheet to the desired workbook. It lets you add a sheet that has names which are already assigned to other cells in different sheets. However, this is a pain to do each time and there has got to be an easier way. Any ideas? Thanks for you help. Hi if you want to name for example cell E8 individually try the following: - goto 'Insert - Name - Define' - ch...

Updating links to external files using macros
Hi all I have a file which contains links to data in an external file. Every week I need to update the link to a new file that is created. So for example, at the moment the link goes to a file called "Sales Firm 2010 2009-11-12", next week there will be a new file called "Sales Firm 2010 2009-11-19" which I will want to change this link to. The following week yet another external file will be created which I will want to change the links to again. All the external files are the same i.e. format, where the data is located, etc it's just new data. At t...

how can I format a cell to change on a specific date
Can a cell that contains text, be formatted to change font color on a future date? The column is reading "Active" in green to indicate that the contract for that item is still valid, but I would like to change the "Active" to red font, or possibly change the text to read "Inactive" in red. It's the color of the font that's important, and the date that I want it to change on is on 9/11/08. thanks for your help. Try conditional formatting Assume the data is in A1 down Select col A (A1 active), then apply conditional formatting using Formula is: =...

Shading cells not working
When I try to shade cells they remain white, but if I go to print preview the color shows. Why won't the cells change color in normal view? If the fill colours aren't appearing, the high contrast setting may be turned on. There's information in the following MSKB article: OFF: Changes to Fill Color and Fill Pattern Are Not Displayed http://support.microsoft.com/?id=320531 Jenny wrote: > When I try to shade cells they remain white, but if I go > to print preview the color shows. Why won't the cells > change color in normal view? -- Debra Dalgleish...

Counting the number cells between two dates
Hi guys, Hope someone can help with this, I'm pretty sure it'll be quite a simple one. Column A:A contains a list dates, I want to use a formula to count the number of cells which contain a date between 01/01/05 - 31/01/05. Any ideas, Many thanks, Dave Try: =SUMPRODUCT((A1:A1000>=--"1/1/05")*(A1:A1000<=-- "1/31/05")) BTW - I'm using American date formats in mine. HTH Jason Atlanta, GA >-----Original Message----- >Hi guys, > >Hope someone can help with this, I'm pretty sure it'll be quite a simple one. > >Column A:A con...

pass arguments to macro of excel file from command line
Hi, I realy need to pass aruments to macro(Auto_Open)s of excel file from command prompt. I tried with following code ----------------------------------------------------------------------------- Declare Function GetCommandLine Lib "kernel32" _ Alias "GetCommandLineA" () As Long Declare Function lstrlen Lib "kernel32" _ Alias "lstrlenA" (ByVal lpString As Long) As Long Declare Function lstrcpy Lib "kernel32" _ Alias "lstrcpyA" (ByVal lpDestString As String, ) ByVal lpSrcString As Long) As Long Sub Test() Ms...

How do I limit the number of characters in a cell?
When try to limit the number of characters allowed in cell by going through the data/validation menu, it still allows more than the number of characters than I specified. I don't get it :( ...

Is it possible to measure/display the column width with a cell function?
Wednesday afternoon Is it possible to measure/display the column width with a cell function? I need to adjust some columns for a report. But I cannot find a way to display the current width in units that I can then place in a VBA macro. Would be very good to have the macro read the values entered in a set of columns and change the width accordingly. (This is very easy in Quattro pro.) J Have a look at ShowWidth at: http://www.sulprobil.com/html/get_cell.html HTH, Bernd -- Use non-volatile INDEX(P11:IV65536,1+w,1+y):INDEX(P11:IV65536,w+y,x+z) instead of volatile OFFSET(P11,w,x,y,z)...

How to see calculation and heading in same cell.
I would like the cell to perform a calculation and then display the answer as will as a heading. In other words the answer and the heading will appear in the same cell. Perhaps you mean something like ="Heading Name: "&A1+B1 ******************* ~Anne Troy www.OfficeArticles.com "Jeracho" <Jeracho@discussions.microsoft.com> wrote in message news:F34BACB9-E2DA-448A-924D-46A475F98F91@microsoft.com... > I would like the cell to perform a calculation and then display the answer as > will as a heading. In other words the answer and the heading will appear in...

Cell Formatting #5
Is there a way to format a cell so that it will automatically round either up or down? I am currently using Excell 2002. Thanks format "number" seems to work just fine Moshe Paul, Is this what you need? This will not limit you in just rounding decimal places. Format>>Cell>>Number>>Custom To make 1,234,567.00 as 1,125 type #,##0, as 1,1234.57 type #,##0.00, You can try other formatting just try it. Please note that this formatting will only change the way you see the number is displayed on the screen but does not change the actual value. Hope this...

Copying Merged Excel cells to Word.
This was posted to excel.programming but no response received :-( Hopefully some kind person can help.... I have an excel worksheet that contains some merged cells which I need to copy to Word I open the word doc with "Set Wordapp = CreateObject("Word.Application")", copy the required area of the worksheet using "selection.paste" and format the word page. This is OK except for any merged cells which copy across as indivual cells in the resultant table in Wword. How can I overcome this problem as I need to keep the same cell format in the Word table?? I hav...

Personal Macro Workbook 12-15-09
Hi, The personal macro workbook doesnt start up automatically in excel 2007. How should i get it to start automatically when excel starts up? Its driving me mad to find out where the personal macro workbook is stored in all honesty. Hi, If you did a 'standard' installation you should find personal.xlsb by looking in C:\program files\microsoft office\office 12\xlstart If it's not there then you don't have one. To create it record a simple macro saved to the personal macro workbook and then it should load every time you start the application. Mike "...

Basic--How do I make dates look a certain way in a cell?
Here's what I'd like to do: I'd like to type 41507 in a cell, and have it display as "4/15/07" or "04/17/07". Instead, it pops up some crazy date like 7/31/85. I've figured out why this is (the number indicates which day since January 1, 1900, and inserts that date). But I can't figure out if it is possible to do what I want. Is there a technique for typing in a date without slash marks or periods, and have these appear in the cell automatically? Thanks for any help you can give. ddc This will *look* like the date you want to display, but it ce...

selecting cells #5
i am wondering if it is possible to select certain cells in different sheets in the same workbook by using a single check box? i.e. sheet 1 cells a1:g1. Then sheet 2 cells a2, g2 and f2. by clicking a check box on sheet 2. I can only recomend you macros since I don't know hou to use checkboxes Sub Select_Range_Sheet1() Sheets("Sheet1").Select Range("R2:BL52,O2:Q2,M28,F28").Select End Sub You can do one for each sheet and use buttons instead check boxes. I hope it has been usefull. Saludos NlC -- NlC -----------------------------------------------------------...

Why does workbook think deleted macro still exits?
I have deleted the macros in a workbook but each time I open it there is still a messge stating there are macros. How do I stop the message from appearing? Thanks Karen ...

Help me! There is problem with cells view...
Hello! By default when I write some text in cell and the text is longer then cell's width, I can see his contents in next cells. But in one excel worksheet when I sellect all cells in worksheet and then draging them it can no longer seen all contents in one cell. In entire worksheet the cells have wrap text turned off, there are no merged cells. In another worksheet with the same cell properties, the text overflow to the next right cells of course the cells behind the edit one are empty. I don't know what is going on. And I don't know how this was happened. It is very import...