Indirect Fuction Equivalent for UDF

alancardoso

Board Regular
Joined
Nov 9, 2004
Messages
82
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

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

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


Thanks!

Alan
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
Assuming Arquivo is a workbook name, Planilha a worksheet name, and Célula a range address,

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

alancardoso

Board Regular
Joined
Nov 9, 2004
Messages
82
Tushar,

that didn´t work quite so well

The code is now the following:

Code:
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,029

ADVERTISEMENT

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.
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029

ADVERTISEMENT

You are welcome.
 

alancardoso

Board Regular
Joined
Nov 9, 2004
Messages
82
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 ?


Code:
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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

Board Regular
Joined
Nov 9, 2004
Messages
82
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.

Code:
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.
 

Forum statistics

Threads
1,147,507
Messages
5,741,564
Members
423,667
Latest member
Kai_357

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top