Hi Mike,
Difficult for us to reproduce, since we don't know what's in your sheets.
But you do address many cells directly from within the function, not via the argument list; all your reference to ActiveSheet do.
So do references like Worksheets("Reference").Range("Month_Start").
BTW ActiveCell doesn't refer to the cell from which te functions was called; what if you had two calls to the function?
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
| Niek,
|
| Thanks for the quick response. I changed my code a little bit so all
| dependencies run thru the function. I also chose not to use a variant since
| its a memory hog. Here's how it looks now:
|
| Public Function GetVintageRate(strDate As String, mStart As String) As Double
| '*********************************************************************************************
| 'returns vintage buildout rate to col K for a given date
| '*********************************************************************************************
|
|
| Dim irow As Integer
| Dim rng As Range
| Dim yearmo As Long
| Dim curYearmo As Long
|
| Set rng = ActiveCell 'what cell are we in
| curRow = rng.Row 'what row are we on
| 'find row where vintage rates are
| irow = 1
| Do Until ActiveSheet.Cells(irow, 1) = "Vintage Rates"
| irow = irow + 1
| Loop
| mStart = Worksheets("Reference").Range("Month_Start") 'what month are we
| in
| curYearmo = CreateYearMo(mStart) 'put current month in format YYYYMM
| If Month(DateValue(strDate)) = Month(mStart) And
| Year(DateValue(strDate)) _
| = Year(mStart) Then 'in the curr mo
| GetVintageRate = ActiveSheet.Range("B" & irow).Value 'M0 vintage rate
| Else 'not in the current month
| If DateValue(strDate) > DateValue(mStart) Then 'future month
| GetVintageRate = 0 'vintage rate = 0
| Else 'prior month
| yearmo = CreateYearMo(DateValue(strDate)) 'put date in YYYYMM
| format
| Select Case curYearmo - yearmo 'delta btwn current mo and mo of
| row #
| Case 1, 89 'back 1 month yo
| GetVintageRate = ActiveSheet.Range("C" & irow).Value
| Case 2, 90 'back 2 months
| GetVintageRate = ActiveSheet.Range("D" & irow).Value
| Case 3, 91 '3 mo
| GetVintageRate = ActiveSheet.Range("E" & irow).Value
| Case 4, 92 '4 mo
| GetVintageRate = ActiveSheet.Range("F" & irow).Value
| Case 5, 93 '5 mo
| GetVintageRate = ActiveSheet.Range("G" & irow).Value
| Case 6, 94 '6 mo
| GetVintageRate = ActiveSheet.Range("H" & irow).Value
| Case Else '> 6 mo, make it 100%
| GetVintageRate = 1
| End Select
| End If
| End If
|
| End Function
| Private Function CreateYearMo(strDate As String) As Long
|
| If Len(Month(DateValue(strDate))) = 1 Then 'length of month = 1 (i.e. -
| Feb = 2)
| CreateYearMo = Val(Year(DateValue(strDate)) & "0" &
| Month(DateValue(strDate)))
| Else
| CreateYearMo = Val(Year(DateValue(strDate)) &
| Month(DateValue(strDate)))
| End If
|
| End Function
|
| I'm still getting a #VALUE! error when I change mStart (the 2nd value passed
| to the function) in the workbook. When I initially call the function it
| works fine though.
|
| "Niek Otten" wrote:
|
| > Hi Mike,
| >
| > You didn't give us the code of your UDF, so we can just guess.
| >
| > But the most common cause is that you don't include all input in the argument list. If you access cells from within the
function
| > directly (not via the argument list) then Excel isn't aware of the dependencies and will not recalculate.
| > Best solution: include all the inputs in the argument list.
| >
| > It is often advised to include Application.Volatile to the function. However, I am still not sure that the cells will be
| > recalculated in the correct sequence (why should they, if Excel is not aware of the dependencies).
| >
| > So best advice is still to include all input in the argument list.
| >
| >
| > |
| > | I have a VBA function that I am using in several worksheets (i.e. - each
| > | cell in column K says the following: =getvintagerate(A41), where
| > | getvintagerate expects a variant and returns a double). When I change
| > | dependent cells in the worksheets, the functions do not automatically
| > | recalculate. I tried writing code that fires on the workbook_sheetchange
| > | event and calculates all sheets, but when I do that all my cells that call
| > | the VBA function return #VALUE!. If I go into each cell and recalc (go to
| > | each cell, hit F2, then hit Enter) the functions return the correct value.
| > |
| > | I don't know if this matters, but I am using Excel 2007.
| > |
| > | Any ideas??????
| >
| >
| >
