Results 1 to 3 of 3

How do you refer to the current cell in a VBA function?

This is a discussion on How do you refer to the current cell in a VBA function? within the Excel Questions forums, part of the Question Forums category; I have a spreadsheet that I routinely add and delete rows in, some of the cell functions in those rows ...

  1. #1
    New Member
    Join Date
    Oct 2010
    Posts
    2

    Default How do you refer to the current cell in a VBA function?

    I have a spreadsheet that I routinely add and delete rows in, some of the cell functions in those rows refer to the row above to do calculations. These cells end up with the wrong values when you insert rows (the row below now refers to the one 2 above) and deleting rows produces lots of #ref's.

    I originally fixed it by replacing the reference to the cell above with =indirect(address(row()-1,column(),4)).

    This works fine, and calculates properly when adding or deleting rows from the spreadsheet, though I'd prefer a simple VBA function. So I wrote (simplified):

    Function Ofst(R As Long, C As Long, Addr As Variant ) As Variant
    Ofst = Cells(Addr.Row + R, Addr.Column + C).Value
    End Function

    So I can replace [A2]= a1+1 with [A2]=Ofst(A2,-1,0)+1

    And this also works ok, but I'd like to drop the A2 from within Ofst() and do

    Function Ofst(R As Long, C As Long, optional Addr As Variant) As Variant
    If IsMissing(Addr) Then
    Ofst = Cells(?.Row + R, ?.Column + C).Value
    Else
    Ofst = Cells(Addr.Row + R, Addr.Column + C).Value
    End If
    End Function

    Which works fine when I specify the address to offset from, but what do I replace the '?'s with, how do you refer to the current cell that the function is being called from in a VBA UDF?

    I'd like this because I have some cells where I want [A2]=Ofst(-1,0)+Ofst(-1,0,X2), so I'd have one function that can either give the value in the cell above (or whatever the Row Column offsets given point to) the current one if the 3rd parameter isn't specified, or above the specified cell if it is.

    It's NOT ActiveCell, because replacing ? in that function with ActiveCell, makes all the cells this function is in refer to whichever cell the cursor is in.

    Can anyone point me in the right direction?
    Last edited by magpac; Oct 4th, 2010 at 09:33 PM. Reason: Typo

  2. #2
    Board Regular
    Join Date
    Dec 2008
    Posts
    5,376

    Default Re: How do you refer to the current cell in a VBA function?

    Untested

    Code:
    Function Ofst(R As Long, C As Long, optional Addr As Variant) As Variant
    If IsMissing(Addr) Then
    Ofst = application.caller.value
    Else
    Ofst = addr.Value
    End If
    End Function
    Or (simplified version)

    Code:
    Function Ofst(R As Long, C As Long, Optional Addr As Variant) As Variant
    If IsMissing(Addr) Then Addr = Application.Caller
    Ofst = Addr.Value
    End Function

  3. #3
    New Member
    Join Date
    Oct 2010
    Posts
    2

    Default Re: How do you refer to the current cell in a VBA function?

    Thanks!,

    Application.Caller was what I was looking for.

    Appreciate the quick response.


    And again, thanks, the second form is even better.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com