Page 2 of 8

Excel tricks

Posted: October 8th, 2012, 12:48 pm
by tagoma
on social.msdn.microsoft.com local experts confirm what i read elsewhere on the internet that is one can not change part of an array in Excel.in the present context, i wish to enumerate classes in QuantLibXL modules (=ohListEnumeratedClasses, =ohEnumeratedType, ....) .i thus don't know beforehand the length of the required array.say i arbitrarily create a 20-cell length array, but that the latter happens to be too long.if i try to delete the rows that contain no values, a dialog box say i can not change part of an array.is it really impossible?

Excel tricks

Posted: October 8th, 2012, 1:07 pm
by daveangel
QuoteOriginally posted by: edouardon social.msdn.microsoft.com local experts confirm what i read elsewhere on the internet that is one can not change part of an array in Excel.in the present context, i wish to enumerate classes in QuantLibXL modules (=ohListEnumeratedClasses, =ohEnumeratedType, ....) .i thus don't know beforehand the length of the required array.say i arbitrarily create a 20-cell length array, but that the latter happens to be too long.if i try to delete the rows that contain no values, a dialog box say i can not change part of an array.is it really impossible?cant you change the ROWS and COLUMNS in the XLOPER for the array ?

Excel tricks

Posted: October 8th, 2012, 3:00 pm
by MattF
Implement a function that returns the size of the data. (I prefer writing to variables passed by reference as it extends to multi-dimensions rather than using return.) Now you know the size of data that will be returned you can create a suitably sized range in advance.

Excel tricks

Posted: October 9th, 2012, 1:25 pm
by Govert
There are some workarounds for the array resizing problem - they're ugly but can be made fairly reliable.Basically you first check whether the calling range is the right size. If not then schedule a macro to run after calculation is complete, and have the macro change the size of the array formula, causing the function to be evaluated again. On recalculation the caller is now the right size, and everything calms down.The main implementation issue is to schedule the macro reliably. Your best entry back into Excel is to use the COM interface to call Application.Run, but this might fail if Excel is 'busy', e.g. if a user is editing a cell, or pressing down on the mouse button. So you need to retry if you get errors ....An implementation in C# based on Excel-DNA is described here http://excel-dna.net/2011/01/30/resizin ... lt-arrays/, and is also a C# and VB.NET sample in the Excel-DNA distribution.To be clear, you could take the Excel-DNA files (the .dna file and a renamed copy of the ExcelDna.xll host) and that would give you a standalone add-in with a =Resize(...) UDF that can wrap any array UDF and resize it automatically. The UDF returning an array need not be implemented in .NET - it can be in a C++ .xll or in VBA or whatever.-GovertExcel-DNA - Free and easy .NET for Excel

Excel tricks

Posted: October 9th, 2012, 7:42 pm
by Darou
Try Pivot Tables: Back-testing of trading strategies with MS Excel pivot table

Excel tricks

Posted: October 9th, 2012, 7:46 pm
by Hansi
That post is using the word 'easiest' incorrectly....

Excel tricks

Posted: October 21st, 2012, 7:41 am
by tagoma
do you know this one?to hide the content (value, formula) of a cell, set the following custom format: ;;;tricky isn't it?

Excel tricks

Posted: October 23rd, 2012, 3:43 pm
by DevonFangs
Also, a question to the world: who the hell uses automatic recalculation?

Excel tricks

Posted: October 23rd, 2012, 5:30 pm
by Hansi
QuoteOriginally posted by: DevonFangsAlso, a question to the world: who the hell uses automatic recalculation?People that correctly use spreadsheet for small tasks and data display and keep expensive calculations outside of a spreadsheet?

Excel tricks

Posted: October 23rd, 2012, 5:46 pm
by tagoma
QuoteOriginally posted by: HansiQuoteOriginally posted by: DevonFangsAlso, a question to the world: who the hell uses automatic recalculation?People that correctly use spreadsheet for small tasks and data display and keep expensive calculations outside of a spreadsheet?using automatic recalculation can sometimes saves life. but, that can be boring as well (=rand())

Excel tricks

Posted: October 23rd, 2012, 7:13 pm
by DevonFangs
QuoteOriginally posted by: HansiQuoteOriginally posted by: DevonFangsAlso, a question to the world: who the hell uses automatic recalculation?People that correctly use spreadsheet for small tasks and data display and keep expensive calculations outside of a spreadsheet?So the answer is: in finance, nobody.

Excel tricks

Posted: October 23rd, 2012, 7:55 pm
by Hansi
QuoteOriginally posted by: DevonFangsQuoteOriginally posted by: HansiQuoteOriginally posted by: DevonFangsAlso, a question to the world: who the hell uses automatic recalculation?People that correctly use spreadsheet for small tasks and data display and keep expensive calculations outside of a spreadsheet?So the answer is: in finance, nobody.I don't think you understand how trivial most the crap people in finance do all day is.

Excel tricks

Posted: October 23rd, 2012, 8:00 pm
by DevonFangs
QuoteOriginally posted by: HansiQuoteOriginally posted by: DevonFangsQuoteOriginally posted by: HansiQuoteOriginally posted by: DevonFangsAlso, a question to the world: who the hell uses automatic recalculation?People that correctly use spreadsheet for small tasks and data display and keep expensive calculations outside of a spreadsheet?So the answer is: in finance, nobody.I don't think you understand how trivial most the crap people in finance do all day is.So it is rather: in quant finance, nobody?