0

I am very new to VBA. I want to use vba function to achieve the following:

Sheet1:

Year ----- | - 1 - | - 2 - | - 3 - |

Revenue | $10 | $20 | $30 |

Sheet2:

Year ------| 1 | 2 | 3 |

Revenue | X | Y | Z |

Revenue is a named cell. Using excel function, I can link the revenue of each year to cell X through Z on Sheet 2 or any other worksheet using: 

=Offset(Revenue,0,column()-1)

I want to achieve the same thing in VBA Function to shorten the formula to something like Obtain(Revenue). Here is what I have come up with so far:

In VBA:

Function Obtain(Account As Range)
Dim i As Integer
i = ActiveCell.Column - Account.Column
Obtain = Account.Offset(0, i).Value
End Function

And in cell X:

=Obtain(Revenue)

While it does (almost) the same thing as the formula version, I run into problem when I copy the formula from X and paste it to Y and Z across the same row. Instead of getting $10, $20, $30, I get $10, $10, $10 because the Active.Cell is "stuck" to X, unless I move the mouse cursor and press Enter to refresh the ActiveCell for each function in Y and Z. What can I do to this Obtain VBA function so that when I copy and paste Obtain(Revenue) to other cells, excel knows which year of revenue I am referring to?

Thanks!

Community
  • 1
  • 1
Excel VBA Noob
  • 35
  • 4
  • Don't use the [ActiveCell property](https://msdn.microsoft.com/en-us/library/office/ff193314.aspx) in a function. See [UDF returns the same value everywhere](http://stackoverflow.com/questions/35910683/udf-returns-the-same-value-everywhere/35956748#35956748) (and others) for more. –  Aug 26 '16 at 16:07
  • Here is what you need: http://stackoverflow.com/questions/5559279/excel-cell-from-which-a-function-is-called – zmechanic Aug 26 '16 at 16:08

1 Answers1

2

In order to detect cell reference you need to use Application.Caller:

Function Obtain(Account As Range)
Dim i As Integer
i = Application.Caller.Column - Account.Column
Obtain = Account.Offset(0, i).Value
End Function
zmechanic
  • 1,842
  • 21
  • 27
  • I'm not going to down-vote or flag this response but link-only answers are frowned upon and I do not believe you have supplied enough of the related content to let this answer stand on it own. It should have remained a comment. –  Aug 26 '16 at 16:17
  • Jeeped: Here you go with a proper answer with example. However, If I look at the link you provided in as a duplicate would take me 10 minutes to digest, while looking at the http://stackoverflow.com/questions/5559279/excel-cell-from-which-a-function-is-called took only 10 seconds of my life. – zmechanic Aug 26 '16 at 16:25