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?

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 need help please" <(E-Mail Removed)> wrote in message
news:A7A2E373-CE6F-4B9C-BF4E-(E-Mail Removed)...
|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?

Also forgot to mention I use Excel 2007.

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 need help please" <(E-Mail Removed)> wrote in message
> news:A7A2E373-CE6F-4B9C-BF4E-(E-Mail Removed)...
> |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 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



"I need help please" <(E-Mail Removed)> wrote in message
newsA8077E5-077F-4F68-A8B7-(E-Mail Removed)...
| 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 need help please" <(E-Mail Removed)> wrote in message
| > news:A7A2E373-CE6F-4B9C-BF4E-(E-Mail Removed)...
| > |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?
| >
| >
| >

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'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 need help please" wrote:

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

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

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

"I need help please" <(E-Mail Removed)> wrote in
message news:E7648004-E541-4361-8318-(E-Mail Removed)...
> 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

Wow, thanks!! that should greatly optimize the workbook!!

"Charles Williams" wrote:

> 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
>
> "I need help please" <(E-Mail Removed)> wrote in
> message news:E7648004-E541-4361-8318-(E-Mail Removed)...
> > 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
>
>
>