Convert to contents

[AlexanderBB]

If I have a string e.g. "Sheet1!$B$2", is there a Excel function that will give me
the contents of that cell? Or must it be a UDF, in which case , no problem.

 

[Eric W]

Try:

=INDIRECT("Sheet1!$B$2")

 

[AlexanderBB]

Gee, that's be so close to perfect, but you can't use it in UDF can you ?

 

[Eric W]

Maybe it would help if you explained what your ultimate goal is. In a UDF, you'd use something like:

x = Sheets("Sheet1").Range("B2")

 

[AlexanderBB]

I meant could the string "Sheet1!$B$2" be used as is.

 

[Eric W]

You could do this:

x = Evaluate("INDIRECT(""Sheet1!$B$2"")")

or you could write some code to parse the string into its components and use the code from post 4. But you haven't answered the question. What is your ultimate goal? There are a lot of ways to reference cells, from the worksheet or from VBA. Each way has advantages and disadvantages. Which one to use depends on what you're doing. The Evaluate method here really isn't a great way to do it.

 

[Rick Rothstein]

You could do this:

x = Evaluate("INDIRECT(""Sheet1!$B$2"")")

You do not need the INDIRECT function inside the Evaluate function (nor is the absolute reference needed either); this should return the same thing...

x = Evaluate("Sheet1!B2")

And if Sheet1!B2 is a fixed, never changing reference with in the procedure, this can be shortened to this...

x = [Sheet1!$B$2]

 

[AlexanderBB]

I wanted to use this in various Functions to get the value of that location, and that single argument format seemed practical . I do have a routine
that changes "Sheet1!$B$2" to Sheets("Sheet1").Range("B2") , but maybe it wasn't needed if something already exists.
Didn't know about Evaluate, so about to try it out. Perhaps see what the disadvantages are.
Thanks for the replies

 

[Eric W]

How is the string generated? Typed in, the result of a formula, a selection object? Generally speaking, it's better to pass a range object instead of a string with the address of your range. With a range object you can use Target.Value, or Target.Offset(1,0), or any number of dozens of built-in methods, saving you the trouble of coding them yourself.

In any case, it looks like you have something to work with, glad we could help.

 

[RoryA]

You don't need Evaluate or Indirect in code, you can just use Range:

application.range("Sheet1!B2")