none
Possible bug in Excel or VBA when using Range.Find on a recently refreshed ListObject, QueryTable Object, or WebQuery Object (DataTables) RRS feed

  • Question

  • Hi,

    Within a UDF in VBA, I have been using Range.Find to locate information in a list of data. This works great most of the time, except when the list data can be refreshed from a data source, such as SQL Server, although it doesn't really matter the underlying data source. I can re-create this issue using several different types of refresh able lists.

    Steps to reproduce

    1. In a new workbook, using Excel 2007 or Excel 2010, use Get External Data from any data source and retrieve a list of information. The underlying data does not need to change, the list just has to be refresh able.

    2. Ensure this new data table is refresh able by right-clicking anywhere in the data and selecting the "Refresh" option. This button is in different places depending on the type of list created. Depending on the type of data source and the connection options, you may be prompted to re-enter credentials.

    3. In the Visual Basic Editor, add a new Module to the workbook and paste in the following code:

     

    Function RangeFind(DataRange As Range, What As String) As String

        Dim NewRange As Range

        Set NewRange = DataRange.Find(What)

        RangeFind = NewRange.Address

    End Function

    4. In a cell on another sheet in the new workbook, insert the new RangeFind function and use the first argument to pass in the range name of the data table you created in step 1. Use the second argument to pass a string you want to locate somewhere in the data table. Visually inspect the data to ensure this value exists in the data table.

    5. If you have successfully completed the first 4 steps, at this point you should have a value in the cell corresponding to the location of the data, typically in the form of an absolute cell reference, such as $a$1. You can f2 the formula and step through the UDF and see that each time you step through the UDF, you get the address of the cell.

    6. Now, refresh the data table you created in step 1 using the same process you used in step 2 to refresh the data table, and the cell with the formula is updated automatically by the Excel calculation engine and returns #VALUE!. This is not the expected result. 

     

    Why do I think this is a bug?

    1. If you immediately F2 the cell with the formula, the cell address returns with no issue, the formula is correct.

    2. If I set calculation to manual, then refresh the data table, then enable automatic calculation, the formula is detected volatile and then refreshes with no issue.

    3. It is only when Excel triggers the automatic calculation of the formula during/after the list refresh that the error occurs.

    4. If you notice I said "during/after" the list refresh. For some reason it seems that , possibly, the cell is trying to refresh against the data table while the data table is being refreshed, hence the #VALUE! error.

    5. If you set a breakpoint in the UDF, and refresh the data table, Range.Find is not the only method to fail. Using the immediate window, other types of commands will fail, such as .Vlookup, but not commands such as DataRange.Address. Actually, in the immediate window, I can locate and display the value of the cell where the data is located, but that's only because I know, in this case, where that data is located.

     

     

    So far in my testing, this issue appears to be time related. The automatic refresh of the formula appears to kick off before the refresh of the list is finished. Sometimes you can even see the "spinning world" icon in the bottom left corner of excel while stepping through the UDF in VBA.

    Things I have already tried in order to fix it:

    1. Disable Background refresh, which appears to be different than a normal refresh.

    2. Disable multi-threaded calculation.

    3. Sleep/Wait for few seconds in the UDF before attempting a Range.Find

     

    Is there anything else I can do at this point to get this UDF to return the correct value immediately after the refresh of the list?

     


    • Edited by Jordan B Wednesday, August 3, 2011 9:23 PM grammar
    Wednesday, August 3, 2011 8:58 PM

Answers

  • You could use a macro to refresh your data, and which also uses a macro to refresh the formula:

    Sub DoBoth()

    RefreshData

    RefreshFormula

    End Sub

    OR...

    Sub DoBothWithDelay()

    RefreshData

    Application.Ontime Now + TimeValue("00:00:10"), "RefreshFormula"

    End Sub


    HTH, Bernie
    • Marked as answer by Jordan B Tuesday, August 9, 2011 10:45 PM
    Thursday, August 4, 2011 4:01 PM

All replies

  • Here are a few things you can try.

    1) Add

    Application.Volatile
    as the first line of your UDF.

    2) Use a wrapper worksheetfunction that is volatile and based on the cells changing to force recalc of the UDF:

    =IF(SUM(Table)=SUM(Table), UDF(), "")

    That might put the cell with the UDF low enough in the recalc priority to have it calc'ed after the refresh.

     


    HTH, Bernie
    Wednesday, August 3, 2011 9:51 PM
  •  

    Hi Bernie,

     

    Thanks for your reply.

    I would really like to try to resolve this issue without introducing volatility, it severely impacts the performance of the UDF.

    I have added the Application.Volatile to the UDF and it does return the correct data instead of #VALUE! after the data list refresh,

    but now - anytime I touch the workbook, thousands of cells recalculate, even without the data list refreshing. It occurs when:

     

    1. Deleting a sheet - even one that does not contain the data list or the functions.

    2. Inserting a column - on a sheet with the functions or the data list.

    3. Manually refreshing one of few thousand functions - using "F2" hot key and pressing enter.

     

    I'm sure there are many other cases where Excel kicks off calculation, but these were just a few I encountered as I was working with the workbook.

     

    Any other ideas?

     

    Thanks,

    Jordan

     


    • Edited by Jordan B Thursday, August 4, 2011 3:37 PM grammar
    Thursday, August 4, 2011 3:36 PM
  • You could use a macro to refresh your data, and which also uses a macro to refresh the formula:

    Sub DoBoth()

    RefreshData

    RefreshFormula

    End Sub

    OR...

    Sub DoBothWithDelay()

    RefreshData

    Application.Ontime Now + TimeValue("00:00:10"), "RefreshFormula"

    End Sub


    HTH, Bernie
    • Marked as answer by Jordan B Tuesday, August 9, 2011 10:45 PM
    Thursday, August 4, 2011 4:01 PM
  • Hi Bernie, I think the first part of your last reply is probably the best bet. It's not ideal, but it will work. I will just have to train users to use my "Refresh" routine instead of the native Data List Refresh method:

    Sub DoBoth()

    RefreshData

    RefreshFormula

    End Sub

    From a performance standpoint it is still not as fast as Excel actually calculating the formula correctly the first time (not returning #VALUE! when using a Range.Find on a recently refreshed DataTable), as it initiates data refreshes in a strange order, but it is faster than applying Application.Volatile to the formula.

    I have to be careful to keep the data lists on separate sheets, otherwise the formulas recalculate multiple times.

    Every time new data is appended to the data table a cascading refresh occurs in factorial.

    So if I have three data lists on one sheet, the RefreshData portion of DoBoth() triggers six calculations (1*2*3).

    The last data table is refreshed three times (and all of its formulas refresh with #VALUE!)

    and the second one two times (and all of it's formulas refresh with #VALUE!)

    and the first sheet once (and all of it's formulas refresh with #VALUE!),

    then RefreshFormula fires and refreshes all of the formulas one last time.

    But, it is better the Application.Volatile constantly refreshing when the user is working in Excel normally.

     

    Keep in mind that this #VALUE! problem does not occur on a static data list, which is really strange.

     

    Thanks for your help

    Tuesday, August 9, 2011 10:04 PM