4 ms·

It isn't the language for everything, but I've found you can do so much with a spreadsheet and a little VBA. Optimization, graphics, math...whatever. I also fi

It isn't the language for everything, but I've found you can do so much with a spreadsheet and a little VBA. Optimization, graphics, math...whatever. I also figure the company you refer to has some real benefit by focusing on one technology everyone knows. But how and the heck do they manage a gate in a spreadsheet?

tluyben2 8y ago
I know about it because my friend (son of the ceo) provided the electronics for the gate and he showed me all the software in 1999 because we were pitching a rewrite in ‘web tech’ (my go to was Perl in those times). The gate software wrote to the parallel port via the win32 api to open and close the gate after the day/night watch entered the numberplate and name into the sheet.
- 4thaccount 8y ago
  That is indeed pretty cool. I wonder how the system works now.
  - tluyben2 8y ago
    Last I heard (few years ago) nothing changed; just more was added in the same way. If it ain’t broke...
wenc 8y ago
I've done some pretty mindbending things with VBA on Excel. They work but they are not pretty and not easy to maintain. VBA is technically a "complete" language (I want to say Turing-complete but that is not a meaningful trait), so it is possible to do a lot with it, but one ends up having to re-implement (sometimes badly) stuff found in other languages in order to write the main parts of the code. Part of what makes VBA deceptively easy is the control over the interactive elements of Excel (a lot of stuff is done with the Range object), but unfortunately that also introduces state that you can't always control, which entails write extra checks to make sure the state is correct before proceeding. This is especially true if your users are on different versions of Excel (I once wrote something in 2010 that doesn't work in 2016). There are now other options like QueryStorm [1], which lets you write C# in Excel and connect to SQL databases. There are also a bunch of Python-Excel solutions that are based on manipulating COM objects, but I've learned that when dealing with a Microsoft stack, there are advantages to using Microsoft-native languages like C#. Coming back to the article, it mentions adding arrays, vectors, and records to Excel itself; this will make Excel much more powerful because it has traditionally been a cell-based computation system, which has disadvantages that higher-level abstractions overcome (like vectors and tables). It also mentions writing Excel functions in a first class manner instead of relying on a separate procedural language like VBA. Operations on arbitrary sized arrays will also help it transcend Excel's issue of operating on fixed size arrays -- this will clean up a lot of very messy formulas. These developments will be interesting to watch, because it brings Excel much closer to a true functional computing system, and gets closer to Quantrix [2] territory. [1] https://www.querystorm.com/ https://www.querystorm.com/ [2] Quantrix is a multidimensional spreadsheet, and a commercial successor to Lotus Improv.
- vba 8y ago
  "but I've learned that when dealing with a Microsoft stack, there are advantages to using Microsoft-native languages like C#." curious. can you elaborate?
  - raddan 8y ago
    You can add custom functions to Excel (e.g., the ribbon) and other Office apps using Visual Studio Tools for Office (VSTO). There’s almost nothing you can’t do once you have C# or other .NET languages in the mix (I like to use F#). One of my collaborators (Ben Zorn) is one of the people featured in the linked article. We’ve been working on visual debuggers (and other things) for non programmers in Excel for a few years now.
  - wenc 8y ago
    There are many little things that just aren't available in Python-Excel libraries, that are available in VSTO. There are also odd little things that Python database libraries don't implement, like SqlClient.SqlBulkCopy. Native bulk inserting is generally unsupported on Python, so I either have to spit out a CSV and use bcp.exe to bulk insert, or I have to use TurbODBC, a Python library (which has certain limitations, like not supporting certain datatypes like VARCHAR(MAX) or GUIDs).

tluyben2 8y ago

I know about it because my friend (son of the ceo) provided the electronics for the gate and he showed me all the software in 1999 because we were pitching a rewrite in ‘web tech’ (my go to was Perl in those times). The gate software wrote to the parallel port via the win32 api to open and close the gate after the day/night watch entered the numberplate and name into the sheet.

4thaccount 8y ago
That is indeed pretty cool. I wonder how the system works now.
- tluyben2 8y ago
  Last I heard (few years ago) nothing changed; just more was added in the same way. If it ain’t broke...

wenc 8y ago

I've done some pretty mindbending things with VBA on Excel. They work but they are not pretty and not easy to maintain. VBA is technically a "complete" language (I want to say Turing-complete but that is not a meaningful trait), so it is possible to do a lot with it, but one ends up having to re-implement (sometimes badly) stuff found in other languages in order to write the main parts of the code. Part of what makes VBA deceptively easy is the control over the interactive elements of Excel (a lot of stuff is done with the Range object), but unfortunately that also introduces state that you can't always control, which entails write extra checks to make sure the state is correct before proceeding. This is especially true if your users are on different versions of Excel (I once wrote something in 2010 that doesn't work in 2016). There are now other options like QueryStorm [1], which lets you write C# in Excel and connect to SQL databases. There are also a bunch of Python-Excel solutions that are based on manipulating COM objects, but I've learned that when dealing with a Microsoft stack, there are advantages to using Microsoft-native languages like C#. Coming back to the article, it mentions adding arrays, vectors, and records to Excel itself; this will make Excel much more powerful because it has traditionally been a cell-based computation system, which has disadvantages that higher-level abstractions overcome (like vectors and tables). It also mentions writing Excel functions in a first class manner instead of relying on a separate procedural language like VBA. Operations on arbitrary sized arrays will also help it transcend Excel's issue of operating on fixed size arrays -- this will clean up a lot of very messy formulas. These developments will be interesting to watch, because it brings Excel much closer to a true functional computing system, and gets closer to Quantrix [2] territory. [1] https://www.querystorm.com/ https://www.querystorm.com/ [2] Quantrix is a multidimensional spreadsheet, and a commercial successor to Lotus Improv.

vba 8y ago
"but I've learned that when dealing with a Microsoft stack, there are advantages to using Microsoft-native languages like C#." curious. can you elaborate?
- raddan 8y ago
  You can add custom functions to Excel (e.g., the ribbon) and other Office apps using Visual Studio Tools for Office (VSTO). There’s almost nothing you can’t do once you have C# or other .NET languages in the mix (I like to use F#). One of my collaborators (Ben Zorn) is one of the people featured in the linked article. We’ve been working on visual debuggers (and other things) for non programmers in Excel for a few years now.
- wenc 8y ago
  There are many little things that just aren't available in Python-Excel libraries, that are available in VSTO. There are also odd little things that Python database libraries don't implement, like SqlClient.SqlBulkCopy. Native bulk inserting is generally unsupported on Python, so I either have to spit out a CSV and use bcp.exe to bulk insert, or I have to use TurbODBC, a Python library (which has certain limitations, like not supporting certain datatypes like VARCHAR(MAX) or GUIDs).