Pointer to Workseet

[zbadger]

All-

I want to know if it is possible to get a pointer to a specific worksheet that I can then dereference.

For instance:

For Each WS In ActiveWorkbook.Worksheets
If WS.Name = "name"
' more code...
Next WS

To me the internal structure is using a pointer to the ws or some other data type that I want to access directly. I want to do something similar to

ws = worksheet(1) or ws = worksheet("name")
ws.Row(x,x) '
more code

Any help would be great.

[austenr]

Are you an engineer?

[Bob Phillips]

[vba]

Set ws = worksheet(1) or ws = worksheet("name")
MsgBox ws.Cells(x,x)
[/vba]

[Paul_Hossler]

I think XLD's approach is by far the more standard way

But if you want to live close to the edge ...


[vba]
Sub drv()
Dim ws As Worksheet
Dim ptr1 As Long, ptr2 As Long
Set ws = ActiveSheet

'recommended way
If ws Is Worksheets(1) Then
MsgBox "Same thing as #1"
Else
MsgBox "Not the same thing as #1"
End If

If ws Is Worksheets(2) Then
MsgBox "Same thing as #2"
Else
MsgBox "Not the same thing as #2"
End If
'NOT recommended way
ptr1 = ObjPtr(ws)
ptr2 = ObjPtr(Worksheets(1))
If ptr1 = ptr2 Then
MsgBox "Same thing as #1"
Else
MsgBox "Not the same thing as #1"
End If
End Sub

[/vba]

Paul

[zbadger]

Thanks all for the reply. I found the answer while I was looking for something else and then came here to update and saw the someone else had found the same thing. The function I was looking for was the Set ws = ....

thanks
again

[GTO]

Greetings zbadger,

I hope you'll forgive the intrusion, hoping to learn :-)
When I try:
[vba]
Sub example()
Dim ws As Worksheet

With ThisWorkbook
Set ws = .Worksheets(1) Or ws = .Worksheets("name")
MsgBox ws.Cells(2, 2)
End With
End Sub
[/vba]
I get err 438, Object doesn't support property/method?

Could anyone explain what I'm missing ("lots" doesn't count), and how the Or is supposed to be working?

Thank you so much,

Mark

[Bob Phillips]

It doesn't, the OR is a conditional operator and there is no condition being enacted, so OR is not appropriate. I think you are mis-reading the OP's meaning in his first post, that OR was just to suggest two ways of (trying to) achieve the same objective.

[GTO]

It doesn't, the OR is a conditional operator and there is no condition being enacted, so OR is not appropriate. I think you are mis-reading the OP's meaning in his first post, that OR was just to suggest two ways of (trying to) achieve the same objective.

Oh for Pete's sakes. Thank you Bob. Of course that makes sense and I mis-read something terrible. Better to ask than wonder, but damn, that is embarrasing. (Now I wonder how much it'll take to bribe admin into deleting the question...hee-hee)

Just so as I don't seem like I've suffered permanent drain bamage... I posted a question here: http://vbaexpress.com/forum/showthre...199#post216199

[mdmackillop]

(Now I wonder how much it'll take to bribe admin into deleting the question...hee-hee)

It will cost exactly £1 more than you can afford!

[GTO]

It will cost exactly £1 more than you can afford!

Jeepers!