Diligence for Excel
Support and Self Help
For Diligence v2.4
This is the starting point for all Diligence help & support.
If you have encountered an error, then please start by reading the support & self-help section below.
If you required more information on a particular subject, then you may wish to start by reviewing it in the Diligence manual, available online in PDF format HERE
We are here to help, so if you have reviewed and checked the necessary data, and are still unable to resolve your issue, then please contact us
Support@DiligenceForExcel.com
This online manual is best navigated by clicking the relevant Contents Index item below which will take you directly to the chapter of interest, or alternatively finding the topic required by pressing Ctrl-F and entering the appropriate text at the bottom of the screen.
Copyright 2016. DiligenceForExcel.com. All rights reserved.
This manual may not be copied in whole or part without the express prior written permission of the copyright holder.
Contents
1. Try it in a new instance of Excel
2. Are other addins or programs causing the problem?
6. Disable Macros / Macro Security Settings
7. Switch it off and on again!
Limitations, Issues & Known Anomalies & Errors
General Limitation: Workbooks containing ‘Event’ macros
General Limitation: User Defined Functions
Known Issue: Excel Cell ‘Dependency’ Issue
Known Issue: Recent File List: Addins will not open when double-clicked from the recent file list.
Support & Self-Help Checks
Below are some checks that are useful to perform before you contact us for support, which relate to the” Three C’s” (contents, connections and corruptions) of your file that are all outside of Diligence control. If you contact us it is likely that these will be the first checks that we will ask you to perform and they will often resolve the issue…
Please undertake the following actions, which are ordered according to the probability of resolving errors and ease of effort ….
1. Try it in a new instance of Excel
Start a new instance of Excel, open only the file in question and re-run the function. Is the result the same?
2. Are other addins or programs causing the problem?
Other addins / com-addins / dll’s or other ‘installed’ programs may be interfering with Diligence’s functionality…Open a new instance of Excel and enter ‘Test’ on cell A1 of a new blank workbook, then re-try the function. If it failed, the likelihood is that there is some external interference. Please contact us for advice.
3. Superfluous Styles?
Due to a legacy Excel error, superfluous styles can build up that cause all manner of issues, eventually resulting in an unusable file. (See the ‘Remove Superfluous Styles’ section in the main manual for more details) From the Diligence menu select ‘My Diligence’ then the ‘Utilities’ tab and finally ‘Remove Superfluous Styles’
This will initially alert the user to the number of styles being
used. Any number under a few hundred is usually ok, but over this it is
recommended to continue with the operation and remove the superfluous styles. Now save and retry in a new instance of
Excel.
4. Does the workbook compile?
This checks whether there are any missing references or incompatible code within the workbook. Any anomalies can affect Excel’s ability to return certain information to Diligence and hence make it impossible for Diligence to complete its function successfully.
Firstly, select the workbook in question.
Now display the VB editor by pressing the ‘Alt’ and ‘F11’ keys
simultaneously. The result should be a new window that looks similar to that
shown below…
Now from the Debug menu, press the first option ‘Compile VBA Project’ (or similar). If there are any errors displayed these are the most likely cause of the problem and should be resolved. If no message is received then it can be assumed that the file compiled without issues (This can be checked by attempting to compile a second time, where the Compile menu will be disabled)
Please note that Diligence can only assist with third-party workbook issues on a chargeable basis.
5. Corrupted file?
File corruptions are relatively rare in the newer xml file format (.xlsx. .xlsm. .xlsb) but more common in the older (.xls) file format, especially where the file has been saved over and over. Corruptions do not necessarily show up immediately to the users and can cause a variety of effects on calling functions such as those employed by Diligence, often resulting in errors.
Make a backup of your file and then ….
If your file is in an old file format (.xls) then save it in the newer file format (.xlsx for normal files, .xlsm for workbooks containing macros and/or .xlsb for large spreadsheets) and re-try in a new instance of Excel. If the function now works it is likely that the original file had a level of corruption and should be investigated (or the new file used henceforth).
If your file is in one of the newer .xlsx or .xlsm formats, save it as an .xlsb and re-run the function in a new instance of Excel.
6. Disable Macros / Macro Security Settings
If the problem is specific to just one, or a few but not all files, we need to know whether macros within the workbook(s) are effecting Diligence’s operations. For example, code in other workbooks or addins may be ‘taking over’ when a new report workbook is created.
If possible, check your macro security settings, by selecting ‘Trust Center’ from Excel options, and then pressing ‘Trust Center Settings’ (note this may be disabled by your company so may not be possible for all users)
 |
From the resultant userform, select ‘Macro Settings’ and ensure the option to ‘Disable all macros with notification’ is selected and press ‘OK’ if necessary.
 |
Now open the file with macros disabled in a new instance of Excel
Close all open instances of Excel, and open one new instance. Now open
the problem workbook. If the previous steps were followed and the workbook OR
ANY OTHER AUTOMATICALLY OPENED WORKBOOK contains macros/VBA, a security notice
will be displayed…press ‘Disable Macros’. If no notice is displayed, then the
workbook does not contain macros. Re-try the function.
If the function now works, it is likely the problem exists within the macro section of the workbook, or possibly other auto-opened workbooks. Diligence support ‘may’ be able to assist, but only if the workbook is supplied to us.
7. Switch it off and on again!
Yes, we know the connotations and humour attached to this well‑known phrase, but both Excel, and Windows itself can get clogged up with data over a surprisingly short amount of time. Rebooting from scratch and re-trying the required function immediately will at the very least prove that memory leakage is not a contributing factor to the problem.
Sending files to Diligence Support
If the problem persists we will be happy to help. Diligence Support can usually respond to queries with much greater speed and accuracy when the workbook in question is sent to us. To assist with any security implications, users may choose to employ the ‘Sanitise Active Workbook’ utility (My Diligence / Utilities / Sanitise Active Workbook) which replaces all numbers with ‘1’ and all text with ‘Text’. See ‘Sanitise Active Workbook’ in the full manual for more information.
Send your file and as much additional information as possible to Support@DiligenceForExcel.com
Limitations, Issues & Known Anomalies & Errors
Function‑specific limitations are described in more detail in function specific chapter. Where applicable, this section only describes brief summaries and pointers to those limitations. See the chapter for more detail…
General Limitation: Workbooks containing ‘Event’ macros
Workbooks containing event macros linked to opening, closing or saving the workbook, or activating or deactivating worksheets are likely to interfere with many of Diligence’s functions. If problems are encountered, it is recommended that these files are closed and re‑opened with macros disabled.
General Limitation: User Defined Functions
The use of User Defined Functions adds a new dimension of both advantages and risks to a spreadsheet. Diligence acknowledges and records the use of all UDFs in the workbook being analysed, but UDFs can also be called from references files or a Personal Macro Recorder, for example. These external UDFs are not currently acknowledged by Diligence and will be missing from the Formula List on the File Summary Report, and the Sheet Complexity comment on the ToC.
Known Issue: Excel Cell ‘Dependency’ Issue
When does it Occur?: When creating a Table of Contents or Analysis Workbook
What Happens?: Diligence appears to hang. The status bar will usually stop on a message ending “analyzing cell 2,500 of 132,000” (or equivalent)
Why does it Happen?: An undocumented issue in Excel means that under rare circumstances (and usually on worksheets exceeding 100,000 used cells) when a third party request (like Diligence) requests the list of dependent cells for a given range, Excel does not return its answer automatically.
Workaround: Press the ‘Esc’ key. The user will be prompted if they want to Cancel, to which they should answer ‘No’ …the data is then usually immediately returned by Excel and the analysis resumes.
Issue Status: Diligence is unable to effect the Excel function. No additional work is planned on the issue by Diligence.
Limitation: Recent File List
In line with the Diligence philosophy of non-interference, the recent file list (as available from within Excel) is the ONLY data source used by Diligence, and ONLY recorded when Diligence is first opened (usually when Excel is opened). A maximum of the 50 latest files are recorded by Excel, and this number can be reduced by the user in advanced settings. Therefore, if the user opens more than maximum number of recorded workbooks in any one instance of Excel, only the latest files will be recorded for the purposes of the Recent File List.
Known Issue: Recent File List: Addins will not open when double-clicked from the recent file list.
When does it Occur?: When attempting to open an addin by double clicking the file from the recent files list AND the an addin with that name has already been opened during that Excel session.
What Happens?: Diligence states that the addin is already open and stops processing.
Why does it Happen?: An Excel anomaly means that the Excel reports the addin as being open when it has been closed.
Workaround: Open a new instance of Excel and retry.
Issue Status: Diligence is unable to effect the Excel function. No additional work is planned on the issue by Diligence.