Page 1 of 3

The future for Excel VBA

Posted: March 17th, 2016, 2:56 am
by dweeb
Opinions welcome. Plenty online re; shortcomings, new directions and alternatives. Besides being useless for MC, it's a workhorse.

The future for Excel VBA

Posted: March 17th, 2016, 7:54 am
by Cuchulainn
VBA and a future?I thought it was in permafrost. Why not just C#, Excel DNA??

The future for Excel VBA

Posted: March 17th, 2016, 8:15 am
by tags
With much humility, I second Monsieur Cuchulainn on this. I have never been a VBA fan and I personally tend to write python UDFs for my spreadsheets, eg using xlwings.

The future for Excel VBA

Posted: March 17th, 2016, 8:28 am
by Cuchulainn
Quote Besides being useless for MC, it's a workhorse. What you can do is write the MC in C (create a dll) and then call it from VBA/Excel. Which is a not all too bad IMO. But one should keep VBA at bay I suppose.

The future for Excel VBA

Posted: March 17th, 2016, 1:07 pm
by dweeb
QuoteOriginally posted by: CuchulainnQuote Besides being useless for MC, it's a workhorse. What you can do is write the MC in C (create a dll) and then call it from VBA/Excel. Which is a not all too bad IMO. But one should keep VBA at bay I suppose.Re; dll - exactly what I've done in the past. Worked like a dream.I guess VBA's future lies in Microsoft's intentions - read somewhere that VBA will no longer be supported. .NET was supposed to be it's replacement. R and Python are also essentially freeware - I wonder where corporate tech policies will head.Programming education is another item. Quant Fin = C/C++, Data Science = R and Python, Finance = VBA???

The future for Excel VBA

Posted: March 17th, 2016, 2:27 pm
by terrorbyte
Hmmmmm, this kind of speaks to a greater issue imo. The use of Excel and therefore the use of VBA. Excel is a great Swiss Army knife. It gets you into trouble but also solves a lot of problems. Right tool for the right job.For example, I still have not yet seen as good a reporting tool as Excel. Users (including myself) have found Crystal, SRS etc pallid imitations. So, VBA behind a spreadsheet to belch MTM and Var Reports, by pulling data from the corporate risk system, provides standardization and also the ability for users to flip the data into additional ad hoc reports that help them troubleshoot figures or generally provide user specific position, financial reports.I also like it as a prototyping tool for logic and maths. You can walk users through a heap of complex calcs, using Excel and VBA. Get their sign off and build a robust version in an enterprise language.I would be bereft if VBA disappeared. * Stands by and waits to be flamed * :-)

The future for Excel VBA

Posted: March 17th, 2016, 3:42 pm
by dweeb
QuoteOriginally posted by: terrorbyte * Stands by and waits to be flamed * :-)Here it comes - a lot of Excel s/s's seem to be prototypes that didn't make it into a IT development stage. Also firms don't seem to bother with 'standards' re; creating s/s's, good VBA coding and documentation. Excel (+VBA) is a great tool for one-off analyses.VB your cleansing ale of choice Terrorbyte???

The future for Excel VBA

Posted: March 17th, 2016, 5:54 pm
by terrorbyte
Dweeb,re: Prototype that didn't make it into development. Below the belt blow but just soooooooo true!!!!!! I can't argue with it. :-) re: Standards. Nothing irritates me more than being sent someone else spreadsheet that "stopped working suddenly" and you open it up to find an absolute dog's breakfast of spaghetti coding. Damn it! 2 for 2!And I am giving you +3 additional points for "cleansing ale of choice" on St Patrick's Day.You got me! :-)

The future for Excel VBA

Posted: March 17th, 2016, 6:43 pm
by Polter
Perhaps something like this may be an evolution (perhaps with Azure being replaced with an in-house solution, otherwise -- due to regulatory constraints -- I don't think this would work for everyone):http://blog.revolutionanalytics.com/201 ... excel.html

The future for Excel VBA

Posted: March 17th, 2016, 9:20 pm
by Hansi
Microsoft has started incorporating R into SQL Server and have bought Revolution R which is now renamed to Microsoft R so I don't think it's completely out of the question that the next Excel would include it as a runtime alternative to VBA. I'd say 70% chance.

The future for Excel VBA

Posted: March 18th, 2016, 12:03 am
by bearish
My take on this question is a little different. Few people posting in the P&S Forum are qualified to have an opinion on VBA because you think nothing of creating a project in Visual Studio, writing efficient code in C++ or C# to solve a problem, and jumping through the ExcelDNA or XLW hoops to create an addin. There are millions of people (at the risk of sounding like Carl Sagan, but I don't think I am exaggerating) who are just about able to augment their workbook with a bit of VBA code, most likely subroutines based on recording spreadsheet actions, who wouldn't have the first idea of how to install VS, let alone figure out what to do with it. From that starting point, there is a very long way to go, including figuring out how to write, debug and test functions, before having any consideration of stepping outside of the native Excel bubble. For this type of user, I think VBA is much better than nothing, which would seem to be the most likely alternative.

The future for Excel VBA

Posted: March 18th, 2016, 12:20 am
by Hansi
bearish I agree, I absolutely hate VBA and Excel but when people around me use it to solve a specific problem in front of them I'm okay with it but generally label it as "tactical" with hopes of replacing said solution with a "strategic" solution at a later date (where as proper IT scream and yell, yet give timelines measures in months or years to replace said working solution).The main strength is that Excel includes the "IDE" and the runtime natively which isn't the case for other options. If there was a built in C#, F# or PowerShell IDE and you could natively embed said code in the spreadsheet things might be a bit different.

The future for Excel VBA

Posted: March 18th, 2016, 1:00 am
by dweeb
VBA also provides an alternative to nested Excel functions - functions within functions within functions (they exist). It's relatively easy to follow what's going on in VBA. Haug's - Complete guide to Option pricing Formulas VBA is a great way to study the code logic behind quant models.Data science (stats and CS) seems to everywhere these days. Stanford's - Elements of Statistical Learning - data mining, inference, and prediction - provides a great framework for the subject. But executing this in code - be it R, Python etc - arguably imposes an opportunity cost of learning and remembering syntax and semantics etc.Stats/econometrics/Data Science almost becomes cerebral after a while the more one studies it. One day we will be able to strap on a virtual reality headset and just think code. But in the meantime there's VBA, R, Python. C/C++, C#, Java.....

The future for Excel VBA

Posted: March 18th, 2016, 1:30 pm
by dweeb
QuoteOriginally posted by: terrorbyte And I am giving you +3 additional points for "cleansing ale of choice" on St Patrick's Day.Interesting that a VBA expert's beer of choice is VB, aye.

The future for Excel VBA

Posted: March 18th, 2016, 1:55 pm
by terrorbyte
;-)