Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Applying programming language research ideas to transform spreadsheets

microsoft.com

Post reply on HN
Print view View original

111–120 of 129 posts

Re: Applying programming language research ideas to transform spreadsheets

#111

Post by kgwgk »

cm2187 wrote: post #109
kgwgk wrote: post #105

Earlier quoted context omitted.

Microsoft want’s to kill VBA but customers won’t allow that. 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”.

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.

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).

Re: Applying programming language research ideas to transform spreadsheets

#112

Post by cm2187 »

kgwgk wrote: post #111
cm2187 wrote: post #109

Earlier quoted context omitted.

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.

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).

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.

Re: Applying programming language research ideas to transform spreadsheets

#113

Post by ohazi »

Types and units would be super useful. I recently lost a few days of work after realizing that a part analysis spreadsheet I was working on for evaluating low noise op-amps was producing incorrect results because I accidentally divided by 1000 instead of a million in a unit conversion somewhere.

I knew the starting and ending units, but Google calculator and Wolfram alpha have made me sloppy.

Re: Applying programming language research ideas to transform spreadsheets

#114

Post by selimthegrim »

ohazi wrote: post #113

Types and units would be super useful. I recently lost a few days of work after realizing that a part analysis spreadsheet I was working on for evaluating low noise op-amps was producing incorrect results because I accidentally divided by 1000 instead of a million in a unit conversion somewhere. I knew the starting and ending units, but Google calculator and Wolfram alpha have made me sloppy.

Hey Oren, (I was a few years ahead of you, in Ruddock), you might be interested in my friend’s product I mention below.

Re: Applying programming language research ideas to transform spreadsheets

#115

Post by clausok »

cm2187 wrote: post #109
kgwgk wrote: post #105

Earlier quoted context omitted.

Microsoft want’s to kill VBA but customers won’t allow that. 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”.

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.

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.

Re: Applying programming language research ideas to transform spreadsheets

#116

Post by cm2187 »

clausok wrote: post #115
cm2187 wrote: post #109

Earlier quoted context omitted.

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.

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 t…

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.

Re: Applying programming language research ideas to transform spreadsheets

#117

Post by clausok »

cm2187 wrote: post #116
clausok wrote: post #115

Earlier quoted context omitted.

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 t…

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…

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?

Re: Applying programming language research ideas to transform spreadsheets

#118

Post by cm2187 »

clausok wrote: post #117
cm2187 wrote: post #116

Earlier quoted context omitted.

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…

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?

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.

Re: Applying programming language research ideas to transform spreadsheets

#119

Post by keithalewis »

cm2187 wrote: post #116
clausok wrote: post #115

Earlier quoted context omitted.

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 t…

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…

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/hand...

Re: Applying programming language research ideas to transform spreadsheets

#120

Post by marcosdumay »

ginko wrote: post #101
thibautg wrote: post #2

The ideal spreadsheet should be a mix of Excel, Geogebra, reactive Jupyter Notebook (like ObservableHQ or Vue.js computed properties), programmable in Python, C#, JavaScript and/or other languages with the ability to embed third-party widgets like maps or custom charts. And decent data connectors with SQL-like query syntax (including JOINs).

It's kind of telling that all the sibling posts suggest SaaS options. The ironic thing about MS Office these days is that, even if it's still proprietary, you at least get to run it locally as a native application and keep control of your data.

Well, as long as the over the net authentication works so your local copy of Office will let you use it.
Post reply on HN