QI Macros Technical Support Archive

Most Common Support Questions for Excel 2000-2003

Common Problems & Questions

* We do not formally support QI Macros for Excel 2000-2003, however if you are using these versions of Excel, contact us for the necessary install files:

Excel 2003

Excel 2002

Excel 2000

 

Find Solutions to Rare Issues & FAQs for Older QI Macros Versions

See our main tech support page for:

  • the most common QI Macros issues and questions
  • installation instructions and troubleshooting
  • uninstall instructions
  • issues specific to each version of Excel

QI Macros Menu Issues

Addin menu still shows old QI Macros Menu (Excel 2007)

July 2009+ versions of the QI Macros remove this menu automatically.

To remove this menu manually, go to:

C:\Documents and Settings\user name\Application Data\Microsoft\Excel

and delete Excel12.xlb (this file stores the old menu).

No QI Macros Menu Appears

Excel has disabled one or more of the QI Macros files. The process for enabling a file is different depending on your version of Excel.
If there are no disabled files check to see if the QI Macros are installed
  • Window 7 and 8 machines: Go to c:\Program Files(x86)\Microsoft Office\Office(10,11,12, 14)\Xlstart -or- c:\Program Files\Microsoft Office 15\root\Office15\Xlstart -or- c:\Program Files(x86)\Microsoft Office\root\Office16\Xlstart
  • Windows Vista and XP: Go to c:\Program Files\Microsoft Office\Office(10,11,12, 14)\Xlstart
  • View the contents of the XLstart folder, it should look like this:
  • install-file-location

  • If your XLStart folder contains some kind of temporary file, delete the temporary file and see if this solves the problem.
  • If your folder is missing the .xlam file, then close Excel and reinstall the QI Macros.
  • If your XLStart folder contains the files, then Excel has disabled them. See above instructions.
If Excel Continues to Disable the file

You may have Macro Security set too high.
Click on Tools-Macros-Security and set to medium or low.

You may have Addin security set too high in Excel 2007-2010. Click on the Office Button-Excel Options-Trust Center-Trust Center Settings-Addins, and uncheck both boxes.

 

No QI Macros Menu Appears and there's a Run Time Error

It's possible that Visual Basic For Applications (VBA) was not installed or is disabled. To check, right click on any sheet name and look at the menu. If "View Code" is grayed out (see below), VBA is disabled or not installed.

To enable VBA, you may need to 1)) get into the registry (per Microsoft) of 2) install VBA.

If VBA is installed, it may be disabled. If one of the following DWORD registry keys exists and has a value that is greater than 0, VBA is disabled. Change it back to zero:

Microsoft Office 2010
HKLM\Software\Microsoft\Office\14.0\Common\VBAOff
HKCU\Software\Microsoft\Office\14.0\Common\VBAOff
HKCU\Software\Policies\Microsoft\Office\14.0\Common\Vbaoff

Microsoft Office 2007
HKLM\Software\Microsoft\Office\12.0\Common\VBAOff
HKCU\Software\Microsoft\Office\12.0\Common\VBAOff
HKCU\Software\Policies\Microsoft\Office\12.0\Common\Vbaoff

Microsoft Office 2003
HKLM\Software\Microsoft\Office\11.0\Common\VBAOff
HKCU\Software\Microsoft\Office\11.0\Common\VBAOff
HKCU\Software\Policies\Microsoft\Office\11.0\Common\Vbaoff

Microsoft Office XP
HKLM\Software\Microsoft\Office\10.0\Common\VBAOff
HKCU\Software\Microsoft\Office\10.0\Common\VBAOff HKCU\Software\Policies\Microsoft\Office\10.0\Common\Vbaoff

To Install Visual Basic for Applications
  1. Exit Excel and Open the Control Panel
  2. Add/remove programs - Microsoft Office 20??- Add/remove features:
  3. Choose Advanced Customization of Applications:
  4. Click on Shared Features:


  5. Install Visual Basic For Applications:
  6.  

  7. Start Excel - you should see the QI Macros menu in your Excel ribbon.

"Missing Text" Menu

If you get a qimacros menu that looks like this:



You have renamed the qimacros.xlam file to some other name (e.g., qimacros1.xlam).
When you do, the menu cannot find the menu text.

Solution: Rename the qimacros file in c:\program files\microsoft office\office11\xlstart to qimacros.xlam.

Pop Out Menus missing (Excel 2003)

Excel 2003: Main menu appears, but there are no choices; just a blue square.

Cause: If Excel or Windows crashes it may leave a ~tmp...xlam file in the XLStart folder. This file gets loaded instead of the qimacros.xlam file. To check this, right click on a sheet name and choose view code. There should only be one QIMacrosSPCforExcel2007 (qimacros.xlam) listing in XL97-2003. (XL2007 will have a qimacrosmenu.xlam for the ribbon)

view-code

vba-view-code

Solution: Close Excel. Go to c:\program files\microsoft office\office11\xlstart and remove the ~tmp...xlam. Reopen Excel and the problem should be gone.

QI Macros menu is not in the language I want

During the installation process, the QI Macros will identify the International version of Excel you are using and load the appropriate language. This language will be used for the pull down menu and any prompts or error messages.

Like the help files and user guides, these translations were done mechanically. If you find any errors or unclear translations, please email us suggestions.

To change the default language:

In Excel 2003 or earlier versions
  1. Click on Data Transformation Tools and then Change Default Language:

  2. The default language window will open:

  3. Input the number that corresponds to the language you want to use. Click ok and the change will be made. Here is an example of the French pull down menu:

 
In Excel 2007:

Click on the QI Macros Help menu and then select Change Default Language:



QI Macros menu shows symbols only, when language changed to Chinese

When changing the default language in Excel 2010 to Chinese, if you see Chinese symbols, instead of words, e-mail support@qimacros.com and we will provide you with a new QI Macros 2010 Menu.

-or-

If you press CTRL-SHIFT-Q, Excel 2010 will load the 2003-style Add-in Menu, which is translated.

QI Macros shows up twice on the menu bar

Windows 2000: For some reason, Windows 2000 "auto opens" the macro twice. It won't affect your performance and either menu will work. I've opened a ticket with Microsoft.

Win XP, 2003: You probably have installed the macros and created an alternate startup link to the macros (you load them twice). Go to TOOLS-OPTIONS-GENERAL and clear the Alternate Startup Folder entry.

Win 2007-2010: You probably have installed the both types of menus: qimacros2003menu.xlam and qimacros2007menu.xlam or qimacros2010menu.xlam.

QI Macros does not appear in Ribbon Menu, in an Active Directory System

If after install, the QI Macros menu does not appear in the Excel ribbon, but the menu file is loading, close Excel and go to Registry (Start menu > type regedit into the search window):

registry-location

Then, press Ctrl + F, and in the search window enter: NoExtensibilityCustomizationFromDocument

1.) Under "Look at", select Values and Match whole string, and deselect Keys and Data:

registry-value

2.) Click Find Next. Change the displayed Data value from 1 to 0.

3.) Then press F3 to go to the next entry, and again change the Data value from 1 to 0. Repeat this until there are no more entries attributed to NoExtensibilityCustomizationFromDocument containing a 1 instead of 0 in the Data value. (You may find 0 values along the way that can be ignored. However, keep searching until all occurrences have been viewed).

QI Macros Menu shows "Order QI Macros"

Resolved with July 2009 Release.

QI Macros Menu Disappears

If you:

  • click on the X in the upper right hand corner to exit Excel
  • or accidentally click it while trying to close a workbook
  • or choose File-Exit

and then Cancel before exiting Excel, the menu will disappear, because we have to remove the menu prior to exit.

Simply exit and restart Excel.

QI Macros won't install in correct Version of Excel

If you have two or more versions of Excel on your PC, the installer assumes that you want to put the QI Macros in the most recent version.

If you buy a new machine, for example, that comes with a 60-day trial of the latest Office programs and then install Office 2000, XP, or 2003, Windows still wants you to try the new version! If you want to change this you can update the registry:

  1. Click on Start-Run and type "regedit"
  2. Click on the + for HKEY_CLASSES_ROOT
  3. Scroll down until you find Excel.Application and click on it's + sign.
  4. Click on CurVer. You should see something that looks like this:


  5. Double click on (Default) and change the version to:
    • Excel.Application.9 (XL 2000)
    • Excel.Application.10 (XL XP - 2002)
    • Excel.Application.11(XL 2003)
    • Excel.Application.12 (XL 2007)
    • Excel.Application.14 (XL 2010)
  6. Reinstall the QI Macros (this won't work with the 30-day trial).

Alternatively, find where the QI Macros were installed and move them to the correct folder.

  • XL 2000 - c:\program files\microsoft office\office\xlstart
  • XL 2002 - c:\program files\microsoft office\office10\xlstart
  • XL 2003 - c:\program files\microsoft office\office11\xlstart
  • XL 2007 - c:\program files\microsoft office\office12\xlstart
  • XL 2010 - c:\program files\microsoft office\office14\xlstart
  • XL 2013 - c:\program files\microsoft office 15\root\office15\xlstart
  • XL 2016 - c:\program files(x86)\microsoft office\root\office16\xlstart

Upgraded Excel and QI Macros, but still see old version of QI Macros (or)
"No RETURN() or HALT() function found on macro sheet" error

When you upgraded Excel, it moved all of the old QI Macros files to:
Windows XP:
- c:\Documents and Settings\User name\Application Data\Microsoft\Excel\XLStart.

Windows 7:
- c:\users\User name\AppData\local\virtualstore\program files\Microsoft Office\Office14\XLStart.

  1. Simply delete all of the *.xlm or *.xlam files from this folder and open Excel to bring up the new version.
  2. If you cannot find these files, search your hard drive for any file named qimacros(.xlm or xlam) or zqimenu.xlm. Remove the xlm files that are not in c:\program files\microsoft office\office..\xlstart
  3. Excel 2000 - Service Pack 3 will fix this problem. Download it here: http://support.microsoft.com/kb/326585/en-us

Also: According to clients, the Excel Y2K updates from Microsoft for DateWatch and DateFix can cause this error. To disable these tools, go to Excel's TOOLS-ADDINS page and remove the tools. Other addins may cause this problem. Turn them off one at a time until you find the culprit.

Change QI Macros Menu to Old or New Version

QI Macros menu has recently been updated and re-organized. If you prefer using the original menu, e-mail support@qimacros.com, and you will be sent a file that will automatically switch between the two.

If you would prefer to change the menu manually, follow the instructions below:

Completely close Excel and open your XLSTART folder, located in the following location:

C: > Program Files (x86) > Microsoft Office > Office15
-or-
C: > Program Files > Microsoft Office 15 > root > Office15

Once you have opened your XLSTART folder, you will find a QITemplates folder, along with both a qimacros.xla file.

Open the QITemplates folder. Then, copy the QIMacros2010menu.xlam file (right-click > copy), and paste it into the XLSTART folder:

Next, drag the QIMacros2014menu.xlam file into the QITemplates folder, so it is no longer in the XLSTART folder:

Open Excel – the QI Macros menu has been changed to the original format.

Worksheet menu bar displays the ' Chart' menu no matter what I do (Excel 97-2003)

Don't know what caused this for a customer, but I found a way to resolve it using VBA.

Download the file by clicking here and it will activate the menus when it opens.

WinZip asks for user name and password

If you have installed a trial of WinZip on your computer and it expired, WinZip has become the default program to open zipped files. You might consider removing WinZip.

Solution: Use Windows-Compressed (zipped) Folders tool to extract the contents. Right click on the zip file and choose Open With-Compressed (zipped) Folders:


Windows Vista - Excel XP opens old version

Vista stores the old version in:
C:\Users\username\AppData\Local\VirtualStore\Program Files\Microsoft Office\Office10\XLStart

To resolve this issue:

  1. Close Excel
  2. Open this folder and delete qimacros.xlam and QITemplates folder.
  3. Then start Excel


Excel Issues and Behavior

Data Mining and PivotTable Wizards, along with Word Count do not Respond in Excel 2016 Professional Plus

"I have a version of QI Macros that was released in February of 2016 or more recently, however when I try to run the Data Mining Wizard, PivotTable Wizard, or Word Count in my Excel Professional Plus, nothing happens. Why is this the case???"

Unfortunately, these 3 tools are not functional in Excel 2016 Professional Plus, as this version of Excel does not respond to VBA code pertaining to PivotTables:

pivottable-pro-plus

While we are currently awaiting a fix from Microsoft for this issue, note that all other functionality and tools found in QI Macros are compatible with Excel 2016 Professional Plus.

Excel quits after loading QI Macros and Risk Solver or other Add-in

Addins sometimes use the same procedure names which confuses Excel.

If you have recently upgraded Risk Solver or other Addin, the July 2009 release of the QI Macros was revised to prevent these most of these kinds of conflicts.

Workaround: Open Excel (to load addins), then open files.
Double clicking on a file to start Excel will still result in Excel quitting unexpectedly.
I have spoken with Excel techsupport and they have no insights or workarounds.

Order an upgrade (item #232) at www.qimacros.com/store/

VBA: "Class Not Registered"

If you receive the VBA error message, "Class Not Registered," this may be because there is a missing or incorrect Microsoft Windows registry entry for the Dynamic Link Library Fm20.dll.

vba-class-not-registered

There are 2 workarounds for this:

  1. Register the Fm20.dll file: click here for more information.
  2. - OR - reinstall Microsoft Office, as at least (1) of the files was corrupted.

Antivirus prevents loading qimacros.xlam

For some reason, Norton Antivirus prevents loading qimacros.xlam.

First, check trusted locations (XL 2007/2010) to make sure the xlstart folder is trusted.

If not, you can run Live Update to get the latest version of the Office Plugin
or you may have to turn off the Office plugin for Norton Antivirus:

Open the antivirus software, click on Options - Miscellaneous and uncheck the Office Plugin. See: http://support.microsoft.com/kb/329820

 

Excel Opens or Closes Slowly (2000-2016)

Excel.xlb Growth

On the PC, Excel stores the toolbar configuration in a file in:
C:\Documents and Settings\user name\Application Data\Microsoft\Excel

Excel 2002: Excel10.xlb
Excel 2003: Excel11.xlb
Excel 2007: Excel12.xlb
Excel 2010: Excel14.xlb
Excel 2013: Excel15.xlb
Excel 2016: Excel16.xlb

These files grow with every open/close of Excel with the QI Macros versions dated 3/07-9/09.

If you would prefer to contact us, we can provide you with a .xlb file removal tool.

If you cannot find the Application Data folder, you may have to show hidden files. Double click on MyComputer and the c: drive. Then click on Tools-Folder Options and select Show Hidden Files:

Changes will be released in September, 2009 to prevent the growth of these files. You may upgrade at: www.qimacros.com/store/.

To resolve this problem temporarily, navigate to the folder and right click on the .xlb file and rename it. Excel will create a newer, smaller one as shown in the example below.

Preventing Excel11.xlb growth causes a different issue:
Clicking on Excel's "Close" button instead of the "Close Window" button to close a file, will remove the QI Macros menu from your toolbar in Excel XP and Excel 2003.
To restore the QI Macros Menu, simply hold down the Control-Shift Keys and press Q.



Antivirus Issues

See Microsoft Support Article
Microsoft Excel 2000 and 2002

SYMPTOMS
When you open one of the Microsoft Office programs listed at the beginning of this article, the program may open slowly.

CAUSE
This behavior may occur if Norton AntiVirus is installed on your computer. It will scan the QI Macros EVERY time you start Excel.

RESOLUTION OPTION 1
To resolve this problem, unregister the Norton AntiVirus Officeav.dll add-in by following these steps:

  1. Click Start and then click Run.
  2. In the Open box, type the following command (including the quotation marks), and then click OK:

    regsvr32 /u "c:\program files\norton antivirus\officeav.dll"

If the command is successful, you see the following message:
DllUnregisterServer in C:\Program Files\Norton AntiVirus\Officeav.dll succeeded. NOTE: If the command is unsuccessful, search your computer's hard disk for the Officeav.dll file. The file may be located in a different folder.

RESOLUTION OPTION 2

If you don't want to change your Norton settings AND if you don't use the QI Macros often, you can move the QI Macros files from the xl startup folder and only have Excel access them when you need to create a graph. Here's how:

  1. Go to c:\qimacros and create a folder called xlstart.
  2. Go to c:\program files\microsoft office\office\xlstart and move (not copy) the QI Macros files from the c:\program files\microsoft office\office\xlstart folder to the c:\qimacros\xlstart folder. Move the contents of the xlstart folder not the folder itself. QI Macros files include the QI Templates folder and all of the QI Macros .xlm files including the zqimenu.xlm file.
  3. When you open Excel it will no longer see the macros in its startup folder and Excel should open faster. Note: QI Macros pull down menu will be gone.
  4. When you want to use the QI Macros, go into Tools/Options/General and type c:\qimacros\xlstart in the alternate startup box. This box is labeled "At startup, open all files in:"
  5. You may need to restart Excel. Excel will open slowly since Norton is checking all of the macros. When Excel opens you should now see QI Macros menu.
  6. When you are done running charts, delete c:\qimacros\xlstart from the alternate startup file box. Excel will open faster again.

Excel Locks Up Or Is Very Slow To Close

Microsoft knows this is an EXCEL bug, but there is no fix to be found in their support pages.

Make sure it is not the .xlb issue listed above. If so, contact us and we will provide you with a .xlb file removal tool.

One customer, however, also said to go into Microsoft Outlook-Options-Journaling and deselect Excel, Word, and other files.

How to Turn Off the QI Macros Temporarily

Press the ESCAPE Key
When you start Excel, any files that are located in the XLStart folder or the alternate startup folder begin to open. You can press ESCAPE after the files start to open to prevent any additional files from opening.

Excel won't Start

Check the other xlstart folder accessed by Excel:
c:\document and settings\user\applicationdata\microsoft\excel\xlstart

Remove any unusual files (e.g., resume.xlw).

You may have a corrupted installation disk.

Excel 2013 Beta

Excel 2013 Beta versions are installed using Click-to-Run. They use a virtual file system which appears as a virtual Q drive. Unfortunately, you cannot access this drive and neither can the QI Macros. If you installed using Click to Run you have two options. If your Excel is running slow you may want to choose option 2 as this should speed up Excel.

  1. Install the QI Macros using the Alternate Startup folder.

    There are three steps to this process:

    1. Click on My Computer, the C drive and the QI Macros folder:

    If you have an XLSTART folder open it. It should look like this:
    If you do nxlstart-altstartup-locationot have an XLSTART folder, contact us at support@qimacros.com and we can send the folder and its contents to you. Let us know if you purchased the software or are using the 30-day trial.

    2. In Excel, click on File, Options and Advanced to open the window below. Scroll down to the General section and type the QI Macros location (c:\qimacros\xlstart\)in the box labeled "At startup, open all files in:"

     

    3. Close Excel and reopen it - the QI Macros menu should appear in your Excel ribbon.

    If your Excel is running slow, you might want to consider re-installing Excel using the traditional method.

Extra Blank Worksheet Appears (Book1)

QI Macros always open a blank worksheet (to mimic Excel at start up). If you double click on a file to start Excel, the information about your file isn't available until after the QI Macros have loaded. So the software cannot check for the existence of an open document to prevent opening a blank sheet. If this really bugs you:

If you purchased the QI Macros prior to Feb 20, 2006

  1. Close Excel
  2. Go to C:Program files/Microsoft Office/Office (10, 11 or 12) and find the zqimenu.xlm file.
  3. Right click on the file, select properties, and uncheck the READONLY box.
  4. Open Excel and click on Window-Unhide and select zqimenu.xlm
  5. In Column A, about line 16 you will find an IF statement that checks for Book1 before it opens a NEW workbook.
    = IF(ISERROR(GET.DOCUMENT(1,"Book1")),NEW(5))
  6. Clear this cell
  7. Select Window-Hide to hide the macro
  8. Exit Excel and save the zqimenu.xlm file over the existing one in the XLSTART folder.
  9. When you start Excel again, it will not open a blank workbook.

If you purchased the QI Macros between Feb 20, 2006 and Jan 1, 2007

  1. Close Excel
  2. Go to C:Program files/Microsoft Office/Office (10, 11 or 12) and find the QIMacros.xlm file.
  3. Right click on the file, select properties, and uncheck the READONLY box.
  4. Open Excel and click on Window-Unhide and select QIMacros.xlm
  5. In Column A, about line 7 you will find an IF statement that checks for Book1 before it opens a NEW workbook.
    = IF(ISERROR(GET.DOCUMENT(1,"Book1")),NEW(5))
  6. Clear this cell
  7. Select Window-Hide to hide the macro
  8. Exit Excel and save the QIMacros.xlm file over the existing one in the XLSTART folder.
  9. When you start Excel again, it will not open a blank workbook.

IBM iSeries add-in for Excel does not run when QI Macros is installed

Using multiple add-ins within Excel, simultaneously, can be problematic, as such is found when running the IBM iSeries Data Transfer tool while the QI Macros is installed.

Double check what version of the iSeries you are running, and that you have enough memory on your computer to run multiple add-ins.

If you require both add-ins within Excel, there is (1) workaround:

  • Take the qimacros.xlam file out of your XLSTART folder and place them onto your Desktop. This effectively disables your QI Macros software.
  • xlstart-contents

  • Start Excel and transfer all of your necessary data, using the IBM iSeries add-in, into a spreadsheet. From Excel, open the QI Macros files to enable the QI Macros software.
  • When you close Excel, this will again disable the QI Macros. Repeat same steps for each data transfer series that you import, if necessary.
  • Macros run when I hold down the shift key to capitalize a letter

    Your CTRL key is stuck. (This happened to me.)

    Turn your keyboard upside down and whack it a couple of times to dislodge whatever is making the keys stick. (Worst case: you may need a new keyboard.)

    Press both CTRL keys simultaneously to unlock them (pressing one or the other for longer can cause them to lock from a Windows perspective).



    Run Time Error Messages

    Run Time Error

    This is usually caused by something unusual in the data or labels you selected when running a chart. If you selected labels and data try just selecting the data.

    If you are still having problems, send your excel spreadsheet to us at support@qimacros.com. Include a description on what chart you were trying to run and what error message you received. We will take a look at your data and get back to you as soon as possible.

    Run Time Error 6 - Overflow

    Prior to March 2009, the QI Macros counters allowed up to 32,767 data points or rows.
    If you had more than 32,767 data points, you get a Run Time Error 6 (overflow).
    After March 2009, counters were changed to long to allow 2 billion.

    Either a) reduce the number of data points or b) upgrade to a newer version of the QI Macros.

    Run Time Error 9 - Subscript Out of Range

    March 2009 only. The RunTime Error 9 is caused by trying to access and change the color of a point that doesn't exist on the chart.

    Example:
    If there's 25 points and stability analysis tries to change the color of the 26th point, RunTime Error 9.

    Run Time Error 1004 - Unable to set the Text property of the Character class

    Error 1004: Unable to Set text property of the Characters class

    Workbook settings need to be updated. Go to Excel Options > Advanced > Display Options for this Workbook > For Objects, Show "ALL."

    Display Options for Excel Workbook to allow charts

    Run Time Error 1004 - Cannot Paste Data

    If you have merged or hidden cells, you may get this error. To correct, unmerge or unhide cells and rerun the macro:

    Run Time Error 1004 - Cannot apply the default chart type

    You may have an unusual "default chart type" that is causing the problem. To fix this, reset your default chart to a line chart as follows:

    1. Select some data and use Excel's chart wizard to draw a line chart

    2. After running the chart, right click on it and select Chart Type

    3. Click "Set as Default Chart" button at the bottom of the window:


    This will set the line chart as your default chart.

    Run Time Error 1004 - Unable to set the MergeCells property of the Range Class”

    Your workbook is setup as Protected/Shared. To get rid of this error message, unprotect and un-share the workbook to allow macros to run.

    workbook-protected

    Run Time Error 1004 - "Cells" method failed of "_Global" object (Excel 2007)

    Excel 2007's error handling routine seems to have glitches in SP1. I have implemented a workaround as of 9/9/08.

    Run Time Error 1004 - Command not available in shared workbook

    If you get an error like this one:

    Excel will not let the QI Macros add charts and sheets to shared workbooks.

    You must go to Tools-Share Workbook and uncheck the sharing to allow the QI Macros to run.

    Run-Time Error 1004 - Macro not found (ANOVA tools)

    Where XXXXX can be ANOVA1, TTESTM, etc.

    Only Applies to QI Macros versions purchased before April 2010
    Customers have noticed and we were able to recreate a situation where Excel seems to lose track of the Analysis Toolpak.

    If you get this error confirm the problem by clicking on the Tools menu and look for the Data Analysis (i.e., analysis toolpak) at the very bottom of the menu. You should not see it (left missing, right correct):

     

    If you click on Tools-Addins, however, you will see that the Analysis Toolpak addin is checked. This is an Excel bug. The Toolpak is checked, but Excel doesn't seem to know it.

    To correct this problem:

    1. Uncheck the Analysis Toolpak in Tools-Addins
    2. Exit Excel
    3. Open Excel
    4. Click on Tools-Addins and Check the Analysis Toolpak.
    5. Confirm that the Data Analysis option is now available on the Tools Menu.


    The Anova tools should now work properly.

    Run Time Error 1004 - QIMacros.xlam could not be found (Excel 2007-2010)

    One of two things has happened:

    1. Excel 2007 disabled qimacros.xlam.
      To enable the qimacros.xlam file:
      • Click on the Office Button in the top left corner of Excel and Select Excel Options.
      • Then Click on Add-Ins in the left-hand column.
      • Then Click on Manage at the bottom of the screen and select Disabled Items:


      • Then Click on the GO button next to your selection to see all disabled items:
      • Click on each of the qi macros disabled items and click Enable.

      • Once you've enabled all of the QI Macros disabled items, exit Excel and start Excel. The qimacros.xlam file should open automatically.

    2. QIMacros.xlam did not get loaded.

      Go to c:\Program Files\Microsoft Office\Office(10,11,12)\Xlstart and view the contents of the XLstart folder, it should look like this:

      install-file-locations

      If your XLStart folder contains some kind of temporary file, delete the temporary file and see if this solves the problem. If your folder is missing the .xlam file, then close Excel and reinstall the QI Macros. If your XLStart folder contains the files, then Excel has disabled them. See Step 2 below.



    Other Error Messages

    Can't Open Two Files With The Same Name

    In the urge to be thorough, you may have run setup.exe and then copied these files into c:\qimacros\xlstart and changed the Alternate Start Up File to include the c:\qimacros\xlstart folder. You've installed two copies and Excel is trying to load both. To resolve this problem, go to TOOLS-OPTIONS-GENERAL and delete the Alternate Startup Folder. Close Excel and reopen it.

    Compile Error in Hidden Module

    Excel startup folder contains either or both of the following Adobe Acrobat PDFMaker add-in template files. To correct:

    1. Exit Excel.
    2. Use Start-Search to find Pdfmaker.dot and /or Pdfmaker.xla
    3. Cut them out of the XLSTART folder and paste them into MyDocuments

    Or, when you go into the Excel Add-Ins menu (File > Options > Add-Ins > select Manage Excel Add-In), is QI Macros selected in the Add-Ins list? If so, de-select, press OK, close Excel reopen Excel.

    "Could not initialize installation. File size expected=1811939584, size returned=8124582."

    The .exe file has been corrupted. Load new .exe file from the backup you made of the software. If the corrupted file IS your backup, call QI Macros.

    Tech Support - Could Not Initialize

    "Demo Expired" or "QI Macros over one year old message"

    Early versions of the 2007 QI Macros had incorrect code that put "Demo Expired" Title on the upgrade message. Nothing has expired. Your QI Macros will continue to function just fine. The revised message looks like this:

    Early versions of the 2007 QI Macros failed to recognize the difference between regional differences in date formats: mm/dd/yy, dd/mm/yy, yyyy/mm/dd. If you are getting QI Macros Over One Year Old message, then you must be using a regional format other than English U.S. (mm/dd/yy). Check Control Panel-Regional and Language Options:

    In the U.S., change your regional language to English U.S.

    To get a corrected version if this happens shortly after purchase, email us and request the revised setup file.

    If your copy is over a year old, you will need to purchase an upgrade.

    Error 400 when opening a template

    There's incorrect code in the XL2007 menu. Send us your file from:

    c:\program files\microsoft office\office12\xlstart\QIMacros2007menu.xlam

    We'll send you the corrected one.

    H8000ffff hex code catastrophic failure on visual basic

    You may not have enough memory to run Windows, Excel and the QI Macros. Go to desktop and right click on My Computer, choose Properties to check memory. One user got this error with only 1GB of memory:

    Invalid Reference Argument at Cell

    Have you installed another macro package for Excel (e.g., some other SPC package)? If so, thise may create conflicts that cause this error. Uninstall the other package. QI Macros should then work correctly.

    Macro Error at Cell A123

    These are often caused by invalid data (e.g., text instead of numbers). Check your data. If you still can't find the problem, send your data file and the error message to us at support@qimacros.com.

    Macro Error at Cell A???

    You may get this error if there is No Printer Defined on your computer.

    Go to START-Settings-Printers and Faxes.
    Add a Printer (any printer) and then rerun the macro.

    Macro Not Found (Excel 2002-2003)

    SYMPTOMS
    Excel can't "see" a macro.

    RESOLUTION
    To resolve this problem,

    1. In Excel, go to HELP-ABOUT EXCEL
    2. Click on Disabled Items
    3. Enable the missing macro

    Macro does not run - nothing happens (Excel 2007-2010)

    Click on the Office Button and Excel Options (bottom):

    Then click on Trust Center Settings (bottom right):

    Excel 2007 defaults to disabling all macros with notification. So to get the macros to run, set macro security to disable all macros except digitally signed macros. QI Macros have been digitally signed since February, 2010:

     

    Macro does not run - nothing happens

    Some recent Microsoft Excel service releases have tightened up security by limiting the macros and files Excel allows to be opened and run. To workaround this issue, change Excels macro security level.

    1. Go to Tools/Macros/Security:


    2. Change your security level to Medium or to Low then close and reopen Excel and try to run a macro or open a template from QI Macros menu. Note: If you have Excel 2000, change the security level to Low:

    .

    No Return or HALT() function found on macro sheet

    When you upgraded Excel, it moved all of the old QI Macros files to:
    Windows XP:
    - c:\Documents and Settings\User name\Application Data\Microsoft\Excel\XLStart.

    Windows 7:
    - c:\users\User name\AppData\local\virtualstore\program files\Microsoft Office\Office14\XLStart.

    1. Simply delete all of the *.xlm or *.xlam files from this folder and open Excel to bring up the new version.
    2. If you cannot find these files, search your hard drive for any file named qimacros(.xlm or xlam) or zqimenu.xlm. Remove the xlm files that are not in c:\program files\microsoft office\office..\xlstart
    3. Excel 2000 - Service Pack 3 will fix this problem. Download it here: http://support.microsoft.com/kb/326585/en-us

    Also: According to clients, the Excel Y2K updates from Microsoft for DateWatch and DateFix can cause this error. To disable these tools, go to Excel's TOOLS-ADDINS page and remove the tools. Other addins may cause this problem. Turn them off one at a time until you find the culprit.

    P Chart Macro Error at Cell A194

    This is caused by file names that are too long. Choose File-Save As and pick a name shorter than 20 characters. Then rerun the chart. If you still can't find the problem, send your data file and the error message to support@qimacros.com.

    Repeated messages to upgrade because QI Macros is more than one year old

    QI Macros can't update your windows registry. When I added the reminder we didn't know that IT departments can lock the registry.

    Solutions
    1. Have your administrator reinstall from your QI Macros Setup File.
    2. Download this file and have your administrator run it to update the registry.

    That command cannot be used on multiple selections

    Most likely you have two different data sets selected in Excel and it does not know which one you want to use to create the chart. Check your spreadsheet and other spreadsheets in your workbook to make sure you do not have other data fields selected. Note: selected fields are usually highlighted in black.

    The formula you typed contains an error (Excel 2003 SP3 only)

    If you received this error, you have a copy of the QI Macros purchased before Dec 2006 and recently loaded Excel 2003 Service Pak 3. Unfortunately, this service pack is not compatible with these older versions of the QI Macros. Here are your options:

    1. Uninstall the new release of Excel.
      • Start-Control Panel-Add or Remove Programs (e.g., Excel 2003 SP3) or
      • Start-All Programs-Accessories-System Tools-System Restore to an earlier date before the installation.
    2. Purchase a new QI Macros License
    QI Macros License
    Download Only

    $249

    1 user
    1 computer
    Download immediately
    Make a backup
    PC & Mac Compatible
    Item # W230

    QI Macros License
    Download + CD

    $249 + s&h

    1 user
    1 computer
    Download immediately
    Backup CD shipped
    PC & Mac Compatible
    Item # 230

    Home/Office License
    Download Only

    $299

    1 user
    2 computers
    Download immediately
    Make a backup
    PC & Mac Compatible
    Item # W230H

    Home/Office License
    Download + CD

    $299 + s&h

    1 user
    2 computers
    Download immediately
    Backup CD Shipped
    PC & Mac Compatible
    Item # 230H



    Problems with Charts, Templates or ANOVA tools

    Anova Analysis Tools

    applies to QI Macros 2010.04 and earlier versions

    Anova and Analysis Tools requires Analysis Toolpak

    If you go to Tools-Addins and check Analysis Toolpak, Excel will either turn these tools on or ask you to install them.

    To check if they have been installed, click on Tools-Data Analysis. If you cannot see Data Analysis in the Tools menu, the statistical analysis tools are not installed. Use your installation disk to install them.

    To Install (get your Excel or Office installation disk).

    1. On the Tools menu, click Add-Ins.
    2. In the Add-Ins available list,
      select the Analysis ToolPak box, and then click OK.
      Follow the instructions in the setup program.
    3. You may have to exit Excel and reopen it to find the data analysis tools at the bottom of the TOOLS menu.

    Office 2000: There may be a Microsoft glitch in Excel 2000 that allows you to install the addins, but still not see Data Analysis. QI Macros Anova and Analysis tools should work, but you may get an "alert" message each time because they cannot find Data Analysis on the Tool bar.

    Possible addin coflict: One client was using the Megastat addin, which may have turned off the Data Analysis menu item.

    Run-Time Error '1004': macro not found

    Where XXXXX can be ANOVA1, TTESTM, etc.

    Only Applies to QI Macros versions purchased before April 2010
    Customers have noticed and we were able to recreate a situation where Excel seems to lose track of the Analysis Toolpak.

    If you get this error confirm the problem by clicking on the Tools menu and look for the Data Analysis (i.e., analysis toolpak) at the very bottom of the menu. You should not see it (left missing, right correct):

    If you click on Tools-Addins, however, you will see that the Analysis Toolpak addin is checked. This is an Excel bug. The Toolpak is checked, but Excel doesn't seem to know it.

    To correct this problem:

    1. Uncheck the Analysis Toolpak in Tools-Addins
    2. Exit Excel
    3. Open Excel
    4. Click on Tools-Addins and Check the Analysis Toolpak.
    5. Confirm that the Data Analysis option is now available on the Tools Menu.


    The Anova tools should now work properly.



    Control Charts

    Control Chart has data labels on all points

    Excel has the default format for a chart set to include data labels. Solution: Reset the default chart type.

    Click on the chart to bring up the chart menu and choose: "Change Chart Type":

    Then click on the plain line graph style and click Set as Default Chart:

    After changing the default chart type, when you run a QI Macros control chart, the data labels should disappear.

    Dates on the X-axis cause data to plot strangely

    When you use dates or time for the X-axis labels, Excel automatically treats them as a time-series: it plots them in date/time order. On a control chart, this can cause points to stack up. Look at this c Chart:

    We have four points for the same day and Excel stacks them instead of spreading them.

    To resolve this problem, visit our Excel FAQ Page.

    p and u chart - can't switch from skyline to wave format

    The QITemplates folder may be read only (IT departments do this sometimes, see below). This makes it impossible to update the ControlChartRules.xls file which keeps the formatting rules. To correct this, have IT make the folder read/write, then redo the p/u format change.

    Control Chart Squeezed into One Column

    If you run a Control Chart and the output is squished into one column in Excel, this is because the default printer page layout is not setup for an 8 1/2" x 11" display:

    squished-control-chart

    To fix, go into Excel and select File > Print. From there, change the Printer to that of a display that is 8 1/2" x 11" and re-run your chart.



    Dashboards

    Upper and Lower Control Limits on XbarR Skyline Not Showing

    This means that your data set in Column A is formatted as "General." Convert the first cell (A2) to anything other than "General." Once you have done so, the Upper and Lower Control Limits will appear.


    Histograms

    Arrows on histograms move around

    Excel 2000 and XP
    In both 2000 and XP, once you create a histogram with USL and LSL and control limits, if you save the file and reopen it, Excel moves the arrows and text fields to the left.

    Excel seems to remember where to put the arrows and text, but doesn't display them properly. If you open the file in 2003 or 2007, the histogram displays correctly. If you click on File-Print Preview in Excel 2000 or XP, you'll see that they should print properly:

    You can use the Drawing toolbar's selection arrow to select and reposition the arrows. Use the selection tool to click on an arrow and move everthing back to it's original position:

    Or, once you've created a chart, you can "protect" the sheet to lock the arrows in place:

     

    No Metrics Appear when Creating a Histogram

    If this occurs, it is because the "R1C1 reference style" has been selected, found in the Formulas portion of Excel. To access, go to File > Options > Formulas > Working with formulas:

    r1c1-formula

    As a short-term troubleshoot, de-select the R1C1 reference style box, press OK, and re-run the Histogram macro.

    To fix, e-mail support@qimacros.com and request an updated .xlam file, regarding this specific issue.

    Chart is scrunched to top of sheet

    Try inserting a chart using Excel's chart tools. Is this one scrunched?
    If so, exit Excel and go to:

    c:\Documents and Settings\your user name\Application Data\Microsoft\Excel

    Delete Excel??.xlb and any .xls files (like XLUSRGAL.XLS above).
    I think Excel stored a previous chart file (XLUSERGAL.XLS) in this folder and uses it as a template for something weird.

    Start Excel. Any charts you create should now look normal.

    Cp, Cpk, etc. are off the chart to the right

    If you have a recent version of the QI Macros, you may experience this problem when your spec limits are far apart but your data is clustered together. In order to make room for the spec limits, the macro may have pushed the metrics too far to the right. You can select and move the text boxes as follows:

    1. Make sure your drawing toolbar is open (usually appears at the bottom of your spreadsheet). If it is not, select View/Toolbars/Drawing.
    2. Click on the arrow to change your cursor:

    3. Click and drag over the text boxes to select them:

    4. Move the boxes back on the page.
    5. Click on the arrow to change your cursor back to normal:

    Histogram Data Not Showing Range it Should

    When entering data with decimal points, the template was not updating said field, and therefore skewing the chart:

    histogram-data-rang

    To fix this, simply move the chart to the right and change the “Decimal Points” section to the required value.

    Printed Histograms have truncated decimal points

    For some reason, some versions of Excel truncate numerical fields linked to the histdata page. To make the decimals reappear, simply click on the bottom right "handle" of the textbox and drag to increase the size of the box.

    Titles and/or Values Stacked atop Each Other

    When you create a chart using either the templates portion or macros portion of the QI Macros, the titles and/or values are stacked on top of each other.

    To fix this, make sure your Microsoft Excel Service Pack is up-to-date: 2007 requires SP3, while 2010 requires SP2. If you do not have the required Service Pack, install latest pack and re-open or re-run your chart/graph:

    title-value-bunching



    Other Charts

    Pareto line is flat. Scatter and Dot Plot have errors

    Excel 2000-2010
    We found that if you've accidentally or on purpose set Excel's default chart type to pie or radar chart, the pareto, scatter and dot plot won't work correctly.

    Set your default chart back to a column or line chart.

    I can't change the sheet name of the chart

    In Excel XP (System release 0), if you type a name that's too long for the sheet name, Excel reverts to the original name.

    1. Use a shorter name
    2. Upgrade to System Release 3 (SR3)

    Using Lot Numbers as data for Chart

    Excel and the Macros cannot distinguish lot numbers from data, unless you put a leading character in front of the number. To do this,

    1. insert a column between your lot numbers and your data.
    2. Use Excel's text function as shown below to convert the numbers into text with leading text, in this case L for lot.
    3. Select the new column/data and run the chart of your choice:

    Strange colored background on charts and spreadsheets

    XL 2000-2003 have the ability to reset your color palette. This may cause problems with your backgrounds. If you get a chart or sheet that looks like this with a green or other colored background:


    Then simply reset your color palette to clear the problem. Click on Tools-Options-Color then click the Reset button and OK to restore the defaults:



    Templates

    Can Run The Macros, but Can't Load a Template

    The macros look for the templates in a standard location. If you have an alternate startup folder specified (Tools-Options-General), the macros may look in that folder instead. If the macros can't find the file, you may get an error:

    You can:

    • Clear the field: "At Startup, open all files in:" (see above)
    • Move the QITemplates folder to the Alternate Startup location
      Copy/Move c:\program files\microsoft office\office11\xlstart\QITemplates to the specified Alternate Startup file location.

    Charts on templates flicker

    Error checking causes charts to flicker.

    Solution: Click on Tools-Options-Error Checking and uncheck Formulas refering to blank cells:

    Dates in the template are off by 4 years

    Dates set to PC or Macintosh date format.
    Go to Tools-Options-General and change date format to match your machine.

    Error 400 when opening a template

    There's incorrect code in the XL2007 menu. Send us your file from:

    c:\program files\microsoft office\office12\xlstart\QIMacros2007menu.xlam

    We'll send you the corrected one.

    Template does not open - nothing happens

    Some recent Microsoft Excel service releases have tightened up security by limiting the macros and files Excel allows to be opened and run. To workaround this issue, change Excels macro security level:

    1. Go to Tools/Macros/Security:


    2. Change your security level to Medium or to Low then close and reopen Excel and try to run a macro or open a template from QI Macros menu. Note: If you have Excel 2000, change the security level to Low:

    XmR Dashboard stops at 24 charts

    Excel has a physical limit of 124 charts per workbook.
    Since there are 4 charts on each XmR sheet, each time we copy it and add one to the dashboard you end up with 5 more charts (24*5=120).
    Solution: Before you create the dashboard:

    1. Delete the other XmR sheet (median or range).
    2. Delete the Histogram, probability plot and even the range chart from the remaining XmR sheet.
    3. This will allow you to create up to 60 charts on the dashboard.
    4. If you need more than 60 charts.
      • Right click on any sheet name and select "view Code".
      • Change the code in XmR-Dashboard.xls, Module1,Create_Dashboard_XmR:
        from "ActiveSheet.ChartObjects(1).Copy" to "ActiveSheet.ChartObjects(1).Cut"
        Rather than make a copy, this will move the chart to the dashboard, minimizing the number of charts in the workbook.
      • Create the Dashboard.


    Excel / Statistics Help

    QI Macros FAQs

    Excel FAQs

    SPC FAQs

    Problems running a training DVD

    The Six Sigma Simplified and SPC Simplified training DVDs require that you have some kind of media player loaded on your PC. If you have a media player loaded it should automatically begin playing the DVD.

    If you don't have a media player, download one free at:

    Once you have a player loaded, insert your DVD into the drive. If it doesn't automatically play or prompt you to open the player then:

    • Open the media player and select File/Open
    • Double click on the DVD drive
    • Double click on the Video_TS folder
    • Double click on the Video_TS.VOB file (if you don't see this file, make sure you are viewing all file types)
    • The DVD should begin playing.

    Note: If you have problems with Windows Media Player and Real Player, try WinDVD.

    Windows XP allows you to set your computer to automatically play a DVD.

    • Without the DVD in your drive, double click on My Computer
    • Right click on the DVD drive and select Properties
    • Click on the Autoplay tab
    • Select DVD Movie on the pull down menu
    • Then select a default player

    DVD VHS formats

    There are three video formats used around the world:

    • NTSC - used in America and Canada
    • PAL - used in Western Europe and Australia
    • SECAM - used in Eastern Europe and France

    All of our training videos are in NTSC format. Your VHS or DVD players must be able to play NTSC format to view our videos.

    If you are not sure which format is used in your country, Google "PAL NTSC" and you will find several sites with information by country and much more information.

    QI Macros software validation

    Free QI Macros Training Resources

     

    Problems With Microsoft Windows or Excel

    If you have any other problems...page faults, can't compile, etc. we have found that it's rarely a QI Macros problem. So, click here to look for answers in Microsoft Support

    Trouble Reporting Form

    If you can't find the answer to your problem on this page, please search our site:

    If you cannot find an answer after searching our site, please complete the following trouble reporting form. We will respond within 24 hours, M-F. Thank you.


    Can you run a chart with data in c:\program files\qimacros\testdata?:










    If you've encountered a problem with your data...
    Please email any data or forms you are using to: support@qimacros.com
    Note: Incomplete information will delay resolution of your problem)


    Purchase QI Macros SPC Software for Excel or a QI Macros Upgrade through our secure shopping cart or try a free, 30-day trial!
    QI Macros License
    Download Only

    $249

    1 user
    1 computer
    Download immediately
    Make a backup
    PC & Mac Compatible
    Item # W230

    QI Macros License
    Download + CD

    $249 + s&h

    1 user
    1 computer
    Download immediately
    Backup CD shipped
    PC & Mac Compatible
    Item # 230

    Home/Office License
    Download Only

    $299

    1 user
    2 computers
    Download immediately
    Make a backup
    PC & Mac Compatible
    Item # W230H

    Home/Office License
    Download + CD

    $299 + s&h

    1 user
    2 computers
    Download immediately
    Backup CD Shipped
    PC & Mac Compatible
    Item # 230H