EBMS Excel Add-In Troubleshooting

The Excel add-in is a powerful tool that allows the user to use MS Excel tools to calculate data accessed directly from EBMS.   This EBMS option is designed for users who have experience entering formulas within MS Excel.   The following steps may assist you in troubleshooting a user's spreadsheet or give the user some tips in using the Excel add-in effectively::

• The Excel Add-in requires the 32-bit version of MS Excel rather than the 64-bit version.   Review the current version of MS Excel by selecting Account > About Excel from the main Excel menu as shown below:
  

• EBMS Add-in feature does not show within MS Excel:   Review Add-in tab does not show within Excel if the EBMS functions are not available within MS Excel.   

• The Add-in feature shows but the EBMS functions are not available: Review Add-in tab does not show within Excel section.

• A #VALUE, #NAME, or other excel error message cannot be found: Review Add-in Error Log for more details about an error.

• Excel is calculating and loading information slowly within the Excel spreadsheet: Review Speed Issues with Excel Add-in for some possible options.

• The EBMS server is impacted when user(s) are opening an Excel spreadsheet: Review Speed Issues with Excel Add-in for some possible options.

Troubleshooting tips:

1. Test the calculated values: Verify a new spreadsheet or dashboard by comparing the calculated totals with a report, totals displayed in EBMS, or other method of validating totals.  

2. Start Simple:  Many formulas are nested with optional parameters to accomplish the desired result.   Replace nested formulas with constants, remove ranges, or simplify variables to troubleshoot a possible issue.   Start simple and add the variables, parameters, and nested functions after testing each additional factor.  

3. Copy nested functions to a blank cell:  A -460 database error indicates a query error from a probably nested function.  Insert the nested function such as an EBMSQuery function into a blank cell in the spreadsheet and evaluate the results.  Example: =EbmsQuery("Account>='50000-000' .and. account <='59999-999' .and. '-"&LEFT(A29,3)&"' $ ACCOUNT")  will return text that is prefixed with %Q or similar characters.   The additional text can be evaluated to identify syntax errors.

4. Solicit help from online spreadsheet samples or from  a Microsoft Excel expert within the office if the user does not have extensive MS Excel experience.  Koble Systems may have some sample spreadsheets available but offers limited MS Excel training.  

Common issues:

1. Result does not show properly:  Prefix the root EBMS function with the '=' sign to communicate the result of the function into the specific spreadsheet cell:  Example: =EBMSHistoryTotal( "GL.Difference", EbmsRange("50000-000", "59999-999", 2019, 4, 2019, 6) places the numeric result into the cell

2. Result does not show properly:  Format the Excel spreadsheet cell to match the function result.     The Catagory General can be used for both text and number results.