Adding an nVision Instance Hook to Call a Macro that Recalculates all Cells

Adding a Macro to nVision Layouts to Force Recalculation of All Cells when the Report is Run

 

As reported in my post Excel Formulas Not Calculating, some older nVision reports that were originally developed in legacy versions of Excel and now being run in Excel 2010 do not recalculate some formulas.

The quick workaround is to force recalculation of all cells. Excel is efficient with resources when it recalculates a spreadsheet and only recalculates those cells that it recognizes as having changed since they were last recalculated. This issue arises because Excel isn't identifying some cells that need to be recalculated. Typing F9 (recalculate) doesn't work, However, typing Alt+Ctrl+Shift+F9 will force Excel to recalculate all cells, even those it thinks don't need to be recalculated.

An option for layouts that have been identified with this issue is to add a macro that performs the above keystrokes, and then call that macro using an nVision instance hook.

Here are the steps to do that.

Recording a Macro and Adding an nVision Instance Hook

• Open layout
• From the Developer ribbon, click Record Macro
   
   
  
  
  
  
• Name the macro something simple and descriptive and store in “This Workbook”. Click OK.
  
   
  
  
  
• Type Alt+Ctrl+Shift+F9 and immediately click Stop Recording
  
                   

   

Establish NvsInstanceHook

Type Ctrl+F3 and click New to add new name

Type Sheet1!NvsInstanceHook in the Name field (be careful not to make any typos)

Type =Recalc (or whatever it was you named your macro) in the Refers to field

Click OK          

 

 

 

The instance hook will appear like this in the names list.

 

 

 

Close the Name Manager

Saving the layout as a macro enabled workbook

Since Excel 2007, Excel files which have macros associated with them must be explicitly identified by the .xlsm file extension. So we now need to identify this file as one that contains a macro, otherwise we will experience an error when we run the report.

• When saving you will be presented with the following dialog box. Click No.
   
  

   
   
• Save the layout as a .xlsm file  
• Close the .xlsm file
• Using Windows Explorer confirm you now have two files with the same name, but one is the .xnv file, and the other is the .xlsm file. Delete the original .xnv file from the layout folder and then Change the .xlsm file to .xnv
• Accept the warning when you change the file extension

 

Test – the nVision instance should be saved as an xlsm file.