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]

Can you tell us what the problem is?

Also, where do you get the error and how is the sub the error is in being called?

 

[Norie]

Not sure about the error you mentioned but try calling like this.

    ApplyToEach "EditHyperLinkFootnote", "Link", Range("Bookingref").Column - Range("Link").Column, 0

and changing the from UBound(args) to args(Ubound(args)) - assuming it's the last argument that determines how the next sub is called.

 

[gregtx81]

Does named range "Link" only have Worksheet scope?

 

[TimLundSE26]

Sorry for delay getting back on this - working on another project.

gregtx81 - no - 'Link' it has global scope - not sure why I had

​

For Each c In ws.Range("Link") and not For Each c In Range("Link")

Norie - the error happens on the line

Run strMacro, c, args(0)​

and the rest of the error message text is 'Cannot run the macro 'EditHyperLinkFootnote'. The macro may not be available in this workbook or all macros may be disabled'

Norie - I wasn't quite sure what you meant by 'changing the from UBound(args) to args(Ubound(args)) ' but it looked as if you were saying to add a final dummy argument to the call - so I did, but got the same error, except on the line

Run strMacro, c, args(0), args(1)​

 

[Norie]

Tim

No I didn't mean that.

As it is your code is only ever going to goto one of the cases in your Select Case structure.

This is because UBound(args) will always return the same value - the index of the last item in the array args.

Now assuming you actually want to get the last item in the array and not it's index you can use args(ubound(args)).

 

[TimLundSE26]

As it is your code is only ever going to goto one of the cases in your Select Case structure.

Not so. When I call 'ApplyToEach' here in 'TestFunctionals'

Public Sub TestFunctionals()

ApplyToEach "ShowAddress", "Booking_ref"

End Sub


Public Sub ShowAddress(c As Excel.Range)

MsgBox c.Address

End Sub​

The case which gets called - successfully - is

Case -1
Run strMacro, c​

because in this case, Ubound(args) = -1

 

[Norie]

Oops, my mistake - I assumed you were passing the value for the select case as one of the arguments.

 

[Norie]

Forgot to mention - the original code actually worked for me.

Think I might have been looking at it so long I created a non-existent error in my head.

 

[TimLundSE26]

Thanks.

Don't you just love machine & configuration dependent problems?