Is there a way in VBA to compare Object References?

Consider the following

Option Explicit
Dim WBArray(1 To 1) As Workbook

Public Sub assign()
Set WBArray(1) = ActiveWorkbook
End Sub

Public Sub test()
If WBArray(1) = ActiveWorkbook Then
MsgBox "Yes"
Else
MsgBox "No"
End If
End Sub

Functionally, I want to make an AddIn with a class module that captures
Application events, but I only want to execute the code for those events on
workbooks that the user has told me are part of my application. So, I was
considering having the user tell me which workbooks are "mine" and in the
event handlers, the first thing I do is check to see if the event causing
workbook is in the list. I was hoping to not have to manage names or other
user definable properties.

Above, in test(), I functionally want to compare the object reference in the
array against the object reference for ActiveWorkbook to see if they refer to
the same object.

Is this something I can do in VBA? (Excel 2003) I could capture the
workbook.name property, and track changes to the name, but I was hoping to
avoid that.

Bob

I forgot to include, the IF statement in Test() bombs with

Run-time error '91':

Object variable or With block variable not set.

Bob


"INTP56" wrote:

> Is there a way in VBA to compare Object References?
>
> Consider the following
>
> Option Explicit
> Dim WBArray(1 To 1) As Workbook
>
> Public Sub assign()
> Set WBArray(1) = ActiveWorkbook
> End Sub
>
> Public Sub test()
> If WBArray(1) = ActiveWorkbook Then
> MsgBox "Yes"
> Else
> MsgBox "No"
> End If
> End Sub
>
> Functionally, I want to make an AddIn with a class module that captures
> Application events, but I only want to execute the code for those events on
> workbooks that the user has told me are part of my application. So, I was
> considering having the user tell me which workbooks are "mine" and in the
> event handlers, the first thing I do is check to see if the event causing
> workbook is in the list. I was hoping to not have to manage names or other
> user definable properties.
>
> Above, in test(), I functionally want to compare the object reference in the
> array against the object reference for ActiveWorkbook to see if they refer to
> the same object.
>
> Is this something I can do in VBA? (Excel 2003) I could capture the
> workbook.name property, and track changes to the name, but I was hoping to
> avoid that.
>
> Bob
>

Sorry, forget that last error message, different code ....

The error message is:

Run-time error '438':

Object doesn't support this property or method.

"INTP56" wrote:

> Is there a way in VBA to compare Object References?
>
> Consider the following
>
> Option Explicit
> Dim WBArray(1 To 1) As Workbook
>
> Public Sub assign()
> Set WBArray(1) = ActiveWorkbook
> End Sub
>
> Public Sub test()
> If WBArray(1) = ActiveWorkbook Then
> MsgBox "Yes"
> Else
> MsgBox "No"
> End If
> End Sub
>
> Functionally, I want to make an AddIn with a class module that captures
> Application events, but I only want to execute the code for those events on
> workbooks that the user has told me are part of my application. So, I was
> considering having the user tell me which workbooks are "mine" and in the
> event handlers, the first thing I do is check to see if the event causing
> workbook is in the list. I was hoping to not have to manage names or other
> user definable properties.
>
> Above, in test(), I functionally want to compare the object reference in the
> array against the object reference for ActiveWorkbook to see if they refer to
> the same object.
>
> Is this something I can do in VBA? (Excel 2003) I could capture the
> workbook.name property, and track changes to the name, but I was hoping to
> avoid that.
>
> Bob
>

Bob,

Change...
If WBArray(1) = ActiveWorkbook Then
To...
If WBArray(1) is ActiveWorkbook Then
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"INTP56"
<(E-Mail Removed)>
wrote in message
Is there a way in VBA to compare Object References?
Consider the following
Option Explicit
Dim WBArray(1 To 1) As Workbook

Public Sub assign()
Set WBArray(1) = ActiveWorkbook
End Sub

Public Sub test()
If WBArray(1) = ActiveWorkbook Then '<<<<<
MsgBox "Yes"
Else
MsgBox "No"
End If
End Sub

Functionally, I want to make an AddIn with a class module that captures
Application events, but I only want to execute the code for those events on
workbooks that the user has told me are part of my application. So, I was
considering having the user tell me which workbooks are "mine" and in the
event handlers, the first thing I do is check to see if the event causing
workbook is in the list. I was hoping to not have to manage names or other
user definable properties.
Above, in test(), I functionally want to compare the object reference in the
array against the object reference for ActiveWorkbook to see if they refer to
the same object.
Is this something I can do in VBA? (Excel 2003) I could capture the
workbook.name property, and track changes to the name, but I was hoping to
avoid that.
Bob

Thanks Jim, that's just what I wanted. Bob

"Jim Cone" wrote:

> Bob,
>
> Change...
> If WBArray(1) = ActiveWorkbook Then
> To...
> If WBArray(1) is ActiveWorkbook Then
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
> (Excel Add-ins / Excel Programming)

Thank you very much, Jim! This is also exactly what I was looking for. I
hope it works with other objects as well. I shall do more testing...

Thanks,
Matthew Pfluger

"Jim Cone" wrote:

> Bob,
>
> Change...
> If WBArray(1) = ActiveWorkbook Then
> To...
> If WBArray(1) is ActiveWorkbook Then
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
> (Excel Add-ins / Excel Programming)
>
>
>
> "INTP56"
> <(E-Mail Removed)>
> wrote in message
> Is there a way in VBA to compare Object References?
> Consider the following
> Option Explicit
> Dim WBArray(1 To 1) As Workbook
>
> Public Sub assign()
> Set WBArray(1) = ActiveWorkbook
> End Sub
>
> Public Sub test()
> If WBArray(1) = ActiveWorkbook Then '<<<<<
> MsgBox "Yes"
> Else
> MsgBox "No"
> End If
> End Sub
>
> Functionally, I want to make an AddIn with a class module that captures
> Application events, but I only want to execute the code for those events on
> workbooks that the user has told me are part of my application. So, I was
> considering having the user tell me which workbooks are "mine" and in the
> event handlers, the first thing I do is check to see if the event causing
> workbook is in the list. I was hoping to not have to manage names or other
> user definable properties.
> Above, in test(), I functionally want to compare the object reference in the
> array against the object reference for ActiveWorkbook to see if they refer to
> the same object.
> Is this something I can do in VBA? (Excel 2003) I could capture the
> workbook.name property, and track changes to the name, but I was hoping to
> avoid that.
> Bob
>
>

Matthew,
You are welcome.
--
Jim Cone
Portland, Oregon USA



"Matthew Pfluger"
wrote in message
Thank you very much, Jim! This is also exactly what I was looking for. I
hope it works with other objects as well. I shall do more testing...
Thanks,
Matthew Pfluger



"Jim Cone" wrote:
> Bob,
> Change...
> If WBArray(1) = ActiveWorkbook Then
> To...
> If WBArray(1) is ActiveWorkbook Then
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
> (Excel Add-ins / Excel Programming)





> "INTP56"
> <(E-Mail Removed)>
> wrote in message
> Is there a way in VBA to compare Object References?
> Consider the following
> Option Explicit
> Dim WBArray(1 To 1) As Workbook
>
> Public Sub assign()
> Set WBArray(1) = ActiveWorkbook
> End Sub
>
> Public Sub test()
> If WBArray(1) = ActiveWorkbook Then '<<<<<
> MsgBox "Yes"
> Else
> MsgBox "No"
> End If
> End Sub
>
> Functionally, I want to make an AddIn with a class module that captures
> Application events, but I only want to execute the code for those events on
> workbooks that the user has told me are part of my application. So, I was
> considering having the user tell me which workbooks are "mine" and in the
> event handlers, the first thing I do is check to see if the event causing
> workbook is in the list. I was hoping to not have to manage names or other
> user definable properties.
> Above, in test(), I functionally want to compare the object reference in the
> array against the object reference for ActiveWorkbook to see if they refer to
> the same object.
> Is this something I can do in VBA? (Excel 2003) I could capture the
> workbook.name property, and track changes to the name, but I was hoping to
> avoid that.
> Bob