How do I refresh user defined function data?

I

I need help please

I have many user defined functions. Mostly they count cells based off of
MANY MANY criteria. The problem is, once data has been updated, many times
the user defined function will not update. If I manually go into the cell
that calls it, delete some text & re-type it, it will call the function
again, and the data is recalculated. But, I have TONS of cells that I need
to do this on. I tried using the refresh data button from the data tab - but
that only works on imported data. Is there any way to cause excel to
recalculate all of the user defined functions on a particual worksheet or the
entire workbook?
 
N

Niek Otten

You should include *all* worksheet data used inside the function in the argument list. That is the only way Excel knows the
dependencies and will recalculate if and when necessary.

You can also include Application.Volatile in your function, but then it will always recalculate with any workbook recalc, even if
it is not necessary.

Including all data in the argument list really is the best solution.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have many user defined functions. Mostly they count cells based off of
| MANY MANY criteria. The problem is, once data has been updated, many times
| the user defined function will not update. If I manually go into the cell
| that calls it, delete some text & re-type it, it will call the function
| again, and the data is recalculated. But, I have TONS of cells that I need
| to do this on. I tried using the refresh data button from the data tab - but
| that only works on imported data. Is there any way to cause excel to
| recalculate all of the user defined functions on a particual worksheet or the
| entire workbook?
 
I

I need help please

Thanks Niek!!!
Sorry for not knowing, but how would I include the *all* worksheet data?

Function SumScreened(dateCell As Long) As Double
Dim range1 As Range
Dim range2 As Range
Set range1 = Worksheets("Individual").Range("A2:A1001")
Set range2 = Worksheets("Individual").Range("C2:C1001")

SumScreened = WorksheetFunction.SumIf(range1, dateCell, range2)
End Function


that is one of my most simple functions. Where would I put the *all*?

Function SumScreened(dateCell As Long) As Double *all*
or
Function SumScreened(dateCell As Long *all*) As Double
or what?


Thanks!!!
 
N

Niek Otten

I did not mean the *all* literally.

Not tested:

Function SumScreened(dateCell As Long, range1 as range, range2 as range) As Double
SumScreened = WorksheetFunction.SumIf(range1, dateCell, range2)
End Function

And of course call it like

=sumscreened(A1,A2:A1001,C2:C1001)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



| Thanks Niek!!!
| Sorry for not knowing, but how would I include the *all* worksheet data?
|
| Function SumScreened(dateCell As Long) As Double
| Dim range1 As Range
| Dim range2 As Range
| Set range1 = Worksheets("Individual").Range("A2:A1001")
| Set range2 = Worksheets("Individual").Range("C2:C1001")
|
| SumScreened = WorksheetFunction.SumIf(range1, dateCell, range2)
| End Function
|
|
| that is one of my most simple functions. Where would I put the *all*?
|
| Function SumScreened(dateCell As Long) As Double *all*
| or
| Function SumScreened(dateCell As Long *all*) As Double
| or what?
|
|
| Thanks!!!
| "Niek Otten" wrote:
|
| > You should include *all* worksheet data used inside the function in the argument list. That is the only way Excel knows the
| > dependencies and will recalculate if and when necessary.
| >
| > You can also include Application.Volatile in your function, but then it will always recalculate with any workbook recalc, even
if
| > it is not necessary.
| >
| > Including all data in the argument list really is the best solution.
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > | > |I have many user defined functions. Mostly they count cells based off of
| > | MANY MANY criteria. The problem is, once data has been updated, many times
| > | the user defined function will not update. If I manually go into the cell
| > | that calls it, delete some text & re-type it, it will call the function
| > | again, and the data is recalculated. But, I have TONS of cells that I need
| > | to do this on. I tried using the refresh data button from the data tab - but
| > | that only works on imported data. Is there any way to cause excel to
| > | recalculate all of the user defined functions on a particual worksheet or the
| > | entire workbook?
| >
| >
| >
 
I

I need help please

ok - I think I misunderstood before - here's what Im changing:

**** OLD ****
Function SumScreened(dateCell As Long) As Double
Dim range1 As Range
Dim range2 As Range
Set range1 = Worksheets("Individual").Range("A2:A1001")
Set range2 = Worksheets("Individual").Range("C2:C1001")

SumScreened = WorksheetFunction.SumIf(range1, dateCell, range2)
End Function


**** NEW ****
Function SumScreened(dateCell As Long, range1 As Range, range2 As Range) As
Double
Set range1 = Worksheets("Individual").Range("A2:A1001")
Set range2 = Worksheets("Individual").Range("C2:C1001")

SumScreened = WorksheetFunction.SumIf(range1, dateCell, range2)
End Function

**** END CODE ****

So now, is there a way to set up default parameters so that I can still use
the same calls from the worksheet? (I know C++, but not too familiar with
VB, and can't seem to find the info online)

like Function SumScreened(dateCell As Long, range1 As Range = Null, range2
As Range = Null) As Double

If I had that, would I still be able to call the function like previous:
=SumScreened(A2)
 
I

I need help please

I've messed around some - here's what I've come up with. Does this seem
better coding practice?

** VB CODE **
Function SumScreened(dateCell As Long, range1 As Range, range2 As Range) As
Double
Set range1 = Worksheets("Individual").Range("A:A")
Set range2 = Worksheets("Individual").Range("C:C")

SumScreened = WorksheetFunction.SumIf(range1, dateCell, range2)
End Function

** FUNCTION CALL **
=SumScreened(A2, A2, A2)


since I set range1 and range2 in the function... is this a good fix?
 
I

I need help please

But, if I call it like =SumScreened(A2, A2, A2) then Im defeating the purpose
of including all parameters....

going back to standard style, and including actual params:

Function SumScreened(dateCell As Long, range1 As Range, range2 As Range) As
Double
SumScreened = WorksheetFunction.SumIf(range1, dateCell, range2)
End Function


=SumScreened(A2, A:A, C:C)

Thanks again
 
C

Charles Williams

If you have to use whole-column references it would be better to subset them
to the usedrange

Function SumScreened(dateCell As Long, range1 As Range, range2 As Range) As
Double
dim SubRange1 as range
dim subrange2 as range

set subrange1=intersect(Range1.parent.usedrange,range1)
set subrange2=intersect(Range2.parent.usedrange,range2)
SumScreened = WorksheetFunction.SumIf(subrange1, dateCell, subrange2)
End Function


=SumScreened(A2, A:A, C:C)


Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

User Defined Function being called by changes to an independent workbook 13
LISTNUM + Mail Merge + INCLUDETEXT : Referencing Paragraph # Can't get to work 0
avoid recalculating user-defined function when file opened 2
Multiple User Defined Categories in Insert Function Form 2
Problem with SET & REF function 0
user defined function which increase consecutively while passingbelow cells 12
WORD VBA Search and switch to advanced ... 1
Editing user defined functions 5

Top