Execute a user-defined function into another cell VBA Excel -
i need automatize process execute various (a lot) user-defined function different input parameters.
i using solution of timer api found in i don't want excel add-in return array (instead need udf change other cells) .
my question following: "does can explain me how working?" if debug code in order understand , change need, go crazy.
1) let passing public function addtwonumbers 14 , 45. while inside addtwonumber, application.caller , application.caller.address chached collection (ok, easier vectors in order not bother type). application.caller kind of structured object can find function called string (in case "my_function"), example in application.caller.formula. !!! in collection mcalculatedcells can find result 59 stored.
2)ok, fair enough. pass through 2 udf routines, set timers, kill timers. inside afterudfroutine2 sub, mcalculatedcell(1) (the first -- , sole -- item of collection) has magically (??!?!?!!!??) obtained in text field result "59" , apparently command set cell = mcalculatedcells(1) (where on left have range , on right have ... don't know) able put result "59" variable cell afterward can write .offset(0,1) range property on cell right.
i understand point because give more task to inside single collection or able wait current task finished before asking new 1 (otherwise over-writing 59 other result). indeed read somewhere tasks scheduled api settimer wait callback execute before execute (or this).
as can see @ loss. really welcomed.
in following try more specific on (as whole) planning achieved. to more specific, have function
public function my_function(input1 string, field2 string) double /*some code */ end function i have (let's say) 10 different strings given field2.
my strategy follow:
1)i defined (with xlw wrapper c++ code) grid of input values 2)define string functions "my_function" various inputs 3)use nested api timer in link write functions in right cells formulas (not string anymore) 3)use macro build entire worksheet , retrieve results. 4)use xlw wrapper xll process further data. you may wonder why should pass through excel instead of doing in c++. (sometime ask myself same thing...) prototype my_function gave above has inside excel add-in need use , work inside excel.
it working pretty in case have 1 "instance" of my_function write give grid of input. can put inside same worksheet more grids, use api trick write various different my_functions different grids , make full calculation rebuild of entire worksheet obtain result. works. however, want give more tasks inside same api trick (because same grid of input need more calls my_function) not able proceed further.
after axel richter's comment ad other information @axel richter thank reply.
sorry that, surely wasn't clear purposes.
here try sketch example, use integer simplicity , let's my_function works pretty sum function of excel (even if being excel native function call sum directly vba sake of example).
if have these inputs: input1 = "14.5" vector of different values field2, instance (11;0.52;45139) , want write somewhere my_function (which makes sum of 2 values given input).
i have write down in cell =my_function(14.5;11), in other =my_function(14.5;0.52) , in third 1 =my_function(14.5;45139).
these input changes time need refresh data, cannot use directly sub (i think) and, in case, far understand, in writing directly without trick linked, obtain strings : '=my_function(14.5;0.52). once evaluated (for example full rebuild or going on written cell , make f2 + return) give me string "=my_function(14.5;0.52)" , not result. tried @ beginning use evaluate method works write 14.5+0.52, doesn't work function (nor user-defined function) used instead.
this "as far can understand". in case can enlighten me (and maybe show easier track follow), great.
so far comments correct in repeat simple point user-defined function called worksheet can return value, , other actions might inject values elsewhere worksheet calculation tree forbidden.
that's not end of story. you'll notice there add-ins, reuters eikon market data service , bloomberg excel, provide functions exist in single cell but write blocks of data onto sheet outside calling cell.
these functions use rtd (real time data) api, documented on msdn:
how set , use rtd function in excel
you may find link useful, too:
excel rtd servers: minimal c# implementation
however, rtd com servers outside excel.exe, have write them in language (usually c# or c++), , isn't question asked: want in vba.
but have, @ least, made token effort give 'right' answer.
now 'wrong' answer, , doing microsoft rather didn't do. can't call function, call subroutine or method function, , write secondary target using subroutine: excel follow chain , detect you're injecting values sheet calculation, , write fail.
you have insert break chain; , means using events, or timer call, or (as in rtd) external process.
i can suggest 2 methods work:
1: monitor cell in worksheet_change event:
private sub worksheet_change(byval target range)
dim strfunc string
strfunc = "nuketheprimarytargets"
if left(target.formula, len(strfunc) + 1) = strfunc
call nukethesecondarytargets
end if
end sub
alternatively...
2: use timer callback api:
however, i'm not posting code that: it's complex, clunky, , takes lot of testing (so i'd end posting untested code on stackoverflow). work.
i can give example of tested timer callback in vba:
using vba inputbox passwords , hiding user's keyboard input asterisks.
but unrelated task. feel free adapt if wish.
Comments
Post a Comment