Windows API

Creates a reference to a procedure or function in an external DLL.
The reason for calling an API function is to perform something that would otherwise be impossible.

These functions are a set of functions that Windows programs use to manipulate Windows.
In traditional applications the functions used / called where 'statically linked' to the program.
The program included all the functions it needed.
The obvious problem is the same functions and procedures are reproduced lots of times.

64 Bit - PtrSafe

In 32 bit office a pointer must be a 32 bit variable In 64 bit office a pointer must be a 64 bit variable.
When declaring an API you must acknowledge this fact by adding the PtrSafe keyword to the API declaration.

When declaring an API you must acknowledge this fact by adding the PtrSafe keyword to the API declaration.
The PtrSafe attribute is optional on the 32-bit version of Office.
When running 32 Bit Office a pointer must be a 32 bit variable
When running 64 Bit Office a pointer must be a 64 bit variable.

Alias Keyword

Whenever you use an API (Application Programming Interface) call function prefix the alias with "A" eg aFindWindow
Windows uses a different approach. Libraries are usually 'dynamically linked' to the program. The common routines reside in a Dynamic Link Library and are stored in the Windows / System directory if more than one program uses them.

These functions provide facilities to create a window, change its size, read and write Registry entries, manipulate a file and so on. Most of these functions are stored in the three link libraries (User32.dll, Kernel.dll & ????)

A full list of all Windows API functions and subroutines, containing the syntax of their calls can be found in the help file "WIN32API.txt" which is available for download from the unit websites.

If you are going to use Windows API calls in your code it may be worth encapsulating them into a class.

Before you can use a Windows API function you must declare the function at the top of a standard code module above the first procedure.
Declaring a Windows API can be a bit tricky for the following reasons:
1) Deciding which API to actually use
2) Deciding in which library the function is located
3) Knowing the exact arguments

Once it is declared you can use it just like a regular function.

Example - GetWindowsDirectory

This function returns the name of the directory in which Windows is installed and has two arguments,
After calling the function, the Windows directory is contained in lpBuffer, and the length of the directory string is contained in nSize.
Once you have added the Declare statement to the top of your module, you can access the function by calling the GetWindowsDirectoryA function.

Creating a Wrapper

Most of the time you will probably want to create a wrapper for the API function.
Creating a wrapper can simplify actually using the function.

Dynamic Link Libraries (DLL)
One of the most powerful features of VBA is the ability to call a DDL from a VBA procedure. A dynamic link library is a list of routines loaded into memory and linked to applications at run-time. DLL's are normally written in C or C++ although you could create then using Pascal or Delphi. You declare a reference to an external procedure in a DLL by the following syntax.
Because DLL routines reside in files that are external to your application, you must let VBA know exactly where the files are
Public Declare PtrSafe Sub name Lib "libname" Alias "aliasname" (arglist)
Public Declare PtrSafe Function name Lib "libname" Alias "aliasname" (arglist) as type
The procedure name that immediately follows the Declare keyword indicates the name you are going to use to call the procedure (ie an alias name). The name in quotes, after the Alias keyword specifies the actual name of the procedure as it can be found in the DLL
Libname is the name of the DLL that contains the procedure being declared and alias name is the name of the procedure in the DLL. To use a DLL you must know the procedure in it and the arguments to each of these procedures.
Accompanying the DLL there may be a file called a 'type library' which describes the procedures within the DLL. A type library normally has the extension (.OLB) or (.TLB) and is registered with the OLE component of Windows. The Setup program that installs the DLL usually creates the properties with the Windows Registry. Tools > References shows all the registered type libraries available

If you use a type library, there is no need to use Declare statements as the functionality is included. Type libraries also avoid the difficulties of passing strings to DLLs. The Windows API doesn't have a type library and so you must Declare the functions before they can be used. A Declare statement must be made in the declaration section of your module.
It is important to save your work before running any code that calls a DLL as an incorrect call will result in a General Protection (GP) fault and will cause VBA to crash losing any unsaved changes. Also take care when returning strings from DLLs

You can only pass numeric arrays to DLLs. (not string or user defined) When passing an array you must give some indication of the size of the array.
The name of the procedure given in the Declare statement is case sensitive. If you want the procedure name in your code to use a different capitalisation you must use the Alias Name.
Converting C parameters into VBA declarations. The documentation for DLLs that aren't part of the Windows API are usually in the form of a C header file that provides the type and number of the arguments to the functions in the DLL. Toy will need to convert the C data types into their equivalent VBA data type.
Calling a DLL procedure is a method of performing tasks that standard VBA functions and statements do not allow. For example VBA has no intrinsic ability to retrieve information about the amount of free memory available but you can easily do this with an API call. Windows itself includes a number of DLLs with hundreds of useful functions. These functions are collectively called the Windows API.

Libraries

DeleteUrlCacheEntry

Deleting a file from the cache:

URLDownloadToFile

Downloading a file from a URL

WNetGetConnection

Visual Basic Editor - References

Here is a list of common Visual Basic Editor Libraries

Data Type Conversions

Advapi32.dll

Private Declare PtrSafe Function RegOpenKeyA Lib "advapi32.dll" _ 
  (ByVal hKey As Long, ByVal sSubKey As String, ByRef hkeyResult As Long) As LongPtr 

Private Declare PtrSafe Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As LongPtr 

Private Declare PtrSafe Function RegSetValueExA Lib "advapi32.dll" _ 
  (ByVal hKey As Long, ByVal sValueName As String, ByVal dwReserved As Long, _ 
   ByVal dwType As Long, ByVal sValue As String, ByVal dwSize As Long) As LongPtr 

Private Declare PtrSafe Function RegCreateKeyA Lib "advapi32.dll" _ 
  (ByVal hKey As Long, ByVal sSubKey As String, ByRef hkeyResult As Long) As LongPtr 

Private Declare PtrSafe Function RegQueryValueExA Lib "advapi32.dll" _ 
  (ByVal hKey As Long, ByVal sValueName As String, ByVal dwReserved As Long, _ 
   ByRef lValueType As Long, ByVal sValue As String, ByRef lResultLen As Long) As LongPtr 

Private Declare PtrSafe Function RegQueryValueExString Lib "advapi32.dll" Alias "RegQueryValueExA" _ 
  (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, _ 
   lpType As Long, ByVal lpData As String, lpcbData As Long) As LongPtr 

Private Declare PtrSafe Function RegQueryValueExLong Lib "advapi32.dll" _ ' _  
  (ByVal hKey As Long, ByVal lpValueName As String, 'ByVal lpReserved As Long, lpType As Long, _  
    'lpData As Long, lpcbData As Long) As LongPtr

Private Const REG_SZ As Long = 1 
Private Const REG_EXPAND_SZ As Long = 2 
Private Const REG_BINARY As Long = 3 
Private Const REG_DWORD As Long = 4 

Public Sub GetRegistry_Value() 
Dim RootKey As String 
Dim Path As String 
Dim RegEntry As String 
Dim lasttime As String 

RootKey = "HKEY_CURRENT_USER" 

'Path = "software\microsoft\office\16.0\Excel"
'RegEntry = "ExcelName"

Path = "software\microsoft\office\16.0\excel\options" 
RegEntry = "Open1" 

'Path = "software\microsoft\office\excel\addins\COMExcelModelCover.MyConnect"
'RegEntry = "FriendlyName"

   Call GetRegistry(RootKey, Path, RegEntry) 

End Sub 

Private Function GetRegistry( _ 
ByVal Key As String, _ 
ByVal Path As String, _ 
ByVal ValueName As String) As String 

'  Reads a value from the Windows Registry

Dim hKey As Long 

Dim lValueType As Long 
Dim sResult As String 
Dim lResultLen As Long 
Dim ResultLen As Long 
Dim x As LongPtr 
Dim TheKey As Long 

TheKey = -99 

Select Case UCase(Key) 
Case "HKEY_CLASSES_ROOT": TheKey = &H80000000 
Case "HKEY_CURRENT_USER": TheKey = &H80000001 
Case "HKEY_LOCAL_MACHINE": TheKey = &H80000002 
Case "HKEY_USERS": TheKey = &H80000003 
Case "HKEY_CURRENT_CONFIG": TheKey = &H80000004 
Case "HKEY_DYN_DATA": TheKey = &H80000005 
End Select 

'   Exit if key is not found
If TheKey = -99 Then 
    GetRegistry = "Not Found" 
    Exit Function 
End If 

If RegOpenKeyA(TheKey, Path, hKey) <> 0 Then 
    MsgBox "cannot find/open key" 
    Exit Function 
End If 

lValueType = REG_SZ 
sResult = Space(150) 
lResultLen = 150 

x = RegQueryValueExString(hKey, ValueName, 0&, lValueType, sResult, lResultLen) 
    
   Call RegCloseKey(hKey) 

    If (x = 2) Then 
        Call MsgBox("Cannot find the file specified") 
        Exit Function 
    End If 

  Call MsgBox("'" & VBA.Left(VBA.Trim(sResult), lResultLen - 1) & "'") 

End Function 

Private Declare Function RegOpenKeyA Lib "ADVAPI32.DLL" _ 
    (ByVal hKey As Long, ByVal sSubKey As String, _ 
    ByRef hkeyResult As Long) As Long 

Private Declare Function RegCloseKey Lib "ADVAPI32.DLL" _ 
    (ByVal hKey As Long) As Long 

Private Declare Function RegSetValueExA Lib "ADVAPI32.DLL" _ 
    (ByVal hKey As Long, ByVal sValueName As String, _ 
    ByVal dwReserved As Long, ByVal dwType As Long, _ 
    ByVal sValue As String, ByVal dwSize As Long) As Long 

Private Declare Function RegCreateKeyA Lib "ADVAPI32.DLL" _ 
    (ByVal hKey As Long, ByVal sSubKey As String, _ 
    ByRef hkeyResult As Long) As Long 

Private Declare Function RegQueryValueExA Lib "ADVAPI32.DLL" _ 
    (ByVal hKey As Long, ByVal sValueName As String, _ 
    ByVal dwReserved As Long, ByRef lValueType As Long, _ 
    ByVal sValue As String, ByRef lResultLen As Long) As Long 

Sub UpdateRegistryWithTime() 
    RootKey = "hkey_current_user" 
    Path = "software\microsoft\office\9.0\excel\LastStarted" 
    RegEntry = "DateTime" 
    RegVal = Now() 
    LastTime = GetRegistry(RootKey, Path, RegEntry) 
    Select Case LastTime 
        Case "Not Found" 
            Msg = "This routine has not been executed before." 
        Case Else 
            Msg = "This routine was lasted executed: " & LastTime 
    End Select 
    Msg = Msg & Chr(13) & Chr(13) 
    
    Select Case WriteRegistry(RootKey, Path, RegEntry, RegVal) 
        Case True 
            Msg = Msg & "The registry has been updated with the current date and time." 
        Case False 
            Msg = Msg & "An error occured writing to the registry..." 
    End Select 
    MsgBox Msg, vbInformation, "Registry Demo" 
End Sub 

Private Function GetRegistry(Key, Path, ByVal ValueName As String) 
'  Reads a value from the Windows Registry

    Dim hKey As Long 
    Dim lValueType As Long 
    Dim sResult As String 
    Dim lResultLen As Long 
    Dim ResultLen As Long 
    Dim x, TheKey As Long 

    TheKey = -99 
    Select Case UCase(Key) 
        Case "HKEY_CLASSES_ROOT": TheKey = &H80000000 
        Case "HKEY_CURRENT_USER": TheKey = &H80000001 
        Case "HKEY_LOCAL_MACHINE": TheKey = &H80000002 
        Case "HKEY_USERS": TheKey = &H80000003 
        Case "HKEY_CURRENT_CONFIG": TheKey = &H80000004 
        Case "HKEY_DYN_DATA": TheKey = &H80000005 
    End Select 
    
'   Exit if key is not found
    If TheKey = -99 Then 
        GetRegistry = "Not Found" 
        Exit Function 
    End If 

    If RegOpenKeyA(TheKey, Path, hKey) <> 0 Then _ 
        x = RegCreateKeyA(TheKey, Path, hKey) 
    
    sResult = Space(100) 
    lResultLen = 100 
    
    x = RegQueryValueExA(hKey, ValueName, 0, lValueType, _ 
    sResult, lResultLen) 
        
    Select Case x 
        Case 0: GetRegistry = Left(sResult, lResultLen - 1) 
        Case Else: GetRegistry = "Not Found" 
    End Select 
    
    RegCloseKey hKey 
End Function 

Private Function WriteRegistry(ByVal Key As String, _ 
    ByVal Path As String, ByVal entry As String, _ 
    ByVal value As String) 
    
    Dim hKey As Long 
    Dim lValueType As Long 
    Dim sResult As String 
    Dim lResultLen As Long 
   
    TheKey = -99 
    Select Case UCase(Key) 
        Case "HKEY_CLASSES_ROOT": TheKey = &H80000000 
        Case "HKEY_CURRENT_USER": TheKey = &H80000001 
        Case "HKEY_LOCAL_MACHINE": TheKey = &H80000002 
        Case "HKEY_USERS": TheKey = &H80000003 
        Case "HKEY_CURRENT_CONFIG": TheKey = &H80000004 
        Case "HKEY_DYN_DATA": TheKey = &H80000005 
    End Select 
    
'   Exit if key is not found
    If TheKey = -99 Then 
        WriteRegistry = False 
        Exit Function 
    End If 

'   Make sure  key exists
    If RegOpenKeyA(TheKey, Path, hKey) <> 0 Then 
        x = RegCreateKeyA(TheKey, Path, hKey) 
    End If 

    x = RegSetValueExA(hKey, entry, 0, 1, value, Len(value) + 1) 
    If x = 0 Then WriteRegistry = True Else WriteRegistry = False 
End Function 

Sub TestIt() 
    RootKey = "hkey_current_user" 
    Path = "software\microsoft\office\9.0\common\autocorrect" 
    RegEntry = "path" 
    MsgBox GetRegistry(RootKey, Path, RegEntry), vbInformation, _ 
        Path & "\RegEntry" 
End Sub 

Kernel32.dll

CopyMemory

GetCommandLine

GetCurrentThreadId

GetLocaleInfo

GetSystemTime

GetTempPath

Getting temp folder path:

GlobalUnlock

GlobalLock

GlobalAlloc

lstrcpy

lstrlen

xLib

Shell32.dll

ShellExecute

SHBrowseForFolder and SHGetPathFromIDList

These two functions are not compatible with 64 bit.

If you are using these APIs to browse for a folder you should replace your code with this:

link - learn.microsoft.com/en-us/windows/win32/shell/shell-browseforfolder 

User32.dll

CallNextHookEx

CloseClipboard

EmptyClipboard

EnumDisplayDevices

EnumDisplayMonitors

EnumDisplaySettings

FindWindow

FindWindowEx

GetAsyncKeyState

link - cpearson.com/excel/keytest.aspx 

Detect Key Stroke

GetClipboardSequenceNumber

GetMonitorInfo

link - docs.microsoft.com/en-us/windows/win32/api/winuser/nf-winuser-getmonitorinfoa

GetSysColor

GetSystemMetrics

link - docs.microsoft.com/en-us/windows/win32/api/winuser/nf-winuser-getsystemmetrics 

GetWindowLong

link - docs.microsoft.com/en-us/windows/win32/api/winuser/nf-winuser-getwindowlonga 

GetWindowRect

LockWindowUpdate

MonitorFromWindow

OpenClipboard

Putfocus

ScrollWindowEx

SetClipboardData

SetDlgItemText

SetForegroundWindow

SetWindowLong

link - docs.microsoft.com/en-us/windows/win32/api/winuser/nf-winuser-setwindowlonga 

SetWindowPos

SetWindowsHookEx

ShowWindow

link - docs.microsoft.com/en-us/windows/win32/api/winuser/nf-winuser-showwindow 

UnhookWindowsHookEx