none
Range.Replace() behaves incorrectly RRS feed

  • Question

  • Hello,

    An Add-in Express customer run into an Excel issue that I reproduce in Excel 2010 SP2 build 7153.5000 32 bit and Excel 2016 build 9031.2004 (Office Insider) 32bit using a VSTO add-in that calls Excel.Range.Replace() in this fashion:

    using Excel = Microsoft.Office.Interop.Excel;
    using System.Runtime.InteropServices;
    using System.Windows.Forms;
    
    private void button1_Click(object sender, RibbonControlEventArgs e) {
        Excel.Worksheet sh = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;
        Excel.Range r = sh.Range["C5"];
        Marshal.ReleaseComObject(sh); sh = null;
        bool result = r.Replace("Old", "New");
        Marshal.ReleaseComObject(r); r = null;
        MessageBox.Show("result = " + result.ToString());
    }

    Now, start Excel, paste the text strings below to C5 (Excel puts them to C5 through C7); then add a new sheet (Sheet2), paste the strings in any cell on that sheet:

    OldBug
    OldBoy
    Old Dog

    Switch to Sheet1 and invoke the code to find that the replacement occurs in C5 through C7 (Sheet2 isn't changed). This isn't expected; expected is: the change should occur in C5 on Sheet1 only.

    Now open the Find dialog (Ctrl+F), expand Options, change "Within: Sheet" to "Within: Workbook", click the Close button on the dialog, paste the "Old" texts to C5-C7, and invoke the code again. The result is: the replacement occurs on both sheets. Expected is: that setting shouldn't influence the search; the Find/Replace methods should operate on the range of cells defined by the Range object which invokes these methods.

    Part 2. Restart Excel and test the scenarios above with this VBA macro:

    Sub dfgdfgds()
    Dim r As Excel.Range
    Set r = ActiveSheet.Range("$C$5")
    Dim res As Boolean
    res = r.Replace("Old", "New")
    End Sub

    You'll find this difference: with the "Within: Sheet" option, Range.Replace() works as expected: the replacement is performed on C5 only. With the "Within: Workbook" option, the replacement is performed on the whole workbook (similar to the C# code above).


    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader

    Please mark answers and useful posts to help other developers use the forums efficiently.

    Tuesday, February 13, 2018 9:45 AM

Answers

  • Thank you, Tao. 

    Bernie, my thanks to you! You've helped me understand that what I only supposed was true: this is an issue in the Excel object model. Or in the Excel documentation.


    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader

    Please mark answers and useful posts to help other developers use the forums efficiently.

    • Marked as answer by Andrei Smolin Wednesday, February 28, 2018 9:45 AM
    Wednesday, February 28, 2018 9:45 AM

All replies

  • That is similar to the SpecialCells issue, where is you use .SpecialCells on a single cell it defaults to the entire worksheet.

    But if you know you only want to change one cell value, why not write the changes directly into the cell?

    Cell.Value = Replace(Cell.Value,"old","new")


    Tuesday, February 13, 2018 9:35 PM
  • Hello Bernie,

    Thank you for responding to this.

    Could you please point me to a description of the .SpecialCells issue?

    I haven't explained that my goal is to find if the issue is reproducible; if it is, I'll inform the Excel team about it.

    As to your suggestion, it can be used as a limited workaround. This follows from the following test. I've modified the VBA macro so that it gets two cells (see below) and it still replaces the contents of all cells in the workbook if I switch to the "Within: Workbook" option in the Find dialog.

    Sub dfgdfgds2()
    Dim r As Excel.Range
    Set r = ActiveSheet.Range("C5:C6")
    Dim res As Boolean
    res = r.Replace("Old", "New")
    End Sub


    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader

    Please mark answers and useful posts to help other developers use the forums efficiently.

    Wednesday, February 14, 2018 9:56 AM
  • The issue with previous settings of Find, Replace, etc. affecting code usage of those methods is also well known: this is from MSDN, for example:

    The settings for LookAtSearchOrderMatchCase, and MatchByte are saved each time you use this method. If you don't specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

    Apparently, this also applies to the within workbook setting, but the only work-around that I have seen is this:

    Set dummy = Worksheets(1).Range("A1:A1").Find("Dummy", LookIn:=xlValues)[/VBA]

    With that line just before any REPLACE operations, the EDIT FIND/REPLACE dialog is reset to look within 'sheet' and not within 'workbook'. 

    And about the specialcells issue:

    SpecialCells in specified multicell range or the used range of the worksheet

    Excel often, and also in the case of SpecialCells, assumes you want the used range of the worksheet if your specified range only contains a single cell. As an example, the below code returns everything in the UsedRange of the active worksheet....

    Wednesday, February 14, 2018 2:53 PM
  • Hello Bernie,

    Thank you very much.

    The construct you provided does reset that setting in the Find dialog. It allows my VBA code to work correctly:

    Sub dfgdfgds3()
    Dim r As Excel.Range
    Set r = ActiveSheet.Range("C5:C6")
    Dim dummy As Variant
    Set dummy = Worksheets(1).Range("A1:A1").Find("Dummy", LookIn:=xlValues)
    Dim res As Boolean
    res = r.Replace("Old", "New")
    End Sub

    This construct also resets that setting if invoked from my VSTO add-in.

    private void button1_Click(object sender, RibbonControlEventArgs e) {
        Excel.Worksheet sh = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;
        Excel.Range r = sh.Range["C5"];
        object dummy = sh.Range["A1:A1"].Find("Dummy", Type.Missing , Excel.XlFindLookIn.xlValues);
        Marshal.ReleaseComObject(sh); sh = null;
        bool result = r.Replace("Old", "New");
        Marshal.ReleaseComObject(r); r = null;
        MessageBox.Show("result = " + result.ToString());
    }
        Private Sub Button1_Click(sender As Object, e As RibbonControlEventArgs) Handles Button1.Click
            Dim sh As Excel.Worksheet = CType(Globals.ThisAddIn.Application.ActiveSheet, Excel.Worksheet)
            Dim r As Excel.Range = sh.Range("C5")
            Dim dummy As Object = sh.Range("A1:A1").Find("Dummy", Type.Missing, Excel.XlFindLookIn.xlValues)
            Marshal.ReleaseComObject(sh) : sh = Nothing
            Dim result As Boolean = r.Replace("Old", "New")
            Marshal.ReleaseComObject(r) : r = Nothing
            MessageBox.Show("result = " + result.ToString())
        End Sub

    Still, Range.Replace() called from a VSTO add-in doesn't work correctly even with the "Within: Sheet" option set in the Find dialog: whether the range consists of one cell or two cells, the Range.Replace() call replaces instances of the search string on the whole worksheet, not on the cells that the range includes. I regard this as a bug in Excel.

    May I ask you to reproduce this issue if you have time?


    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader

    Please mark answers and useful posts to help other developers use the forums efficiently.

    Friday, February 16, 2018 11:27 AM
  • "May I ask you to reproduce this issue if you have time?"

    Sorry - I haven't done any non-Excel-VBA work in years - i no longer have the need or required software. 

    Friday, February 16, 2018 2:33 PM
  • No problem. Hopefully, the information above will be enough for the Excel team to solve this issue. thank you very much for your help.

    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader

    Please mark answers and useful posts to help other developers use the forums efficiently.

    Friday, February 16, 2018 2:37 PM
  • Hi Andrei,

    For this issue, it is by design and is documented in the Range.Replace Method.

    Using this method doesn’t change either the selection or the active cell.

    The settings for LookAt, SearchOrder, MatchCase, and MatchByte are saved each time you use this method. If you don’t specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 28, 2018 7:00 AM
  • Hello Tao,

    Thank you very much for looking into this.

    The discussion above is caused by the fact that the list of settings which "are saved each time you use this method" doesn't include the "Within: {Sheet or Workbook}" option. Accordingly, this fact should mean that you have every reason to expect that calling the Replace() or Find() method on a Range object works only within the cells specified by the Range object, not by that setting in the Find dialog.


    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader

    Please mark answers and useful posts to help other developers use the forums efficiently.

    Wednesday, February 28, 2018 8:37 AM
  • Since this is limit by the API, you may consider submitting your feedback.

    Hope this feature will be provided in next version.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 28, 2018 8:43 AM
  • Thank you for the suggestion. I've sent them a frown.

    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader

    Please mark answers and useful posts to help other developers use the forums efficiently.

    Wednesday, February 28, 2018 8:51 AM
  • If there is no other issue, I would suggest you mark the helpful reply as answer to close current thread.

    If you have any other new issue, please feel free to post in this forum.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 28, 2018 8:53 AM
  • Thank you, Tao. 

    Bernie, my thanks to you! You've helped me understand that what I only supposed was true: this is an issue in the Excel object model. Or in the Excel documentation.


    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader

    Please mark answers and useful posts to help other developers use the forums efficiently.

    • Marked as answer by Andrei Smolin Wednesday, February 28, 2018 9:45 AM
    Wednesday, February 28, 2018 9:45 AM