Pass Worksheet to general Sub (or Function)

[Ramses505]

This is not finished code - I am trying soemthing out and using this as my test example - I have soem code which I want to able to run against any sheet - so I need to pass in the sheet that I want the code to run against; this is the code I have;

Sub Test(pSheet As Worksheet)              ' SEEMS TO WORK EVEN AFTER DELETING SOME DATA
Dim c As Long, r As Long, iStr As String

  c = pSheet.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
  r = pSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
  iStr = Replace(Cells(r, c).Address, "$", "")                          ' Address with no "$"
  MsgBox iStr
End Sub

If I replace the 'pSheet' part of the code with "Sheet1" - it all works as expected (I do have a Sheet1)- if I call it like this;
Call Test("Sheet1") or Test(Sheet1) I get an error 'subscript out of range'

I have been through many (many) itereations of trying to get this to work - I have used a String variable to pass the name I have tried assigning the worksheet to a variable like this;
Dim ws As Worksheet
Set ws = Worksheet("Sheet1")
Call Test(ws) or Call Test("ws")

all to no avail. I obviously have something very wrong with my syntax (and probably my understanding of it) - what can I do to pass in 'any' worksheet to run the code against here ?

Thanks very much for reading.

 

[MARK858]

If I select Sheet3 and run Test2 below it works for me (i.e. I get the message box and A2 on Sheet1 gets filled)

Sub Test(pSheet As Worksheet)
    MsgBox "it works"
    pSheet.Cells(2, 1) = "xxxx"
End Sub

Sub Test2()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    Call Test(ws)
End Sub

Btw

iStr = Replace(Cells(r, c).Address, "$", "")

in your code should be

iStr = Replace(pSheet.Cells(r, c).Address, "$", "")

if you want it to happen on Sheet1

 

[PeteWright]

It works for me as intended with this simple example:

Sub Test(ws As Worksheet)
   ws.Cells.Range("A1").Value = "test"
End Sub

Sub TestCall()
   Call Test(Sheet1)
End Sub

BTW, there are two different ways to call a sheet, by it's Name (eg. MyFirstSheet - that's what is displayed on the tab) or Internal Name (eg. Sheet1 - you can change it in the VBA Editor)

 

[Ramses505]

If I select Sheet3 and run Test2 below it works for me (i.e. I get the message box and A2 on Sheet1 gets filled)

Sub Test(pSheet As Worksheet)
    MsgBox "it works"
    pSheet.Cells(2, 1) = "xxxx"
End Sub

Sub Test2()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    Call Test(ws)
End Sub

Btw

iStr = Replace(Cells(r, c).Address, "$", "")

in your code should be

iStr = Replace(pSheet.Cells(r, c).Address, "$", "")

if you want it to happen on Sheet1

Hi Mark, thanks very much for the reply. I found this did not work in the worksheet I was using, but did work in a brand new worksheet File | New. I think this is down to me not understanding the difference between the Codename and the Name. Changing the Set to Set ws = Sheet1 seems to work.

I was trying to make sure I used a name that would withstand relabelling the actual sheets - I think I now have a better understanding of what I was doing wrong. I am grateful for you taking the time to help. Thanks

 

[MARK858]

I think this is down to me not understanding the difference between the Codename and the Name. Changing the Set to Set ws = Sheet1 seems to work.

The worksheets Codename is the name outside the parentheses and is referenced as Sheet2.Range("Range")

The worksheets Name is inside the parentheses and is referenced as Sheets("MySheet").Range("Range") or Worksheets("MySheet").Range("Range")

Basically just elaborating on what @PeteWright wrote

 

[Ramses505]

OK - so if I wanted to just call the Sub Test without using another sub to do that - so just calling it in the middle of other code - do I have to create and assign;

Dim ws As Worksheet<br>
Set ws = Worksheet<strong><span>s</span></strong>("Sheet1")

or is there some way I can just call it like Call Test(ThisIsTheSheet) ?

Thanks again, appreciate the help.

 

[MARK858]

Do you mean like this?

Sub Test(pSheet As Worksheet)
    MsgBox "it works"
    pSheet.Cells(2, 1) = "ZZZZ"
End Sub

Sub test2()
    Call Test(Sheet1)
End Sub

 

[Ramses505]

Yes, exactly like that !

Thanks very much Mark, I appreciate the help, again.

 

[MARK858]

You're welcome