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]

p.s. in old original black screen bbc basic or anywhere else really, it should be as easy as telling a machine, to process what you want first, and then process that result. I mean , in my head at least, that would be fundementally functoional in my programming language (where I ever develop one). Im old school basic, "Go to" , & Logo logic !!! leaving leon, going home.

I dont know, but that seems to be a very commonsense logical vivid memory for me. i,e, why cant things be so straughtforward?

 

[Spyros13]

sitting on a bench at moorfields eye hospital.

left my phone at home. leon was too distracting.

vlookup can take any argument in its first parameter. so why cant this udf do !?

just recorded a macro.

Sub Macro1vlop()'
' Macro1vlop Macro
'


'
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-23]C[-12],R[-25]C[-12]:R[-10]C[-10],3,0)"
    Range("AK457").Select
End Sub

use activecell.formula method? to basically say/do (paraphrasing - Activecell.formula = [copyfrom] in that first parameter.

or Activecell.formular1c1 over the udf

or Activecell.formular within the first parameter, and that entry is just being evaluated. ok im nuts. too much. night

Im addicted/hooked.

21:32 - NO! Im not nuts !! we can tell the udf to ... just lost it, I know what I was saying, its gone from the head .

Will need to learn reseach more until I do it. Its like a timer after all. the actuve cell method or evalute method will ''fly'' in the udf when entered/processed (you press return) and evaluate whatever it is youve writing in there.

.... and then process the udf.

Interesting.

 

[Rick Rothstein]

sitting on a bench at moorfields eye hospital.

left my phone at home. leon was too distracting.

vlookup can take any argument in its first parameter. so why cant this udf do !?

just recorded a macro.

Sub Macro1vlop()'
' Macro1vlop Macro
'
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-23]C[-12],R[-25]C[-12]:R[-10]C[-10],3,0)"
    Range("AK457").Select
End Sub

I presume the code is failing for you... what cell is active when you execute this code and it fails to work?

 

[Spyros13]

Thanks Rick.

AK457. 10 cells to the right of and 4 cells below of the udf macro1vlop range , Y432:AA447. (post # 22)

No It works (the macro in post #22 i just did. its as a test - the lookup - to give me an idea of what i could do to macro udf in post #20 . or indeed the original question and the problem which you explained so well was becuase there could be no parent to parameter 1 = range&string , or parameter 1= (function of range).)

 

[Spyros13]

so its this code that doesnt work, I need to read up on what im doing. I bet the problem is my lack of vba programming.

        Function CopyCellContents3z(ByVal CopyFrom As String, ByVal copyTo As Variant) 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)
        Dim zed As Range
        zed = Evaluate(CopyFrom)
        
            copyTo.value = zed.value
        End Sub

or

         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

- tonight, add byval to zed in both version. And try Evaluate(CopyFrom) again properly [learn some coding around it * it might require more levels]

and try a third sub, dedicsted to evaluating just parameter 1.

22:46 also noticed i have

 copyfrom as string

in both here. Will change to

 copyfrom as Variant.

(because ideally it could should be anything [i would like])

Notes for myself.

 

[Spyros13]

https://www.mrexcel.com/forum/excel-questions/189056-vba-equivilant-eval-execute.html

Running code in run time. (no possible with vba)

So, another try is , if I have a subroutine macro running on the sheet, it might detect my udf being entered and act upon the first parameter in full (no matter what I write in it) and excecute that, save it somewhere and apply the 2nd parameters command on that.

because what i want is not native ti VBA ofcourse. so must pay buck to do course to learn to do this. basically i want to enhance VBA's functionality . To enable people to change other cells , change things via worksheet or a version of moving the mouse.

so people could write in excel sheet, in b1, =changesheet(whetever you want to do to A1, copied to z99 or wherever you want, optional parameters: do further movements and actions on other cells )

At the moment i cant even do whatever i want to a1 in this function in this this cell b1 (unless i do it in another cell before hand and use the copycell udf on that, which removes and doesnt have the functionality I ideally want).

 

[Spyros13]

I do now realise its a circular argument (request) im making (when it comes to and with respect to vba/excel) but the fellows thread and question he asked (davestewart I believe is his name), in post #26 I pasted is similar to mine in spirit and pre-expected output and functionality.

If Microsoft ever allowed or developed such an Excel, it would no longer simply be the excel we all know and alot will change.

' what could do this? Java, C or Machine Code? (Excel is made with C isnt it?). So the functionality of what I want must be available in C, or to use C to do it. I guess.

 

[Spyros13]

I think this udf here at https://stackoverflow.com/questions/38187294/parsing-the-parameters-of-a-function can help:

although fact has to be a string

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Function GetParameterN(func AsString, n AsLong)AsString
    Dim args AsVariant
    Dim safeArgs AsString
    Dim c AsString
    Dim i AsLong, pdepth AsLong

    func = Trim(func)
    i = InStr(func,"(")
    args = Mid(func, i +1)
    args = Mid(args,1, Len(args)-1)

    For i =1To Len(args)
        c = Mid(args, i,1)
        If c ="("Then
            pdepth = pdepth +1
        ElseIf c =")"Then
            pdepth = pdepth -1
        ElseIf c =","And pdepth =0Then
            c ="[[,]]"
        EndIf
        safeArgs = safeArgs & c
    Next i
    args = Split(safeArgs,"[[,]]")
    GetParameterN = Trim(args(n -1))
EndFunction

</code>

 

[Spyros13]

Yep, I guess my or any answer to this question would have to be UDF Function CopyCellContents (post #25 ) COMBINED with UDF Function GetParameterN (post #28 )

So needs a Re-writing CopyCellcontents with the Functionility of GetParametrN embedded into it. That way you could make the Cells 'fly' and do things to them while flyig, so when they 'land' they land as you want.

Yes Im asking. Any takers. (I noticed more people viewed the thread since we wrote it), so thought to say that incase there is someone who could

 

[Spyros13]

I think I have to delete this post for privacy reasons.