4 ms·

Especially with ExcelDna, it's hard to make an argument for using VSTO. And even with ExcelDna (i.e., an xll written in F#\C#), I find there are still niche are

Especially with ExcelDna, it's hard to make an argument for using VSTO. And even with ExcelDna (i.e., an xll written in F#\C#), I find there are still niche areas where vba is effective, particularly when working with the Excel object model. The split I like is to have vba orchestrate interactions with the object model, Sheets, Books, Ranges, Range.Find(), etc. So for example, have your F#\xll code parse "60d" into the last 60 trading days, and then use vba to select those days in Excel's autofilter.

cm2187 8y ago
Possibly stating the obvious, but you know that you can manipulate the excel object model from ExcelDNA in the same way that you can with VBA (thanks to VSTO!). Add a reference to Microsoft.Office.Interop.Excel, and get a handle on the Excel Application object using ExcelDna.Integration.ExcelDnaUtil.Application. Alternatively you can use the excellent NetOffice.Excel (instead of Office.Interop.Excel), which will have less functionalities but with better version support.
- clausok 8y ago
  Where has NetOffice.Excel been hiding?! Thank you for that. What I've found with interop is there's a little more debuggability/edit-and-continue friction that can accumulate into a large loss of time during development. Maybe that loss is offset, or more than offset, by doing the interop with a vastly superior language. I'm not sure. Any thoughts about this trade-off?
  - cm2187 8y ago
    I don't know. I find VBA to lack so many modern features (type inference, linq, generics, etc) that I favor using a more modern language over edit&continue. VBA and interop have nearly the same syntax so you can always toy in VBA and then implement it in .net. NetOffice sometimes has a different syntax so is a bit more iterative.
- keithalewis 8y ago
  If you know C++ and want to embed C++ objects in Excel you can use a library I wrote. Here is an example of how single inheritance works: https://github.com/keithalewis/xll12/blob/master/sample/handle.cpp https://github.com/keithalewis/xll12/blob/master/sample/hand...

cm2187 8y ago

Possibly stating the obvious, but you know that you can manipulate the excel object model from ExcelDNA in the same way that you can with VBA (thanks to VSTO!). Add a reference to Microsoft.Office.Interop.Excel, and get a handle on the Excel Application object using ExcelDna.Integration.ExcelDnaUtil.Application. Alternatively you can use the excellent NetOffice.Excel (instead of Office.Interop.Excel), which will have less functionalities but with better version support.

clausok 8y ago
Where has NetOffice.Excel been hiding?! Thank you for that. What I've found with interop is there's a little more debuggability/edit-and-continue friction that can accumulate into a large loss of time during development. Maybe that loss is offset, or more than offset, by doing the interop with a vastly superior language. I'm not sure. Any thoughts about this trade-off?
- cm2187 8y ago
  I don't know. I find VBA to lack so many modern features (type inference, linq, generics, etc) that I favor using a more modern language over edit&continue. VBA and interop have nearly the same syntax so you can always toy in VBA and then implement it in .net. NetOffice sometimes has a different syntax so is a bit more iterative.
keithalewis 8y ago
If you know C++ and want to embed C++ objects in Excel you can use a library I wrote. Here is an example of how single inheritance works: https://github.com/keithalewis/xll12/blob/master/sample/handle.cpp https://github.com/keithalewis/xll12/blob/master/sample/hand...