Long load times in Excel 2013 if function locations are not specified

Symptoms

Consider the following scenario:

  • You open a Microsoft Excel 2013 workbook.
  • Add-ins are loaded in the workbook.
  • Lots of calls to user-defined functions in the VBA code are present in the workbook.
  • The locations of the functions are not specified in the calls to those functions.
In this scenario, you experience longer load times.

Cause

If the location of a function is not specified in a function call, Excel is forced to search all possible sources of the function for every call that is made. These sources may include add-ins, a Visual Basic for Applications (VBA) project, and so on. Depending on the size of the sources and the number of function calls in the workbook, this situation can greatly affect the load time.

Resolution

To resolve this issue, specify the location of the function in the function call. To do this, include the path in the formula so that Excel can directly address the correct add-in during calculation instead of having to search for it: 
C:\my path\my add-in.xlsm'!myfunction()

The following is an example of a function call whose location is explicitly specified:

Filename.xlsm!Function1()

More Information

A typical scenario follows.

You have an Excel add-in loaded in which lots of functions are defined. You also have an Excel workbook that uses several simple user-defined functions in a VBA project. The workbook has lots of function calls. However, most of them call only one of the few user-defined functions in the VBA project. If you don't specify the function location (in this case, the VBA project), Excel will search both the very large-add in and the small VBA project during the loading process for the workbook every time that it encounters a function call in the workbook. If you specify the function location, Excel can skip this check for each function call.
Egenskaper

Artikel-id: 3017197 – senaste granskning 26 nov. 2014 – revision: 1

Feedback