🚀 Overview: Explicit Return Type Requirements for 64-Bit VBA

When transitioning Microsoft Office environments from 32-bit to 64-bit architectures, IT administrators often encounter compatibility hurdles within legacy VBA (Visual Basic for Applications) macros. A critical technical requirement in the 64-bit version of Office is the explicit declaration of return types for callback functions used in API calls.

In a 32-bit environment, VBA is more permissive; if a return type is omitted, the engine defaults to a Variant data type, which typically allows the code to execute without failure. However, due to fundamental changes in how pointers and memory addresses are handled in 64-bit systems, the 64-bit VBA engine cannot process Variant return types for these specific callback routines. Failure to explicitly define the return type—or using a Function when a Sub (void) is expected—will result in macro execution errors.

⚙️ Key Technical Details

• The Variant Default: In standard VBA logic, any function defined without an “As [DataType]” suffix is treated as a Variant. While functional in 32-bit Office, this is a breaking change in 64-bit versions for callback procedures.
• Callback vs. Declare: It is important to distinguish between the Declare statement and the callback function. The 64-bit limitation does not apply to standard Declare statements used to call external API methods. It applies exclusively to the VBA functions that are passed to those APIs as callbacks (e.g., via AddressOf).
• Architecture Differences: In 32-bit VBA, callback functions can return 16-bit Integers, 32-bit Longs, or Void values via Sub procedures or Variant Functions. In 64-bit VBA, the pointer logic requires strict type matching to ensure the stack is maintained correctly.
• Sub vs. Function: If a callback is intended to return a void value (no return), it must be declared as a Sub. If it must return a value, the specific type (such as LongPtr or Long) must be explicitly stated.

🧪 Technical Demonstration: The SetTimer Scenario

To visualize this issue, IT Admins can examine the following code, which utilizes the user32.dll timer functions. This code is designed to run in a 64-bit Word environment.

Option Explicit
Dim iCounter As Integer
Dim lngTimerID As Long
Dim BlnTimer As Boolean

Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As Long, _
 ByVal nIDEvent As Long, _
 ByVal uElapse As Long, _
 ByVal lpTimerFunc As LongPtr) As Long

Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As Long, _
 ByVal nDEvent As Long) As Long

Sub TimerProc(ByVal hwnd As Long, _
 ByVal uMsg As Long, _
 ByVal idEvent As Long, _
 ByVal dwTime As Long)
iCounter = iCounter + 1
End Sub 

Private Sub ToggleTimer()
 If BlnTimer = False Then
 lngTimerID = SetTimer(0, 0, 200, AddressOf TimerProc)
 If lngTimerID = 0 Then
 MsgBox "Timer not created. Ending program"
 Exit Sub
 End If
 BlnTimer = True
 Else 
lngTimerID = KillTimer(0, lngTimerID)
 If lngTimerID = 0 Then
MsgBox "Could not kill the timer"
 End If
 BlnTimer = False
 MsgBox " Timer Count " & iCounter
 End If

End Sub

Sub Macro1()
 ToggleTimer
End Sub

⚠️ The Failure Point: If you modify the TimerProc from a Sub to a Function without an explicit type, as shown below, the code will fail in 64-bit Office:

Function TimerProc(ByVal hwnd As Long, _
 ByVal uMsg As Long, _
 ByVal idEvent As Long, _
 ByVal dwTime As Long)

iCounter = iCounter + 1
End Function

In this state, the function defaults to Variant, triggering an error because 64-bit callback functions cannot return Variant types.

🛡️ Impact on Infrastructure and Support

• Deployment Roadblocks: Organizations migrating from 32-bit Office to 64-bit Office may find critical Excel or Word-based tools suddenly non-functional.
• Audit Requirements: IT Admins and developers must audit legacy codebases for any use of the AddressOf operator. Any procedure targeted by AddressOf must be reviewed for explicit return type declarations.
• Code Stability: Ensuring explicit declarations improves the overall stability of the Office environment and prevents memory-related crashes that can occur when the VBA engine misinterprets pointer data returned from an API.
• Resolution Strategy: The recommended fix is to ensure all callback routines that do not return values are defined as Sub procedures. If a return value is mandatory, use a specific scalar type (e.g., As Long) instead of allowing the default Variant behavior.

Read the full article on Microsoft.com