Is there anyway that I can assign a value/name to cell within a function ?
Sorry for being unclear, here is my requirement.
I'm having a user defined function (=MyFunction()) which can be called from a excel sheet. Thus, I also having a menu button where I need to recall the all the functions calls to =MyFunction(), when user click a the button.
My plan is to inside the MyFunction(), assign a name rerefence to the calling cell and store it inside vba. So I could have a array of cell names. Then I can recall these cell references when the menu button is clicked.
Please help me to achieve this. Is there a any better way of keeping cell references ?
Ah, now I see.
The easiest way to do it is to make a bogus argument:
MyFunction(ByVal r As Variant), and, whenever you use this function on a sheet, provide exactly same cell as the argument:
=MyFunction(A1). When the menu item is clicked, change the value in A1 to whatever, and all MyFunctions will recalculate.
Or, you can use
Application.Volatile in the body of the function. This way it will recalculate each time any cell in any opened workbook is changed.
You could also use a module-level collection to store references, too, but Excel sometimes just resets the project thus losing module-level variables. If you're brave enough to try:
Option Explicit Private RefsToCalculate As New Collection Public Function MyFunction() As Long Static i As Long i = i + 1 MyFunction = i If TypeOf Application.Caller Is Excel.Range Then On Error Resume Next RefsToCalculate.Add Application.Caller, Application.Caller.Address On Error GoTo 0 End If End Function Public Sub MenuButtonClicked() Dim i As Long For i = 1 To RefsToCalculate.Count RefsToCalculate(i).Dirty Next End Sub
"I'm having a user defined function (=MyFunction()) which can be called from a excel sheet. Thus, I also having a menu button where I need to recall the all the functions calls to =MyFunction(), when user click a the button."
This can easily be done without creating a cache to store a range of cells. However, you need to be careful with the calculation method. I believe that the code below ensures that your range will always be calculated, but: (1) If calc method is not manual, then Excel ultimately controls what is calculated, when and why, so it may recalculate other cells too. (2) Again, I do believe it guarantees recalculation of all cells with your function regardless of calc method, but I haven't tested the code below for tables and semi-automatic calculation method.
The code below offers two approaches:
(1) - Recalculate all cells containing a formula: the advantage is that you skip a loop and the code therein, the disadvantage is that you might force the recalculation of many more cells than you really need.
(2) - Build a range of interest and recalc that range: the disadvantage is that building that range may take some serious computational effort. The advantage is that, if calc method is set to manual, then I BELIEVE that Excel will only recalc the cell in that range.
I guess the choice depends on the specific details of the problem you need to solve.
"My plan is to inside the MyFunction(), assign a name rerefence to the calling cell and store it inside vba. So I could have a array of cell names. Then I can recall these cell references when the menu button is clicked."
If you really want to follow this approach, or if you definitely need to create a cache of cells for the purpose you describe, then this can be done and, although rudimentary, it can even be built in such a way that it is preserved between Excel sessions. However, this requires some more work, a more advanced approach and it would still be pretty rudimentary. IMO, a clear overkill for this problem. To make matters worse, you would have to invoke code everytime a cell is updated to ensure that the cache is kept up to date, which could take a good hit on performace. As for GSerg's suggestion: that approach - as he himself mentions - does not give you any real control over the life of the cache itself. This is, everytime you reach the cache, you would have to check if Excel has wiped it out and, if this is the case, rebuild it.
Conclusion: I'd recommed that you don't cache the cells. Instead, I'd suggest you try to find the cells you need to recalc on an on-demand basis, and force recalculation of those in the most optimal way you can find to do so. Still not convinced? In that case, use
Application.Caller.Address (see code below) to retrieve the address of the cell invoking your function.
REMARK: Implemented and tested in Excel 2003. C#-style comment symbols included for formatting purposes.
Option Explicit Public Sub ReEvaluateMyFunction() On Error GoTo Handle_Exception Dim targetCells As Range Dim targetCell As Range Dim rangeToRecalc As Range /*'Workbook and worksheet names 'hard-coded for the example*/ Set targetCells = Application _ .Workbooks("Book1") _ .Worksheets("Sheet1").UsedRange _ .SpecialCells(xlCellTypeFormulas) If targetCells Is Nothing Then Exit Sub /*'You can narrow down the range if you know 'more about the function's return type, e.g.: '.SpecialCells(xlCellTypeFormulas, xlNumbers) '.SpecialCells(xlCellTypeFormulas, xlTextValues) 'OPTION 1: re-calculate all cells in the range 'Remark: unless calc method is set to "Manual", which 'should give you full control, I think there's no 'guarantee that other cells will not be recalculated*/ If Application.Calculation = xlCalculationManual Then //'Use to force recalculation if calc mode is manual targetCells.Calculate Else //'Use this to force recalculation in other cases targetCells.Dirty End If Set targetCells = Nothing Exit Sub /*'OPTION 2: create a range specific to your 'function and recalculate that range*/ For Each targetCell In targetCells If targetCell.Formula = "=MyFunction()" Then If rangeToRecalc Is Nothing Then Set rangeToRecalc = targetCell Else Set rangeToRecalc = Union(rangeToRecalc, targetCell) End If End If Next targetCell //'Same comments as before If Application.Calculation = xlCalculationManual Then rangeToRecalc.Calculate Else rangeToRecalc.Dirty End If Set rangeToRecalc = Nothing Set targetCell = Nothing Set targetCells = Nothing Exit Sub Handle_Exception: Set rangeToRecalc = Nothing Set targetCell = Nothing Set targetCells = Nothing MsgBox "An error has been found: " + Err.Description, vbCritical End Sub Public Function MyFunction() As String MyFunction = Application.Caller.Address End Function