One Year Of RAD Excel

When I logged on today, WordPress notified me that it’s the one year anniversary of RAD Excel. Time really does fly! When I think that it was this time last year that I made my first post – which funnily enough was on SQL Server rather than Excel – it amazes me how much this blog has grown over the last twelve months. Please keep the comments and emails coming. Some of the topics I have in mind (nothing guaranteed and in no particular order) for next year are:

  • Interacting Excel with SQL Server: Using Excel as a front end to let users maintain data in SQL tables. Stored procedures, triggers.
  • More VBA string art add-in work.
  • What’s new in Excel 2013.
  • Excel distinct/unique counts and lists: Frequency() function, VBA UDFs, windows timers, advanced filter tricks. [Adapted from my old website.]
  • ADO with Excel files: using ADO to import data from Excel feed files.  [Adapted from my old website.]
  • The best bits of MZ-Tools for VBA.
  • From VBA to C#.NET.

I’d also like to scatter in some less advanced topics. What would you like to see?

Thanks for reading and I’ll see you on here again next year.

Advertisements

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in General. Bookmark the permalink.

3 Responses to One Year Of RAD Excel

  1. Stefan says:

    Hi Colin, really looking forwards to seeing you cover these topics, especially the last three. I would also like to see some posts on the new Data Model in Excel 2013.

    Cheers.

    Like

  2. Hi Colin, I am a bit late replying, and I’m not sure that you haven’t answered this already, but for Excel and SQL Server, I would like to know if it is possible to store a table in SQL Server, and have Excel formulas lookup values from that table, without needing to store a copy of the table in Excel. If this is possible, then the next step would be to understand how efficient or inefficient this is, e.g. would there be a problem with many occurrences of the same formula each separately accessing data from SQL Server, and is there a workaround for this (e.g. using array formulas).

    Thank you!

    Like

  3. Colin Legg says:

    Hi John,

    Sure, it’s possible to query a SQL Server database from a VBA UDF. You can call a stored proc or read data from a view or table – provided you have the relevant permissions. In addition to having a well optimised database, the key to this would be to both minimise trips back and forth to the server as well as keeping the amount of data being transferred to a minimum. I’ve seen companies implement a web service approach which takes care of the authentication, querying and data aggregation server side.

    There are plenty of good (and less daunting) alternatives to using formulas though: pivot tables, query tables and powerpivot to name a few. All of this would make for an excellent series of blog posts so I will add it to my ever growing to do list. A couple of friends of mine are really well versed in this field so I might even try to use beer to bribe them into writing a couple of guest author posts.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s