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]

Couldnt we ammed the subroutine part to include something like:

Dim r As [# of argumennts in the fucntion Parameter, so it would be 2 really)
codes = Array(the array being 2 items,; each items a string of the fucntions arguments)
life = Array(evaluation parameter 1, evaluation parameter 2)

for each r ,

evaluate it it

next r

copyto.value=codes
copyfrom.value=life

copyto.value=life.value

end sub



im thinking arrays, becuase no matter what I do to the subroutine, it doesnt simply evaluate the 1st parameter. maybe it will do so if we calculate (execute the 1st parameter), store it 'somewhere', and evaluate that, even if the evaluation just ends up being the range.

so, i.e. range&"B" , such simple thing, it which can be evaluated in a subroutine, for some reason isnt recognised by the subroutine in the the code.

 

[Spyros13]

becuase, in a single cell, =GetParameterN(GetFormula(AJ5),1) WORKS to getting the formula in AJ5 as a string, and extracting the 1st (in this case) parameter of it.

It works for any # parameter. GetParameterN(Getformula(CELL),N) where N is the nth parameter of the fucntion in CELL.

AJ5 can be ANY Formula and GetParameterN with Getformula works. Ive tried it on any complicated function, and the combination of =GetParameter and GetFormula give me the exact parameter/argument im looking for. Which can be evaluated in excel

Problem is , when the code for GetParamterN and GetFormula are simply used the subroutine or UDF of CopyCellsOver , they don't!!!

 

[Spyros13]

I think its done as far as I am concerned for now Rick Rothenstein . Thank you for your encouragement before-hand.

Function MOVEME25(a As Variant, b As Variant, Optional CELLR As Range, Optional cellq As Range) '21/05/2018 works copied to ar4
 Dim WTVR1 As Variant '' ''20/05/2019''
 Dim WTVR2 As Variant
 Dim P As String
 Dim P1 As String
 Dim bb As String
 Dim bb1 As String
 
 'x' a = Evaluate(a)
P = Chr(34) & a & Chr(34)
P2 = Chr(34) & [P] & Chr(34)


bb = Chr(34) & b & Chr(34)
bb1 = Chr(34) & [bb] & Chr(34)


'P2 = Chr(34) & "'''" & [P] & "'''" & Chr(34)
'p1 = Chr(34) & p & Chr(34)
    
''WTVR1 = "MOVEUS1(" & Application.Caller.Offset(0, 2).Address(False, False) & "," & Chr(34) & P2 & Chr(34) & "," & b & ")"
   WTVR1 = "MOVEUS110(" & Application.Caller.Offset(0, 2).Address(False, False) & "," & [P2] & "," & b & ")"
Evaluate WTVR1


    
WTVR2 = "MOVEUS220(" & Application.Caller.Offset(0, 1).Address(False, False) & "," & [P2] & "," & [bb1] & ")" ' used or be adjacent - maybe redo rhat pr put a GO TO sub. '' ''20/05/2019''
    
Evaluate WTVR2


wtvr3 = "CopyFrom.Parent.Evaluate CopyOver2346(" & CELLR.Address(False, False) & "," & cellq.Address(False, False) & ")" ''''20190531 1929
    Evaluate wtvr3
    
    wtvr4 = "CopyFrom.Parent.Evaluate CopyOver2344(" & CELLR.Address(False, False) & "," & cellq.Address(False, False) & ")" ''''20190531 1929
    Evaluate wtvr4
    
MOVEME25 = "Hello world       " & " / " & WTVR1 & " / " & WTVR2


' DO AS WHATVER = "MOVEUS3(" APPLICATION.CALLER.OFFSET(THE ROW & COLUMN IE CELL YOU REFERENCES IN a as variant (copy from)'
    'with ="" in sub 30052019 19:28
    
    'CopyFrom.Parent.Evaluate "CopyOver2(" & CELLR.Address(False, 1) & "," & CELLR.Address(False, False) & ")"  ''''2019050 1929
                        
                                                
                        
End Function


Private Sub MOVEUS110(CELL1 As Range, G1 As Variant, G2 As Variant)


    '[ak333] = a




    CELL1 = Chr(34) & G1 & Chr(34) & "B" & "//" & G2




End Sub
    


Private Sub MOVEUS220(CELL2 As Range, G3 As Variant, G4 As Variant)
   
    CELL2 = Chr(34) & G3 & Chr(34) & "<>" & G4
    
End Sub


'' with chr(34) arond the p's and a's in sub or fucntion changes behavior. thinking of doing if a is string, then a=x , x as string, if not kep as variant
''27/05/2019 :(


'''''30/05/2019 .....'''''''




'------------------------------------------------------------------------------- ADD THIS 30052019 -------------------------------
'private sub Movus3(cellfrom as range, cellto as range)
'End Sub






Private Sub CopyOver2346(CopyFrom As Range, copyTo As Range) ''''2019050 1929
   '' copyTo.Value = CopyFrom.Value ''''2019050 1929
   
   ''CopyFrom.Value = ""
   Application.Calculation = xlCalculationManual
  copyTo.Value = CopyFrom.Value
  
  Application.Calculation = xlCalculationManual
''Application.Wait (Now + TimeValue("00:00:01"))
  
  ''CopyFrom.Value = ""
   
End Sub ''''2019050 1929




Private Sub copyover2344(CopyFrom As Range, copyTo As Range)
Application.Calculation = xlCalculationManual
CopyFrom.Value = ""
Application.Calculation = xlCalculationManual
End Sub

&

Function MOVEME27(a As Variant, b As Variant, Optional CELLR As Variant, Optional cellq As Variant) '21/05/2018 works copied to ar4


'' 03/06/2019 23:30 was cellr as range , cellq as range - changed to variants
 Dim WTVR1 As Variant '' ''20/05/2019''
 Dim WTVR2 As Variant
 Dim P As String
 Dim P1 As String
 Dim bb As String
 Dim bb1 As String
 Dim A1 As Long
 Dim A2 As Long
 
 Dim c As String
 
 'x' a = Evaluate(a)
P = Chr(34) & a & Chr(34)
P2 = Chr(34) & [P] & Chr(34)


bb = Chr(34) & b & Chr(34)
bb1 = Chr(34) & [bb] & Chr(34)


c = Chr(34) & CELLR & Chr(34)
f = Chr(34) & callq & Chr(34)




'P2 = Chr(34) & "'''" & [P] & "'''" & Chr(34)
'p1 = Chr(34) & p & Chr(34)
    
''WTVR1 = "MOVEUS1(" & Application.Caller.Offset(0, 2).Address(False, False) & "," & Chr(34) & P2 & Chr(34) & "," & b & ")"
   WTVR1 = "MOVEUS11h(" & Application.Caller.Offset(0, 2).Address(False, False) & "," & [P2] & "," & [bb1] & ")"
Evaluate WTVR1


    
WTVR2 = "MOVEUS22h(" & Application.Caller.Offset(0, 1).Address(False, False) & "," & [P2] & "," & [bb1] & ")" ' used or be adjacent - maybe redo rhat pr put a GO TO sub. '' ''20/05/2019''
    
Evaluate WTVR2


A1 = cellq.Row
A2 = cellq.Column


CELLRR = Chr(34) & CELLR & Chr(34)
CELLRR1 = Chr(34) & [CELLRR] & Chr(34)
cellqq = Chr(34) & cellq & Chr(34)
cellqq1 = Chr(34) & [cellqq] & Chr(34)




''wtvr3 = "CopyFrom.Parent.Evaluate CopyOver234h(" & c & "," & f & ")" ''''20190531 1929
wtvr31 = "MOVEUS33h(" & Application.Caller.Offset(A1 - ActiveCell.Row, A2 - ActiveCell.Column).Address(False, False) & "," & [CELLRR] & "," & [cellqq] & ")"


    Evaluate wtvr31
    
MOVEME27 = "Hello world       " & " / " & WTVR1 & " / " & WTVR2 & "\\\\\/////" & wtvr31 & "\\\\\/////---" & ActiveCell.Row - A1 & "//////---" & ActiveCell.Column - A2


' DO AS WHATVER = "MOVEUS3(" APPLICATION.CALLER.OFFSET(THE ROW & COLUMN IE CELL YOU REFERENCES IN a as variant (copy from)'
    'with ="" in sub 30052019 19:28
    
    'CopyFrom.Parent.Evaluate "CopyOver2(" & CELLR.Address(False, 1) & "," & CELLR.Address(False, False) & ")"  ''''2019050 1929
                        
                                                
                        
End Function


Private Sub MOVEUS11h(CELL1 As Range, G1 As Variant, G2 As Variant)


    '[ak333] = a




    CELL1 = Chr(34) & G1 & Chr(34) & "B" & "//" & G2




End Sub
    


Private Sub MOVEUS22h(CELL2 As Range, G3 As Variant, G4 As Variant)
   
    CELL2 = Chr(34) & G3 & Chr(34) & "<>" & G4
    
End Sub


'' with chr(34) arond the p's and a's in sub or fucntion changes behavior. thinking of doing if a is string, then a=x , x as string, if not kep as variant
''27/05/2019 :(


'''''30/05/2019 .....'''''''




'------------------------------------------------------------------------------- ADD THIS 30052019 -------------------------------
'private sub Movus3(cellfrom as range, cellto as range)
'End Sub


Private Sub moveus33h(cell3 As Range, CopyFrom As Variant, copyTo As Variant) ''''2019050 1929 ''' 03062019 change ema back to as Range here. :)
   '' copyTo.Value = CopyFrom.Value ''''2019050 1929
   
   ''CopyFrom.Value = ""
   
   cell3 = Chr(34) & CopyFrom & Chr(34) 'Chr(34) & CopyFrom & Chr(34)
   
End Sub ''''2019050 1929

The key I feel was in wrapping the perameter / argument in char(34) 's and the evaluating that (and i did an evaluation of that too to be sure once i saw it was working/getting close.

Its all abour Chr(34) or Char(34) !! How powerful double quote wrapping is !

 

[Spyros13]

screen shots to follow.. when i edit this message. trying to get a screen shot in here of the excel sheet i did the work on/in.