11 ms·
Excel Functions in F# Language
- Nelkins 4y agoNice use of F# scripting. The #r directive for Nuget packages was a great addition.
- lsm07 4y agoI'm always happy to see F# get a mention, but using it inside Excel seems funny when you could just use it against a raw CSV file or whatever for data analysis/data manipulation.
- coldacid 4y agoIt's not for people like us who even know what a CSV is, it's for people who have some scripting/programming knowledge, but mainly live in Excel all day.
- nerdponx 4y agoPlenty of people who do know what a CSV is still sometimes (or frequently) use Excel, either for their own work or as a sort of "data app" that they can distribute to coworkers.
- Idiot_in_Vain 4y agoExcel is a lot more user friendly interface for data analysis/manipulation, than a raw CSV file.
- JaggerJo 4y agonice!
- bsdooby 4y agoAn interesting F# blog that was found for me: https://www.planetgeek.ch/ https://www.planetgeek.ch/
- airstrike 4y agoAwesome find, thank you!
- DoctorOW 4y agoI've done some VBA for work. If I build an Excel file with SharpCells and send them to someone who doesn't have it. What happens when they open it? From what I understand, .NET is pretty easy to decompile. Is it possible to make a SharpCells F# to VBA translation before saving?
- layer8 4y agoVBA is the old win32/COM-based Visual Basic, not .NET, so that might be difficult. I agree that the non-portability of such Excel extensions is a showstopper for many use cases.
- joenathanone 4y agoMicrosoft is currently doing about everything they can to kill VBA.
- sharpcells 4y agoAn F# to VBA translation would be very difficult. The languages are very different and F# relies heavily on the .NET runtime which VBA does not use. When you use Sharp Cells the F# scripts are embedded with the workbook when you save so sending the .xlsx will work for the recipient provided they also have Sharp Cells installed. For many workbooks the free tier would be sufficient for use. If the user doesn't have Sharp Cells the functions won't load and the results relying on Sharp Cells UDFs will be replaced with `#NAME?` errors.
- orthoxerox 4y agoAh, at first I thought that was a library that implemented Excel functions in F#.
- layer8 4y agoHow do they implement the subscription plans when no internet connection is required?
- sharpcells 4y agoThe subscription validation does require an intermittent internet connection to revalidate. Currently it is checked once per week but besides that check Sharp Cells can be used offline. We have updated the website to clarify this.
- velcrovan 4y agoCall me when Excel “programs” don’t silently give corrupted output when a formula or critical input is accidentally clobbered by a keypress.
- cjohnson318 4y agoCall me when Excel doesn't randomly insist that things are dates.
- libraryatnight 4y agoI recall opening a csv of user data in excel, and one particular user had a username of april0204 (can't recall the actual number but you get the idea). It took me a moment longer than I care to admit to realize what had happened as i stared at the random date in the middle of my username column
- cjohnson318 4y agoA while ago I got some Excel CSV exports that I couldn't read as I normally do. I tried several formats that I knew were associated with Windows systems. I ended up looking at the raw bytes and I noticed that every other byte was something like 0x00, so I wrote a script to strip all of that out. Several weeks later I figured out that I had been looking at utf-16. Derp.
- inglor 4y agoI work on Excel and this is really cool and I’ll share it with the rest of the engineers.
- inglor 4y agoTeam says it’s cool and already possible with different us through https://learn.microsoft.com/en-us/office/client-developer/excel/developing-excel-xlls https://learn.microsoft.com/en-us/office/client-developer/ex... https://learn.microsoft.com/en-us/office/troubleshoot/excel/set-up-realtimedata-function https://learn.microsoft.com/en-us/office/troubleshoot/excel/...
- ripley12 4y agoI mean... sorta. It's not easy to write a Office add-in just using the C API, and when I tried a year or two ago the only example code I could find was like 20 years old and all documentation was heavily pushing people toward the newer JS-based add-ins. I quit that job because the extensibility story for Excel was so painful (but they had good business reasons for being in the space), sigh.
- airstrike 4y agoWriting an XLL add-in is a terrible pain I wish on no one at all. The documentation is all over the place, terribly outdated and generally an aggravating process to try to learn it from scratch
- sharpcells 4y agoWe use those APIs internally to implement Sharp Cells. You can certainly build your own integration however the XLL API in particular was poorly documented and very difficult to implement correctly. Sharp Cells' aim is to make it easy to use these APIs from F# so you don't have to worry about P/Invoke, manual memory management, dynamic .NET assembly loading, etc. to just write custom functions.
- rwhaling 4y agoReally cool - the stuff they are doing with the Excel API to inspect editing modes is super interesting: https://www.sharpcells.com/docs/blog/monitoring-edit-mode https://www.sharpcells.com/docs/blog/monitoring-edit-mode I work with data warehouses, but I'm really jealous of the way our Finance team uses some abysmal plugin to directly query our GL from inside Excel - building something like that the can make the contents of a modern data warehouse available to Excel users has always been a holy grail for me. My hunch is that exposing free-form SQL in Excel doesn't work, but something more like structured metrics (something roughly like dbt metrics) could potentially work? And tooling like this is probably what I'd want to prototype with.
- listenallyall 4y agoCan't be that abysmal if you're jealous of it and is similar to your idea of a holy grail. Is it Jedox perhaps?
- matt3D 4y agoI think PowerQuery largely solves this problem
- inglor 4y agoYou can both feed SQL data sources into Excel and expose Excel as an ODBC data source (natively, usable today and used by a lot of companies!)
- giaour 4y agoWhy is this a subscription?
- andix 4y agoInteresting that it is F# only, usually .NET based products can also use C# and VB.NET. Especially VB.NET could be useful when porting VBA code.
- sharpcells 4y agoWe would say it is F# first. We found F# to be far superior as a scripting language and so focused on that integration. Projects written in C# or VB.NET can be referenced using the `#r` syntax as shown here https://www.sharpcells.com/docs/example-ref-proj https://www.sharpcells.com/docs/example-ref-proj
- eggy 4y agoGlad to see F# vs. C#. I prefer F# and it doesn't get as much play as C#. But, being more of a Lisper, I've already subscribed to Acceλerate for Microsoft 365[1]. It's basically a full scheme available in Excel with VSA (Visual Scheme for Applications - nice play on VBA to dupe the unaware ;) ). It has a full REPL and an editor and also creates UDFs. It is Excel's new Lambda on steroids. I'll have to try Sharp Cells. I've played with J[2] and some Excel tie-in scripts, but it is not integrated as nicely as Sharp Cells or Acceλerate. [1] https://apexdatasolutions.com/home https://apexdatasolutions.com/home [2] https://code.jsoftware.com/wiki/Scripts/OLEExcel https://code.jsoftware.com/wiki/Scripts/OLEExcel
- japanman425 4y agoHave you ever used F# in production?
- eggy 4y agoNot in the sense you probably mean, but I have performed three or four analyses when I worked at an engineering firm (entertainment - structural, mechanical) in F#. One involved an FSI system (Fluid-Solid Interaction using Project Chronos in C++) that I used F# for the mathy parts before the simulation. I also used F# to munge failure data and perform a Weibul analysis and generate a report for ride equipment. I have not used F# at all for web stuff. I typically reach for Mathematica (tried Julia, love it, but Mathematica's all-in-one notebook with curated data is hard to beat when you are doing something ecelctic and don't want to lose the flow of trying to pull in a data source or search for a library). I wish F# had more presence in the scientific community. It's very simple compared with Haskell, less verbose than C#, and it has the entire .Net ecosystem to draw on.
- xupybd 4y agoWe use F# and Excel as our main tools. This seems like a product made in heaven. But this would never fly for us. The pricing model could not be sold to management. What would work is if it was something like $5k for a year of updates. We're on 365 so office's constant updates would force us to upgrade every year. But management would not feel like they're renting something they would rather own.
- japanman425 4y agoWhat industry are you in? I’ve never found a need for it (probably quite rightly just writing crud apps…)
- xupybd 4y agoI'm in B2B manufacturing. We are abusing Excel no one should use it for what we do. We have a spreadsheet that takes information from online orders into input cells. Then it has lines that if they calculate a qty of 1 or greater get consumed by CAM software. The product then gets sent to the appropriate machining center. Not a single line of VB. There are a few things that I could extract into custom formulas that would remove pages and pages of Excel formulas. However the entire point of the Excel setup is so that product experts not programmers can edit them. We currently have F# in our internal tools. We have a couple of CRUD apps on the SAFE stack. A few ETL tasks in F#. The bug count seems to be much lower when using F#. There is some legacy stuff in PHP, JAVA and C#. Then we have an online store in C# but that is just using an open source tool NopCommerce. I couldn't recommend that highly enough. It has been amazing compared to everything I've worked with in the past.
- pjacotg 4y agoI was expecting you to say finance where Excel is abused in a similar fashion!
- gigatexal 4y agoContact them. For corporates I’m sure they could work something out.
- qwerty456127 4y agoVery cool, but... Limiting (let alone this severely) a number of functions a developer can introduce and locking them in having to pay recurrently just to overcome this limitation and keep their code working is insane. I believe this dooms the product to be extremely unpopular. This way people probably won't even invest time in giving it a try. Real-Time Data Sources sound like a thing which can be a premium feature. Unlimited UDFs and Unlimited Commands don't.
- keithalewis 4y agoWhy drag F# into this? =LAMBDA(name, "Hello, "&name) works just fine. Modern Excel has MAP, REDUCE, SCAN, MAKEARRAY, etc.