Hi all,

I'm pretty sure that you can't do this. The things that you can change from an
Excel function being called from a worksheet recalculation are very limited - in
fact I think all you can do is return the result.

If you stop and think about it this is understandable, at the point you are
executing your function you have been invoked by Excel itself internally and it
won't allow you modify its environment. This holds for VB and VBA as well and is
not an APL related issue.


David

-----Original Message-----
From: dyalogusers@yahoogroups.com [mailto:dyalogusers@yahoogroups.com] On Behalf
Of David Barg
Sent: 07 November 2006 18:14
To: dyalogusers@yahoogroups.com
Subject: [dyalogusers] Re: Dyalog inside Excel - returning results

Michael,


thanks very much. I thought I HAD tried that and it had failed, but
I must have been making some other obscure mistake (real pain in the
neck to develop this stuff, switching back and forth between apl and
excel, closing excel each with each iteration to be able to "export"
the ws to the dll!)

Anyway, I am wondering whether you were able to overcome another
problem that you alluded to in your earlier posts. Namely, the
problem of returning the result from Dyalog via a "=function()" cell
entry with the result to be displayed in an excel range beyond the
calling excel cell.

In your previous post you mentioned that you had to highlight the
recieving range. I believe that in addition to highlighting the
range you have to establish the "=function()" entry with ctrl-shft-
enter key sequence. What happens if the dimensions of the result
are dependent on the data and you don't know ahead of time what
region to highlight?

My question is, and this is really a vba/excel question, is there
any way to programmatically fill in the appropriate excel cells from
a "=function()" call -- let's say using the calling cell as the
upper-left hand corner? I cannot seem to get VBA to alter ANY cell
outside the calling cell when executing from within a "=function()"
where the function is a user-defined function.

I know that I can programatically fill into excel willy-nilly if I
operate from some other user interface component (say a command
button) that I drop onto the worksheet, but I want to be able to do
this from within the "=function()" code.

any help would be appreciated....david

--- In dyalogusers@yahoogroups.com, "Michael Baas" <news-mb@...>
wrote:
>
> David,
>
> I am passing ranges and it works!
>
> If yo have the #.ServerNS.[range]-variable in APL, this is a
refence to the
> range that was passed to your function - not the values
themselves. You need
> to extract those, and you can do so by simply referring to the
> .Value2-property of that object. You then also need to take care
of handling
> empty cells (which come through as []NULL in Dyalog), but all that
is
> feasible. Just search the group-msgs for "Excel", there's a lot of
good info
> already there...
>
> Good luck ;)
>
> Michael
>
>
>
>
> _____
>
> Von: dyalogusers@yahoogroups.com
[mailto:dyalogusers@yahoogroups.com] Im
> Auftrag von David Barg
> Gesendet: Montag, 6. November 2006 21:39
> An: dyalogusers@yahoogroups.com
> Betreff: Re: [dyalogusers] Dyalog inside Excel - returning results
>
>
>
> Michael,
>
> You say that you are passing two ranges as arguments, but it seems
> to me that you are really passing in two individual cells, not
> really "ranges" of cells. I point this out because I am trying to
> pass multi-cell ranges from excel to APL and am having difficulty.
>
> Naturally, as APLers we would hope that a range, let's say "A1:C2"
> would come across as a 2x3 (or a 3x2), but that does not seem to
be
> the case. APL seems to receive an OBJECT of type #.ServerNS.
[range]
> and I don't know what to do with this on the APL side.
>
> Now, I know I can write a little VBA cover function that would
take
> the range and convert it into a VB variant array and pass THAT and
> then APL receives it in the manner we would hope for, but does
> anyone know of a way to handle this on the APL side? Ie. I'd like
> to write a dyalog function, let's say AVG that would do the
expected
> simple calculation for Averaging in APL and then call it from
excel
> with =Avg("A1:A44"), without looping on the VBA side.
>
> thanks in advance for any advice...david barg
>
> --- In dyalogusers@ <mailto:dyalogusers%40yahoogroups.com>
yahoogroups.com,
> "Michael Baas" <news-mb@>
> wrote:
> >
> > Tony,
> >
> > ok, you've confused me as well with your 3 msgs ;)
> >
> > Hope I don't miss a point by picking one of them to reply to.
> >
> > Actually I did not show the function-call in XL itself, but I
> passed two
> > ranges as arguments and get back a result corresponding to the
> size of these
> > ranges - which is much faster than calling the function cell-by-
> cell.
> >
> > Cheers
> >
> > Michael
> >
> > _____
> >
> > Von: dyalogusers@ <mailto:dyalogusers%40yahoogroups.com>
yahoogroups.com
> [mailto:dyalogusers@ <mailto:dyalogusers%40yahoogroups.com>
yahoogroups.com]
> Im
> > Auftrag von TonyC
> > Gesendet: Montag, 16. Oktober 2006 21:51
> > An: dyalogusers@ <mailto:dyalogusers%40yahoogroups.com>
yahoogroups.com
> > Cc: news-mb@
> > Betreff: Re: AW: [dyalogusers] Dyalog inside Excel - returning
> results
> >
> >
> >
> >
> >
> > ok, now i'm confused
> >
> > your writing a vb function that takes 3 [what we would call in
apl]
> > ''scaler''
> > arguments, and returns a ''scaler'' result. the vb function
passes
> the 3
> > arguments to apl, and apl calculates and returns the result
> >
> > then you are entering the vb function as a ''array'' call in XL
to
> process
> > multiple instances of the arguments . . . in other words you
hold
> down the
> > control key so the formula in any individual cell looks like
this:
> >
> > { vbfuntion(arg1,arg2,arg3)}
> >
> > the curly braces being XL's way of denoting an array call
> >
> > so if you have arguments
> > A B C
> > 1 | arg1| arg2| arg3
> > 2 | arg4| arg5| arg6
> > 3 | arg7| arg8| arg9
> >
> > why not pass the matrix a1:c3 [in XL terms], to the vb function
> [and
> > subsequently to APL] and have the function return a 1 by 3
column
> result . .
> > .
> > it must be quicker to pass the matrix all in one go than to pass
3
> seperate
> > rows of arguments and 3 seperate results back and forth?
> >
> > or have i missed the point?
> >
> > > --- Michael Baas <news-mb@dls- <mailto:news-mb%40dls-
planung.de>
> > planung.de> wrote:
> > >
> > > >
> > > > Aaarrrgh - when entring array-formulas in Excel, one has to
> highlight
> > the
> > > > target-range before, as the formula will put its result into
> the
> > selected
> > > > range!
> > > >
> > > > So this actually worked all the time, I just did not notice
it
> because
> > of
> > > my
> > > > error in entering the formula! :(
> > > >
> > > > Actually it can even be simplified to
> > > >
> > > > Public Function AdjustTD(ByVal value As Object, ByVal month
As
> Object,
> > > ByVal
> > > > country As String) As Variant
> > > >
> > > > Dim dtLink As Object
> > > > Set dtLink = CreateObject("dyalog.dtLink")
> > > > AdjustTD = dtLink.dtAdjustTD(month, value, country)
> > > >
> > > > Set dtLink = Nothing
> > > > End Function
> > > >
> > > >
> > > > Cheers
> > > >
> > > > Michael
> > > >
> > > >
> > > >
> > > > _____
> > > >
> > > > Von: dyalogusers@ <mailto:dyalogusers%40yahoogroups.com>
> yahoogroups.com
> > [mailto:dyalogusers@ <mailto:dyalogusers%40yahoogroups.com>
> yahoogroups.com]
> > Im
> > > > Auftrag von Michael Baas
> > > > Gesendet: Samstag, 14. Oktober 2006 17:57
> > > > An: dyalogusers@ <mailto:dyalogusers%40yahoogroups.com>
> yahoogroups.com
> > > > Betreff: AW: [dyalogusers] Dyalog inside Excel - returning
> results
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > Thanks, Richard - I really like your VB-advices, specially
the
> last one
> > ;)
> > > >
> > > > Actually I had started without the array-spec after the Dim,
> but I then
> > > only
> > > > got a result in the cell which had the formula, but as I
> entered this as
> > an
> > > > array-formula, it was supposed to return a larger result.
> > > > So I then tried to add explicit size-declaration (which I
also
> saw in
> > > > example-code for user-defined array-formulas) - but then I
> stumbled
> > because
> > > > of the error shown in my msg.
> > > > And the whole thing is a function and not a sub because it
is
> supposed
> > to
> > > > return a result. When using functions, you can actually write
> > > "=MyFn(a,b,c)"
> > > > into a cell of your spreadsheet and will execute the
function!
> > > >
> > > > Hmm, I'm booked for the OO-Workshop, but if anyone from
Dyalog
> could
> > > confirm
> > > > we will be covering such details, I'll do everything to
change
> workshops
> > ;)
> > > >
> > > > Cheers
> > > >
> > > > Michael
> > > >
> > > >
> > > > _____
> > > >
> > > > Von: dyalogusers@ <mailto:dyalogusers%40yahoogroups.com>
> yahoogroups.com
> > [mailto:dyalogusers@ <mailto:dyalogusers%40yahoogroups.com>
> yahoogroups.com]
> > Im
> > > > Auftrag von Richard Procter
> > > > Gesendet: Freitag, 13. Oktober 2006 23:54
> > > > An: dyalogusers@ <mailto:dyalogusers%40yahoogroups.com>
> yahoogroups.com
> > > > Betreff: Re: [dyalogusers] Dyalog inside Excel - returning
> results
> > > >
> > > >
> > > >
> > > > This looks like a good one to bring to Monday's "Office
> Automation"
> > > workshop
> > > > in Helsing�r.
> > > >
> > > > I'm just guessing, but maybe you should not declare "xx" as
an
> array.
> > (ie.
> > > > just use: Dim xx As Variant)
> > > > I notice that the CalcPayments/Loan example which ships with
> Dyalog APL
> > > > returns an APL array of loan payment numbers to a VB
variable
> which is
> > > > declared in this way (ie. not an explicit array), and that
> works OK. The
> > > > other main difference is that your VB code is a Function
> instead of a
> > "Sub"
> > > > (like the Loan example) - so maybe that causes a problem?
> > > >
> > > > My general VB-programming advice would be: start with
> something that
> > works
> > > > (like the Loan example); tweak it in steps to do what you
> want; test
> > > between
> > > > each step; stop tweaking one step before it stops
> > > working...cheers...richard
> > > >
> > > >
> > > > At 2006-10-13 02:23 AM, Michael Baas wrote:
> > > >
> > > >
> > > >
> > > >
> > > > Still having 'fun' writing an Excel-Addin ;)
> > > > So now I'm calling my Dyalog-fn through OLE, basically this
> all works
> > fine,
> > > > BUT my fn returns an array and when I try to assign this
array
> to the
> > > > VB-variable I get an error during compilation because I am
not
> allowed
> > to
> > > > assign to a 'Datenfeld' ('data field', I guess).
> > > >
> > > > My VB is this:
> > > >
> > > > Public Function AdjustTD( value As Object, month As Object,
> country As
> > > > String) As Variant
> > > > Dim dtLink As Object
> > > > Dim xx(42, 1) As Variant
> > > > Set dtLink = CreateObject("dyalog.dtLink")
> > > > xx = dtLink.dtAdjustTD(month, value, country)
> > > > AdjustTD = xx
> > > > Set dtLink = Nothing
> > > > End Function
> > > >
> > > > I understand VBhas an issue with the array, but is there
> anything I can
> > do
> > > > about that? I wouldn't really like to call my fn
individually
> for every
> > > > cell, if at all possible there should be one call only...
> > > >
> > > > Any comments?
> > > >
> > > > Cheers
> > > >
> > > > Michael
> > > >
> > > >
> > > >
> > >
> > >
> > > __________________________________________________________
> > > Being too poor to host one of those cool BlackBerry servers on
> > > his own personal Internet domain...
> > > ... this was arduously tapped out on the dialpad of Tony
Corso's
> > > cellphone
> > >
> >
>






Yahoo! Groups Links