Ranges or Tables ? that is the question

[GerryPerry]

Hi all
I have some very large Excel 2007 applications that, in their core, download a large number of tables from an ERP system ( SAP in fact ) into memory, put each table into a range in a worksheet, name each range to suit the size, add some formulas ( mostly vlookup) and then refresh many Pivot tables based on these ranges. We will be moving to Excel 2013 and I look forward to using "distinct count" metrics in the Pivots ( SO painful now). To get to that point I must use Tables ( rather than ranges) and I wish to start that move while in 2007. My question is:
Should I ? Performance is key, primarily during the post data receive to the "workbook ready for use".
Are tables quicker or slower ?
What is the best way to pop the memory data into the sheets ?
and an ancillary point : Should I add formula in 'memory' or after the sheet update ?
Many thanks for your experiences please
Gerry

 

[Luke M]

Welcome Gerry,

I myself just started using Tables more, rather than ranges. In my experience, they definitely helped increase performance, as I no longer have to worry about changing ranges, dealing wiht dynamic named ranges/formulas, and they are more robust if people start inserting/deleting things.

My big tip, make sure you are giving the tables good descriptive names as you go. Don't want to finish and have a list like:
Table1
Table2
Table3
etc.

Not quite sure what you mean by "memory". Do you mean an type of query, or VBA? If you start using Tables, could just have the formulas already setup there, as the table growth/shrink will automatically add your formulas as needed.

Also, need a Distinct Count in PivotTables prior to XL 2013? Some options listed here:
http://blog.contextures.com/archives/2014/05/20/unique-count-in-excel-pivot-table/

 

[Jon von der Heyden]

Tables! You can spare yourself a headache managing dynamic named ranges and also very effective to ensure that formulae always extend down all records in a table.

As a frequent SAP user I suspect you have suffered (or do suffer) the same frustrations dealing with reports exported to Excel. I tend to prefer using exports from System > List > Local file and then download as CSV. I frequently drop reports into a directory and then use that directory as a data source and suck the data in using the old fashioned MS Query. It allows me to define query tables with somewhat complex query definitions, and the resulting queries are presented in Table Objects. So yes, Tables are a good way forward.

But the caveat is that you must be aware of the limitations.

1. You cannot employ Custom Views in the same workbook that contains tables
2. If you have calculated columns in your Tables (i.e. formulae) and also wish to lock the sheet down, autofill of the tables will be disabled.

You can circumvent limitation #2 using something like this.

And ditto to Lukes comment about naming tables! And like Luke I don't know what you mean by 'memory'. I tend to think of arrays when referring to values housed in memory. If that's the case - no problem; just unload the array and if appended to a table the Table will expand, otherwise just use VBA to define the Table.

Good luck!

 

[GerryPerry]

Thanks Both
I WAS vague about "memory" . I have some Function Modules in SAP BW which I call from VBA and return a suite of data tables which I then put into worksheets with code as here where sap_table is one of those returned 'tables'. Data_range is the top left corner cell

        rowct = sap_table.rowcount:          colct = sap_table.ColumnCount
       Range(data_range).Resize(Sheets(sh).UsedRange.Rows.Count, colct).ClearContents  ' removes from range down for all columns of range
        If rowct > 0 Then Range(data_range).Resize(rowcount, colct) = sap_table.Data  ' the data population itself

after which I add various formula columns and then name a range to cover the data. that range is then the source to pivot tables ( using a common cache where relevant ).
What I am looking for is 2 things I think
1) If there is a smarter way apply formula BEFORE I 'paste' the data
2) can I paste directly to an excel "table" using vba

Some of these tables can be large ( >100,000 rows and 100 columns not being uncommon) so improving speed is always my target. For background the tools set is in use by > 1000 people so any performance improvement is widely felt . With ranges the performance is not bad but could be better. But will using tables improve this I wonder ....

thanks again

 

[Jon von der Heyden]

I'm assuming your are using SAP BEx addin and that when you refresh a query you are using VBA to trap the event and do something. It's hard to tell without seeing more code.

I used to do the same, a long time ago. When dealing with large data sets I used to also use calculated columns, but for performance I often used to use VBA to perform the calc and then drop constant values. I suppose it depends on whether or not you need 'live' calcs; i.e. do they refer to other variables in the workbook? If not then it may be a good idea to drop constant results rather than use formulae.

 

[GerryPerry]

Hi. you are right ( SAP Bex .... although migrating to HANA now).
I do a lot of range=range.value after the calculations for the non "live calcs" which certainly helps ..

Gerry

 

[GerryPerry]

I'm getting there but really wish to focus now on the question of performance expectations. Considering a range, a table and its use by a Pivot.
How many "copies" do I have?
Is the Pivot cache held in addition to the underlying Table ?
Is the table another set of memory than the Range/worksheet ?

thanks

Gerry

 

[GerryPerry]

Sorry I should have added that the data source is passed to me ( via function module ) in such a wat that I treat it as an array before posting to the worksheet

Gerry