Hidden macro names from Assign Macro Window

[YasserKhalil]

Hello everyone
I know that to hide a macro name from "Assign Macro" window (Alt + F8) we can type the word Private before Sub plus macro name
Private Sub Test()

I have a weird file that has no macro names in the Assign Macro window .. although there is no private before Sub nor before Module
Please have a look at it

[LJMetzger]

Hi Yasser,

Fortunately or unfortunately your file exhibits the same behavior for me.

You seem to have some kind of corruption. I have seen similar symptoms when there is a duplication somewhere. This kind of problem can often take a long time to resolve.

I have the following suggestions.
a. Make a backup copy of your file.
b. Open your file. Go to VBA. Export (and delete) all code modules.
c. Import a module.
d. Go to Excel to see if you can see the Macros (ALT F8).
e. If successful, repeat steps c. and d., one Module at a time.
f. If unsuccessful, the imported module is the cause of the problems.

Lewis

[PCI]

"I have a weird file that has no macro names in the Assign Macro window .. although there is no private before Sub nor before Module"
but what is your need?
it is possible to see the macros : in Visual Basic select view > Project Explorer

[YasserKhalil]

Thanks a lot Mr. Lewis for this great solution
I applied the steps and delete each module one by one and the same with userform modules ..
After that saved my file and open it again and begin to import a module one after another and checked the Alt + F8 window ..
There is no module which cause any error
After importing all modules and userforms everything is ok now.. That's weird

I thought of a quick way to such a problem .. so I thought of exporting all modules (userforms and sheet modules and standard mouldes) then delete all in on shot
But I don't know how to import all the modules from specific path (The same workbook path and the folder name which has all the modules inside)
I need your help at this point
Here' the first part which exports all modules and then delete all ...

Sub Export_Delete_Import()
    'References >> Microsoft Visual Basic for Applications Extensibility
    '-------------------------------------------------------------------
    Dim VBComp As VBIDE.VBComponent
    Dim DestDir As String, FName As String, Ext As String
    
    If ActiveWorkbook.Path = "" Then
        MsgBox "You must first save this workbook somewhere so that it has a path.", , "Error"
        Exit Sub
    End If
    
    DestDir = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & " Modules"
    If Dir(DestDir, vbDirectory) = vbNullString Then MkDir DestDir
    
    For Each VBComp In ActiveWorkbook.VBProject.VBComponents
        If VBComp.CodeModule.CountOfLines > 0 Then
            
            Select Case VBComp.Type
                Case vbext_ct_ClassModule: Ext = ".cls"
                Case vbext_ct_Document: Ext = ".cls"
                Case vbext_ct_StdModule: Ext = ".bas"
                Case vbext_ct_MSForm: Ext = ".frm"
                Case Else: Ext = vbNullString
            End Select
            
            If Ext <> vbNullString Then
                FName = DestDir & "\" & VBComp.Name & Ext
                
                If Dir(FName, vbNormal) <> vbNullString Then Kill (FName)
                VBComp.Export (FName)
            End If
        End If
    Next VBComp
    
    Call DeleteAllVBACode
End Sub

Private Sub DeleteAllVBACode()
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    
    Set VBProj = ActiveWorkbook.VBProject
    
    For Each VBComp In VBProj.VBComponents
        If VBComp.Type = vbext_ct_Document Then
            Set CodeMod = VBComp.CodeModule
            With CodeMod
                .DeleteLines 1, .CountOfLines
            End With
        Else
            VBProj.VBComponents.Remove VBComp
        End If
    Next VBComp
End Sub

[LJMetzger]

Hi Yasser,

Thanks for sharing the VBE code. I know it is more efficient to do it with VBE, but I don't like referencing the VBE Library unless I absolutely have to.

After importing all modules and userforms everything is ok now.. That's weird

I see similar type of behavior all the time. I do most of my VBA in Excel 2003. When there is an Active X control in a questioner's file (Excel 2007+), the file acts like it is corrupt in Excel 2003. To correct the problem I have to:
a. Save the file as a .xls file.
b. Delete all the 'Active X' controls on a sheet.
c. Make a copy of the sheet
d. Delete the Original Sheet
e. Rename the copy to the name of the Original Sheet.
f. Insert 'Active X' controls if necessary.
g. Save and close the file.
h. When the file is opened, the file acts normally.

Lewis

[PCI]

Do you know that if you open twice Excel and load in each Excel a different file you can open 2 VBA windows and just pick up a module from one VBA and put it in the other VBA: This could simplify the manipulations

[YasserKhalil]

Thank a lot Mr. Lewis for the logic steps
In fact I like quick solutions for problems..
Now I have solved half of the problem.. Just additional step to import all modules from the folder (exported in the last step)
Is there an easy way to import all modules in one shot...?

Another approach I tried to use VBA Cleaner (32 Bit only) but nothing repaired ..
It still only one way to solve the problem .. is to remove all modules and save the workbook then open it and import all the modules again
Thank you very much Mr. PCI for useful information ..It may help me

[LJMetzger]

Ron de Bruin to the rescue: http://www.rondebruin.nl/win/s9/win002.htm

It will import all the modules in the specified path.

[YasserKhalil]

I tried this link earlier but I couln't execute it
How to specify the path for my folder which store my exported modules
I tried this change

SpecialPath = ThisWorkbook.Path & "\Example.xlsm Modules\"

But it doen't work ..

I need a simple way which uses file dialog to select my folder then import desired modules
Can you help me doing that task?

[LJMetzger]

The easiest way is to:
a. Create a new Folder that is empty.
b. Put the file with the code in that folder
c. Set the path in the VBA code to:

SpecialPath = ThisWorkbook.Path & "\"

d. All the imported and exported files will be in the same folder.
e. After you're done, move your file to whatever folder you like.

[YasserKhalil]

Thank you very much Mr. Lewis
I worked now very well ..