Excel 2013 - VBA - Are there issues with named ranges using Tables?

Mellsie

New Member
Joined
Apr 1, 2015
Messages
1
Hi I've got a workbook which has a "Dashboard" worksheet which displays drop down boxes to allow viewers to customize what PROJECT AREA is displayed on their dashboard.

The workbook contains multiple tables on multiple worksheets for storing information such as cost, revenue, performance etc for monthly or weekly periods. The data is used to build graphs to visually show project status. The calculations for these graphs are on a separate worksheet to build the graphs dependent on various user choices from the dropdown boxes. It uses INDEX function so that the graph source data changes depending on which tables/rows/column etc relates to the user choices. (Note: these graphs update instantly in 2010 & 2013 when testing via manual change to cell values instead of via linked drop down choices).

The main issue I'm having is that in Excel 2013 the performance of the workbook has dropped significantly from when it was in Excel 2010. An example is when the user clicks on the dropdown box to choose a different view (ie changing from COST to REVENUE) it can take between 20-55 seconds to display the users choice on the screen. In 2010, it is literally instant.

A change in the dropdown box runs VB code to hide/unhide dropdown boxes, changes the "formula" which the "picture objects" on the dashboard point to - for displaying different graphs contained on a Calculation Worksheet and sets default values for linked cells. I don't understand why there is such a significant performance drop in the new version.

Yes, I have optimized the code by turning off Events, Calculation, page Breaks, and Screen updating at the start of the code and enabling them again at the end of the code.

When running the INQUIRE tool contained in 2013 it says there are a number of Errors in my formulas for example: Max(#Ref)
but when I look at the formula in the spreadsheet the formula looks correct for example: Max(tbl_Performance[DirectMhrs])

From reviewing the INQUIRE analysis anywhere it refers to table lists it says there is a #Ref error.

I'm wondering whether this is the reason the performance has dropped. Does Excel 2013 have issues with working with table list references?


NOTE: In 2013 I have also noticed that if I have linked a dropdown combo box list to a named range which obtains data from a table:
e.g. lst_Perf named range is mapped to "=tbl_ChartView[Chart Name]"

that the named range disappears as the list setting within my dropdowns, and I've gotten around that by creating another named range, which points to the table based named range by doing the following:

Create new named range eg lst_PerfChoice, and in the Refers to box press F3 key and in the Past Name window select the "lst_Perf" existing named range and click OK. Then applying this new named range as the list source for the drop down box
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Forum statistics

Threads
1,147,820
Messages
5,743,387
Members
423,792
Latest member
travisds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top