Returning the address of a procedure to a variable

Question

first at all sorry for my English, it's not my native language.

I'd like to associate the address of a procedure that I created to a variable. Actually, I'm using a Windows Timer and in the Timer code, the function SetTimer is used this way :

    Sub StartTimer()
    TimerSeconds = 0.1 ' how often to "pop" the timer.
    TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf move2)
End Sub

I'd like to replace "AddressOf move2" with a public variable and to do so I need to obtain the address of the procedure but I don't know how to do this. I tried to write

variable = AddressOf move2

And it doesn't work (variable type = long)

Answer 1

That is easy to implement. The only thing you didn't tell us is the "bitness" of your Excel. Well, I'm using Excel2019 64Bit. So, in my case, I'm using LongLong integers (8 Bytes). If you're running a 32Bit version, you have to account for that. On 32Bit platforms, you do not have LongLongs, but only Long integers (4 Bytes). I just mention that, because you wrote:

And it doesn't work (variable type = long)

The best thing you can do is declare your pointer variable of type LongPtr. A LongPtr is not really a type of its own, but a neat feature that was introduced with VBA7. Any integer variable you declare as of type LongPtr is a Long on 32Bit systems and a LongLong on 64Bit systems.

In the declaration section of your module set your variable to

Public LngPtrMove2 As LongPtr

If you still running VBA6 you can use

Public LngPtrMove2 As Long ' for 32Bit

or, on a 64Bit system, you can use conditional compilation if you have to support backward-compatibility:

#If Win64 Then
    Public LngPtrMove2 As LongLong
#Else
    Public LngPtrMove2 As Long
#End If

Now, use the following function to assign the function pointer to your public variable on a 64Bit system running VBA 7 like so:

Public Function LngPtrMove2() As LongPtr
    LngPtrMove2 = VBA.CLngPtr(AddressOf move2)
End Function

On a 64Bit VBA 6 and on all 32Bit systems use this:

Public Function LngPtrMove2() As Long
    LngPtrMove2 = Vba.CLng(AddressOf move2)
End Function

Please be aware of two things:

1. You cannot test the assignment in the VBE Immediate Window!

2. You must keep the leading "VBA." in all calling variants. For more information read this SO question: Difference between VBA.CBlah and CBlah

Hope this helps :-)