It seems Application.Volatile and ActiveSheet.Calculate are not what I thought they were, I need help creating a Function that recalculates correctly when relevant data changes. Here's what I have going right now:
Public Function Availability(EmpName As Range)
Application.Volatile (True)
ColLetter = Cletter(EmpName.Column)
Set NameRange1 = Range("$" & ColLetter & "$1:$" & ColLetter & "$" & (Application.ActiveCell.Row - 1))
Set SumRange1 = Range(Cletter(Application.ActiveCell.Column) & "1:" & Cletter(Application.ActiveCell.Column) & (Application.ActiveCell.Row - 1))
Sum1 = Application.SumIfs(SumRange1, NameRange1, EmpName)
Availability = Sum1
End Function
So as you can see, it takes a cell and does a sumifs using that cell to provide the criteria and the criteria column, using the column the function is placed in for the actual numbers to sum (I know this seems like reinventing the wheel but I'm just posing the most simple form of my problem). It performs these calculations on every previous row, essentially.
CLetter is just another small function that turns a Column# into the letter because I'm too lazy to invoke R1C1 and find this more readable.
But whenever I make changes to the criteria or sum ranges, instead of recalculating to account for changes in the identifiers or the numbers, it seems to forget that the affected line exists (even if I just changed it to MATCH the criteria) or just throw a #VALUE error. This is easily fixed by clicking on the "=Availability(A#)" function and hitting enter, but I would hate to have write an extra macro to refresh all of these cells when they'll be all over the spreadsheet. Toggling Volatility helped a little bit but didn't solve the problem, nor did adding some variations on the following sheet change event:
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Calculate
End Sub
Surely there is some way to get a function to refresh itself whenever ANY cell is altered, not just its own.
Since calculation was already automatic, and the whole point was not to add more arguments to be defined (i.e. to have it all be automatic except the criterion for sumif) the only necessary change was making Application.ActiveCell into Appplication.ThisCell. Application.Caller may also have worked but this way seemed more certain. Thanks for your help Tim and Scott!