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]

 

[Spyros13]

so what I want to enable this code to be able to do and take, is any normal function that you would expect to normally carry out in Excel, and evaluate it in Parameter 1. (and if it cannot, return a #value , as would be normal), and then export that result to the cell you want to perform the action to from your udf,

Think of it as performing any action on a a cell, c1. and transporting that to g7 for instance, in that UDF , as opposed to


Stripped of comments to make better reading .

'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.value & "," & copyto.Address(False, False) & ")"
CopyCellContentstry22Y = "DONE :) "
End Function




Private Sub evalparm22Y(ByVal copyfrom As Variant, ByVal copyto As Variant) 'as variant and as object
Dim z As Variant
  
   z = [copyfrom.value] = z
   '''' 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


    copyto.value = copyfrom.value ' 04;08 18032019 WAS : copyto.value = evaluate (copyfrom.value)
End Sub

 

[Rick Rothstein]

...This is my 40th attempt.

It is hard to answer your original question when you do not show us the code it was based on. This being your 40th attempt, I have no idea where anything stands with relation to your original question.

 

[Spyros13]

a version of it that works, from Tims basic UDF or wellsr basic udf, I was able to :

=MID(SUBSTITUTE(GetFormula(AV384),"=",""),FIND("(",SUBSTITUTE(GetFormula(AV384),"=",""),1)+1,FIND(",",SUBSTITUTE(GetFormula(AV384),"=",""),1)-FIND("(",SUBSTITUTE(GetFormula(AV384),"=",""),1)-1) so AV399 equalled AN379

if AV385 =CopyCellContentstry2(AN379,AW378)

and again that again to somewhere else.

Here is the UDF working (on a basic level of code as in wellsr / Tim Williams original self posting thread) with Indirect and Vlookup , but obviously it does work as the vlookup only works internally to serve the indirect which serves the code. =CopyCellContents4(INDIRECT("ag"&VLOOKUP(31,AI395:AK397,3,0)+105),AW396)&"bbb"

I want the function to work with vlookups or any other function really, whether they serve the range object or not. So they are add fucntionality the udf (tell it where to do and what to work on.)

 

[Rick Rothstein]

Here is the UDF working (on a basic level of code as in wellsr / Tim Williams original self posting thread) with Indirect and Vlookup , but obviously it does work as the vlookup only works internally to serve the indirect which serves the code.

Code... we need to see the original UDF code, not an example of it being used.

 

[Spyros13]

Function CopyCellContentstry22X(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 
'Application.Volatile
'copyfrom.Parent.evalute "evalparm22Y(" & copyfrom.Object & "," & copyto.Address(False, False) & ")"


copyfrom.Parent.Evaluate "CopyOvertry22X(" & [copyfrom] & "," & copyto.Address(False, False) & ")"
CopyCellContentstry22X = "DONE :) "
End Function


Private Sub CopyOvertry22X(ByVal copyfrom As Variant, ByVal copyto As Variant) 'as variant and as object


    copyto.value = [copyfrom] ' 04;08 18032019 WAS : copyto.value = evaluate (copyfrom.value)
End Sub

Im pleased to say the following (above) WORKS. But it doesn't do and cant do anything further then work on a single cell (or range is that what you call it?) .

Parameter 1 cannot be an addition or something you write in it (such as a string like "sunshine") or anything else, but a reference to a cell.

This is why I was trying the evaluations (of evaluations). (so that parameter 1 could be anything that you, within reason, wanted it to be)

 

[Spyros13]

Ive been saying (earlier in the thread) that everyone (experts with 40+ years experince with excel , VBA and others) say its imposible to develop this further with VBA alone (as a normal UDF and sub) as per all the experts who this code came from. and that any roundabout solution ive considered in my head and the researches has also been told by equal experts with over 40x10 years experience of programming in every language, that such an ability is not native or inherent in VBA (as it is with Data science 'king' Python, or C or others), therefore dont try or give up.

*BUT* no matter what the experts say, I still think, if i learned enough VBA , that it isnt (impossible to do with just a UDF and a SUB alone).

Theres gotta be a non time stamp, non-change worksheet non-workaround workaround for this. ! I Know there must be !! I cant explain how I know, but I know!!

 

[Spyros13]

.. .As there ALWAYS (and that "Always" is an understatement) is.!

Have a good night.

 

[Rick Rothstein]

copyfrom.Parent.Evaluate "CopyOvertry22X(" & [copyfrom] & "," & copyto.Address(False, False) & ")"

...But it doesn't do and cant do anything further then work on a single cell (or range is that what you call it?) .

The reason it can only work on a single cell is the above statement... when you say copyfrom.parent, copyfrom can only be a cell reference (a text string, what you said you wanted to pass in, cannot have a Parent).

 

[Spyros13]

OMG!!!! THANK YOU!!!

The reason it can only work on a single cell is the above statement... when you say copyfrom.parent, copyfrom can only be a cell reference (a text string, what you said you wanted to pass in, cannot have a Parent).

..... Amazing.

Is there any code i can do to it to make it evaluate copyfrom 'as is (what you - or i rather - would expect a cell to output if I did do an addition, a sum, or strng in it or a lookup?)' [ greedy question]

but 100 Thank you's anyway because you taught me something and the big penny dropped !!!

 

[Spyros13]

I'm still trying on this, in the few minutes a day I genuinely have free.

But thank you Rick Rothstien for the biggest penny drop / learning curve experience of VBA i have experienced since my first ''pull data from internet macro'' (2 i was going to say) but its actually 6 years ago.

Im eating in leon. Should go home soon and try on it again.

 Function CopyCellContents3z(ByVal CopyFrom As String, ByVal copyTo As Variant, zed()) As Variant
Dim zed As Range
zed = Evaluate(CopyFrom) 'do from sub ONLY? maybe?
 
'[CopyFrom]
  zed.Parent.Evaluate "CopyOverz3(" & zed.Address(0, 0) & "," & copyTo.Address(False, False) & ")"
' i realise that zed still has to be a range - a cell - (with this particulr method , parent evaluate (address,addres) here in this form.
' but afterall, i am trying to force the udf to evaluate, run, compile and calculate commands ithin the first parameter
' i will have a go at worksheet evaluate or doing something like ''compile'' as in LISP
 
CopyCellContents3z = "ok"
 
End Function
 
Private Sub CopyOverz3(CopyFrom As Variant, copyTo As Variant, zed())
Dim zed As Range
zed = Evaluate(CopyFrom)
 
    copyTo.value = zed.value
End Sub

https://www.quora.com/How-can-you-input-the-value-from-a-cell-to-a-Macro-in-Excel
&

https://stackoverflow.com/questions/39579638/custom-eval-function-in-excel
&
I realise this isn’t VBA, but Compile just make sense.!!
https://stackoverflow.com/questions/15669675/evaluate-the-arguments-of-a-macro-form

Its just notes for me !! I will look at them all again later !!