SERVING THE QUANTITATIVE FINANCE COMMUNITY

 
User avatar
dweeb
Topic Author
Posts: 142
Joined: July 11th, 2009, 8:10 pm

The future for Excel VBA

March 18th, 2016, 4:18 pm

QuoteOriginally posted by: terrorbyte ;-)My turn to get flamed.
 
User avatar
rmax
Posts: 6080
Joined: December 8th, 2005, 9:31 am

The future for Excel VBA

March 21st, 2016, 1:41 pm

QuoteOriginally posted by: bearishMy 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.Agreed. VBA has its issues without a doubt, but if you remove VBA then all productivity will stop. IMO VBA should be made neater/cleaner and easier to code and then embed C# for anything really complex.
 
User avatar
dweeb
Topic Author
Posts: 142
Joined: July 11th, 2009, 8:10 pm

The future for Excel VBA

March 21st, 2016, 3:53 pm

QuoteOriginally posted by: rmaxQuoteOriginally posted by: bearishMy 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.Agreed. VBA has its issues without a doubt, but if you remove VBA then all productivity will stop. IMO VBA should be made neater/cleaner and easier to code and then embed C# for anything really complex.Such is the status quo of the current user base. But with the proliferation of Masters in Business Analytics and Data Science in business schools - MIT Sloan is the latest - will this suffice for the future.An econometrics PhD who taught at a business school once told me that most students do enough to reach a 50% pass on the one compulsory MBA stats/quant subject, and never touch it again.
Last edited by dweeb on March 20th, 2016, 11:00 pm, edited 1 time in total.
 
User avatar
fulmerspot
Posts: 511
Joined: July 8th, 2009, 12:44 pm

The future for Excel VBA

March 21st, 2016, 3:57 pm

QuoteOriginally posted by: rmaxQuoteOriginally posted by: bearishMy 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.Agreed. VBA has its issues without a doubt, but if you remove VBA then all productivity will stop. IMO VBA should be made neater/cleaner and easier to code and then embed C# for anything really complex.Every bank I have worked for in the last 20 years has had a vision which involves taking spreadsheets off traders desktops and replacing them with a 'proper' solution.20 years on they are no nearer this goal but that's no reason to stop trying.Herr KartoffelKopf und der Osterhase
 
User avatar
Polter
Posts: 2526
Joined: April 29th, 2008, 4:55 pm

The future for Excel VBA

March 25th, 2016, 1:35 pm

Interesting: QuoteStencila is a platform for creating documents that are driven by data. At present we have two types of documents, Stencils : like traditional templates for embedding data analysis and presentation code but designed to allow what-you-see-is-what-you-get editing while still maintaining reproducibility (think RMarkdown meets Jade meets Google Docs) Sheets : like traditional spreadsheets but with cells that are expressions in the host language (i.e. R or Python or ...) and built from the ground up to for transparency, testability an version control while still maintaining accessibility (think R meets Google Sheets meets git)http://stenci.la/stencila/blog/introduc ... a/stencila
Last edited by Polter on March 24th, 2016, 11:00 pm, edited 1 time in total.
 
User avatar
Cuchulainn
Posts: 60518
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

The future for Excel VBA

March 25th, 2016, 2:33 pm

From the docs, Stencila is only on Linux. Looks like an unfortunate _first_ choice?
http://www.datasimfinancial.com
http://www.datasim.nl

Approach your problem from the right end and begin with the answers. Then one day, perhaps you will find the final question..
R. van Gulik
 
User avatar
Edgey
Posts: 219
Joined: March 23rd, 2005, 11:01 am

The future for Excel VBA

March 30th, 2016, 2:19 pm

I think VBA will remain and stagnate. MS are pushing powerquery and powerpivot for database connection and creating pretty, interactive visualisations. There is no way to add UDFs within this environment, although the power BI desktop application allows you to add graphs created by an R script. I'm going to add an extra 5% to Hansi's prediction of deeper R integration.
 
User avatar
Polter
Posts: 2526
Joined: April 29th, 2008, 4:55 pm

The future for Excel VBA

April 17th, 2016, 8:23 pm

Here comes another one: http://www.alphasheets.com/
 
User avatar
Cuchulainn
Posts: 60518
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

The future for Excel VBA

April 18th, 2016, 7:35 am

undocumented code change in VBA few years back...
Last edited by Cuchulainn on April 17th, 2016, 10:00 pm, edited 1 time in total.
http://www.datasimfinancial.com
http://www.datasim.nl

Approach your problem from the right end and begin with the answers. Then one day, perhaps you will find the final question..
R. van Gulik
 
User avatar
fulmerspot
Posts: 511
Joined: July 8th, 2009, 12:44 pm

The future for Excel VBA

April 18th, 2016, 7:46 am

QuoteOriginally posted by: EdgeyI think VBA will remain and stagnate. MS are pushing powerquery and powerpivot for database connection and creating pretty, interactive visualisations. There is no way to add UDFs within this environment, although the power BI desktop application allows you to add graphs created by an R script. I'm going to add an extra 5% to Hansi's prediction of deeper R integration.UDFs are available on Office Web Apps through the OWA addin model, this addin model also works on Desktop Excel but Microsoft has not yet unified the UDF calling mechanism, the OWA one comes from Excel Server.Office Web Apps UDFsI am currently exposing two interfaces - Desktop Excel through ExcelDNA and OWA through the [UDFClass] interface.Herr KartoffelKopf
 
User avatar
Edgey
Posts: 219
Joined: March 23rd, 2005, 11:01 am

The future for Excel VBA

May 2nd, 2016, 2:16 pm

@fulmerspotExcel server is different to the Data Model, (which is like Pivottables 2.0) that is programmed using DAX. There doesn't seem to be a way to add UDFs to the DAX language.
 
User avatar
fulmerspot
Posts: 511
Joined: July 8th, 2009, 12:44 pm

The future for Excel VBA

May 9th, 2016, 11:19 am

QuoteOriginally posted by: Edgey@fulmerspotExcel server is different to the Data Model, (which is like Pivottables 2.0) that is programmed using DAX. There doesn't seem to be a way to add UDFs to the DAX language.Don't disagree with you on this- the wider scope of this discussion is the future - or lack of it - of VBA.I write Excel Addins - I've used the C API with C and C++, Automation in VB, C# through ExcelDNA and just about every combination going. VBA is like Gaffer Tape - it's great for any quick and dirty fix and for simple automation tasks. I would be sorry to see VBA go, equally worrying would be the demise of the C API without there being a reliable performant replacement.I'm currently looking at what is needed to host my Addins for Office Web Apps - I would not be surprised to see the C API demised in favour of the [UdfClass] interface from the Excel Server API in some near future version of Excel.Her KartoffelKopf
 
User avatar
Cuchulainn
Posts: 60518
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

The future for Excel VBA

May 9th, 2016, 11:38 am

If you want C++ and close to COM there is another way to write addins for Excel. It's called ATLATL == COM<Template>I prefer it to all the other solutions. You can call it from VBA. Nicer than DLLS hell.It is easy once you get the first one working. BTW you need registry privileges!!! // Year ago Microsoft warned C API was frozen, so I suppose
Last edited by Cuchulainn on May 8th, 2016, 10:00 pm, edited 1 time in total.
http://www.datasimfinancial.com
http://www.datasim.nl

Approach your problem from the right end and begin with the answers. Then one day, perhaps you will find the final question..
R. van Gulik
 
User avatar
fulmerspot
Posts: 511
Joined: July 8th, 2009, 12:44 pm

The future for Excel VBA

May 9th, 2016, 11:57 am

QuoteOriginally posted by: CuchulainnIf you want C++ and close to COM there is another way to write addins for Excel. It's called ATLATL == COM<Template>I prefer it to all the other solutions. You can call it from VBA. Nicer than DLLS hell.It is easy once you get the first one working. BTW you need registry privileges!!! // Year ago Microsoft warned C API was frozen, so I supposeI did play with this before deciding to use C# and therefore ExcelDNA on my current project. I suspect it will be a while yet before Microsoft can demise the C API but if the [UdfClass] interface becomes available on the next version then I'd use that to keep a single common API across local and hosted Excel.Herr KartoffelKopf (with many different hats)
 
User avatar
Cuchulainn
Posts: 60518
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

The future for Excel VBA

May 9th, 2016, 2:36 pm

Maybe a long shot, but what about C++/CLI? It speaks native C++ as well as .NET and C++/CLI can call to and be called from C#, native C++ etc. I like it for calling Boost C++ and it is super for ADO.NET.// I have a related question myself; is DDE used these days?
Last edited by Cuchulainn on May 8th, 2016, 10:00 pm, edited 1 time in total.
http://www.datasimfinancial.com
http://www.datasim.nl

Approach your problem from the right end and begin with the answers. Then one day, perhaps you will find the final question..
R. van Gulik
ABOUT WILMOTT

PW by JB

Wilmott.com has been "Serving the Quantitative Finance Community" since 2001. Continued...


Twitter LinkedIn Instagram

JOBS BOARD

JOBS BOARD

Looking for a quant job, risk, algo trading,...? Browse jobs here...


GZIP: On