Evaluate any UDF arguments as is, as a cell in excel would do

[Spyros13]

How do I evalute myudf as excel evaulates its own in-built functions? many experts has said it is imposible , VBA doesnt have native ability to this, and to basically "Go and learn Python or C" instead. And there are many ways to do this.

But I think VBA can do this, becuause some UDF's evaluate just like excels inbuilt fucntions so, so why cant mine?

=myudf(parm1,parm2)

when PARM1 = C1 or any other cell , it works.

Parm1 = "C1"&"Goodbye" - I get #errors and #na's or #cant excecute functions

MYUDF(c1,parm2)&"Goodbye" it works (although thats not what i want to do. If it was I wouldnt be asking the question.

MYUDF(vlookup(a1,a1:z1,3,0),parm2) - I get #errors and #na's or #cant execute function

im using

Evaluate "mysubinmyudf(" & Evaluate(parm1.value(0, 0))

myudf(anything at all bar once an indirect to refernce a cell,parm2) - I get #errors and #na's or #cant execute function

As i said, an expert on another forum (which I have in my history but cant dig up to give you the link right now) was arguing and upset with VBA as a whole that VBA did not have the native ability to handle arguments in arguments in arguments (and so on) in a UDF.

Am I right? or if anyone does know of a subroutine to tell excel to read the argument in the funtion first before performing the UDF, Id love to know. Im sure there is, and it would be said if it didnt.

p.s. Arguments or parameters or whatver you call them. Ive been calling them arguments,

[needs an edit and to add to]

 

[Rick Rothstein]

Parm1 = "C1"&"Goodbye" - I get #errors and #na's or #cant excecute functions

I am not completely following everything you posted (without seeing the UDF, it is kind of hard to know what is going on), but for the above, since it is a UDF being used in an Excel formula, I would think the C1 should not be in quotes (so Excel could see it as a cell reference instead of just text characters). With that said, you should try this as Parm1...

C1&"Goodbye"

 

[Spyros13]

My complete bad !! Thank you very much for replying! You are absoltely right. But Its a typo.

when Parm1 = C1&"Goodbye" - I get #errors and #na's or #cant excecute functions

anything else other then a cell in that parameter, it returns error, even if I put in a vlookup to lookup the same cell or any other cell. Even string additions or simple arithmetic. To me that is very nonsensical and simply illogical and very frustrating. extremely frustrating.

I do realise that Excel says you cant do what I am adamant to try to do or get to work. But I am 100% sure it can be done. even with just VBA itself.

 

[Rick Rothstein]

My complete bad !! Thank you very much for replying! You are absoltely right. But Its a typo.

when Parm1 = C1&"Goodbye" - I get #errors and #na's or #cant excecute functions

anything else other then a cell in that parameter, it returns error, even if I put in a vlookup to lookup the same cell or any other cell. Even string additions or simple arithmetic. To me that is very nonsensical and simply illogical and very frustrating. extremely frustrating.

I do realise that Excel says you cant do what I am adamant to try to do or get to work. But I am 100% sure it can be done. even with just VBA itself.

Can you show us your UDF code? If not the whole code, at least show us the header (the first line... the one with the keyword Function in it).

 

[Spyros13]

I'm thinking of 2nd subroutine to evaluate the first parameter in the UDF as a cell expression (a normal function in cell ), first[i/], and then apply the 1st subroutine on That.

(which is what my original funtion is "doing" : its calling/operating on the first subroutine ) .

But I seem to need 2 subroutines operating on the function (form the function/on the function), to be able to do what I want to do. Namely, use the first parameters end-process results (be they if they are only referencing a single cell, or, doing anything else ) to get around this lack of functionality in this udf.

A. am i right and B. can that be done in vba

 

[Rick Rothstein]

I'm thinking of 2nd subroutine to evaluate the first parameter in the UDF as a cell expression (a normal function in cell ), first[i/], and then apply the 1st subroutine on That.

(which is what my original funtion is "doing" : its calling/operating on the first subroutine ) .

But I seem to need 2 subroutines operating on the function (form the function/on the function), to be able to do what I want to do. Namely, use the first parameters end-process results (be they if they are only referencing a single cell, or, doing anything else ) to get around this lack of functionality in this udf.

A. am i right and B. can that be done in vba

I think the problem is in your UDF, probably its header... see my request in Message #4 .

 

[shg]

Evaluate "mysubinmyudf(" & Evaluate(parm1.value(0, 0))

Properties apply to objects. Value is a property of Range object (among others). I don't know what you intended by the subscripts.

C1 & "some text" is not a range, it's a scalar Variant/String. It has no Value (or any other) property.

I can't tell what you're trying to usefully do.

 

[Spyros13]

Is this what you mean by udf header?

 Function myudfa (ByVal firstone As Variant, ByVal secone As Variant) As Variant

it does the same (i.e. not work other than when there is a strict cell reference in firstone (other then that indirect i managed to use to do nothing more than reference the cell) it doesnt work.

i think in my codes instance, i need to expand my function to evaluate the written out contents/expression (the actual expression) of firstone first, and then perform the 2nd subroutine. It will be tricky for me in any case, if it does work but I don't want to waste anyones or my own time on this further as all the experts say that isn't native to VBA and/it cant be done from VBA (without some next level module macros, timers, and stilllimited functionality. or using C or Python instead)

Shame.

p.s. its Tim Williams (from stackoverflow) & the WellsrCopyto/Copyfrom Sub/UDF.

There must be a way to make it do things to the cell you ideally want to (and in my case, change value of/do stuff with & to) 'move'. I'm not going to believe a word experts tell me about things being impossible to do (Because they never are). There must be a way (or many ways) that arnt that complicated to do this!

Subroutine to evalauate what is physically written in parm1first, followed by tim william's code to act on that (so store the result of the first subroutine in a variable (or object?), first, and then act on that). Is what I want to try.

 

[shg]

No one here has said it can't be done, since we don't know what you're trying to do.

A lucid concrete example would help.

 

[Spyros13]

Maybe Ive lost my head. & jumping the deep end, ( i dont mind its how I learn)

https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet

and my concrete try is here (or one of the many diverse versions of):

 'Function CopyCellContentstry22Y(ByVal copyfrom As Variant, ByVal copyto As Variant ByVal thirdnecc As Variant) As Variant ' 24/03/2019
Function CopyCellContentstry22Y(ByVal copyfrom As Variant, ByVal copyto As Variant) As Variant ' 24/03/2019 was as object ''copyWith As Action*[* will be the cell itself]) As Variant ' wooley talm to me. LL!
'Application.Volatile


copyfrom.Parent.evalute "evalparm22Y(" & copyfrom.Address(0, 0) & "," & copyto.Address(False, False) & ")"


copyfrom.Parent.Evaluate "CopyOvertry22Y(" & z.Object & "," & copyto.Address(False, False) & ")"
CopyCellContentstry22Y = "DONE :) "
End Function
'CopyOvertry2(CopyFrom(false,false),copyTo.Address(False, False))


'i need want another subroutine refrenced in the fucntion (so 2 in total). the one i want is a subroutine to
'evaluate copyfrom entry as (a formula of it own maybe) as result.
'so we are pasting the result of the evaluation of the entire 1st part into copy2




Private Sub evalparm22Y(ByVal copyfrom As Variant, ByVal copyto As Variant) 'as variant and as object
Dim z As Variant


'If CopyFrom.value > 1 (or <> cell.address(0,0) Then ' i was tryng to say iff its just a cell, then do as before,if theres an ooperation in it, then do something difrerent - do the opperation
'Then
    'copyTo.value = CopyFrom.value
    'Else
    'copyto=copyfrom
    
   Evaluate copyfrom.value = z.Object
   
   '''' thirdnecc.value = z
   
    ' 04;08 18032019 WAS : copyto.value = evaluate (copyfrom.value)
End Sub
Private Sub CopyOvertry22Y(ByVal copyfrom As Variant, ByVal copyto As Variant) 'as variant and as object


'If CopyFrom.value > 1 (or <> cell.address(0,0) Then ' i was tryng to say iff its just a cell, then do as before,if theres an ooperation in it, then do something difrerent - do the opperation
'Then
    'copyTo.value = CopyFrom.value
    'Else
    'copyto=copyfrom
    
    copyto.value = copyfrom.value ' 04;08 18032019 WAS : copyto.value = evaluate (copyfrom.value)
End Sub

Its a mess, please don't laugh. This is my 40th attempt . And I have different versions too. Many of them without the 2nd subroutine which are trying to do the same thing. without time stamps, timers, python, c or anything else. Even though we are nearing circular loop territory (which i don't fear, infact circular loops can work just as well as regular ones)