CODEWITHSUNDEEP
excelvbavariablesparameter-passing
user2320210
user2320210

Reputation: 11

Passing through variable to sub in VBA

This is probably very basic, but I have tried so many things and the always give errors.

Basically what I'm trying to do is run a procedure every time a cell gets changed in Excel. So I'm using Sub Worksheet_Change(ByVal Target As Range). All works well, but in this procedure, I'm calling another sub procedure several times.

I want to reuse the 'Target' value in this procedure, but for some reason, I can't find how to do it. I tried placing 'Public rTarget As Range' and do a 'rTarget = Target' at the beginning of the procedure. But the rTarget stays empty when I call the sub procedure.

How do I make this work? I'm now adding Target as one of the variables to to subroutine, but that just looks stupid.

Thanks!

Upvotes: 1

Views: 10272

Answers (2)

Dick Kusleika
Dick Kusleika

Reputation: 33145

Passing Target as a variable is how you should do it. You want to keep your variables in the tightest scope possible and passing them as arguments is the tightest.

If you're passing the variable to a bunch of different procedures in the same module, it may make sense to have a module-level variable. That looks like this

Private mrTarget As Range


Private Sub Worksheet_Change(ByVal Target As Range)

    Set mrTarget = Target
    MySub

End Sub

Private Sub MySub()

    Debug.Print mrTarget.Address

End Sub

Based on your questions, it's just one sub that gets called repeatedly. In that case, I would stick with passing it as an argument.

Upvotes: 0

John Bustos
John Bustos

Reputation: 19544

Private Sub Worksheet_Change(ByVal Target As Range)
    MySub Target
End Sub

Sub MySub(ByVal Target As Range)
    ' Your sub code goes here and can work with the Target Range from the Worksheet_Change Event
End Sub

Upvotes: 3

Related Questions