Indirect Fuction Equivalent for UDF

[alancardoso]

What I´m trying to do is to concatenate a few cells, creating a text string.
Then, I want to convert this string to a formula.
As I will be using this code for a lot of cells, I´ve decided do create a User Defined Function.

The problem that I´m having is that as far as I know there is no equivalent do the Indirect Excel function, and there is no way to access this function from VBA

Function Facil(Arquivo As String, Planilha As String, Célula As String)

Application.Volatile
Dim Texto
Texto = Arquivo & Planilha & Célula
Facil =

Thanks!

Alan

 

[alancardoso]

Any ideas?

 

[tusharm]

Assuming Arquivo is a workbook name, Planilha a worksheet name, and Célula a range address,

Workbooks(Arquivo).Worksheets(Planilha).Range(Célula)

 

[alancardoso]

Tushar,

that didn´t work quite so well

The code is now the following:

Function Facil(Arquivo As String, Planilha As String, Célula As String)

Application.Volatile
Facil = Workbooks(Arquivo).Worksheets(Planilha).Range(Célula)


End Function

Any other ideas ?

By the way, you were right in your assumptions
Arquivo is the Workgroup
Planilha is the Worksheet
Célula is the Range


Thanks!

 

[tusharm]

How does it not work?

As long as the various names are correct and the workbook/sheet/range exists, it should return the value of that cell.

 

[alancardoso]

My mistake.

Thanks for the help, Tushar!

 

[tusharm]

You are welcome.

 

[alancardoso]

Tushar,

I´m having another problem in the next step in the function.

What I want to do is to make the functions retain it´s previous value
if the path for Facil is empty

So, in an example:

The fuctions returns 7

On Error the fuction should keep 7 instead of displaying a 0 (that is what happening right now)

Any ideas ?

Function Facil(Arquivo As String, Planilha As String, Célula As String) 
Application.Volatile 

Dim Zero 
On Error GoTo Final 
'If Célula = "" Then GoTo Final 
'If Zero = "" Then GoTo Final 

Zero = Workbooks(Arquivo).Worksheets(Planilha).Range(Célula) 

Facil = Zero 

Final: 

Exit Function 


End Function

 

[tusharm]

There's no easy way to do this. I don't know if Laurent Longre's MoreFunc has something along the lines of what you want. Check http://longre.free.fr/english/

Another way is to enable iteration (Tools | Options... | Calculation tab) though it comes with its own set of baggage. In this case, you could use in, say D3, the formula =IF(ISERR(UDF(x,y,z),D3,UDF(x,y,z)) or the UDF could contain the VBA equivalent.

The last way is to substantially complicate your UDF. It must keep track of the last result it returned on a cell-by-cell basis. Then, in case of an error, return the previous (stored) result. In the case of a legitimate result, update the stored result and return the new value. Even then, it is not fully protected against user-initiated changes to workbook/sheet/range names. If you want to implement persistence across add-in/XL shutdown, it will get even more complicated.

 

[alancardoso]

I decided to use the iteration to solve my problem.
I suppose I´m doing something wrong, as I´ve never used this Excel feature before.

I tested the formula in Excel without using the UDF just to make sure that I could make it work.

Cell A3 Formulas
=IF(ISERR(A5);A1;A5)

But, if A5 results in an error, and tell excel to calculate Cell A3, I get a 0 as a result.