Serving the Quantitative Finance Community

 
User avatar
Govert
Posts: 0
Joined: January 28th, 2006, 10:02 am

The future for Excel VBA

May 14th, 2016, 10:34 am

QuoteOriginally posted by: fulmerspotI 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.@fulmerspot:The managed UDFs for Excel Services (the version of Excel online that runs in SharePoint) were introduced 10 years ago, and though it seems to still be supported, the feature has not been updated or incorporated in the Excel client in the last decade through four Excel client versions - 2007 to 2016. The Excel C API has had significant updates in these versions, including large sheet and long string support, multi-threaded functions and native async functions.In fact, you are the first person I have heard of that uses the managed UDFs on Excel Services.I would love to get in touch, especially as you also use Excel-DNA.When the managed UDFs for Excel Services were first introduced, I added an option in Excel-DNA that allows you to expose the managed UDF binaries to the Excel client without modification, recompiling or adding any dependencies. You should be able to take exactly the same binary that runs on the server, and with a .dna that gives the path file and a copy of ExcelDna.xll you can run the same UDFs on the Excel client. However, I have never been able to test this, having never found or seen such a managed UDF library. I'd be very happy to test and check that this feature works right, if that would have any value to you.Without needing your server-side UDF library to add any references, you can also mark up your functions with [Description] attributes, and both the function wizard and new in-sheet IntelliSense feature (https://github.com/Excel-DNA/IntelliSense) should expose those function and argument descriptions.-GovertExcel-DNA - Free and easy .NET for Excel
 
User avatar
fulmerspot
Posts: 80
Joined: July 8th, 2009, 12:44 pm

The future for Excel VBA

May 19th, 2016, 8:24 am

QuoteOriginally posted by: GovertQuoteOriginally posted by: fulmerspotI 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.@fulmerspot:The managed UDFs for Excel Services (the version of Excel online that runs in SharePoint) were introduced 10 years ago, and though it seems to still be supported, the feature has not been updated or incorporated in the Excel client in the last decade through four Excel client versions - 2007 to 2016. The Excel C API has had significant updates in these versions, including large sheet and long string support, multi-threaded functions and native async functions.In fact, you are the first person I have heard of that uses the managed UDFs on Excel Services.I would love to get in touch, especially as you also use Excel-DNA.When the managed UDFs for Excel Services were first introduced, I added an option in Excel-DNA that allows you to expose the managed UDF binaries to the Excel client without modification, recompiling or adding any dependencies. You should be able to take exactly the same binary that runs on the server, and with a .dna that gives the path file and a copy of ExcelDna.xll you can run the same UDFs on the Excel client. However, I have never been able to test this, having never found or seen such a managed UDF library. I'd be very happy to test and check that this feature works right, if that would have any value to you.Without needing your server-side UDF library to add any references, you can also mark up your functions with [Description] attributes, and both the function wizard and new in-sheet IntelliSense feature (https://github.com/Excel-DNA/IntelliSense) should expose those function and argument descriptions.-GovertExcel-DNA - Free and easy .NET for ExcelHi GovertFrom what I see from Microsoft, the Excel Services which have been available when running Excel online through SharePoint have been expanded - for Office Web Apps (OWA) there are a number of new addin types, this is where I get the Excel Services in Office Online info: Configure UDFs in Excel Online in Office Online Server Preview I don't yet have a full dev setup of SharePoint and OWA to confirm all this. Herr KartoffelKopf
 
User avatar
Govert
Posts: 0
Joined: January 28th, 2006, 10:02 am

The future for Excel VBA

May 19th, 2016, 3:04 pm

QuoteOriginally posted by: fulmerspotQuoteOriginally posted by: GovertQuoteOriginally posted by: fulmerspotI 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.@fulmerspot:The managed UDFs for Excel Services (the version of Excel online that runs in SharePoint) were introduced 10 years ago, and though it seems to still be supported, the feature has not been updated or incorporated in the Excel client in the last decade through four Excel client versions - 2007 to 2016. The Excel C API has had significant updates in these versions, including large sheet and long string support, multi-threaded functions and native async functions.In fact, you are the first person I have heard of that uses the managed UDFs on Excel Services.I would love to get in touch, especially as you also use Excel-DNA.When the managed UDFs for Excel Services were first introduced, I added an option in Excel-DNA that allows you to expose the managed UDF binaries to the Excel client without modification, recompiling or adding any dependencies. You should be able to take exactly the same binary that runs on the server, and with a .dna that gives the path file and a copy of ExcelDna.xll you can run the same UDFs on the Excel client. However, I have never been able to test this, having never found or seen such a managed UDF library. I'd be very happy to test and check that this feature works right, if that would have any value to you.Without needing your server-side UDF library to add any references, you can also mark up your functions with [Description] attributes, and both the function wizard and new in-sheet IntelliSense feature (https://github.com/Excel-DNA/IntelliSense) should expose those function and argument descriptions.-GovertExcel-DNA - Free and easy .NET for ExcelHi GovertFrom what I see from Microsoft, the Excel Services which have been available when running Excel online through SharePoint have been expanded - for Office Web Apps (OWA) there are a number of new addin types, this is where I get the Excel Services in Office Online info: Configure UDFs in Excel Online in Office Online Server Preview I don't yet have a full dev setup of SharePoint and OWA to confirm all this. Herr KartoffelKopfYes - that link is exactly the managed UDF support for Excel Services that I'm referring to.It was introduced in 2006. Shahar Prish wrote a whole series of blog posts: How UDFs work in Excel Services.-GovertExcel-DNA - Free and easy .NET for Excel
Last edited by Govert on May 18th, 2016, 10:00 pm, edited 1 time in total.
 
User avatar
MaxCohen
Posts: 0
Joined: June 13th, 2007, 2:44 pm

The future for Excel VBA

May 25th, 2016, 7:59 pm

From my experience as a RAD developer in banks VBA has its place long term especially as the power of Excel grows and more data needs to managed by the end user.Regardless of strategic infrastructure written in .NET/WPF/Python even with likes of Excel DNA these aren't RAD tools. If you are working on a trading desk and need something quanty or in middle office and need a new report. VBA integrated with some in-house analytics or scripting turn around time is quick. So long as code is written well you have a robust/transparent solution.I have seen large front office applications built in Excel VBA. Only to be replaced by .NET/Python what ever is in fashion which add no more value apart from a shinier GUI. Further they offer slower development time and are more difficult to debug. Right tool for the right job.
 
User avatar
dweeb
Topic Author
Posts: 11
Joined: July 11th, 2009, 8:10 pm

The future for Excel VBA

May 26th, 2016, 2:43 pm

An IT boss told me way back that a bank will eventually be one big spreadsheet. Blockchain could become an extension of this.
 
User avatar
bearish
Posts: 5188
Joined: February 3rd, 2011, 2:19 pm

The future for Excel VBA

May 26th, 2016, 7:51 pm

QuoteOriginally posted by: dweebAn IT boss told me way back that a bank will eventually be one big spreadsheet. Blockchain could become an extension of this.I understand spreadsheets. I don't understand blockchain. I would hate to see the two of them getting together.