Page 2 of 3

The future for Excel VBA

Posted: March 18th, 2016, 4:18 pm
by dweeb
QuoteOriginally posted by: terrorbyte ;-)My turn to get flamed.

The future for Excel VBA

Posted: March 21st, 2016, 1:41 pm
by rmax
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.

The future for Excel VBA

Posted: March 21st, 2016, 3:53 pm
by dweeb
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.

The future for Excel VBA

Posted: March 21st, 2016, 3:57 pm
by fulmerspot
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

The future for Excel VBA

Posted: March 25th, 2016, 1:35 pm
by Polter
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

The future for Excel VBA

Posted: March 25th, 2016, 2:33 pm
by Cuchulainn
From the docs, Stencila is only on Linux. Looks like an unfortunate _first_ choice?

The future for Excel VBA

Posted: March 30th, 2016, 2:19 pm
by Edgey
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.

The future for Excel VBA

Posted: April 17th, 2016, 8:23 pm
by Polter
Here comes another one: http://www.alphasheets.com/

The future for Excel VBA

Posted: April 18th, 2016, 7:35 am
by Cuchulainn
undocumented code change in VBA few years back...

The future for Excel VBA

Posted: April 18th, 2016, 7:46 am
by fulmerspot
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

The future for Excel VBA

Posted: May 2nd, 2016, 2:16 pm
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.

The future for Excel VBA

Posted: May 9th, 2016, 11:19 am
by fulmerspot
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

The future for Excel VBA

Posted: May 9th, 2016, 11:38 am
by Cuchulainn
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

The future for Excel VBA

Posted: May 9th, 2016, 11:57 am
by fulmerspot
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)

The future for Excel VBA

Posted: May 9th, 2016, 2:36 pm
by Cuchulainn
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?