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

magpac

New Member
Joined
Oct 4, 2010
Messages
2
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:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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
 
Upvote 0
Thanks!,

Application.Caller was what I was looking for.

Appreciate the quick response.


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

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top