take sheet to procedure

[Guest]

I want to take sheet to procedure and i don't know how write it
I thought I must do this like that:

sub procedure1(sheet1 as worksheet)
....
end sub

sub main()

Dim file As Workbook
Dim sheet2 As Worksheet

Set file = Workbooks("file to use.xls")
Set sheet2= file.Sheets("somekindsheet")

procedure1 sheet2 ''I tried too procedure1(sheet2) and didn't work

end sub

and I don't know what I'm doing wrong. When I create procedure for value
everything is ok and working but for worksheet...

 

[John Coleman]

The following gave me no problem:

Sub procedure1(ws As Worksheet)
MsgBox ws.Name
End Sub

Sub procedure2()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks("test1.xls")
Set ws = wb.Sheets(1)
procedure1 ws
End Sub

Running procedure2 results in a msgbox with "Sheet1" appearing.
"test1.xls" has to be open for this to work of course, but that is a
separate issue. Maybe the problem is in what your procedure 1 is trying
to do. Could you elaborate on "didn't work"?

HTH

-John Coleman

 

[Guest]

Your logic is correct but your naming is going to be a problem... File,
Sheet1 and Sheet2 are going to cause you issues. The default code names for
worksheets is Sheet1 and Sheet2, ... You can rename them, but if you have not
already done so then that will be an issue... Try this...

sub procedure1(sht as worksheet)
msgbox sht.name
end sub

sub main()
Dim wbk As Workbook
Dim sht As Worksheet

Set wbk = Workbooks("file to use.xls")
Set sht = file.Sheets("somekindsheet")

Call procedure1(sht)

end sub

 

[John Coleman]

You are probably right, but, somewhat oddly, the following works for
me:

Sub procedure1(sheet1 As Worksheet)
MsgBox sheet1.Name
End Sub

Sub procedure2()
Dim wb As Workbook
Dim sheet1 As Worksheet
Set wb = ActiveWorkbook
Set sheet1 = wb.Sheets(2)
procedure1 sheet1
End Sub

When run a message box with "Sheet2" appears. If Sheet1 is a global
identifier then any local declaration should override the global one
within the local scope (although *why* anyone would want to do that is
beyond me). On the other hand, my first attempt to do such an
experiment somehow triggered a wierd and unreproducible bug in which
the IDE was unable to locate either of the procedures until I copied
them to the clipboard and then repasted - so maybe that usage is
unstable in addition to being ill-advised.

-John Coleman
-

 

[Guest]

I know where I made mistake. I wanted to create procedure in loop it look
like this(names are in polish and I use example names)

Dim i As Integer

Sub do_samething(use_file As Worksheet)

If use_file.Cells(i, "A") > 0 Then

use_file.Cells(i, "G") = "samething"

End If

End Sub

Private Sub CommandButton1_Click()
i = 1

'file_one is file with button with this macro

Dim file_two As Workbook
Dim sheet_two As Worksheet 'sheet in file_two

Set file_two = Workbooks("file to use.xls")
Set sheet_two = file_two.Sheets("some_kind_sheet")

Do While file_one.sheet_one.Cells(i, "A") <> Empty

do_samething (file_two)

i = i + 1
Loop

End Sub

and you see now that in while I'm checking one thing and inside loop I'm
using seckend file. I think this is problem but I'm not sure.