how to pass a variable no matter what it is

Colo

MrExcel MVP,

Joined

Mar 20, 2002

Messages

1,659

Office Version

1. 2016
2. 2003 or older

Platform

1. Windows

• Oct 6, 2004

• #2

miconian

You can use a Variant type variable as an argument of the other routine. Normally a variable named Target is declared as a Range type, but you can change it.

Code:

Sub Test()
    OtherRoutine [A1]
    OtherRoutine "Hey!"
End Sub


Private Sub OtherRoutine(ByVal Target As Variant)
    MsgBox TypeName(Target)
End Sub

 

Upvote 0

M

miconian

Well-known Member

Joined

Aug 18, 2004

Messages

769

• Oct 6, 2004

• #3

I'm sorry, but I still don't get it. Could you give me an example where the first routine is the worksheet_change event, and it passes Target to a routine created by the programmer?

Thanks...

 

Upvote 0

D

dk

MrExcel MVP

Joined

Feb 15, 2002

Messages

2,942

• Oct 6, 2004

• #4

Hi,

I'm not entirely sure what you're asking...

Here is an example of how to pass a variable from one procedue to another. Let me know if this isn't what you want.

Code:

Sub RunMe()
    Dim Target As Range


    Set Target = Range("A1:A10")

    'Now pass target to the PassMeSomething procedure
    PassMeSomething Target

End Sub


Sub PassMeSomething(SomeValue As Range)

    MsgBox "I was passed a Range object whose address is " & SomeValue.Address

End Sub

HTH
Dan

 

Upvote 0

D

dk

MrExcel MVP

Joined

Feb 15, 2002

Messages

2,942

• Oct 6, 2004

• #5

Miconian - re your last post. Here is an example of passing in a variable from the Worksheet_Change event procedure into another sub:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

    'Call an external procedure passing in Target
    MyCustomSub Target

End Sub


Private Sub MyCustomSub(SomeRange As Range)

    MsgBox SomeRange.Address & " was changed."

End Sub

 

Upvote 0

M

miconian

Well-known Member

Joined

Aug 18, 2004

Messages

769

• Oct 6, 2004

• #6

Oh. That's it? You just give the name of the routine you're passing to, and the name of the variable? Hrmph. I thought it was a bigger deal. Thanks very much...

 

Upvote 0

M

miconian

Well-known Member

Joined

Aug 18, 2004

Messages

769

• Oct 6, 2004

• #7

Wait...dk, are you saying that the variable that was Target in worksheet_change becomes the variable SomeRange in OtherRoutine?

 

Upvote 0

D

dk

MrExcel MVP

Joined

Feb 15, 2002

Messages

2,942

• Oct 6, 2004

• #8

I was in the middle of typing some long reply but then thought it would be easier to search instead:-

Take a look at : http://msdn.microsoft.com/library/d...98/html/vbconpassingargumentstoprocedures.asp

Pay particular attention to ByRef and ByVal. If you don't specify either then the default is ByRef i.e. if you pass an argument from procedure A to procedure B and B change the argument, that variable will also be changed in procedure A.

These things can be confusing so feel free to post back if something's unclear.

Regards,
Dan

 

Upvote 0

M

miconian

Well-known Member

Joined

Aug 18, 2004

Messages

769

• Oct 6, 2004

• #9

I just got an "The object has disconnected itself from the clients" error, and now the code that was running fine to a point, is not running at all.

Basically, the code involves creating a series of validation lists, calling a different procedure depending on which column the target is in. I was passing the value with "OtherSub Target" and then starting OtherSub with (Target as Range).

But now, even if I change to (ByVal Target as Range), or even if I close the workbook and re-open it, the code won't run at all...drat.

 

Upvote 0

M

miconian

Well-known Member

Joined

Aug 18, 2004

Messages

769

• Oct 6, 2004

• #10

...and this seems to persist, even if I put everything in the same procedure...it's like code isn't connected to the workbook anymore.

 

Upvote 0