Invalid Reference (Macro)

[johnske]

Anyone?

I've been sent a workbook that's got 17 macros listed yet there's no macros in the VBE window. (If you try to run these macros you get "Reference Is Not Valid" error message)

How do you remove these non-existent macros from the list of macros?

TIA
John

[Jacob Hilderbrand]

Check your references in the VBE and see if another workbook is referenced.

[K. Georgiadis]

Go to Edit>Links and see if the workbook is linked to another workbook. If the "Break links" button is greyed out, you may have to run an Add-In such as J. Walkenbach's PUP (Power Utility Pak) to track down the links.

oh, there is also a free download called FindLink for tracking down phantom links. I personally use PUP

[johnske]

Thanx Jake: No, no refs set...

Thanx K. Georgiadis: Downloaded PUP (no luck)...

However, I found a way (of sorts) - the given task was to delete a heap of named ranges (7773 names) with some throwing errors with a normal Name.Delete 'cos they weren't valid names, and all these names bloated the file to 2.9 MB - so I just went ahead with the task, reducing it to 116KB leaving only 85 "unremovable" names, half of which can then be deleted 'by hand'.

On closing and re-opening the file, I found all these phantom macros had disappeared!!!

Many Thanx,
John

[K. Georgiadis]

Congratulations on solving this problem (one with which I am familiar). It doesn't matter how it happens, as long as it happens!

[Cyberdude]

johnske, you touched on a subject that has annoyed me for a very long time. I have a workbook that has 3 defined names that I have so far been unable to delete. I click on the delete button, and it seems to work, but the next time I look, they are still there. In fact one of the names is a duplicate ... how can THAT happen?? (Whew!)

[johnske]

johnske, you touched on a subject that has annoyed me for a very long time. I have a workbook that has 3 defined names that I have so far been unable to delete. I click on the delete button, and it seems to work, but the next time I look, they are still there. In fact one of the names is a duplicate ... how can THAT happen?? (Whew!)

Hi Cyber,

Not sure why, but try this:

Sub DeleteNamedRanges()
Dim Name As Name
If MsgBox("Start: " & Names.Count & _
" named ranges - Delete?", vbYesNo, _
"Delete Names?") = vbNo Then Exit Sub
For Each Name In ActiveWorkbook.Names
On Error Resume Next
Name.Delete
Next Name
MsgBox "Finished: " & Names.Count & " named ranges remain"
End Sub

Can't guarantee it'll work, cos they may be some 'rogue' names (that're apparently 'undeleteable').

If I could work out how ppl can put these rogue names in there in the first place I could maybe come up with a way of removing them. But I can't - Excel just jacks up and refuses to do it. (I suspect that they're able to be put in there when ppl have corrupted files)

John

[Cyberdude]

Thanx, John ... I'll give it a try.

[Cyberdude]

John, I studied your code in the previous reply, and it appears that it will try to delete ALL defined names (both good and bad). I don't want to do that. But it introduced a new problem (to me).
To clarify, if I define a name "XXX" as "=Sheet1!A1", then I call "XXX" the "defined name" and "=Sheet1!A1" the definition.
When your code refers to "each NAME", I'm not sure what it's looking at. I used your code as a basis and instead of deleting, I listed them all out. Unfortunately, the list contained only definitions, not the name of the definitions. Then I stumbled onto a method called "ListNames", and used it. It DID list both the defined names and the definitions. But nowhere does it show how to refer to the defined name itself. I don't want to use the definitions as a search arg because in some cases I have two different defined names that both have the same definition. In such a case I want to delete just one of the two, not both.

[geekgirlau]

Try the following:

Sub ListNames()
' Purpose: Paste a list of all range names on a new sheet
Sheets.Add
Selection.ListNames
Selection.Columns.AutoFit
ActiveCell.Select
ActiveSheet.Name = "ListRanges"
End Sub
 
Sub RecreateNamedRanges()
' Purpose: * Remove all range names
' * Recreate range names that appear in list
' Restrictions: Active cell must currently be sitting within
' a list of range names and the range to which they
' refer. The list should start at cell A1 on an
' otherwise blank sheet.
Dim nm As Name
Range("A1").Select
' a pasted list of range names will have the following:
' * sheet name = "ListRanges"
' * column 1 = no space in range name
' * column 2 = range starting with "="
' * column 3 = blank
If ActiveSheet.Name = "ListRanges" And _
InStr(1, ActiveCell.Formula, " ") = 0 And _
Left(ActiveCell.Offset(0, 1).Formula, 1) = "=" And _
ActiveCell.Offset(0, 2).Formula = "" Then
If vbYes = MsgBox("This macro will do the following:" & vbCrLf & vbCrLf & _
vbTab & "* delete all range names" & vbCrLf & _
vbTab & "* recreate only the range names in this list" & vbCrLf & _
vbTab & "* delete the 'ListRanges' sheet" & vbCrLf & vbCrLf & _
"Continue?", vbQuestion + vbYesNo + vbDefaultButton2, _
"Recreate Range Names?") Then
' delete all range names
For Each nm In ActiveWorkbook.Names
On Error Resume Next
nm.Delete
Next nm
' loop through list redefining listed range names
Do Until ActiveCell.Formula = ""
ActiveWorkbook.Names.Add ActiveCell.Formula, ActiveCell.Offset(0, 1).Formula
ActiveCell.Offset(1, 0).Select
Loop
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
MsgBox "The listed range names have been recreated.", vbInformation, _
"Range Names Completed"
End If
Else
MsgBox "Please run 'ListNames' macro prior to recreating range names.", vbInformation, _
"Range Names Missing"
End If
End Sub

The first macro creates a new sheet listing all range names. The second macro deletes all range names, then recreates only those appearing in the list.

The idea here is that after listing all range names, you delete any row containing names you want to remove. You can also fix up any errors, perform a search and replace on ranges, and manually add new ranges. When you run the second macro, all range names in your updated list are created.

I used something like this on some very large workbooks containing over 200 range names that often didn't behave as they were supposed to!!!

[Cyberdude]

Hey, geekgirlau, your code looks interesting, and probably what I can use. I'll copy it and give it a try. Thanx!