Current parameter values in .find

[Bhyte]

I am using a VBA statement as follows:

Set rng = rng.Find(Desc, LookIn:=xlValues, LookAt:=xlWhole)

Is there a way to determine what the LookIn and LookAt values are before I execute this statement, save them in variables so I can restore them to their original values after I execute the above statement?
Thanks in advance.

 

[Bhyte]

And indeed it is:



The insidious one is that if you do a Replace from the UI Within: Workbook, that scope persists to a VBA invocation of Replace, even though you can't force it directly from VBA. That can be a very rude surprise.

Thanks. I stand by my statement that VBA should not change worksheet parameters. If VBA does, then I need a way to save the current parameters, then execute the VBA code and restore the original parameters. The changing of worksheet parameters in the way VBA does is exceptionally poor programming practice. I'm surprised that Microsoft did not follow this very basic programming principle. I've done this for many years; it has served me well and I don't get surprises in my coding.

 

[shg]

If VBA does, then I need a way to save the current parameters, then execute the VBA code and restore the original parameters.

It does, and just to repeat what others have said, you can't.

The Excel object model is large, complex, and has its warts.

 

[Bhyte]

Thanks. Well, I can fix my problem by simply setting the parameter values to what I want on my spreadsheet with VBA code after I execute the VBA method.
If I have to do a VBA find that needs LookAt to be xlWhole and on my spreadsheet I want xlPart then I can execute a dummy statement such as:
Set Dummy_Range = Range("A1").Find("", LookAt:=xlPart) ' Restore my spreadsheet Ctrl+F to what I almost always use
While this will work fine for me, it makes my coding useless to anyone else. Thank you Microsoft for your programming expertise!

 

[mikerickson]

One ugly work around would be to (in a remote location)put

="Cat"
="cat"
="Catalog"
="catalog"
Catalog
catalog
Cat
cat

into cells and depending on which cell .Find("cat") finds, you can tell what LookIn, LookAt, MatchCase are set to.

 

[Bhyte]

Thanks! Hehe. Yes indeedy. I knew it would take some Mickey Mouse method to play and find out what the current parameters are. I would have to do that before who knows how many methods. But I've been a professional programmer for so many years, I would be ashamed to show anyone that I determined the current parameters that way! And, it does not speak highly of Microsoft for forcing me to do so. I used to write operating system level code that saved the program's state, did its thing, restored the program's state and the program did not even know that I had been there. For example: compare a to b; test results of comparison. Between the compare and the results test, my code is executed because of an interrupt. But because I save and restore the program's state the program code works fine. I'm used to programming this way. Leave no traces, no evidence that I was even there. Thanks for your response. Your method appears to be the only way that will work.

 

[Rick Rothstein]

One ugly work around would be to (in a remote location)put

="Cat"
="cat"
="Catalog"
="catalog"
Catalog
catalog
Cat
cat

into cells and depending on which cell .Find("cat") finds, you can tell what LookIn, LookAt, MatchCase are set to.

Very clever, but shouldn't the formula words be in the same order as the non-formula words?

 

[mikerickson]

Yeah, they probably should. Some actual testing would have to be done to determine the behavior and the appropriate order.

 

[mikerickson]

I came up with this

With Range("E:E")
    .Cells(2, 1).Formula = "=""C""&""atalog"""
    .Cells(3, 1).Formula = "=""c""&""atalog"""
    .Cells(4, 1).Formula = "=""C""&""at"""
    .Cells(5, 1).Formula = "=""c""&""at"""
    .Cells(6, 1).Formula = "Catalog"
    .Cells(7, 1).Formula = "catalog"
    .Cells(8, 1).Formula = "Cat"
    .Cells(9, 1).Formula = "cat"
    
    Select Case .Find("cat", after:=.Cells(1, 1)).Row
        Case 2
            MsgBox "LookIn:Values, LookAt:Part, MatchCase:False"
       Case 3
            MsgBox "LookIn:Values, LookAt:Part, MatchCase:True"
        Case 4
            MsgBox "LookIn:Values, LookAt:Whole, MatchCase:False"
        Case 5
            MsgBox "LookIn:Values, LookAt:Whole, MatchCase:True"
        Case 6
            MsgBox "LookIn:Formulas, LookAt:Part, MatchCase:False"
       Case 7
            MsgBox "LookIn:Formulas, LookAt:Part, MatchCase:True"
        Case 8
            MsgBox "LookIn:Formulas, LookAt:Whole, MatchCase:False"
        Case 9
            MsgBox "LookIn:Formulas, LookAt:Whole, MatchCase:True"
    End Select
End With

But, it appears that on my Mac (Excel 2011), the MatchCase argument does not persist. .Find("cat") will search case insensitive unless specified.