Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I refresh user defined function data?
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I refresh user defined function data?
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" wrote in message ... |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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I refresh user defined function data?
Also forgot to mention I use Excel 2007.
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I refresh user defined function data?
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" wrote in message ... |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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I refresh user defined function data?
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" wrote in message ... | 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" wrote in message | ... | |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? | | | |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I refresh user defined function data?
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) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I refresh user defined function data?
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) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I refresh user defined function data?
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I refresh user defined function data?
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" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I refresh user defined function data?
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using dcount function within user-defined worksheet function | Excel Programming | |||
Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
Need to open the Function Arguments window from VBA for a user defined function. | Excel Programming | |||
User-defined data type; Error: Only User-defined types... | Excel Programming | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming |