I've been writing pricers in VBA for using with Excel. I want to have a function which will return an array of numbers-price, greeks etc. - and display them on an excel sheet. However I cannot get Excel to display the array. I wrote the following to try to test it:Function ArrayTester() As Integer()Dim myArray(6) As IntegerDim i As Integeri = 0DomyArray(i) = ii = i + 1Loop While i < 6ArrayTester = myArrayEnd FunctionWhen I call this function on the excel sheet- having highlighted a column of 6 cells and pressing Control+Shift+Enter , the 6 cells just fill up with zeroes. Can anyone tell me what I'm doing wrong?

all VBA functions return vectors as row vectors - my guess is that nesting transpose in excel around the function call will do the trick

Declare your array as Dim myArray(6,1)and access it as myArray(i,1)..or call Application.WorksheetFunction.Transpose before returning to the sheet

I would add a bool parameter, isVertical, which defaults to true. That way you can control the orientation, and default to whichever way is most likely to be used.

Another excel-related question...I have been developing a pricing application using Excel and VBA. I'm starting to get on to more complex products now and the computation time is going up correspondingly. Now I'm wishing I could write my pricers in C++ or Java instead... My question: is there any easy way of integrating a program written in C++ or Java with excel? Ideally one which would read in parameters from the sheet and return an excel array? Or is that beyond the scope of excel?Any suggestions appreciated, as always.

certainly not beyond the scope of excel .. you could write a XLL or DLL or put it all in a COM+ wrapper. get yourself the EXcel SDK.

You can get a cheap performance boost of (about) 100% simply by getting VB 6. It will compile your VBA code into an ActiveX DLL.Cheap, easy. Fun.

QuoteOriginally posted by: jebusI have been developing a pricing application using Excel and VBA. Whoa! Rewind, rewind. Writing pricing applications in VBA is not a good idea for numerous reasons which I wont enumerate now. Just trust me.QuoteMy question: is there any easy way of integrating a program written in C++ or Java with excel? Ideally one which would read in parameters from the sheet and return an excel array? Or is that beyond the scope of excel?There is an open source wrapper for writing exactly what you want here XLW which I found very easy to get working with Visual C++ 6.0 [which isnt free but if youre employed in a bank will probably be effectively free]. However I found it easy because I've been playing with computer software for over 20 years and know when to just skip things I dont understand properly and concentrate on the important stuff. I could easily envisage it taking a few days for someone to get to grips with this, but worth the effort.If you really want to understand the issue inside-out you can buy the book "Excel add-in development in C/C++ Applications in Finance" by Steve Dalton which has already been reviewed on this site favourably. I've only just got it and havent worked through it yet. It will certainly tell you more than you could possibly want to know on the topic [it's telling me more than *I* want to know and I'm quite interested in the subject].