What is making 'Run' give a 1004 error here?

[TimLundSE26]

I want to run any number of different bits of code, at different times for each cell in various different ranges, without every time having to write a separate For each ... next loop every time. So I wrote the following

Public Sub ApplyToEach(strMacro As String, strHeading As String, ParamArray args())

Dim c As Excel.Range

For Each c In Range(strHeading)
Select Case UBound(args)
Case -1
Run strMacro, c
Case 0
Run strMacro, c, args(0)
Case 1
Run strMacro, c, args(0), args(1)
End Select
Next​

End Sub
​

And successfully tested it as follows:

Public Sub TestFunctionals()

ApplyToEach "ShowAddress", "Booking_ref"

End Sub​

Public Sub ShowAddress(c As Excel.Range)

MsgBox c.Address

End Sub​

and also with another macro more complex macro - that modified addresses of hyperlinks in cells.

But with some code to edit a reference number into the Word document that these hyperlinks refer to, I get a 1004 message

Public Sub EditHyperLinkFootnote(c As Excel.Range, iRefOffset)

Dim appWord As Word.Application
Dim oWordDoc As Word.Document

If c.Hyperlinks.Count = 1 Then
On Error Resume Next

Set appWord = GetObject(, "Word.Application")
If Err.Number > 0 Then Set appWord = CreateObject("Word.Application")
Err.Clear
On Error GoTo 0
appWord.Visible = True
Application.DisplayAlerts = False

c.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
If Right(c.Hyperlinks(1).Address, 4) = ".doc" Then

Set oWordDoc = appWord.Documents(appWord.Documents.Count)

With oWordDoc
.Sections(1).Footers(wdHeaderFooterPrimary).Range.Text = "Bedlam Bunch ref" & vbTab & vbTab & c.Offset(0, iRefOffset).Value
.Close SaveChanges:=True

End With

End If

End If

End Sub​

So that for this macro to work

Public Sub EditHyperLinkFootnotes()

Dim c As Excel.Range

' ApplyToEach "EditHyperLinkFootnote", "Link", Range("Booking_ref").Column - Range("Link").Column

For Each c In wsBookings.Range("Link")
EditHyperLinkFootnote c, -1
Next

End Sub​

I have to go back to writing an explicit For Each ... next loop.

What is different about my sub "EditHyperLinkFootnote" that causes this?

 

[Norie]

Tim

So you've found the problem?

Just of interest what was it?

The only thing I could think that could be wrong was something to do with how/where the subs were declared.

Kind of ruled that out as everything appeared to be declared properly.

The only other thing that came to mind was some security issue.

 

[gregtx81]

Tim, Norie

I bet I've found the cause of the error

With the Run statement you'll get a 1004 error if you call a sub of the same name as certain objects (even though the direct,explicit call will work). This seems to include worksheet and name objects at a minimum. To hazard a guess, it may include all child objects of the Workbook object.

 

[TimLundSE26]

Norie - no - I meant that if it worked for you, but not for me, it was in some way machine dependent - and I'm not so great at these. I'll get back to this, but am on another project right now

 

[Norie]

Tim

No problem.

I think gregtx81 has a good point which about names.

I think in your case though it's more likely to be some other function/sub/module you or someone else has created that's clashing with the code.