Refreshing My User Defined Function 1

[PWD]

Following on from my previous thread (1518249 - thanks DaveInIowa) I now have a Function applied to many cells throughout various Sheets of the Workbook - in Excel 2000. Obviously I need these to update automatically when I change the data in my 'variable' cell. I've been looking around and can't see an obvious answer. I tried

 Application.Volatile

but that did what it said on the tin and just made the whole Workbook explode. Similarly 'F9' in the Formula Bar just removes the formula and substitutes the result. I can't very well ask my user to 'Press F2 & Enter' on every cell with the Function in the formula. I even tried adding the 'Refresh All' button from the 'external data' Toolbar - just in case! Surely there's something I've overlooked? Help. Please!

Many thanks, Des.

 

[PHV]

What about this ?
Application.Calculate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[combo]

Why not pass values in dependant cells as arguments of the function? You will get it recalculated every time one of them changes, moreover the UDF will be much more flexible.

combo

 

[DaveInIowa]

That's interesting that F9 will replace the formula with the cell's value when the cursor is in the formula bar. I didn't know that.

However, F9 will recalculate the worksheet's formulas if the cursor is not in the formula bar but a cell is selected instead.

To PHV's point, you can also set this application attribute manually by going to Tools > Options... > Calculation tab, and selecting the Manual option.

 

[DaveInIowa]

D'oh ... Automatic option.

 

[PWD]

PHV. Sorry that made no difference at all.

Dave. Nope; it's been set to Auto all along. F9 does nothing.

combo. Huh? I'm sorry I don't understand the question. Here is the Function

Function TestDate2() As Variant
   
        RowNum = ActiveCell.Row '=TestDate2() in multiple cells
    
    Select Case Range("Q7") 'This cell is linked to the dates on Sheet 'D'
        Case Sheets("D").Range("A1") 'The sheet that holds the Dates
            TestDate2 = "XXX" 'Month 1 (March) - Has no cummulative depreciation
        Case Sheets("D").Range("A2") '(April)
            TestDate2 = Range("AK" & RowNum) 'Cell with 1st cumulative total
        Case Sheets("D").Range("A3") '(May)
            TestDate2 = Range("AL" & RowNum) 'Cell with 2nd cumulative total
            'Case etc...........
        Case Else: TestDate2 = "Error"
    End Select

End Function

The result in any cell with =TestDate2() in it only appears to update by individual manual intervention. It's SOOOOOOOO frustrating!

 

[PHV]

Replace this:
Function TestDate2() As Variant
with this:
Function TestDate2(R As Range) As Variant
Application.Volatile

and the formula should now be:
=TestDate2([anyCellRefHere])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PWD]

I'm always going to be testing Q7 on each sheet because the user will be changing this to link in with each subsequent month from the date range on sheet 'D', so I'm not really sure how that will help.
Des.

 

[PHV]

how that will help.
Excel will recalculate due the cell reference in the UDF call ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DaveInIowa]

This is getting messy. Now you've introduced a variable row number into the function. How are you planning on using this function ... in a single cell, multiple cells? It seems unusual to have a user defined function without any parameters referencing cell values.

I don't know if PHV's suggestion above will cause the proper recalculations or not. You have, what, 25 different cell references in a single function, any of which could cause the function's value to change. I'm not sure how Excel would keep track of all these different dependencies to know it needs to recalculate a single cell.

Try playing around with the Formula Auditing toolbar, notably the Trace Precedents and Trace Dependents options to see if Excel "knows" about these embedded cell references.

 

[DaveInIowa]

Come to think of it; I'm pretty sure your revised function can't possibly work. Range("AK" & RowNum) is not a real cell that Excel can build a dependency on.

 

[SkipVought]

Hi,

Your problem is ACTIVECELL!!!

There is ONLY ON activecell on a sheet.

Function TestDate2([b]r As Range[/b]) As Variant
    Application.Volatile
    RowNum = [b]r[/b].Row '=TestDate2() in multiple cells
    
    TestDate2 = Range("D" & RowNum) 'Cell with 2nd cumulative total
    'Case etc...........

End Function

the reference can be ANY cell in the row other than the calling cell.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Good morning all. (08:40 in the UK). Well I can see the logic of why it shouldn't work....... but. It has actually worked (a bit) when I've been 'messing around' but I can't find out what combination of what has made it work. Grrr.

OK not really working, but. If I put

Application.CalculateFull

at the end of the Function it has a fit when I press F5 and puts #VALUE! in EVERY cell in EVERY sheet that contains the formula. If I then amend it to just

Application.Calculate

and F5 it puts the value (in every cell that contains the formula) of the range, e.g., AL & whatever is the ActiveCell row for that particular sheet. So for Sheet1 the ActiveCell is M10 it will populate the values from cell AL10; on Sheet2 the ActiveCell is M25, it will populate the values from cell AL25 – all based on the value in cell Q7. At least I’ve managed to figure out what was happening – it’s just a shame I can’t get it, at least, to ‘work’ without any errors. I could then think about moving it forward some. Does this give anyone any inspiration? Wow 09:55 already! I’m not just slow, I’m fielding HelpDesk calls as well – for my sins. LOL, Des.

 

[combo]

Hi,
I meant a function like:

Function TestDate2(DateToTest As Range, FirstMonthDate As Range, TestedDates As Range, CumulatedTotals As Range) As Variant
If DateToTest = FirstMonthDate Then
    TestDate2 = "XXX"
Else
    TestDate2 = "Error"
    For i = 1 To TestedDates.Cells.Count
        If TestedDates.Cells(i) = DateToTest Then
            TestDate2 = CumulatedTotals.Cells(i)
            Exit For
        End If
    Next i
End If
End Function

The user need to point to ranges. tested dates is a range excluding first month date.

combo

 

[PWD]

Thanks combo. I suppose that's the whole point of what I'm trying to do really. I have this ‘ideal world’ vision of all the target cells having the Function Formula in them – they have now – and them being updated somehow, either as a result of the data in cell Q7 changing or with a button linked to the Function to run it.

Skip, I think we have muddied the waters a bit with the Sheet called ‘D’. It was called Dates (or at least something longer than D) by the users when they created it but they found that than ran out of characters when trying to create nested Ifs in the original formula, e.g. If(Q7=Dates!A1,123,If(Q7=Dates!A2,234,…..,If(Q7=Dates!A7,789,”Error”))). Obviously we hit the Nested If limit after they had hit the character limit for the original formula.

Des.

 

[SkipVought]

Chnaces are, if you are getting sums based on date ranges, then a simple SUMPRODUCT formula might fit the bill...
[tt]
=sumproduct((DateRange>=StartDate)*(DateRange<=EndDate)*(SumRange))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

I don’t know. It just seems so complicated. I’ll go back to basics and try to explain what they’ve put in this spreadsheet and what they want to get out of it. It’s all to do with cumulative depreciation. They have the total value for certain items and they’re going to write that value down by a certain amount over the next 12 months. So say it cost 100 units and they are going to write off ¼ of it this year, then 25 units have been allocated. These 25 units are then chopped into 12 monthly pieces of depreciation. It is the cumulation of these 12ths that is being tracked. (Month 1 (April) will have no cumulation; month 2 (May) will have 1/12th cumulation.)

The resulting cumulation month on month is then subtracted from the starting value to give a valuation for that item at any particular month. So we have cells (AKn), (ALn) etc that are 1 x 1/12, 2 x 1/12 etc. of the annual depreciation. So the resulting cumulation depends on what month we’re in. This is determined by cell Q7 which is linked to Sheet ‘D’ and is amended by the user according to that month (The months are represented on Sheet ‘D’ in cells A1(April) to A12(March).). Each month will only ever have one value in Q7; so in Month 1 (April) Q7 reads =D!A1; in Month 2 (May) Q7 reads =D!A2. What we’re trying to achieve for many rows is basically which cumulative cell result do we put in, e.g. if Q7 =D!A2 then put in the value of ALn in cell Mn – for all the applicable rows in column M. (I assume they print off the sheet and store it away for the auditors.) Does this help at all?
Des.

 

[SkipVought]

My eyes glazed over reading your prose.

Please post a coherent example, suitable descriptions, along with the results you hope to receive.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DaveInIowa]

Have you investigated the Financial functions available in the Analysis ToolPak Add-In?
Tools > Add-Ins... > check "Analysis ToolPak"
then
Insert > Function... > then select category "Financial

 

[PWD]

Dang. How do I post bits of a spreadsheet? I've made up a simple model example. Des.