Tuesday, 20 December 2011

Pointers in VBA.. Huhh !!! (delegates in VBA)

“Pointers“ – aaah the word which is not pleasing to most of the programmers is a very powerful functionality in programmers world. (Rightly said power and pain goes along)

I thought working in VBA arena one would be safe from the world of pointers and I wouldn’t have to deal with it. But embarking on voyage to explore the object oriented nature of VBA and its support to OOP’s (Object oriented programming) I stumbled upon an article and resources in line of using pointers in VBA.

So keywords associated with pointers in VBA are as follows:

Note: All the Address values of pointers are stored in the “Long” data type

1. AddressOf: This functionality in VBA provides the addresses of objects (i.e. module, projects, but Not Classes), functions & procedures address.
2. VarPtr: Returns the address of a variable.
3. VarPtrArray: Returns the address of an array.
4. StrPtr: Returns the address of the UNICODE string buffer.
5. VarPtrStringArray: Returns the address of an array of strings.
6. ObjPtr: Returns the pointer to the interface referenced by an object variable.

Using the concept of pointers I have designed a VBA code to illustrate the implementation of “delegate” (function pointers) in VBA and also illustrated the use of the above mentioned keywords.

Also to mention this concept of pointers is also associated with the Callback functionality of VBA with external DLL’s.

Delegate Sample Code:
Option Explicit

'-----External Library Declaration which helps call the Proc by Address -----
Private Declare Function CallWindowProc _
                          Lib "user32.dll" Alias "CallWindowProcA" ( _
                              ByVal lpPrevWndFunc As Long, _
                              ByVal hwnd As Long, _
                              ByVal msg As Long, _
                              ByVal wParam As Long, _
                              ByVal lParam As Long) As Long

'-----This is the main function calling upon the proc via pointer -----
Public Sub test_delegate()
    Dim sMessage As String
    Dim nSubAddress    'As Long

    'This message will be passed to our Sub as an argument
    sMessage = InputBox("Please input a short message")
    'Get the address to the sub we are going to call
    nSubAddress = ProcPtr(AddressOf ShowMessage)
    'Do the magic! Function Called via Pointer...
    CallWindowProc nSubAddress, VarPtr(sMessage), 0&, 0&, 0&
End Sub

'-----This is the subroutine we want to call by address-----
Private Sub ShowMessage( _
        msg As String, _
        ByVal nUnused1 As Long, _
        ByVal nUnused2 As Long, _
        ByVal nUnused3 As Long)
'This is the Sub we will call by address
'it only use one argument but we need to pull the others
'from the stack, so they are just declared as Long values
    MsgBox msg
End Sub

'-----This function is used to extract the address of value to long -----
Private Function ProcPtr(ByVal nAddress As Long) As Long
'Just return the address we just got
    ProcPtr = nAddress
End Function

Pointer Sample Code:
Option Explicit

Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
                               (ByVal dst As Long, _
                                ByVal src As Long, _
                                ByVal nBytes As Long)

Sub pointerDemo()

    Dim iCount As Double
    Dim jCount As Double
    Dim ptr_iCount As Long
    Dim ptr_jCount As Long

    iCount = 100.23
    jCount = 200.45

    ptr_iCount = VarPtr(iCount)
    ptr_jCount = VarPtr(jCount)

    '--Common mistake: This will only overwrite the pointer variables
    '--The underlying values refrneced by the pointers will remina the same.
    'ptr_jCount = ptr_iCount

    '--The below mentioned method is the correct version to change the
    '--value of the underlying variables refrenced by the pointers.(2 version)
    'CopyMemory VarPtr(jCount), VarPtr(iCount), Len(iCount)
    CopyMemory ptr_jCount, ptr_iCount, Len(iCount)

    Debug.Print iCount
    Debug.Print jCount

End Sub


Download Solution
Download solution

Link1: http://msdn.microsoft.com/en-us/library/y72ewk2b%28v=vs.80%29.aspx
Link2: http://support.microsoft.com/kb/199824 
Link3: http://www.vbforums.com/showpost.php?p=2046053&postcount=5

No comments: