Hello,
Doe's "AddressOf" works with Excel macros?
I keep getting a Compile Error for using it.
"Invalid use of AddressOf operator"

This is the code:

Private Sub TestSub()
i = 0
End Sub

Private Function GetAddr(ByVal Addr As Long) As Long
GetAddr = Addr
End Function

Private Sub Test()
Dim Addr as Long
Addr = GetAddr (AddressOf TestSub)
End Sub

The error is for Test Sub
Tnx

I don't have much experience with AddressOf, but from what I can see, if
TestSub is moved to a regular module that should fix it.

From my book "VB and VBA In a Nutshell" by Paul Lomax:

AddressOf Operator
Rules at a Glance:
"The callback function must be stored in a code module; attempting to store
it in a class or form module generates a compile-time error, "Invalid use of
AddressOf operator."

When I tested your code, with TestSub moved to a regular code module, it
worked.


--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility

"E.Z." <(E-Mail Removed)> wrote in message
news:egZ1Sf$(E-Mail Removed)...
> Hello,
> Doe's "AddressOf" works with Excel macros?
> I keep getting a Compile Error for using it.
> "Invalid use of AddressOf operator"
>
> This is the code:
>
> Private Sub TestSub()
> i = 0
> End Sub
>
> Private Function GetAddr(ByVal Addr As Long) As Long
> GetAddr = Addr
> End Function
>
> Private Sub Test()
> Dim Addr as Long
> Addr = GetAddr (AddressOf TestSub)
> End Sub
>
> The error is for Test Sub
> Tnx
>
>

Tnx a lot,
What do you mean by "regular code module"?

"Tim Zych" <tzych@nospam at earthlink dot net> wrote in message
news:ekn$(E-Mail Removed)...
>I don't have much experience with AddressOf, but from what I can see, if
>TestSub is moved to a regular module that should fix it.
>
> From my book "VB and VBA In a Nutshell" by Paul Lomax:
>
> AddressOf Operator
> Rules at a Glance:
> "The callback function must be stored in a code module; attempting to
> store it in a class or form module generates a compile-time error,
> "Invalid use of AddressOf operator."
>
> When I tested your code, with TestSub moved to a regular code module, it
> worked.
>
>
> --
> Regards,
> Tim Zych
> http://www.higherdata.com
> Workbook Compare - Excel data comparison utility
>
> "E.Z." <(E-Mail Removed)> wrote in message
> news:egZ1Sf$(E-Mail Removed)...
>> Hello,
>> Doe's "AddressOf" works with Excel macros?
>> I keep getting a Compile Error for using it.
>> "Invalid use of AddressOf operator"
>>
>> This is the code:
>>
>> Private Sub TestSub()
>> i = 0
>> End Sub
>>
>> Private Function GetAddr(ByVal Addr As Long) As Long
>> GetAddr = Addr
>> End Function
>>
>> Private Sub Test()
>> Dim Addr as Long
>> Addr = GetAddr (AddressOf TestSub)
>> End Sub
>>
>> The error is for Test Sub
>> Tnx
>>
>>
>
>

In the VBE -> Insert -> Module.


--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility

"E.Z." <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Tnx a lot,
> What do you mean by "regular code module"?
>
> "Tim Zych" <tzych@nospam at earthlink dot net> wrote in message
> news:ekn$(E-Mail Removed)...
>>I don't have much experience with AddressOf, but from what I can see, if
>>TestSub is moved to a regular module that should fix it.
>>
>> From my book "VB and VBA In a Nutshell" by Paul Lomax:
>>
>> AddressOf Operator
>> Rules at a Glance:
>> "The callback function must be stored in a code module; attempting to
>> store it in a class or form module generates a compile-time error,
>> "Invalid use of AddressOf operator."
>>
>> When I tested your code, with TestSub moved to a regular code module, it
>> worked.
>>
>>
>> --
>> Regards,
>> Tim Zych
>> http://www.higherdata.com
>> Workbook Compare - Excel data comparison utility
>>
>> "E.Z." <(E-Mail Removed)> wrote in message
>> news:egZ1Sf$(E-Mail Removed)...
>>> Hello,
>>> Doe's "AddressOf" works with Excel macros?
>>> I keep getting a Compile Error for using it.
>>> "Invalid use of AddressOf operator"
>>>
>>> This is the code:
>>>
>>> Private Sub TestSub()
>>> i = 0
>>> End Sub
>>>
>>> Private Function GetAddr(ByVal Addr As Long) As Long
>>> GetAddr = Addr
>>> End Function
>>>
>>> Private Sub Test()
>>> Dim Addr as Long
>>> Addr = GetAddr (AddressOf TestSub)
>>> End Sub
>>>
>>> The error is for Test Sub
>>> Tnx
>>>
>>>
>>
>>
>
>

Yup, got it. works.
Tnx again