QI Macros Technical Support
Archives

Versions of Excel
No Longer Supported by Microsoft

Excel 2016 (Mac)
Excel 2011 (Mac)
Excel 2010 (PC)
Excel 2007 (PC)

QI Macros Menu Issues

If you are recieving a message that says, "qimacros.xlam isn't a recognized file type," this is potentially because of a few things:

  1. The QI Macros installer or the qimacros.xlam file has been corrupted.
  2. The macro settings in your Excel have been set to "Disable all macros without notification." These settings can be found in File > Options > Trust Center > Trust Center Settings > Macro Settings.
  3. Your security settings have removed a portion of the necessary code found within the qimacros.xlam file - you will need a new file.

If you are receiving this message, please contact our technical support via e-mail or phone: 1-888-468-1537.

This occurrence happens if:

You have VBA password-protected files/add-ins. The password prompt will appear when you close Excel.

-or-

VBA projects remain open if you close a password protected file.

-or-

There are duplicates of VBA project names in the VBA Editor.

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

regedit-start-menu

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).
Excel Issues and Behavior

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

Unfortunately, these 3 tools are not functional in Office Professional/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 Office Professional/Professional Plus.

If you have multiple computer screens and Excel freezes when you are trying to use QI Macros from a secondary monitor, try the following steps:

  1. Move the Excel window from your secondary monitor onto your main monitor and then run QI Macros again.
  2. Perform a Quick Repair on Excel.
  3. Check to see if your monitor drivers are outdated.
  4. Disable your Intel(R) HD Graphics Control Panel Service found within "Settings" on your device.

If you have completed these different options and Excel is still freezing on your secondary monitor, please contact our support team.

If you create a Scatter Chart or a Pivot Chart and a "Trendline Error" message appears, this is due to an issue found in your Office 365 Build.

It was discovered that if your X values are all the same or close to the same, or if your data includes zeroes or data close to 0, the "Trendline Error" appears, because Excel thinks the slope on your chart is infinite. Therefore, Excel does not know how to truly calculate and properly chart your Trendline.

End users have also found this occurrence in Pivot Charts, but it is not known why or what causes "Trendline Error" to be placed on that chart, specifically.

While both issues have been submitted to Microsoft for review, unfortunately there is no available patch to fix your Build. However, please note that there are (2) ways to resolve this issue:

  1. Update your Office 365 to a more recent Build
  2. -OR-

  3. Rollback your Office 365 to the previous Build where this error did not occur.

If you run a macro on your data set and receive the following prompt, it is because you have a duplicate naming reference within your file:

vba-class-not-registered

There are 2 workarounds for this:

  1. Copy and paste your data set into a brand new spreadsheet. Once you have done so, re-run the macro on the data set found in your new spreadsheet.
  2. -or-

  3. Enter non-duplicated names into the "New name" prompt(s). NOTE: You may have to enter multiple "New names" before your chart is created.
Microsoft Support Article - Name Conflict Dialog Box

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.

    -or-
  2. Reinstall Microsoft Office, as at least (1) of the files was corrupted.

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

First, check the Trusted Locations found in Excel, select File > Options > Trust Center > Trust Center Settings, 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: https://support.microsoft.com/kb/329820

This is potentially due to Excel.xlb Growth. Note that on PCs, 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
Excel2016/2019/Office 365: Excel16.xlb

These files can potentially grow with the opening/closing of Excel. To resolve this matter 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:

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 My Computer and select the C: Drive. Then, click on Tools > Folder Options and select Show Hidden Files:

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.

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.

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 it onto your Desktop. This effectively disables your QI Macros software.

    ibm-iseries-menu
  • 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.

Your CTRL key is stuck. (This happened to one of us!)

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).

If this occurs, completely quit out of Excel and simply delete the temporary file.

Once you have done so, open Excel – QI Macros and Excel should now be functioning properly.

If Excel is closed and you try to open an Excel file link in Word:

Link to Excel file in Word

You may get one or more messages like this one:

Excel has Stopped Working

After 3 tries, Excel will give you a message to open the file.

Excel opening file

If you click OK, it will open the file. This seems to be a problem between Word and Excel when there are add-ins like the QI Macros installed. There doesn't seem to be a Microsoft fix for this.

Solution: Open Excel before you click on embedded links.

If you try to start Excel from the Dock, recent releases cause Excel to crash with QI Macros installed.

(Note: This does not happen in all versions of OS X. High Sierra and Yosemite seem to have a problem.)

Solutions:

  • Double-click on an Excel file (e.g., cchart.xlsx from QI Macros Test Data) and Excel will start normally. (Weird huh?)

-or-

  1. Successfully open Excel using the above option and open a blank workbook.
  2. Next, select the Excel drop-down menu in the top-left corner of your Mac and choose Preferences > General.
  3. Then, check the “Open Workbook Gallery when opening Excel” option. This will allow you to start Excel from the Dock.

    XL2016 Workbook Gallery

Once you have completed these steps, does Excel still crash when you start Excel from the taskbar?

Other Miscellaneous Excel Issues and Behavior

Excel 2016 may show incorrect format of dates on the X axis. This is an Excel bug - seems to be off by 14 years.

We figured out that cell B1 contained "Billed Total", but there was a carriage return between Billed and Total.

When the return is removed, everything works correctly. Doesn't seem to be a problem in other versions of Excel.

Solution: Use Wrap Text, not returns in title fields.

Mac Excel 2016 X-Axis Date Formatting Problem

If this occurs, make sure there are no pending updates for Office 2013. To check, open Excel, open a blank workbook and select File > Account > Update Options.

Sometimes Excel gets confused. Try opening Excel Options-Advanced and unchecking the box shown below. Close Excel and then try double clicking on an Excel file:

Excel not opening file when double clicking

This occurs when you have redirected MyDocuments to an external drive (e.g., f:\mysharedocs). NOTE: QI Macros is trying to install test data in My Documents, but does not have access to the drive.

If you start Excel and the QI Macros menu is on the ribbon, the install was successful.

To Install QI Macros, follow the instructions below:

  1. Right click on My Documents and choose Properties
  2. In Properties, click on Location:

    How to Change My Documents
  3. Copy the external drive location (to restore your linkages)
  4. Change to some place on your c drive (e.g., c:\qimacros)
  5. Save your changes
  6. Install the QI Macros
  7. Restore the external My Documents Location

QI Macros could be doing battle with another add-in. In one case, it was an old Lotus Notes add-in DDocCtrlO.xla and .dll (.com add-in).

To troubleshoot, disable other add-ins to see if the automation error goes away. To do this, go into Excel, open a blank workbook and selection File > Options > Add-Ins.

Then, click on the “Manage Excel Add-Ins” drop-down menu and open Excel Add-Ins and COM Add-ins – uncheck any unused add-ins.

Once you have done so, open Excel – do you still receive the same message? If so, try de-selecting other Excel add-ins that are currently in use.

Note that these instructions are for Excel 2010 and 2007 only:

In order to fix this issue, you need to install Visual Basic as follows:

  1. Close Excel.
  2. Go into Control Panel on your PC by clicking on "Start" and then Control Panel.

  3. In the Control Panel, choose "Add or Remove Programs" (Programs and Features in Vista).
  4. Select Microsoft Office in the list and click on the Change button

  5. Another window will open with a list of options, choose "Add or Remove Features.“

  6. Open the "Office Shared Features" list by clicking on the "+" next to it. Select "Visual Basic for Applications" and set it to "Run from My Computer." Then click continue.

  7. You will then get a window that shows you the progress of the Configuration Process. This will take a couple of minutes and then you should receive a "Configuration Successful" window. Click Close and then reopen Excel.

Microsoft Excel Starter 2010 is a simplified version of Excel that does not include all of the features found in the full version of Excel including installation of add-ins, and the ability to read .xla and .xlam files.

These features are required to use the QI Macros. Therefore, the QI Macros will not work with Excel Starter 2010. You will need to purchase a full version of Excel.

There have been a number of weird behaviors that have cropped up in Excel over the years (e.g., Excel's chart menu appears on a worksheet instead of a chart sheet). When we repair Excel, everything (including the QI Macros) starts running better.

To do so, go to your Control Panel and select Programs > Add or Remove Programs > Microsoft Office. Then, right-click on Microsoft Office and select Repair.

Once Office has been repaired, open Excel – it should now be functioning more smoothly.

When exiting Excel, it prompts for a password to open the QI Macros code:

Excel Prompts for QI Macros Addin Password

To get rid of the dialogue box, uninstall QI Macros and run a registry cleaner. Once the cleaner is complete, close Excel and re-install QI Macros.

Reopen Excel and close – you should no longer receive the password prompt.

Please note that QI Macros is not compatible with Windows 10 S. If you have this Operating System installed, and want to install QI Macros, you must upgrade to Windows 10 Pro: Windows 10 S to Windows 10 Pro

Office 365 is a monthly service versus a one-time purchase. Some Office 365 plans include Excel Online and some include “desktop versions of Excel” that are loaded on your PC or Mac.

In order to use QI Macros, you will need to purchase a plan with a desktop version of Excel. See an excerpt from Microsoft's website below. Plans with the Desktop versions show the icons at the bottom.

For more details see the Microsoft Office 365 Compare Plans webpage

Runtime Error Messages

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.

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

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

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.

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

If you get an error like this one:

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

In Excel, you must go to Tools > Share Workbook and uncheck the sharing to allow the QI Macros to run.

Other Error Messages

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

  1. Completely quit out of Excel.
  2. Use the Search option to find Pdfmaker.dot and/or Pdfmaker.xla
  3. Once found, select one or both of them, right-click and select Cut, removing them from the XLSTART folder.
  4. Next, paste them into your Documents folder.

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 and reopen Excel.

If you run the QI Macros Mac install and receive the following prompt, this is because of the security settings implemented on your Apple device:

Error 0755 Mac Message

Error 0755 Application will Exit Message

To troubleshoot this matter, contact our Support line via e-mail support@qimacros.com, or call 1.888.468.1537. If your QI Macros license is compatible with your version of Excel and Operating System, we will provide you with the necessary manual install files.

The QI Macros .exe install file has been corrupted. Load a new .exe file from the backup you made of the software.

If the corrupted file IS your backup, e-mail support@qimacros.com, or call 1.888.468.1537.

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

To troubleshoot this matter, go to START > Settings > Printers and Faxes.

Add a Printer (any printer) and then re-run the macro.

This is caused by file names that are too long.

To troubleshoot, in Excel select File > Save As and pick a name shorter than 20 characters. Then re-run the chart or macro!

QI Macros can't update your windows registry. To troubleshoot, see instructions below:

  1. Have your administrator reinstall QI Macros using your QI Macros install file.
  2. Download this file and have your administrator run it.

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.

Control Charts

Excel has the default format for a chart set to include data labels.

To troubleshoot, 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.

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.

The QITemplates folder may be read only (IT departments do this sometimes, see below).

To correct this, have IT make the folder read/write, then redo the p/u format change:

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

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

If this occurs, it is because the "R1C1 reference style" has been selected, found in the Formulas portion of Excel. To access, go into Excel and select 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.

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/.xlsx files.

Excel stored a previous chart file (XLUSER.XLS) in this folder and uses it as a template.

Start Excel – any charts you create should now look normal.

If you have a recent version of 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:

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

data-not-showing-range-it-should

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

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.

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

title-value-bunching

To fix this, make sure your Microsoft Excel is up to date. If you do not have the required updates, install them and re-open or re-run your chart/graph.

Other Charts

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.

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

  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:

Templates

If you have created a skyline template and the error bars are not extending, below are instructions on how to update your file accordingly:

  1. If your template is formatted to Wavy Control Limits, convert them to Skyline Control limits within QI Macros by first selecting your chart. From the QIMacros Chart portion, select the "Convert SkyLine/Wave" drop-down and click "Convert Wave to Skyline."
  2. Next, click any point on your Upper Control Limit and choose "Format" from the Excel ribbon.
  3. Then, select "Series UCL X Error Bars" from the Chart Area drop-down:
  4. p u templates error bar chart area selection

  5. Once you have done so, click the "Format Selection" button and then select "Specify Value" from the Format Error Bars section:
  6. p u template error bar format selection button

    p u template specify value button

  7. When the "Custom Error Bars" window opens, update the Positive Error Value and Negative Error Value from $M$70 and $T$70 to $M$150 and $T$150 accordingly:
  8. p u template customer error bar prompt

  9. Repeat these steps for UCL Y Error Bars, LCL X Error Bars, and LCL Y Error Bars.

Excel 2010 does not recognize #NA as valid cell contents. QI Macros uses this to control the format of Gage R&R charts. This is a known problem in Excel 2010.

Just hit "ok" and ignore the message as there is nothing wrong with the template or its formulas. Once you have hit ok after pressing save, close the Excel file and reopen – you will no longer receive the error message.

While Excel 2000-2010 have no problems with the QI Macros Control Chart Templates, Excel 2013 overwrites the title with UCL/CL/LCL values:

Excel 2013 Overwriting Title with Data Values

These can be either deleted or moved back into position:

Excel 2013 Data Values Moved

We expect this problem to be corrected in one of Excel 2013's service pack updates.

Error checking causes charts to flicker.

Solution: Click on Tools > Options > Error Checking and uncheck Formulas referring to blank cells:

Excel has a physical limitation 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.xlsm, 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

The Six Sigma Simplified and SPC Simplified training DVDs require that you have some kind of media player loaded on your device. 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/or Real Player, try WinDVD.

  • 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 within the pull down menu
  • Then, select a default player

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

NOTE: 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, along with much more information.



Call or chat M-F 8 a.m. to 5 p.m. (MT) 888.468.1537


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, Monday through Friday, 8 a.m. to 5 p.m. MT. 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 e-mail any data or forms you are using to: support@qimacros.com

Note: Incomplete information will delay resolution of your problem.