Submitting your feedback...
Knowledge Base Article
KB00804 - VBA Errors in Microsoft Excel
Product: PI BatchView / PI DataLink / PI Tag Configurator
Version(s): All
 

Issue

As you use Microsoft Excel with one or more PI add-ins enabled (PI DataLink, PI Tag Configurator, or PI BatchView for Excel), you may encounter one of the Microsoft Visual Basic for Applications errors below. You may also see these errors when you open Microsoft Excel or use certain functions within the add-ins, such as the Insert Trend function in PI DataLink.
  • Could not load an object because it is not available on this machine
  • Compile error in hidden module: modAddin
  • Compile error in hidden module: Main
  • Object library invalid or contains references to object definitions that could not be found
  • Run-time error ‘459’: Object or class does not support the set of events
  • System Error &H80040111 (-2147221231). ClassFactory cannot supply requested class
PI Excel add-ins make use of certain Microsoft-provided ActiveX controls. When there are problems with these controls, add-ins which use these controls will encounter them and produce errors. 

These error messages may also be caused by the Microsoft security patches, such as MS12-060. See KB00686 for more information.

Solution

These errors are often related to a malfunctioning Microsoft ActiveX control used by an add-in, or by old cached VBA files. The following steps may help to resolve this issue. 

Verify required prerequisites and settings

  1. Make sure that the add-ins and related items are installed properly. For example, if the PI SDK is not installed, that will affect many of the PI add-ins.
  2. Confirm that the ActiveX controls are enabled (not disabled). In Excel select File> Options> Trust Center> Trust Center Settings> ActiveX Settings. This should NOT be set to 'Disable all controls without notification'.

Check for cached *.exd files

An .exd file is effectively a temporary cache that expedites internal lookups for VBA calls. In general, when an ActiveX control is inserted into a file (such as a Word document, Excel spreadsheet, or ProcessBook display), an .exd file will be created for that control in the %temp% folder. The .exd is not deleted when the program exits so it is available the next time the program runs. These files can be the source of VBA errors, particularly when runtime files are updated through Microsoft patches.

Search for all files (including hidden files) with the extension *.exd. Note that by default, Windows does not search for hidden files, so you must change that setting to see them. To find all *.exd files in a given hard drive:
  1. Open a command prompt and navigate to the root directory of the drive (for example, by first entering “C:” and then “CD \” to get to the root of the C: drive),
  2. Run the following command:
dir *.exd /b /a /s
If you find any exd files, delete them using the following command, and then reopen Excel:
del *.exd /s

Repair non-functioning ActiveX controls

VBA errors often occur when Microsoft-provided ActiveX controls are not working. It may be difficult to isolate which control(s) are affected, but in our experience ListView2 and RefEdit are common culprits.
  1. Determine if ListView2 and RefEdit are working properly.
    1. Open up Visual Basic Editor in Excel (Alt+F11).
    2. Right-click on the sheet in the Project pane, and Insert a UserForm.
    3. Open up the Toolbox (View >Toolbox).
    4. Right-click in the Toolbox and select Additional Controls.
    5. Select the checkboxes for RefEdit.Ctrl and for Microsoft ListView Control, version 6.0, and then click OK.
    6. In the Toolbox, drag the ListView control to the UserForm. Do the same with RefEdit.
If either of these actions causes an error, there is definitely a problem with the corresponding file (MSCOMCTL.OCX for ListView, and refedit.dll for RefEdit). If there is no error, this is not necessarily an indication of a clean bill of health, as there are other ActiveX controls which may have problems.
  1. If RefEdit is the affected control, delete or rename RefEdit.dll and repair Office.
    1. Open C:\Program Files (x86)\Microsoft Office\Office<version number>.
    2. Find refedit.dll and delete or rename the file. Microsoft Office will usually detect the missing file and initialize a repair the next time you open Excel.
    3. To repair Microsoft Office explicitly, open the Control Panel and choose Programs and Features (Add or Remove Programs in older versions of Windows).
    4. Select your Microsoft Office installation, click Change and then Repair. (In older versions of Windows, select support information and click Repair.)
  2. Most other controls (including ListView2), are in mscomctl.ocx. Try one of the following options to get a working, properly registered version of mscomctl.ocx.
  • (Option 1) Manually re-register mscomctl.ocx. Open a command prompt as Administrator and enter these commands (for 64-bit operating systems, reference syswow64 instead of system32):
regsvr32 -u C:\Windows\System32\mscomctl.ocx
regsvr32 C:\Windows\System32\mscomctl.ocx
  • (Option 2) Repair MS Office
    1. Run the following command (on 64-bit operating systems, you will need to reference syswow64 instead of system32):
regsvr32 -u C:\Windows\System32\mscomctl.ocx
  1. Rename C:\Windows\System32\mscomctl.ocx.
  2. Repair MS Office.
  • (Option 3) Download the latest version of mscomctl.ocx. 
    1. Download the OSIsoft Prerequisites Kit.
    2. Unzip the kit file. Open a command prompt as Administrator and enter this command:
msiexec /a <path_to_MSRuntimes.msi> /qb TARGETDIR=C:\Temp

(mscomctl.ocx will now be found in C:\Temp\Windows\System32 or one of its subfolders)

  1. Unregister the old file. In the directory containing the old mscomctl.ocx, enter:
regsvr32 -u mscomctl.ocx
  1. Rename the old mscomctl.ocx and then drop the new version into the same directory.
  2. Register the new mscomctl.ocx. At the command prompt, enter:
regsvr32 mscomctl.ocx

Repair relevant software

If you have not already done so, try repairing the installation of PI DataLink as well as Microsoft Office or Excel. If a necessary runtime file or reference is missing, then a simple repair will correct this.

Notes

This KB combines and replaces information from earlier KBs:
  • KB00364 - PI add-ins to Excel not available with various VBA errors after upgrading to Office 2010
  • KB00267 - Run-time error '459': Object or class does not support the set of events" or "Compile Error in Hidden module: Main" error message appears when opening Excel installed with PI DataLink
  • KB00526 - PI DataLink error: Object library invalid or contains references to object definitions that could not be found
  • KB00631 - PI DataLink Error: "System Error &H80040111 (-2147221231). ClassFactory cannot supply requested class
Article ID: KB00804 Created: 2013-06-14
Article Type: Informational Last Updated: 2017-08-22