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

 

[Ron Rosenfeld]

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




Exceluser

You can use the INDIRECT worksheet function ( = INDIRECT(ADDRESS(....)) ) to return the contents of the address referred to.

If you MUST use a UDF, then try:

Function ConvToRange(s As String) As Range
Application.Volatile
Set ConvToRange = Range(s)
End Function

 

[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

 

[Ron Rosenfeld]

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

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.

In other words

A1: some_value

B1: =ConvToRange("A1")

B1 will then return "some_value"

If the function is non-volatile, changes in A1 will NOT be reflected in B1 until you trigger a manual recalculation of the entire workBOOK(e.g. <ctrl><alt><F9> )

 

[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

 

[Ron Rosenfeld]

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

A1: some_data
A2: =ADDRESS(1,1)
A3: =ConvToRange(A2)

Change A1 and A3 will not change.

A1: some_data
A2: =ADDRESS(B2,B3)
B2: 1
B3: 1
A3: =ConvToRange(A2)

Change A1. A3 does not change.

In both of those instances, =INDIRECT(A2) will reflect the changes in A1.

Not sure why you think the versions would make any difference in this instance, but:

Excel (12.0.6545.5000) SP2 MSO (12.0.6545.5004)
VBA: Retail 6.5.1053

 

[Ron Rosenfeld]

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


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

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?

 

[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!