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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,998
Messages
5,834,808
Members
430,322
Latest member
excelnoobnoob

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top