4 ms·

Microsoft want’s to kill VBA but customers won’t allow that. https://en.m.wikipedia.org/wiki/Visual_Studio_Tools_for_Office https://en.m.wikipedia.org/wiki/Visu

by kgwgk 8y ago

Microsoft want’s to kill VBA but customers won’t allow that. https://en.m.wikipedia.org/wiki/Visual_Studio_Tools_for_Office https://en.m.wikipedia.org/wiki/Visual_Studio_Tools_for_Offi... may make sense for developers, but users like the ability to do things in the “wrong way”.

cm2187 8y ago
VSTO isn't great. There was something called VSTA earlier, basically a little visual studio embedded in Office, you could do with any .net language what you could do with VBA. Unfortunately they canned it.
- kgwgk 8y ago
  Did VSTA for Excel ever exist? I can find only references to InfoPath (whatever it is, it seems it was part of Office for a while).
  - cm2187 8y ago
    No it never existed. But I believe that at one point it was the intention, it was meant to cohabit with VBA, and work essentially in the same way.
- clausok 8y ago
  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...

by kgwgk 8y ago

cm2187 8y ago

VSTO isn't great. There was something called VSTA earlier, basically a little visual studio embedded in Office, you could do with any .net language what you could do with VBA. Unfortunately they canned it.

kgwgk 8y ago
Did VSTA for Excel ever exist? I can find only references to InfoPath (whatever it is, it seems it was part of Office for a while).
- cm2187 8y ago
  No it never existed. But I believe that at one point it was the intention, it was meant to cohabit with VBA, and work essentially in the same way.
clausok 8y ago
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...