UDF refreshing problem

[maxxev]

Hi, I have a UDF I nicked from the internet, which works great, however:

I have one sheet that is the "input" worksheet.
I have 2 lookup sheets that use the "vlookupall" UDF to create a list of products that meet the criteria in these 2 sheets from the list provided in the input sheet.

One of the 2 lookup sheets refreshs the Vlookupall code when the variables in that sheet are modified, the other sheet (using the exact same code) doesn't.

However NEITHER of the lookup sheets update the UDF when the input sheet is updated.

I believe all the variables have to be in the argument for a UDF to update but as I only just grasp the basics of what this code is doing (and i'm not brialliant with VBA) I don't know how to fix this (my thoughts are that perhaps the "sDel" should somehow be in the argument?

If it helps, I don't need sDel to be anything other than "," (e.g. it doesn't need to be a variable).
I tried removing it completely but that started all my concacenations with "," at the begining which I don't want if possible.

I could make it all work by just filling down the forumula before the lookup sheets are printed or saved but it woulden't show the user real time info (which is something I REALLY don't want to happen), I don't want to use the volitile code as everytime the input form is changed it takes about 20 seconds before the user can do anything (without interupting the refreshing).

So the formula is:
=vlookupall(B6,'CHECK WEIGHER SHEET.xls'!rRange,2,", ")

The code is:

Function vlookupall(sSearch As String, rRange As Range, _
    Optional lLookupCol As Long = 2, Optional sDel As String = ",") As String
'Vlookupall searches in first column of rRange for sSearch and returns
'corresponding values of column lLookupCol if sSearch was found. All these
'lookup values are being concatenated, delimited by sDel and returned in
'one string. If lLookupCol is negative then rRange must not have more than
'one column.
'Reverse("moc.LiborPlus.[URL unfurl="true"]www")[/URL] PB 16-Sep-2010 V0.20
Dim i As Long, sTemp As String
If lLookupCol > rRange.Columns.Count Or sSearch = "" Or _
    (lLookupCol < 0 And rRange.Columns.Count > 1) Then
    vlookupall = CVErr(xlErrValue)
    Exit Function
End If
vlookupall = ""
For i = 1 To rRange.Rows.Count
    If rRange(i, 1).Text = sSearch Then
        If lLookupCol >= 0 Then
            vlookupall = vlookupall & sTemp & rRange(i, lLookupCol).Text
        Else
            vlookupall = vlookupall & sTemp & rRange(i).Offset(0, lLookupCol).Text
        End If
        sTemp = sDel
    End If
Next i
End Function

Cheers for any help.

 

[maxxev]

Ammendment:

Aparently since I last checked the sheet, the 2 lookup sheets UDF is now refreshing if there are changes to the input sheet, so now the ONLY issue is that one of the lookup sheets dosen't refresh the UDF when the lookup variables are changed (the other does).

Any thoughts?

 

[maxxev]

Another ammendment, i've discovered that the lookup sheet IS refreshing, but unlike the sheet that is working correctly it is refreshing one stage AFTER changes....

E.g. make the change, nothing happens, make another change to the sheet and then the first change happens (2nd one doesn't) etc...

Again if you have any thoughts i'd really appreaciate it!

 

[SkipVought]

Try adding

Application.Volatile

Skip,

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

 

[N1GHTEYES]

I don't think you should need Application.volatile.

I tried out your code and it works fine. I used the following configurations:

source data, function control cells and function output all on same sheet,
source data on a separate sheet and a copy of the formula and control values on a 3rd sheet,
formula and control values on two different sheets of the same workbook and the source data on a completely separate workbook.

In all of these configurations, the function output instantly and correctly updated whether I changed the source data (rRange) or any of the other parameters.

Have you tried debugging to see exactly what is changing and when?


To do so, click on the first line of the function (begining "Function vlookupall(sSearch "etc) then hit F9. Then go back to the workbook and change one of your input parameters. The code pane should then become active and the line will turn yellow. To progress through the code one line at a time, hit F8. Hover the mouse over any variable to see its current value.


Have you also tried seting up a simple set of data in a new workbook and importing the code, then running the function with the source data and the function on the same sheet. If that works OK I'd say there is some other problem with your current workbook.

Tony

 

[xlbo]

if you have lots of data it may simply be a timing issue....do you have the "calculating....%" message in the bottom left of your excel window after you change inputs?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Deniall]

Sounds almost like a problem in the way Excel has constructed its recalculation tree.

Try replacing the innards of your UDF with something absolutely minimal, like
vlookupall = ""
and see if the problem still happens.

Try using "recalculate all" instead of "recalculate".

Given the recalc times you are talking about, it is obvious you have a large and complex spreadsheet. There are limits to how large and complex a spreadsheet can be before Excel ceases to be able to maintain its recalculation tree.

 

[maxxev]

Thank you for all suggestions / comments

I think Deniall has got the problem sussed because at the moment because (fingers crossed) without doing anything to the code or workbook the function is now working correctly....

I'm going to hope that it remains to do so and look for a better solution just in case.

Cheers all.