Excel not recalculating cells containing a UDF

[GaryStainburn]

Hi folks,

I have a number of functions which are short-cuts to a master function providing function arguments.

This function then calls a number of lookups to ranges in another workbook returning values based on codes, and return the sum of these values.

If I edit the cell (e.g. press F2) it recalculates the cell I am in and the correct new value appears. However, I cannot get Excel to automatically recalculate all cells.

Recalculating is set to automatic, and even running recalc manually doesn't update the cells. I have tried adding 'Application.Volatile' to each of the cells too but this has not fixed the problem.

Can anyone suggest what to do next. I am using Excel 2007 and 2010 and the problem occurs in both versions.

Thanks.

Gary

 

[RoryA]

We'd have to see the UDF but I would suspect you are not passing the changing values as arguments to the UDF.

 

[Richard Schollar]

Care to post the code you are using? I suspect a function redesign would fix and make the function more flexible too.

 

[GaryStainburn]

Any recommendations on improving these functions will be very welcome. I don't write VB at all, so I have no doubt there will be scope.

The short-cut functions appear in thousands of cells within the spreadsheet are are there (a) to reduce the amount of typing and (b) make it easier to change the worksheet names later if required.

The short-cut functions all look like this:

Function LNL(ParamArray Nominals() As Variant)
  Application.Volatile
  Dim V() As Variant
  V = Nominals()
  LNL = nGetNL("RingwaysLeeds", V)
End Function

The main function is:

Function nGetNL(SName, Nominals() As Variant)
  Application.Volatile
  Dim CNumber As Long
  Dim ULimit As Long
  Dim NlResult As Double
  Dim Period As String
  Dim Sh
  ULimit = UBound(Nominals)
  CNumber = 3                            ' Default to this month
  If UCase(Nominals(ULimit)) = "Y" Then  ' "Y" = This year to date
    CNumber = 4
    ULimit = ULimit - 1
  End If
  If UCase(Nominals(ULimit)) = "P" Then  ' "P" = Prior year this month
    CNumber = 5
    ULimit = ULimit - 1
  End If
  If UCase(Nominals(ULimit)) = "PY" Then ' "PY" = Prior year year to date
    CNumber = 6
    ULimit = ULimit - 1
  End If
    
  NlResult = 0
  For n = LBound(Nominals) To ULimit
    NlResult = NlResult + WorksheetFunction.VLookup(Nominals(n), Workbooks("Period_TB.xlsx").Sheets(SName).Range("A:G"), CNumber, True)
  Next n
  nGetNL = NlResult * -1
End Function

An example cell content is

=LNL(000001,000121,002345,123232,'P')

 

[GaryStainburn]

Hi folks,

I'm still working on this, but I'm going round in circles getting nowhere.

Can anyone suggest what I can try next.

Thanks.

Gary