VBA function doesn't update in each cell on refresh

[jcher1979]

Hi,

I have written a function in VBA whose input depends on the position of the cell in which the function is found. I have used ActiveCell to locate the row and column of the cell.

The function works fine if i type it into a cell manually and hit enter. However, if i copy and paste the function across a range of cells the results don't update when i press enter. I have to select each cell individually and press F2, Enter to get it to calculate the correct value.

Any idea how i can get my function to do this automatically? "Calculate" dosen't seem to be working.

Code is below:

Function Swap_CFs(mat As String)
    'Calculate the cashflow of the bond paying the swap coupon.
    'Decides whether there should be a coupon payment, par+coupon pmt or nothing
    'Part of the process used to get discount factors from swap rates.
    
    Dim par As Integer
    Dim maturity As Integer
        
    par = 100
    maturity = CInt(Left(mat, Len(mat) - 1))
       
    If Cells(Range("Init_Swap_CF_Yr").Offset(-1, 0).Row, ActiveCell.Column).Value < maturity Then
        Swap_CFs = par * Cells(ActiveCell.Row, Range("Init_Swap_LIBOR_Rate").Column).Value
    ElseIf Cells(Range("Init_Swap_CF_Yr").Offset(-1, 0).Row, ActiveCell.Column).Value = maturity Then
        Swap_CFs = par * (1 + Cells(ActiveCell.Row, Range("Init_Swap_LIBOR_Rate").Column).Value)
    Else
        Swap_CFs = ""
    End If
    
     'Worksheets("Discount Curve").Columns("H:J").Calculate
         
End Function

 

[Andrew Poulsom]

Instead of ActiveCell try Application.Caller.

 

[jcher1979]

I'm not sure I understand how that could be used in this context. Can you be more specific?

 

[Andrew Poulsom]

In a new workbook put this function in a General module:

Function WhatCell() As String
    WhatCell = ActiveCell.Address(False, False)
    MsgBox Application.Caller.Address(False, False)
End Function

In A1 enter:

=WhatCell()

and press Enter. The message box will show A1 and the function will return A1. Now activate B1 and press Ctrl+Alt+F9 to force a recalculation. The message box will again show A1, but the function will return B1.

I think you want to use the cell that called the function, not the ActiveCell. Right?

 

[jcher1979]

This is absolutely perfect, thanks for the demonstration.