MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Accessing set s1=worksheets("input") globally

  Asked By: Tye    Date: Mar 02    Category: MS Office    Views: 1936

Generally a worksheet object is referred by statement
Set s1= worksheets("input") within the subroutine.
I never saw set statement mentioned in the declarations.

I was able to use s1 within the same subroutine but not in the other
subroutine. Therefore, I had to take the same statement in all the
subroutines. Is it possible to make s1 global and define only once.



1 Answer Found

Answer #1    Answered By: Abagail Cohen     Answered On: Mar 02

You can declare s1 as a public worksheet  object as shown below. Just
place the declaration at the top of any module. Once assigned in the
sub Test1(), s1 is accessible in Test2() also. Note that "Set" is
used when assigning objects. If you are dealing with Objects and
forget to use "Set", you get an error.

Public s1 As Worksheet

Sub Test1()
Set s1 = Worksheets("Sheet1")
End Sub

Sub Test2()
Set s2 = s1
MsgBox s2.Name
End Sub

Didn't find what you were looking for? Find more on Accessing set s1=worksheets("input") globally Or get search suggestion and latest updates.