Nicholas A. Drouillard

Nicholas Drouillard

VBA Refresh Order

Common VBA Pitfall

It is no fun to execute an elaborate VBA subroutine only to produce a stale, inaccurate report. In most cases, update failures are caused by the programmer failing to track and control object source update order. It is easy fall into this pitfall when working with a complex program with many objects.

Solution

Clearly define source update order in your code. Pictured below is a sample of code from one of my projects. It uses a condition to force the update of certain pivot tables (those housed on worksheets where cell T1 = 1). This is a slick, simple approach to control object update order with VBA.

Excel Power Query
VBA Refresh Order: Code Sample