Passing parameters to subroutine

ljtong

Board Regular
Joined
May 23, 2005
Messages
71
Hello all,

Why does this not work?

Code:
Sub generic()
    Call Clear1("SlowDown")
End Sub

Sub Clear1(ByVal ws As Worksheet)
    Workbook("Gap").Worksheet(ws).Activate
End Sub

I get "mismatch type" error

If I use
Code:
Call Clear1(SlowDown)
I get "object required" error

How do I pass a worksheet argument to a subroutine?

I am obviously not grasping something important about arguments in subroutines/functions
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

ws expects a worksheet but you are passing a string.

Change:
ws as worksheet to ws as string
workbook("gap").sheets(ws).activate
to workbooks("Gap").sheets(ws).select

HTH

Tony
 

Mark O'Brien

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530
Or you could go the other way from acw's suggestion, pass the workbook object:

Code:
Sub generic() 
    Call Clear1(Workbook("Gap").Worksheet("SlowDown")) 
End Sub 

Sub Clear1(ByVal ws As Worksheet) 
    ws.Activate 
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,379
Messages
5,571,785
Members
412,419
Latest member
acemali
Top