Calling a private VBA subroutine with Parameter

Question

I have a VBA Add-In which is supposed to add functionality to several worksheets. I have a class to listen for worksheet events and trigger macros. This works but I can't seem to call private macros with parameters from it.

My event listener is in a class module and is this:

Option Explicit
Private WithEvents App As Application

Private Sub Class_Initialize()
    Set App = Application
End Sub

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Source As Range)
    On Error GoTo Finish
    App.EnableEvents = False
    Call Application.Run("Worksheet_Change", "Source")
 Finish:
    App.EnableEvents = True
End Sub

It is initialized on opening the workbook in a module like so:

Sub Auto_Open()
    Set clsAppEvents = New clsApplicationEvents
End Sub

And the macro I'm trying to call is in a separate module again and takes the form:

Private Sub Worksheet_Change(ByVal Target As Range)
    'Do some work on range
End Sub

I've tried the following ways of calling the macro and none have worked so far:

Call Application.Run("Worksheet_Change", "Source")
Application.Run "Worksheet_Change", "Source"
Application.Run "Worksheet_Change" "Source"

It would help to include the error you get when you try this. In any case, the most obvious problem seems to be that you're passing Source as a string instead of a Range object. Try removing the quotes around Source in your call to Run. — Tim Williams
– Tim Williams, Commented Jul 29, 2013 at 4:37
I didn't have an error message the macro simply didn't run however removing the quotes like so: Application.Run "Worksheet_Change", Source - Did the trick. If you want to make an answer I'll mark it as solved. Thanks — baarkerlounger
– baarkerlounger, Commented Jul 29, 2013 at 6:24

Tim Williams · Accepted Answer · 2013-07-29 15:00:41Z

3

The arguments to Run don't all have to be strings, so

Application.Run "Worksheet_Change", "Source"

Should be

Application.Run "Worksheet_Change", Source

answered Jul 29, 2013 at 15:00

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Calling a private VBA subroutine with Parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related