VBA Assign a value to a cell inside a function?

Question

Hi,

Is there anyway that I can assign a value/name to cell within a function ?

Thank you

EDIT

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 ?

Answer 1

EDITED:

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

Answer 2

Hi there,

"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