Functions not actualizing
I execute a VBA code that takes a database, treats it and export it into a sheet. This is working fine. However, I have a sheet that produces graphs depending on the data in the particular sheet. The datas开发者_如何学编程 does not actualize. I have to enter the cell and click enter to actualize it. I'm pretty sure there is an easier way to do this. Calculation is set to automatic but that doesn't seem to change anything.
In my cell, I have my own vba function that needs to be updated once the report is done. When I click the cell and then enter, the result is updated but I would like this to be done automatically. I hope this is clearer !
Thanks in advance,
Etienne NOEL
HEre is the code of my function
Public Function number_of_appearances(term As String, sheet As String, column As Integer) As Integer
Application.Volatile
Dim number_of_rows As Integer
Dim appearances As Integer
Dim row As Integer
appearances = 0
row = 1
number_of_rows = Worksheets(sheet).UsedRange.Rows.Count
Do While row <= number_of_rows
If Worksheets(sheet).Cells(row, column).Value = term Then
appearances = appearances + 1
End If
row = row + 1
Loop
number_of_appearances = appearances
End Function
A cell example of a user of the function
=number_of_appearances('test';'sheet1'; 3)
Sounds like your UDF might not depend on any cells that change value when your DB is processed.
See This MSDN Link
Post your UDF (or just its header if you prefer) and an example of its use...
EDIT:
Yes, none of the parameters to the UDF are cell references, therefore the UDF is not triggered to recalculate when data on the shet changes.
You have two choices:
1. rewrite your UDF to include parameter(s) that reference cells that change value when the DB is processed
2. make your UDF volitile (include Application.Volatile
in the UDF code) WARNING: this can be very inefficient, depending on how many time the UDF is used and how intensive its calculation is
EDIT 2:
Heres a refactor of your udf using the first option mentioned:
Public Function number_of_appearances(term As String, rng As Range) As Integer
Dim v As Variant
Dim i As Long, j As Long
Dim appearances As Long
v = Intersect(rng, rng.Worksheet.UsedRange)
For j = LBound(v, 2) To UBound(v, 2)
For i = LBound(v, 1) To UBound(v, 1)
If v(i, j) = term Then
appearances = appearances + 1
End If
Next i, j
number_of_appearances = appearances
End Function
use like
=number_of_appearances("test";Sheet1!C:C)
EDIT 3:
If all you are doing is counting number of occurances of a string in a range, consider using
=COUNTIF(Sheet1!C:C;"test")
精彩评论