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??????

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.


"Mike" <(E-Mail Removed)> wrote in message news:65062913-7C0A-4D21-AF30-(E-Mail Removed)...
|
| 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??????

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.
>
>
> "Mike" <(E-Mail Removed)> wrote in message news:65062913-7C0A-4D21-AF30-(E-Mail Removed)...
> |
> | 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??????
>
>
>

Hi Niek,

> 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).

AFAIK once Excel has evaluated a function that contains direct internal cell
references (which it will do with application.volatile) then it knows what
the internal dependency is (provided that the internal cell reference is
actually executed and not bypassed with an IF) and will therefore correctly
place the UDF in the calculation chain, even in cases where the UDF is
calculated more than once per recalc.
But I am not sure that I have actually tested ALL the possible combinations
of circumstances ...

In any case I agree with you that it is MUCH better to place all the cell
references in the function argument list.

Charles
_________________________________________
FastExcel 2.3
Name Manager 4.0
http://www.DecisionModels.com

Forgot to tell you, I also included Application.Volatile and I am still get
#VALUE! errors.

"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.
>
>
> "Mike" <(E-Mail Removed)> wrote in message news:65062913-7C0A-4D21-AF30-(E-Mail Removed)...
> |
> | 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??????
>
>
>

I have exactly the same problem. I have a custom function that calculates
some values from an Internet query area in the sheet. The query auto updates
in 3 minutes and the cells that have the custom functions don't update.
I guess it's a bug.

To verify it try this:
Create a custom function in a module with a single input.
Enter it in a cell and a value in the different cell.
Change the value, the cell with the custom function doesn't change.




"Mike" wrote:

>
> 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??????

Hi Thanasis,

Please give an example of such a function (the VBA code) and the way you call it
BTW did you check Tools>Options>Calculation tab, Automatic?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Thanasis Siozos" <(E-Mail Removed)> wrote in message
news:EB1422FB-AEC1-4BAC-9ED7-(E-Mail Removed)...
|I have exactly the same problem. I have a custom function that calculates
| some values from an Internet query area in the sheet. The query auto updates
| in 3 minutes and the cells that have the custom functions don't update.
| I guess it's a bug.
|
| To verify it try this:
| Create a custom function in a module with a single input.
| Enter it in a cell and a value in the different cell.
| Change the value, the cell with the custom function doesn't change.
|
|
|
|
| "Mike" wrote:
|
| >
| > 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??????

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


"Mike" <(E-Mail Removed)> wrote in message news5DE36AC-9390-4646-A163-(E-Mail Removed)...
| 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.
| >
| >
| > "Mike" <(E-Mail Removed)> wrote in message news:65062913-7C0A-4D21-AF30-(E-Mail Removed)...
| > |
| > | 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??????
| >
| >
| >