Creating a UDF to convert ADDRESS function output as a reference

[exceluser]

Is it possible to create a UDF that converts the output returned by
the ADDRESS function as a reference and not as text ?




Exceluser

 

[exceluser]

Ron,

Thanks for the quick response.

I was trying to avoid use of the INDIRECT function since it's
volatile and would cause excessive calculation time.

Would it be a problem to change the UDF from "Application.Volatile"
to "Application.Volatile (False)" ?



Exceluser

 

[joeu2004]

(Or simply eliminated the statement altogether, which has the same
effect.)

You can change it to be non-volatile so long as you
don't mind the fact that there will be no automatic
recalculation when you change the cell referred to.

Can you provide a concrete example that demonstrates that problem?
Also, please specify the Excel and VBA versions.

In my experience (XL2003 SP3, VBA 6.5), as long as the UDF itself
accesses the parameter, it will create an Excel dependency, so
Application.Volatile is not necessary.

Example:
B1:B3 contains 1, 2, 3
A1 contains 1
A2 contains 2
A3: =ADDRESS(A1,A2)
A4: =myIndirect(A3)

Initially, A3 returns $B$1, and A4 returns 1, the contents of B1.

After changing A1 to 2, A3 returns $B$2, and A4 returns 2, the
contents of B2.

UDF....

Function myIndirect(s As String)
myIndirect = Range(s).Value2
End Function

-----

PS.... I also tried myIndirect defined as Ron did (below), which
makes it useful in other contexts as well, for example the following
nonsensical formula just to demonstrate the difference between range
and value result:

=VLOOKUP(myIndirect(A3),myIndirect(A3),1)

This form, as well, tracked changes in A1 without the need for
Application.Volatile.

UDF....

Function myIndirect(s As String) As Range
Set myIndirect = Range(s)
End Function

 

[joeu2004]

By the way, in your version of Excel, did the example I
provided initially, in my response to the OP, track the
changes in A1?

Right, your original example was sufficient. I wasn't paying
attention. My bad!