Pages

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.

    1 comment:

    1. Having the final file with a .xls extension as a result but based on an XLSM layout (renamed to .xnv) results in an error being issued at the time the user attempts to open the file. What can be done to automate this process of recalculating, as well as getting the file to wind up with a .xls or .xlsx file extension?

      ReplyDelete