0

I would like to write a function that is self-aware of its own cell address. For instance, I would like to be able to place an argumentless function in any cell, and when called, that function will perform an operation based on the value in the cell immediately to its left.

I tried the following, which works great if the cell with the function in it is the active cell, but that won't be my case in general.

Function Test()  
    Test = Range(Application.ActiveCell.Address).Offset(0,-1)  
End Function

I'm guessing that there's an easy solution to this challenge, but it's only easy once you know it!!!

Thanks in advance!

(Excel 365)

Scott Craner
  • 22,693
  • 3
  • 21
  • 25
BrianH
  • 1
  • 1
  • 1. I would not do that. You would need to make it volatile to make sure it picked up changes in the target cell, which would make it calculate every time anything in Excel changes thus slowing down the process. 2. If you insist that this is the only way, use `Application.Caller`: `Test = Application.Caller.Offset(-1,-1)` – Scott Craner Oct 26 '21 at 13:22
  • Thanks so much Scott for your prompt response! I tried the "Application.Caller" approach, and my spreadsheet is small so the recalc time is not really an issue, but unfortunately, it's not working (or specifically it's not updating). For instance, if I change the value of the cell being pointed to (i.e., Offset(-1,-1), the "Test" function is not called, and the value of the Test function cell is not updated. Even after an F9 and Shift-F9 the Test function cell does not update. Maybe I'm doing something wrong? – BrianH Oct 26 '21 at 13:52
  • As I stated you would need to make it volatile to calculate every time something in Excel changes. Or you need to make the cell a reference, otherwise Excel does not know it needs to recalc. – Scott Craner Oct 26 '21 at 13:55
  • Apologies, but I do not know what "make it volatile" means. I'm assuming that you're referring to the function itself? Making the function volatile? (how do I do that?) – BrianH Oct 26 '21 at 13:59
  • https://docs.microsoft.com/en-us/office/vba/api/excel.application.volatile – Scott Craner Oct 26 '21 at 14:21
  • You ROCK!!! Just what I needed. Very, very appreciative for the help!! – BrianH Oct 26 '21 at 14:44
  • With thanks to all that provided responses, Scott Craner provided the answer (making the function volitale) above in the comments section. Thanks again for your help! Brian – BrianH Oct 26 '21 at 15:23

1 Answers1

1

Don't use VBA if you don't have to.

=IF(relative_address=some_condition,{result if true},{result if false})

For example, if I want to show "Spooktacular" in H6 if G7 contains "Scooby Dooby Don't", or display nothing otherwise, then I would do this:

enter image description here

=IF(G7="Scooby Dooby Don't","Spooktacular","")

This formula can be copied to any other cell and it will always refer to the cell one column to the left and one row down. This happens because the address is written as G7 and not $G$7 (which would lock both the row and the column to that cell) or $G7 (which would lock the column to G) or G$7 (which would lock the row to 7).

FlexYourData
  • 6,430
  • 2
  • 6
  • 21
  • Thank you so very much for your prompt contribution here, but unfortunately although I presented a very simply function in my question, the actual function that I have is somewhat complicated, so it really must be done in VBA, vice a per-cell calculation. But again, thanks for your help! – BrianH Oct 26 '21 at 13:40
  • 1
    If you edit your post with something approaching the complexity of your actual needs, it may be the community can help in a more effective way. – FlexYourData Oct 26 '21 at 14:06
  • Thanks so very much- Turns out Scott Craner provided the solution above using Application.Volatile. But thanks again for your replies and recommendations. – BrianH Oct 26 '21 at 14:45