Databases
StackOverflow2013
Postsdbo
POStrange issue with Excel Custom function recalculation
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POStrange issue with Excel Custom function recalculation
    primarykey
    data
    text
    <p>I have previously found problems with user-defined (custom) functions not recalculating when input data changes, but this problem is different - it is recalculating, but only partially.</p> <p>The function is essentially a sum-if function that allows the user to select a month, and pass a range of dates, and a range of data to sum. It then sums-up values where the months match. Code is as follows:</p> <pre><code> '//Function to allow values to be summed, only if the dates are in the appropriate month. '//Inputs: '//rMonthToCheck: Range Object (single cell) holding a date (normally the 1st of the month). This will be the month used to filter sums. '//rRangeOfDates: A Range Object (multiple cells) holding a list of dates. Each will be checked against the rMonthToCheck. '//rRangeToSum: A Range Object (multiple cells) which should correspond to the dates in rRangeOfDates. These will be the values actually summed if the month criteria is correct. Public Function SumIfMonth(rMonthToCheck As Range, rRangeOfDates As Range, rRangeToSum As Range) As Double 'Application.Volatile (True) Dim i As Long Dim j As Long '//Get Month ID Dim nMonth As Long nMonth = Month(rMonthToCheck.Value) '//Get Year ID Dim nYear As Long nYear = Year(rMonthToCheck.Value) '//Now sum valid dates Dim dSum As Double For i = 1 To rRangeOfDates.Rows.Count If Month(rRangeOfDates(i, 1).Value) = nMonth And Year(rRangeOfDates(i, 1).Value) = nYear Then For j = 1 To rRangeToSum.Columns.Count '//Correct month and year, sum all columns dSum = dSum + rRangeToSum(i, j).Value Next j End If Next i SumIfMonth = dSum End Function </code></pre> <p>Now: This sits in a spreadsheet. Data is pulled-in from a database, and it should then recalculate. Here is the weird bit:</p> <p>When the function is input on its own, then it recalculates correctly.</p> <pre><code>=SUMIFMONTH(U48,ECData!$A$13:$A$400,ECData!$F$13:$H$400) </code></pre> <p>If it is nested, things get weird:</p> <p><code>=IF(MONTH(A48)&gt;MONTH(EnteredMonth),0,SUMIFMONTH(U48,ECData!$A$13:$A$400,ECData!$F$13:$H$400))</code></p> <p>In this case, it actually does recalculate, but the answer is wrong: It only sums-up the first column of the rRangeToSum range! For whatever reason the other column(s) are ignored.</p> <p>I tried application.volatile (as you can see) and it didn't work. CTRL+ALT+SHIFT+F9 doesn't work either. Entering each cell and hitting F2 then Enter works, as does find/replace '='.</p> <p>Obviously I have options (the best is to split the nested formula into two. However I would like to understand whether there is a more elegant solution.</p> <p>EDIT: It appears to be "resolved" by removing the named range, and replacing it with the cell reference that the named range refers to. Go Figure...</p> <p>EDIT2: Nope. Not robustly. Still getting the error.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USainwood
    1. USainwood
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COis the result correct if you use this test function call: `=IF(1=2,0,SUMIFMONTH(U48,ECData!$A$13:$A$400,ECData!$F$13:$H$400))`??
      singulars
      1. POStrange issue with Excel Custom function recalculation
      1. USKazimierz Jawor
    2. COOK - this is stranger. To make this easier to test (including your suggestion), I moved the code and sheets to a new spreadsheet and simulated the database download by copying and pasting values into the ranges. This all worked fine, no matter how it was set-up (i.e. calculated correctly). I wonder if the original spreadsheet has somehow become corrupted?
      singulars
      1. POStrange issue with Excel Custom function recalculation
      1. USainwood
    3. COIt appears to be "resolved" by removing the named range, and replacing it with the cell reference that the named range refers to. Go Figure...
      singulars
      1. POStrange issue with Excel Custom function recalculation
      1. USainwood
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload