Return formula with VBA

[jgema]

Dear users,

How can I return a formula with VBA when I make a function there? Whenever I return a string in the format of "=count(......" the string is displayed without activating the formula in the cell.

Thanks

[coreytroy]

Please Login or Register  to view this content.

[jgema]

I'm trying to make a function with this, is it possible? As in

Please Login or Register  to view this content.

How can I make it to return a function?

[MrShorty]

If I'm understanding correctly, you want to use a UDF to return a text string. You then want Excel to evaluate this text string as if it were a function. Is that correct?

If that is correct, I think it is a difficult way to do things. My first suggestion might be to take another look at your goal, and see if there is a better way to do it.

If you work out the return string correctly, you could nest your UDF inside of the INDIRECT() function and see if that would work for you http://office.microsoft.com/en-us/ma...778.aspx?CTT=1
Perhaps something like =count(indirect(getformularef(rowno)))

[jgema]

Perfect. I got it to work, expect when I have brackets in my function. How can I work with brackets in VBA, as in return brackets.

Example: Function = "if(a3="test")"

[MrShorty]

I almost never use the INDIRECT() function, so I'm not sure I'm the best help for this. As I understand it, the INDIRECT() function is used to convert a text string that looks like a cell reference into an actual cell reference. Since cell references don't include brackets or paretheses, I don't think INDIRECT() will work. I also don't think INDIRECT works for a case like this where you are trying to convert a text string that looks like a function into a function.

For the latest function, I could see something like =if(indirect(getformularef(rowno))="test",TRUE,FALSE) where getformularef(rowno) returns a text string that looks like a cell reference. Going with this kind of approach, your UDF needs to focus solely on returning an appropriate reference. The formula that will use that reference will be hardcoded into the cell.

coreytroy's suggestion might be the easiest way to do this, but his approach would need to be coded into a sub procedure rather than a UDF. The advantage of his suggestion is that a sub procedure can write directly into the formula property of the desired cell.

At this point, I might also suggest that, instead of these oversimplified examples, you give us a good explanation of what you want to start with, how you want to process the input (why a UDF, what is the UDF really trying to do, etc.), and what the desired output needs to be. I'm not sure a UDF is the best approach to your problem, but it is hard to picture the details of what you are doing. A sample spreadsheet might help as well.