VBA and find: "lookat"

[Guest]

Hi All

I have the following issue, which is discussed on a lot of sites but not
with the approach that i am trying to do:

I use the range.find in a sub in my program, now, as we can read in the help
file, excel stores the settings and reuses them if omitted. So far so good.
Now I would like to store the current settings, use the range.find function
and restore the settings.
My Problem is, that I do not know where excel stores these settings.

Hope someone can help me.

Thanks in Advance

Carlo

 

[Dave Peterson]

Excel doesn't store the current settings.

Setting them to what you think is ok is about as good as you can do.

 

[Bill Renaud]

This would be an interesting programming exercise. Excel does store the
settings (apparently internally as static variables somewhere that are not
exposed in the class libraries).

I guess I would try temporarily adding a new worksheet to the workbook, set
up some data with formulas, comments, etc., then do a FindNext method to
"discover" the settings currently being saved by Excel. Save these
"discovered" values inside your routine, do your Find method (with your
settings), then return to the temporary worksheet, do a new Find method
with your saved values, then delete the temporary worksheet.

 

[Bill Renaud]

Correction: Use another "Find" method to discover the settings, not a
"FindNext" method.

(Aside: This is probably not worth all the effort, but useful only as a
"class exercise" to learn more about Excel VBA programming!)

 

[Guest]

Hi Bill

Thanks a lot for your answer. I agree with you, that it is not worth the
effort.
But still a nice exercise.

I thought, as excel needs to store these values somewhere, that we can get
the information out of excel via VBA. Well, nothing we can do about, right?

Thanks again

Carlo

 

[Guest]

Hi Dave

well, excel needs to store them somewhere, that's for sure, because it
remembers the last settings.

But the question was, how can I get to this information.

Thanks for taking the time to answer to my question

Carlo

 

[Dave Peterson]

You're right. Excel does store them, but it doesn't make that storage area
available to the developer/programmer.