Troubleshooting

link - learn.microsoft.com/en-us/deployoffice/security/internet-macros-blocked 

Code is Incompatible

When trying to run some VBA code.

alt text

Remove Module Disabled

It is possible that a VBA Project can acquire multiple workbook and worksheet objects.

alt text

Copy out all the worksheets in to a new blank workbook.


Excel.Sheet.12 Link

Word cannot obtain the data for the Excel.Sheet.12 link.

alt text

Macro Cannot Be Found

If the name of the subroutine cannot be found in any open project is displayed the following error message

alt text
alt text

Worksheet Cannot be made Visible

When you try and make a worksheet visible in code you see the following error message.

alt text

To try and recreate this problem, perform the following:
Create a new blank workbook
Insert another worksheet called Sheet2
Hide the Sheet1 worksheet.
Display the Review tab and press Protect Workbook
Open the Visual Basic Editor and try to set the Sheet1 worksheet to visible.


VBA Project Still Visible

When you open a workbook that contains macros (i.e. a VBA Project) this project can sometimes remain visible in the Visual Basic Editor after the workbook has been closed.

alt text

To try and recreate this problem, perform the following:
Open Excel and create a new blank workbook.
Press (Alt + F11) to display the Visual Basic Editor.
Insert a module (not a class module) into this project.
Create a new subroutine as follows:

Sub Testing() 
End Sub

Save the workbook (e.g. C:\Temp\Book1.xlsm).
Close the workbook.
The VBA project Book1.xls remains visible in the Visual Basic Editor Project window.


Duplicate VBA Projects

If you open and close a workbook that contains macros this project can appear multiple times in the Visual Basic Editor.
Every time you close and re-open the workbook another VBA Project can be created in the Project window.
This behaviour has been seen after a COM Add-in has been loaded.
Removing and reloading the COM Add-in will remove the duplicate project.

alt text

Project is Unviewable

VBA Project was locked when the workbook was in a "Shared" state. More Details
The macro was created with Excel 5.0 macros which are not viewable in the newer versions of Excel.


Prompting for Password

If you use VBA code and the VBA Project associated with that workbook or add-in has a password then you may be prompted for a password when you close Excel.
Pressing cancel will either close this dialog box after several attempts or it will be displayed indefinitely (resulting in a Task Manager > End Process).

alt text

To try and recreate this problem, perform the following:
Open Excel and create a new blank workbook.
Select (View > Toolbars > Control Toolbox) to display this toolbar.
Add a command button to a worksheet.

alt text

Double click this command button and add the following line of code to the Click Event Handler:

Private Sub CommandButton1_Click() 
   Call Testing
End Sub

Insert a Userform into this project.
Insert a module (not a class module) into this project.
Create a new subroutine as follows:

Sub Testing() 
   Load Userform1
   Userform1.Show
End Sub

Exit Design View and press the button to make sure that the userform is displayed.

alt text

You can close the userform using the cross in the top right hand corner.
Add a password to the project.
Return to the Visual Basic Editor and select (Tools > VBA Project Properties) and select the Protection tab.
Select "Lock project for viewing" and enter the password "aa".
Confirm the password and press OK.
Save the workbook (e.g. C:\Temp\Book2.xls).
Close Excel.
Open Excel and open the workbook you just saved (e.g. C:\Temp\Book2.xls).
Press the button and make sure that the userform is displayed.
Close Excel using (File > Exit) (do not close the workbook first).
The VBA Project Password dialog will be displayed if you have a COM Add-in installed that is loaded with mscoree.dll.


Uninstall any COM Add-ins (for example Google Desktop) that might be installed or loaded.
You can uninstall this add-in using the (Control Panel > Add or Remove Programs).
You can check which add-ins are installed by looking at the following registry entry:

HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\ 

Out of Memory

More Details


Bad DLL Calling Convention

More Details


Blank Error Messages

When I try and customise my QAT I receive lots of blank error messages
This message was supressed in Office 2010.

alt text

If you open a workbook or load a VBA add-in that contains a custom ribbon with dynamic callbacks that generate errors then you will see blank error messages when you select this tab in the customise dialog box.
Examples of dynamic callbacks are: getImage, getScreentip, getLabel
To try and recreate this problem, perform the following:
Create a workbook with a custom Ribbon Like This
Copy and paste the xml tags below into the customUI.xml file.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> 
  <ribbon >
    <tabs>
      <tab id="CustomTab" label="My Tab">
        <group id="SimpleControls" label="My Group">
          <button id="Button1" imageMso="HappyFace" size="large"
            label="Large Button"
            onAction="ThisWorkbook.MyMacro"/>
          <button id="Button1" image="MyMissingImage" size="large"
            getLabel="ThisWorkbook.MyMacro2" />
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext