referring cell in macro function

If a create a macro intended to be used as a function in a worksheet
is there a property of the referring cell?

I want to know what cell the formula is in.
0
zxcv
4/29/2010 7:29:16 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
407 Views

Similar Articles

[PageSpeed] 52

Dim callCell As String
callCell = Application.Caller.Address

Returns the cell address of the cell where the UDF is used.


"zxcv" <zxcvnosend@yahoo.com> wrote in message 
news:aef3cafd-4c52-40a6-8348-ebf957b3abad@a21g2000yqn.googlegroups.com...
> If a create a macro intended to be used as a function in a worksheet
> is there a property of the referring cell?
>
> I want to know what cell the formula is in. 


0
JLGWhiz
4/29/2010 7:50:24 PM
On Apr 29, 3:50=A0pm, "JLGWhiz" <JLGW...@cfl.rr.com> wrote:
> Dim callCell As String
> callCell =3D Application.Caller.Address
>
> Returns the cell address of the cell where the UDF is used.
>
> "zxcv" <zxcvnos...@yahoo.com> wrote in message
>
> news:aef3cafd-4c52-40a6-8348-ebf957b3abad@a21g2000yqn.googlegroups.com...
>
> > If a create a macro intended to be used as a function in a worksheet
> > is there a property of the referring cell?
>
> > I want to know what cell the formula is in.

Thanks.
0
zxcv
4/29/2010 8:11:39 PM
Reply:

Similar Artilces:

When I am in one cell highlight another
Hello, When I am in one cell highlight another. Lets say that if my cursor is in A1 I want D1 highlighted or with another cursor on it. Then if I move to A2, D2 should be the one with another cursor or highlighted. Thank you for your help, Jose Juan Diaz hi, Jose Juan ! > When I am in one cell highlight another > ... if my cursor is in A1... D1 highlighted or with another cursor on it. > ... if I move to A2, D2 should be the one with another cursor or highlighted. 'put' a cursor on non-active-cell... [I don't think it's possible] :( to highlight 'D' wh...

Cells print so small I cannot read numbers. How do I fix?
I have been working with page break. Now I have the grid on 1 page..but it is far to small to read. now when I try to spread it back to 2 pages, it just takes the same tiny microscopic type and spreads it into 2 pages. I am stuck printing tiny type. How can I get the grid cells back to a size that is readable. It sound like you have selected Fit to 1 page in File > Page setup > Page > Scaling. Either select to fit it to 2 pages or select Adjust to 100% size -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Repl...

referencing to worksheet names in macro for each new worksheet inserted
Hi I created a code to insert new worksheets and rename them according t values on the new worksheet itself. Say in Cell D1, i have th worksheet name. My question is when i want to refer to this worksheet in subsequen coding, how should i code it? For eg, How should i write the ???? for Sheets("????").select? Would creatin the a variable to store the names help? Thanks in advance Ken -- Message posted from http://www.ExcelForum.com After inserting your new worksheet set it's name equal to a variable. For example SHEETS.ADD VWORKSHEET = ACTIVESHEET.NAME This method ...

Process all cells in a (user) selection
I am using this code to give me cell by cell access to a code defined selection; Dim aCell As Range For Each aCell In Sheet4.Range("A4:A34") ' Do Stuff Here Next aCell which processes every cell in the A4:A34 Range How do I do the same thing for a selection drawn by the user before pressing my 'Process' button ? Thanks On 05 May 2010 11:10:21 GMT, Isis <isissoft@NOSPAMbtinternet.com> wrote: >I am using this code to give me cell by cell access to a code defined >selection; > >Dim aCell As Range >For Each aCell In S...

VB code for Macro
I have set up a rule on my InBox to check for specific words and move emails to my Work folder. Now I review emials in my Work folder and drag and drop them into 1 of 4 folders based on a number 1-4. After clicking on the folder, I need to perform the following on each of the four folders: Click on first email in the folder Clt+A (to select all the emails in the folder) Ctl+C (to copy) Drag the selections to a folder name HH Click #_Button (customized button set to send an email) Ctl+v (to paste the contents in the body of the email) Click Send Steps without the comments: Click folde...

think cell program
is anyone familiar with a program called think cell? Any thoughts? (powerpoint v 2003). Is this an add-in? thanks Sara It's very good. You can "try for free" from their website: http://www.think-cell.com/ Recent interview with one of the founders on Indezine: http://blog.indezine.com/2009/12/think-cell-conversation-with-markus.html -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover Kit http://tinyurl.com/3...

Help with ShowFilter Macro
I'm trying to use this ShowFilter UDF written by Tom Ogilvy (see bottom of post). It says to use... =showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32) ....in a cell to show the criteria for Column B. For one thing, I don't understand the cells B2 and B3 business. What is supposed to be in those cells? I would like this function to appear in the cell directly above or below the Autofilter menu cell. How do I change the function if the Autofilter criteria menu is in, cell A3, for example, and I want the criteria (this function) to appear just above, in cell A2? After trying all so...

How to view the code for excel built-in functions?
Is it possible? -For example the function PMT(). thanks. No, the code is compiled, so it would likely be less than useful anyway. About the best you can do is check out the equations used in Help (see "PV"). In article <OSU3OXOBGHA.1676@TK2MSFTNGP09.phx.gbl>, "serdar" <s@s.com> wrote: > Is it possible? -For example the function PMT(). > thanks. ...

Newbie Cell Reference Question...
Hello, I apologize if this question has been answered before, but I'm no quite sure what to search for as I'm not very good with excel. I'm making a spreadsheet where one sheet references another. I'll tr to explain this as best as possible.... I'm creating a spreadsheet for a fantasy basketball league. I have "Data" sheet that contains data for all players. I have another sheet "Teams" that has all the players on each team. Column B contains th players name, and column C contains a number that corresponds to th row this player is on in the dat...

Macros #35
Hi I run a daily download from another system, via a .txt file into Excel. Each day the WorkSheet has a different name ie A3_12_11_04 then tommorow it will be A3_13_11_04 etc to represent the date it was downloaded. I then have to create a macro and pull off some of of the data on a daily basis...this is where my problem arrisses. How can my Macro recognise the different Worksheet name on a daily basis? My 2 thoughts would be to get data from another open worksheet or be able to put a promt in my Maco to search the name of the Worksheet. Confused...I am!! Any Help would be welcome. ...

macro
hi, i have various worksheets and within that worksheet there are cells having #DIV/0!. I want this to be replace by zero. i know there is a formula which will give out zero but if someone can write a macro would good. Thanks Sub ErrorTrapAdd() Dim mystr As String Dim cel As Range For Each cel In Selection If cel.HasFormula = True Then If Not cel.Formula Like "=IF(ISERROR*" Then mystr = Right(cel.Formula, Len(cel.Formula) - 1) cel.Value = "=IF(ISERROR(" & mystr & "),0," & mystr & ")" ...

Cell background shading in Excel
Why is it that the background shading colors available under the Paintbucket Icon chjange from file to file? Also, how can I add some of the colors to the paintbucket that are availabe under the format/cells/pattern pulldown? Colors are a Workbook-level property, so you can change the color palette for any workbook. Choose Tools/Options/Colors... and modify the colors to your hearts' content. You can also import another workbook's color palette from that dialog. Note however, that there are only 56 bins in the color palette, so you can only use 56 colors at a time. The paint b...

Sum every other cell?
I own a used-book shop. I have an Excel SS to track how many books pe day I sell in each of 28 categories and the $$$ I take in for eac category. So there's two columns for each day: #books & $$$. therefore want to add every odd-numbered cell in a row to get the tota number of books for a category in a given period of days and ever even-numbered cell in the same row for the the total $$$. Is there simple formula or function for this -- bookmanj ----------------------------------------------------------------------- bookmanjb's Profile: http://www.excelforum.com/member.php?acti...

Help Required with Macro to Manipulate Data
I have a spreadsheet(s) with the following data on it (this is imported from a text file) A B C D 1 Branch Date Total 2 4501 030204 29 3 4 4501 030204 14 5 6 4502 030204 331 7 8 4502 030204 52 9 10 4503 030204 54 11 12 4503 030204 85 Hi Alay and what do you want to achieve :-) -- Regards Frank Kabel Frankfurt, Germany "Alan T >" <<Alan.T.12jthe@excelforum-nospam.com> schrieb im Newsbeitrag news:Alan.T.12jthe@excelforum-nospam.com... > I have a spreadsheet(s) with the following data on it (this is imported ...

Using Rept function
I want to repeat one cells entry somewhere else in my (same) worksheet and have used the Rept function successfully. However, ifthe 1st cell (e.g. A3 is in date format like 12/12/2003, when I use the Rept A3,1 function in another cell (e.g.cell R3), it returns the date as a number (37967). What I want is for the date to be repeated exactly as is (12/12/2003 or whatever date is typed). Can anyone help please Don't use the REPT function. Just enter =A3 and apply the date format you want using Format, Cells if you do not automatically get it. -- Jim Rech Excel MVP You can't unless ...

MERGE CELLS
I have Name, PO Box, street address, city, state, zip across a row in 6 seperate cells/columns. I want to have this format in 1 cell: Name PO Box Street address City, State Zip STEVE wrote: > I have Name, PO Box, street address, city, state, zip across a row in 6 > seperate cells/columns. > > I want to have this format in 1 cell: > > Name > PO Box > Street address > City, State Zip > You do realize that this will screw up your ability to sort the data. A better solution would be to describe what you want to do with the data. You may not need it in the ...

How do I format a cell to auto date
I would like to have a date automatically enter itself each time I open the spreadsheet. Is there a way to do this? Any help would be appreciated. Thanks Hi George Use a function like this one for todays date =TODAY() -- Regards Ron de Bruin http://www.rondebruin.nl "George" <George@discussions.microsoft.com> wrote in message news:7EF21130-EBD9-41C2-8CB5-5723BE40CBB1@microsoft.com... >I would like to have a date automatically enter itself each time I open the > spreadsheet. Is there a way to do this? Any help would be appreciated. > Thanks ...

Forecast Function #2
How can I use the forecast function on columns that are not adjacent to each other, or alternately, is there another function I can use for this. Otherwise, I have to type in the whole regression line equation thusly, =AVERAGE(L3,AB3,AR3)+((((3*((L3*1998)+(AB3*2001)+(AR3*2002)))-((L3+AB3+AR3)*(1998+2001+2002))))/((3*((1998^2)+(2001^2)+(2002^2)))-((1998+2001+2002)^2))*(2003-AVERAGE(1998,2001,2002))) I'm forecasting 2003 data from 1998,2001, and 2002 data. Thanks, X ...

Repeating columns cuts off merged cells
Hi all, I hope you can help because I can't find a solution to this one. I've got a two page spreadsheet (side by side) and I'm trying to repeat columns A & B because their row headings on the left and should only appear when printed (split onto two pages) (as in print setup > columns to repeat at left > $A:$B), ...... but B57:I57 are merged and wrapped (another grief that merged wrapped cells don't auto height, but that's for another day) and so are B58:I58 and B59:I59 (footer things). ...... and when printed, B57:I57, etc. cuts off on the second page and...

macro to action formula
sir, this is my first qst. I have a workbook with sheets for each date x so almost 30sheets x and I am using cpy data macro to copy all these date to one sheet x whenever I run the macro this sheet is newly created with the latest date x I want to use sum and sumif formulas to calculate this sheet data x can u adv a macro to do this -- pvkkutty new to discussion group however a freequent reader of discussion group posts It would be better to just have ONE sheet with all and use data>filter to see for each day desired. If desired, send your file to my address ...

Send To Mail Recipient function does not work
I have WinXP w/Outlook 2003 and the send to mail recipient function does not work. I have got it to work one time by going to Control Panels\Add Remove Programs\Set Program Access and Defaults. There I select Microsoft Windows, (instead of Custom), restart the computer and I can then send to mail recipient, but only one time. After that I get the Error message, Microsoft Office Outlook, The operation failed. This feature works when I select Outlook Express as my default Email client in Internet options. Please can someone help! Thanks in advance. Gregg ...

Macro sur les retrait excel
Bonjour =E0 tous, J=92ai un petit probl=E8me sur excel. J=92ai en effet un fichier avec des donn=E9es dans des cellules avec des retraits. Je voudrais tout simplement utiliser une macro afin de r=E9cup=E9rer dans une colonne B les valeurs de ces retraits des cellules d'une colonne A. Je sais que le champ utilis=E9 pour le retrait est =AB indentlevel =BB. J=92ai trouv=E9 l=92aide suivante sur le site de microsoft , mais je ne sai= s pas l=92utiliser . http://msdn.microsoft.com/fr-fr/library/microsoft.office.tools.excel.namedr= ange.indentlevel(VS.80).aspx --------------------------------...

error C2440: 'type cast' : cannot convert from 'overloaded-function' to 'LPHANDLER_FUNCTION'
I wrote a VC program for service...In VC 6 it working fine...But In Visual Studio 2005 it got errorrs... My code is give below.... void CRRMService::ServiceMain() { int error; //MEMORYSTATUS memory; int result; ServiceStatus.dwServiceType = SERVICE_WIN32; ServiceStatus.dwCurrentState = SERVICE_START_PENDING; ServiceStatus.dwControlsAccepted = SERVICE_ACCEPT_STOP | SERVICE_ACCEPT_SHUTDOWN; ServiceStatus.dwWin32ExitCode = 0; ServiceStatus.dwServiceSpecificExitCode = 0; ServiceStatus.dwCheckPoint = 0; ServiceStatus.dwWaitHint ...

Macro to close GP 10.0
Hello, I've read lots of posts on how to use a macro to close GP - which worked great for me in versions older than GP 10.0. But, I have not been able to duplicate the ability to close GP 10.0 using a macro. I've tried to create a macro where the only thing it does is close GP 10.0 and I still couldn't get it to work. Thanks in advance!! Jeffrey, In version 10, the macro command for exiting the application changed a bit. Include the following in your macro: # DEXVERSION=10.0.193.0 2 2 CommandExec form BuiLtin command cmdQuitApplication That should do it! -- MG.- Ma...

How to detect if function dialog is calling a custom function
I have a custom VBA function that I use in my spreadsheet. The problem is that when it is called using the function editor, every keystroke causes it to recalculate the function. Now, the function can sometimes take a long time to return because it is calling a database, causing the editor to lock up on every key stroke making it impossible to use the editor. A- is there a way to make it not calculate on each key press. -- or -- B- is there a way to determine if the function editor is calling the function so I can return dummy data until its called from an actual cell? ...