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

Will VBA Die? (2019)

thespreadsheetguru.com

Post reply on HN
Print view View original

121–130 of 170 posts

Re: Will VBA Die? (2019)

#121

Post by TeMPOraL »

jmnicolas wrote: post #98
kevas wrote: post #63

Nah... why should it? It works and it does what it needs to do and more if you decide to hook into the Windows API or Mac’s API. Want to parse 500mb structured XML file? Okay. Takes 3 seconds or so. I had a lot of fun creating a full featured & modern look & feel application using Excel’s VBA runtime as my platform. Sure... I had to create everything from scratch, but learned so much while doing it. Kind of miss it a…

A +30K LOC VBA app to maintain is exactly the punishment I expect to get when I'll eventually end up in Hell ;)

Well, of course. While the Devil promotes web SaaS solutions to entrap mortals in sin, Hell itself runs on Excel and VBA because it works.

Re: Will VBA Die? (2019)

#122

Post by kevas »

markus_zhang wrote: post #75
kevas wrote: post #63

Nah... why should it? It works and it does what it needs to do and more if you decide to hook into the Windows API or Mac’s API. Want to parse 500mb structured XML file? Okay. Takes 3 seconds or so. I had a lot of fun creating a full featured & modern look & feel application using Excel’s VBA runtime as my platform. Sure... I had to create everything from scratch, but learned so much while doing it. Kind of miss it a…

However, it would take a lot of effort to maintain that 30K+ lines of VBA application. But I think that's because the infrastructure at that time was archaic. If it's today you would probably ditch Access for SQL Server and build reports with something more modern.

It was extremely easy. For awhile, I used naming conventions to keep everything organized, but then found RubberDuckVBA and there... I found enlightenment.

The codebase was clean and extensible. I built out a standard lib (I/O sync/async, networking, array methods, dictionary class, XML utilities (DOM/SAX). Then built out database functionality that included auto exporting to various formats, many tabs and with optional conditional formatting. Built the UI from the ground up to be async by building all UI components to be ‘reactive’ and have standardized interfaces. Started with just the frame & label and made my version of the modern web design in VBA’s user form. All components were reusable—from buttons, checkboxes, custom filterable drop-downs, tables, and the ability to display a subset of markdown (which allowed end users write their own documentation for the workflows they’d end up owning)

It had standardized parser interface that allowed for quick buildout of a new parser class; same for full pages. The navigation was completely dynamic and handled by the routing/navigation module. The permissions for all users were in a hosted database and the ui would only build out what the user had permission for and nothing more. SQLServer would handle user authentication as it would get the users name from the connection metadata.

I handed over the project a few months back to a former colleague, one who has only been programming for about a year and one who only knew how to do macro recordings. Since leaving, the application has thrived and is used all across the country, supporting hundreds...

Just before I moved on, I started working on a VBA native toy browser that supported basic HTML, but ended up leaving and not finishing it.

One pain in the ass was custom :hover functionality over ui components. Tried a lot... even tried hooking into window messages, but that messed up some async events...

I think I went off topic, but it wasn’t really a problem.

.

Re: Will VBA Die? (2019)

#123

Post by alexisread »

cm2187 wrote: post #13

Microsoft should rather provide a better path toward office automation rather than just frustrate users. As I have seen it, javascript isn’t even remotely close to the sort of integration that made the success of VBA. Like how can I save a javascript macro as a user? Javascript user defined function? VSTA was a good attempt in its time, a mini visual studio integrated in office with VB.net and C# instead of VB6. That…

I think there's a different focus with JS vs VBA or C#. JS is the future because of Office365 ie. Cloud/online apps are the future.

I don't think C# integrates with the o365 codebase, but I'd consider both JS and C# as attempt to move people to good dev practices, C# with IDE/versioning/debug etc and JS with the approach of funneling data in and out of excel eg. Cloud compute, and then using excel for basic functions that don't involve using macros etc.

Re: Will VBA Die? (2019)

#124

Post by clausok »

dokka wrote: post #27

I'm working on an excell addin right now. It doesn't use VBA (it uses microsoft interop libs for .NET) And I have zero interest in using VBA, but I will admit, it would have been much easier to do this project in VBA instead of C#. Debugging would have been easier. I could debug inside the VBA code-behind instead of Attaching to the excel process in Visual Studio. Deployment would have been easier. I could make an .x…

One thing to consider, which I've found powerful, is a hybrid approach where you use vba for what it's best at -- interacting with the Excel object model -- and have the vba load and call a C# xll that leverages the .Net libraries and does all the work that would be tedious to do in vba. For your users, they would simply open a "host" .xlam file, which is the only one they would be aware of, and this file would load additional .xlam or xll files and typically also check for updates to these files. No installer necessary.

For example, your vba code would pick up a range of index (or ETF) tickers from a sheet, use Application.Run("lib.constituents") to call a C# function that pulls the stock tickers and weights that represent each indices' constituents, your vba code would receive back an array of arrays and use Excel's row grouping feature to group the members & weights under the parent index and perhaps add some formatting and\or formulas.

The advantage of this division of labor is that you get faster dev & debug feedback loops within your Excel-centric code, while minimizing the amount of time you spend working in a dev environment that has stood still for 20+ years as others have raced far past.

Re: Will VBA Die? (2019)

#125

Post by n0rbwah »

TeMPOraL wrote: post #121
jmnicolas wrote: post #98

Earlier quoted context omitted.

A +30K LOC VBA app to maintain is exactly the punishment I expect to get when I'll eventually end up in Hell ;)

Well, of course. While the Devil promotes web SaaS solutions to entrap mortals in sin, Hell itself runs on Excel and VBA because it works .

And it's a hellish punishment for all the developers who end up in hell and have to maintain it.

Re: Will VBA Die? (2019)

#126

Post by ourlordcaffeine »

kevas wrote: post #63

Nah... why should it? It works and it does what it needs to do and more if you decide to hook into the Windows API or Mac’s API. Want to parse 500mb structured XML file? Okay. Takes 3 seconds or so. I had a lot of fun creating a full featured & modern look & feel application using Excel’s VBA runtime as my platform. Sure... I had to create everything from scratch, but learned so much while doing it. Kind of miss it a…

The company I work for just moved all automated bookkeeping and analysis into R scripts (running automatically), completley ditching office and Microsoft.

Re: Will VBA Die? (2019)

#127

Post by ptx »

kevas wrote: post #122
markus_zhang wrote: post #75

Earlier quoted context omitted.

However, it would take a lot of effort to maintain that 30K+ lines of VBA application. But I think that's because the infrastructure at that time was archaic. If it's today you would probably ditch Access for SQL Server and build reports with something more modern.

It was extremely easy. For awhile, I used naming conventions to keep everything organized, but then found RubberDuckVBA and there... I found enlightenment. The codebase was clean and extensible. I built out a standard lib (I/O sync/async, networking, array methods, dictionary class, XML utilities (DOM/SAX). Then built out database functionality that included auto exporting to various formats, many tabs and with optio…

Did you build some kind of component tree diffing for the reactive UI, like React? Or data binding?

And how did you design the async parts, given that VBA doesn't have first-class functions? Classes and interfaces? Or nested event loops?

Re: Will VBA Die? (2019)

#128

Post by tonyedgecombe »

animalnewbie wrote: post #110
tonyedgecombe wrote: post #103

Earlier quoted context omitted.

It could have made for an interesting alternative to PowerShell as well.

Not a PS1 expert by any means but you have to spend some time with it to appreciate the design decisions. It's a very "wholesomely" designed language. Try eg. The for each parallel construct which made me realize why the output in PS1 behaves how it does.

That's always been the problem for me, I don't use it often enough to become fluent with it.

Re: Will VBA Die? (2019)

#129

Post by ptx »

jbeam wrote: post #65
dokka wrote: post #27

I'm working on an excell addin right now. It doesn't use VBA (it uses microsoft interop libs for .NET) And I have zero interest in using VBA, but I will admit, it would have been much easier to do this project in VBA instead of C#. Debugging would have been easier. I could debug inside the VBA code-behind instead of Attaching to the excel process in Visual Studio. Deployment would have been easier. I could make an .x…

I’m the other way. Started a large project in VBA once, jumped over to C# interop and never looked back. I found the libraries to map fairly well to the same named functions in VBA when it came to worksheet manipulation. My biggest issue was that the interop has quite a bit of latency. You really want to accomplish as much in one call as you can (i.e. read whole ranges into an array instead of looping through cell by…

The latency might be because the C# is running out-of-process and has to serialize all the data and make calls by (internally) sending window messages. If you can create the COM component in-process (in the Excel process) it should be a lot faster.

Re: Will VBA Die? (2019)

#130

Post by lmm »

Ididntdothis wrote: post #83
markus_zhang wrote: post #75

Earlier quoted context omitted.

However, it would take a lot of effort to maintain that 30K+ lines of VBA application. But I think that's because the infrastructure at that time was archaic. If it's today you would probably ditch Access for SQL Server and build reports with something more modern.

Maintaining VBA code is probably easier than maintaining C code. You have all the tools to structure things nicely. You even have classes. One problem is that you can’t version control Excel or Access code. I think that’s the biggest weakness.

C is a pretty low bar. And yeah, not being able to do version control, diffs, releases, dependency management... it all adds up.
Post reply on HN