+ Reply to Thread
Results 1 to 6 of 6

get line number from where a funcion is called and use it inside the function

  1. #1
    Registered User
    Join Date
    09-16-2010
    Location
    spain
    MS-Off Ver
    Excel 365
    Posts
    14

    get line number from where a funcion is called and use it inside the function

    This issue is a little hard to explain but I will do it with an example:

    I want to create a formula (VBA) which will be used in a table in each line and a specific column, letīs say column T. So the sheet has this formula in each line of column T.

    The function gathers information from different parts of the Excel Workbook and depending on this information needs to return a value.

    There is a case when the function needs to get the information of column AT of the same line as the function is placed.

    How can I refer to a cell of the same line where the funcion is being used? I mean, how can I hand over to the function the current line from where the function is being called?

    Or is there any command to be used in VBA inside the function to track back from which cell coordinates the function is being called?

    Thanks!

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: get line number from where a funcion is called and use it inside the function

    You can use Application.ThisCell, but you really shouldn't, especially for what you describe. Why can't you just pass the cell reference from column AT as an argument directly?
    Rory

  3. #3
    Registered User
    Join Date
    09-16-2010
    Location
    spain
    MS-Off Ver
    Excel 365
    Posts
    14

    Re: get line number from where a funcion is called and use it inside the function

    Thanks for your reply.

    Yes, it obviously can be done by passing the data as an argument. I thought that other ways are possible, but this seems tricky.

    So I will probably need to add quite a lot more arguments because several data of the same line needs to be used. It's more work when using the funciont because you need to select many more cells for the arguments, but maybe it's the only way!!

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: get line number from where a funcion is called and use it inside the function

    It's not the only way, but it is the best way. If necessary, you can pass entire range blocks rather than individual cells.

  5. #5
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: get line number from where a funcion is called and use it inside the function

    You could try this line of code inside your UDF...

    RowNumFunctionIsOn = Application.Caller.Row

    And, of course, you can get other range related information about the cell the calling formula is in using the Application.Caller object as well.
    Last edited by Rick Rothstein; 10-07-2020 at 01:40 PM.

  6. #6
    Registered User
    Join Date
    09-16-2010
    Location
    spain
    MS-Off Ver
    Excel 365
    Posts
    14

    Re: get line number from where a funcion is called and use it inside the function

    Thanks Rick!

    Application.Caller.Row is working fine!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 05-08-2020, 05:40 PM
  2. INDIRECT/ADDRESS inside LINEST function versus inside INTERCEPT
    By slny06 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-14-2019, 08:51 AM
  3. Function inside a button to randomly generate number?
    By fkalinx in forum Excel General
    Replies: 2
    Last Post: 09-16-2014, 03:31 PM
  4. Function inside Concatenate Funcion ?
    By shd in forum Excel General
    Replies: 19
    Last Post: 12-19-2007, 11:16 AM
  5. Using MID function within an IF funcion
    By Ray K in forum Excel General
    Replies: 0
    Last Post: 06-07-2006, 09:05 PM
  6. [SOLVED] Using CELLS function to define RANGE funcion
    By Brian Barbre in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-03-2005, 08:20 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1