Using Windows API calls in VB/VBA 

Even though VB/VBA has a rich functionality sometimes when you want to do something you'll find that you can't simply because the function or procedure that you need just isn't available. If that happens, before giving up on it check out the win32 API just in case what you need is in there. You may be surprised since there are literally thousands of functions and subroutines, normally at quite a low-level, that you can utilize. OK so where do you find all these great routines?. Look for a file called win32api.txt on your computer. Note that in VB you can also use the API viewer. This file on its own only includes the declarations and names for procedures, constants and types but it doesn't explain how to use them. For that you'll need to refer to the Microsoft's SDK. Alternatively do what I did and work out roughly from the procedure name what it does then go to the NET and get a concrete example of its use. Here's an example showing how to use three of the WInAPI functions to sleep, set the cursor position and send a mouse click. 


Public Declare Function SetCursorPos Lib "user32" _
(ByVal x As Long, _
ByVal y As Long) As Long

Public Declare Sub mouse_event Lib "user32" _
(ByVal dwFlags As Long, _
ByVal dx As Long, _
ByVal dy As Long, _
ByVal cButtons As Long, _
ByVal dwExtraInfo As Long)

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Sub SendLeftClick()
    mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
    mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
End Sub

Private Sub SendRightClick()
    mouse_event MOUSEEVENTF_RIGHTDOWN, 0, 0, 0, 0
    mouse_event MOUSEEVENTF_RIGHTUP, 0, 0, 0, 0
End Sub

Sub macro1()
SetCursorPos 460, 410
Sleep 5000
End Sub

​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking