RefEdit doesn't respect tables (listobjects)

[jasmith4]

I've got a UserForm with a RefEdit in it. When I start up the form, I click the RefEdit's button on the side, then I can select a range, and the RefEdit populates: Sheet1!$A$1:$Z$10, for example.

But if Sheet1 has a table on it, and I start selecting within that table, I expect Table1[[#Headers],[Hdr1]], Table1[[#Data],[Col2]] or something like that, as if I were editing a formula or defining a name. Instead I get the normal range address, nothing referring to Table1.

I'm using C:\Program Files (x86)\Microsoft Office\Office14\REFEDIT.DLL -- is there something newer and better?

 

[Zack Barresse]

Hi there,

I'm afraid this is a known issue. The control has not been updated to understand what structured referencing is, and since it doesn't use the Excel engine to get the range, it can't translate either. AFAIK there isn't a workaround for this.

 

[jasmith4]

I got one for you regarding tables -- you may already know about it. Just follow these steps in Excel >=2007:

1. Start from a blank workbook with at least two tabs
2. Create a table (ListObject) with dummy data on one tab
3. Create on another tab a formula that refers to the table data, e.g., "=SUM(Table1[#Data])"
4. Select the formula cell and try to use Trace Precedents: nothing!
5. Switch tabs, select data in the table, and try to use Trace Dependents: nothing!
6. Convert the table to a normal range, and now the tracing works.

I've submitted this as SR number 116041113946418 to casemail@microsoft.com, and I'm told it's a known issue since 2007 with no plans to fix it without some kind of business justification. They write:

The product developers have provided some feedback that they are not sure they are seeing the level of critical impact required to approve a design change request for the current version of Excel (specifically because this feature has never been in place, and therefore there hasn’t been a loss in functionality). They are looking for significance beyond that the feature would be “nice to have”. Is there significant financial impact caused by not having the feature? Is there more to add in addition to the business impact already provided, that would help the product developers understand the criticality better?
​

I mean, they're kidding, right? It's a bug in an existing feature, not a design change request for a new one that's never been in place! You're an MVP -- I was just wondering if you can convince them if this better than I?

 

[Zack Barresse]

This is the real world of software development, which most people are not privy to. You've glanced through the looking glass. Software boils down to hard, real-world decisions about what to fix, what to add, and where to put the limited number of engineer-hours you have at your disposal. There's close to a dozen different end-points for Excel (i.e. desktop, Mac, iOS, Android, Universal, etc.), that's a lot of software. Not to mention most of the code base is probably 20 years old now. It's daunting, to say the least. So, while I understand your frustration, I understand theirs as well. Trust me, there are many things those on the Excel team wishes they could fix!

 

[jasmith4]

I understand all that as a developer, but my main point here is that they're calling it an enhancement, or a new feature. It's simply not: it's a bug in a long-standing feature, and a pretty important one: formula auditing. I work in finance, and trust me, these guys who develop financial models in Excel need to audit formulas! I have several real-world example of workbooks that have hidden tabs with tables that get data, plus visible tabs that have formulas based on those tables, and charts based on those, all to make them pretty. When I trace a formula's precedents, I'd like that to work!

My workaround is that I have an add-in of tools in which I've written a short procedure that converts all tables to ranges. Oh sure, I lose the connection string and SQL (great), but at least formula-auditing works! So I have to maintain two copies of each workbook, with and without tables/queries, the latter being for formula-auditing purposes.

 

[Zack Barresse]

I agree, it should be called a bug. But not all bugs are fixes. And some bugs do end up becoming "features", as bad as that sounds. At the end of the day I know two things, 1) I agree completely with you, and 2) it will never get changed, not this particular issue. The risk/reward isn't worth them fixing it. Especially for an almost 10 year old version, which was crap to start with. 2007 should be abolished. 2010 is still the fastest and most stable version IMO.

 

[jasmith4]

Good news -- they're going to fix it! Definitely for 2016, maybe 2013, but probably not 2010.

 

[Zack Barresse]

Is this on UserVoice? Or a communication from Microsoft? I'm wanting to be happy for this, but is it official?

 

[jasmith4]

Direct from Microsoft.

 

[Zack Barresse]

Awesome!! That's great news. Thanks for sharing.