Troubleshooting Custom Functions

Custom functions can behave differently depending on your local system software and Office or Excel version and if you're using a template

C
Written by Chris Ware
Updated over a week ago

OVERVIEW

Cryptosheets power templates often contain custom functions that require a minimum version of Office or Excel to be installed. If you load a template but the data doesn't pull through, try using the below steps to troubleshoot and resolve.

❓  TIP  Look for "Hover for help" and "legend" cells in templates for a quick reference

SCENARIO:

⚠️ PROBLEM
I loaded my template but the formulas are broken, say #NAME? or show another error?

  • We create templates using the latest version of custom functions but not everyone has the latest version of Office or Excel installed. 

  • To prevent destabilizing your Excel environment Microsoft may automatically convert the Cryptosheets custom function into a dummy static placeholder if it thinks it detects an incompatible version or you're logged into an Office account that hasn't been upgraded.

Technically the FUNCRES.XLAM refers to both a storage container/module for Excel custom functions as well as the popular legacy Analysis Toolpack add-ins. 

As explained by Microsoft the issue often arises from a custom function logic & security collision where the security and certificate trust settings of legacy VBA/macro based custom function(s) & add-ins are not compatible with of new generation custom functions and certain instances of Excel 365.

😄 SOLUTION(S)

  1. Find the active formula cell  in the template (usually color coded light gray with orange text*) and adjust the formula syntax manually to read correctly as shown then press enter to refresh the formula

  2. Try using a blank new workbook with NO VBA, macros, activeX objects and/or other legacy Excel artifacts or objects

  3. Make sure you're logged into your Office 365 subscription account withIN Excel itself (click upper right hand corner with name and person icon)

  4. Close one or both of your Excel desktop and/or Excel Online open windows
    > Use Task Manager to end the task for the Excel desktop application
    > Reopen the Excel workbook in question from INside of Excel online using File > Open

ADDITIONAL RESOURCES

👉 For Microsoft Excel

👉 For Googlesheets

Cryptosheets is free to sign up and start using now. What are you waiting for?

Signing up for Cryptosheets is free --> Get crypto data in 30 seconds or less

Website  |  WebApp  |  Excel  |  Googlesheets  |  Help Center  | Blog  | Twitter
______________________________________________________________

TAGS : #_xldudf, #udf, formulas, custom functions, troubleshooting, error, help, no data, #NAME? #N/A, excel, googlesheets, spreadsheet, template, CS.QUERY, CSQUERY, #xludf, FUNCRES, FUCNRES.XLAM, xll, #_xldudf_, #udf

Did this answer your question?