View Macro Pop-Up Window lists macros that don't exist

A

AmyDeez

New Member
Joined
Oct 14, 2015
Messages
21
Greetings! I am using Excel 2013.

I have a large file that has 5 actual macros saved in it. These 5 macros are listed in the Macros Pop-up menu (View - Macros - View Macros).
The problem is that this list includes somewhere around 50 non-existing "macros" with names like "s" and "un" and "unshade" and I have to search through them to find the actual existing macros. When I select the "s" / "un" / "unshade" macros, the only option the pop-up windo allows is "Create".

I looked at the modules and the code for all of the pages...and I don't see anything that doesn't belong.

Does anyone know how to get the non-existant "macros" out of my view macros list?

Thank you for your help!
Amy
 
Sort by date Sort by votes
MARK858 said:
@AmyDeez, you haven't got any regular sub's that are in sheet modules have you (clutching at straws as I can't remember if they show up in the macro list and no Excel with me at the moment)?
Click to expand...

I do...there is one. But it does not show up in the Macro List and hasn't caused this issue in previous files.
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
If there isn't any confidential infobin the workbook can you post the workbook to a free file hosting site like box.com or Dropbox.com and post the link it provides in the thread.
 
Upvote 0
MARK858 said:
If there isn't any confidential infobin the workbook can you post the workbook to a free file hosting site like box.com or Dropbox.com and post the link it provides in the thread.
Click to expand...

Unfortunately...the whole file is confidential.
 
Upvote 0
RoryA said:
Does un still appear in the macro list afterwards?
Click to expand...

AmyDeez said:
RoryA...nothing happened other than naming the cell I was on :)
Click to expand...

RoryA said:
If you type un into the name box to the left of the formula bar and press Enter, what happens (if anything)?
Click to expand...

I've checked all named ranges in the workbook now. The only named ranges are those that should be there. Nothing is named "s" or "un" or "unshade"...
 
Upvote 0
I tried saving the file as a regular excel file. It did not get rid of the "s"/"un"/"unshade" ghost macros from the list.
If I delete all the tabs, the ghost macros dissapear...LOL...Had to try, but this is NOT an option :eeek: Really don't want to re-build this thing.
 
Upvote 0
AmyDeez said:
The following is a screen capture of the Macro List with one of the non-existing macros selected.
https://www.dropbox.com/s/muebomebczraoao/MacroList.PNG?dl=0
Click to expand...

I am afraid a screenshot of the macro list doesn't tell me anything I didn't already know.

When you run the code below by Jon Peltier what results does it give (please note the "set a reference to the Microsoft Visual Basic for Applications Extensibility 5" comment is important)?

Rich (BB code): 
'' Based on:
'' Displaying a List of All VBA Procedures in an Excel 2007 Workbook
''     from the Ribbon (June 2009)
'' by Frank Rice, Microsoft Corporation
'' http://msdn.microsoft.com/en-us/library/dd890502(office.11).aspx#

'' set a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 Library

Sub GetProcedures()
  ' Declare variables to access the Excel workbook.
  Dim app As Excel.Application
  Dim wb As Excel.Workbook
  Dim wsOutput As Excel.Worksheet
  Dim sOutput() As String
  Dim sFileName As String

  ' Declare variables to access the macros in the workbook.
  Dim vbProj As VBIDE.VBProject
  Dim vbComp As VBIDE.VBComponent
  Dim vbMod As VBIDE.CodeModule

  ' Declare other miscellaneous variables.
  Dim iRow As Long
  Dim iCol As Long
  Dim iLine As Integer
  Dim sProcName As String
  Dim pk As vbext_ProcKind

  Set app = Excel.Application

  ' create new workbook for output
  Set wsOutput = app.Workbooks.Add.Worksheets(1)

  'For Each wb In app.Workbooks
  For Each vbProj In app.VBE.VBProjects

    ' Get the project details in the workbook.
    On Error Resume Next
    sFileName = vbProj.Filename
    If Err.Number <> 0 Then sFileName = "file not saved"
    On Error GoTo 0

    ' initialize output array
    ReDim sOutput(1 To 2)
    sOutput(1) = sFileName
    sOutput(2) = vbProj.Name
    iRow = 0

    ' check for protected project
    On Error Resume Next
    Set vbComp = vbProj.VBComponents(1)
    On Error GoTo 0

    If Not vbComp Is Nothing Then
      ' Iterate through each component in the project.
      For Each vbComp In vbProj.VBComponents

        ' Find the code module for the project.
        Set vbMod = vbComp.CodeModule

        ' Scan through the code module, looking for procedures.
        iLine = 1
        Do While iLine < vbMod.CountOfLines
          sProcName = vbMod.ProcOfLine(iLine, pk)
          If sProcName <> "" Then
            iRow = iRow + 1
            ReDim Preserve sOutput(1 To 2 + iRow)
            sOutput(2 + iRow) = vbComp.Name & ": " & sProcName
            iLine = iLine + vbMod.ProcCountLines(sProcName, pk)
          Else
            ' This line has no procedure, so go to the next line.
            iLine = iLine + 1
          End If
        Loop

        ' clean up
        Set vbMod = Nothing
        Set vbComp = Nothing

      Next
    Else
      ReDim Preserve sOutput(1 To 3)
      sOutput(3) = "Project protected"
    End If

    If UBound(sOutput) = 2 Then
      ReDim Preserve sOutput(1 To 3)
      sOutput(3) = "No code in project"
    End If

    ' define output location and dump output
    If Len(wsOutput.Range("A1").Value) = 0 Then
      iCol = 1
    Else
      iCol = wsOutput.Cells(1, wsOutput.Columns.Count).End(xlToLeft).Column + 1
    End If
    wsOutput.Cells(1, iCol).Resize(UBound(sOutput) + 1 - LBound(sOutput)).Value = _
        WorksheetFunction.Transpose(sOutput)

    ' clean up
    Set vbProj = Nothing
  Next

  ' clean up
  wsOutput.UsedRange.Columns.AutoFit
End Sub
 
Upvote 0
If they don't appear on the list with the code in the previous post (out of interest) do they appear with the code below by NateO?

Code: 
[color=darkblue]Sub[/color] Rprt()
[color=darkblue]Dim[/color] nm [color=darkblue]As[/color] Name, n [color=darkblue]As[/color] [color=darkblue]Long[/color], y [color=darkblue]As[/color] Range, z As Worksheet
Application.ScreenUpdating = [color=darkblue]False[/color]
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "NameTest"
[color=darkblue]Set[/color] z = Sheets("NameTest")
n = 2
[color=darkblue]With[/color] z
    .[a1:g65536].ClearContents
    .[a1:D1] = [{"Name","Sheet Name","Starting Range","Ending Range"}]
    [color=darkblue]For[/color] [color=darkblue]Each[/color] nm [color=darkblue]In[/color] ActiveWorkbook.Names
        .Cells(n, 1) = nm.Name
        .Cells(n, 2) = Range(nm).Parent.Name
        .Cells(n, 3) = nm.RefersToRange.Address(False, False)
        n = n + 1
    [color=darkblue]Next[/color] nm
[color=darkblue]End[/color] [color=darkblue]With[/color]

[color=darkblue]Set[/color] y = z.Range("c2:c" & z.[c65536].End(xlUp).Row)
y.TextToColumns Destination:=z.[C2], DataType:=xlDelimited, _
    OtherChar:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1))
[a:d].EntireColumn.AutoFit

Application.ScreenUpdating = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
MARK858 said:
I am afraid a screenshot of the macro list doesn't tell me anything I didn't already know.

When you run the code below by Jon Peltier what results does it give (please note the "set a reference to the Microsoft Visual Basic for Applications Extensibility 5" comment is important)?

Rich (BB code): 
'' Based on:
'' Displaying a List of All VBA Procedures in an Excel 2007 Workbook
''     from the Ribbon (June 2009)
'' by Frank Rice, Microsoft Corporation
'' http://msdn.microsoft.com/en-us/library/dd890502(office.11).aspx#

'' set a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 Library

Sub GetProcedures()
  ' Declare variables to access the Excel workbook.
  Dim app As Excel.Application
  Dim wb As Excel.Workbook
  Dim wsOutput As Excel.Worksheet
  Dim sOutput() As String
  Dim sFileName As String

  ' Declare variables to access the macros in the workbook.
  Dim vbProj As VBIDE.VBProject
  Dim vbComp As VBIDE.VBComponent
  Dim vbMod As VBIDE.CodeModule

  ' Declare other miscellaneous variables.
  Dim iRow As Long
  Dim iCol As Long
  Dim iLine As Integer
  Dim sProcName As String
  Dim pk As vbext_ProcKind

  Set app = Excel.Application

  ' create new workbook for output
  Set wsOutput = app.Workbooks.Add.Worksheets(1)

  'For Each wb In app.Workbooks
  For Each vbProj In app.VBE.VBProjects

    ' Get the project details in the workbook.
    On Error Resume Next
    sFileName = vbProj.Filename
    If Err.Number <> 0 Then sFileName = "file not saved"
    On Error GoTo 0

    ' initialize output array
    ReDim sOutput(1 To 2)
    sOutput(1) = sFileName
    sOutput(2) = vbProj.Name
    iRow = 0

    ' check for protected project
    On Error Resume Next
    Set vbComp = vbProj.VBComponents(1)
    On Error GoTo 0

    If Not vbComp Is Nothing Then
      ' Iterate through each component in the project.
      For Each vbComp In vbProj.VBComponents

        ' Find the code module for the project.
        Set vbMod = vbComp.CodeModule

        ' Scan through the code module, looking for procedures.
        iLine = 1
        Do While iLine < vbMod.CountOfLines
          sProcName = vbMod.ProcOfLine(iLine, pk)
          If sProcName <> "" Then
            iRow = iRow + 1
            ReDim Preserve sOutput(1 To 2 + iRow)
            sOutput(2 + iRow) = vbComp.Name & ": " & sProcName
            iLine = iLine + vbMod.ProcCountLines(sProcName, pk)
          Else
            ' This line has no procedure, so go to the next line.
            iLine = iLine + 1
          End If
        Loop

        ' clean up
        Set vbMod = Nothing
        Set vbComp = Nothing

      Next
    Else
      ReDim Preserve sOutput(1 To 3)
      sOutput(3) = "Project protected"
    End If

    If UBound(sOutput) = 2 Then
      ReDim Preserve sOutput(1 To 3)
      sOutput(3) = "No code in project"
    End If

    ' define output location and dump output
    If Len(wsOutput.Range("A1").Value) = 0 Then
      iCol = 1
    Else
      iCol = wsOutput.Cells(1, wsOutput.Columns.Count).End(xlToLeft).Column + 1
    End If
    wsOutput.Cells(1, iCol).Resize(UBound(sOutput) + 1 - LBound(sOutput)).Value = _
        WorksheetFunction.Transpose(sOutput)

    ' clean up
    Set vbProj = Nothing
  Next

  ' clean up
  wsOutput.UsedRange.Columns.AutoFit
End Sub
Click to expand...

Do I need to change something in this to get it to work in my file? When I run it, it highlights the "Dim vbProj As VBIDE.VBProject" and says "Compile error: User-defined type not defined".
 
Upvote 0
MARK858 said:
If they don't appear on the list with the code in the previous post (out of interest) do they appear with the code below by NateO?

Code: 
[COLOR=darkblue]Sub[/COLOR] Rprt()
[COLOR=darkblue]Dim[/COLOR] nm [COLOR=darkblue]As[/COLOR] Name, n [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], y [COLOR=darkblue]As[/COLOR] Range, z As Worksheet
Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "NameTest"
[COLOR=darkblue]Set[/COLOR] z = Sheets("NameTest")
n = 2
[COLOR=darkblue]With[/COLOR] z
    .[a1:g65536].ClearContents
    .[a1:D1] = [{"Name","Sheet Name","Starting Range","Ending Range"}]
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] nm [COLOR=darkblue]In[/COLOR] ActiveWorkbook.Names
        .Cells(n, 1) = nm.Name
        .Cells(n, 2) = Range(nm).Parent.Name
        .Cells(n, 3) = nm.RefersToRange.Address(False, False)
        n = n + 1
    [COLOR=darkblue]Next[/COLOR] nm
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

[COLOR=darkblue]Set[/COLOR] y = z.Range("c2:c" & z.[c65536].End(xlUp).Row)
y.TextToColumns Destination:=z.[C2], DataType:=xlDelimited, _
    OtherChar:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1))
[a:d].EntireColumn.AutoFit

Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
Click to expand...

When I ran this one it highlighted ".Cells(n,2)=Range(nm).Parent.Name" and gave the following error "Run-time error '1004': Method 'Range' of object'_Global' failed"
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Macro works for one file, does not work for all files in the directory
2
Replies
13
Views
1K
Alex Blakenburg
A
E
  • Solved
Pop Up Showing Image
Replies
6
Views
1K
excelakos
E
R
  • Solved
Compile error : Named argument not found, when using Formula1:
Replies
7
Views
1K
Richhodg
R
D
  • Question Question
Completely cleaning account numbers and storing with a specific format
Replies
2
Views
1K
diversification
D
Tigron
  • Solved
VBA Runtime Error when Selecting Dropdown List
2 3 4
Replies
31
Views
3K
Tigron
Tigron

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,638
Latest member
Oluwabukunmi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top