VBA Conditional Compilation and Environment Detection: Write Macros That Adapt Across Excel Versions, Operating Systems, and Deployment Contexts

Introduction

You've built a beautiful macro. It automates a weekly sales report, connects to a database, and formats everything exactly right. You send it to a colleague who runs Excel 2016 on a Mac, and it crashes immediately. Meanwhile, another colleague runs it in a locked-down corporate environment where certain file paths don't exist. You spend the rest of the day on support calls instead of doing actual work.

This is the quiet frustration of VBA development at scale: code that works perfectly in one environment becomes fragile the moment it moves. The root cause is usually the same — the macro was written for your setup and nobody else's. The fix is a technique called conditional compilation, combined with runtime environment detection. Together, they let you write a single codebase that adapts intelligently to wherever it runs.

By the end of this lesson, you'll know exactly how to do that. You'll understand how VBA can inspect its own environment — the Excel version, the operating system, whether it's running in a 32-bit or 64-bit process — and make decisions based on what it finds. This is foundational knowledge for anyone distributing macros to multiple users, building enterprise tools, or maintaining VBA code that needs to survive across years of Office upgrades.

What you'll learn:

• What conditional compilation is and how it differs from runtime branching
• How to use #Const, #If, #ElseIf, and #End If directives to control which code actually compiles
• How to detect Excel version, operating system, and bitness at both compile time and runtime
• How to use Application.Version, Environ(), and other runtime detection techniques
• How to structure a macro so it gracefully degrades when features aren't available

---

Prerequisites

You should be comfortable writing basic VBA procedures and working with the Visual Basic Editor. If you're new to VBA, start with Getting Started with VBA Macros in Excel and VBA Variables, Data Types, and Control Structures: Building Robust Excel Automation before continuing here. You should also understand how error handling works, because environment detection and error handling often go hand in hand.

---

What Is Conditional Compilation (And Why Does It Matter)?

Before we write any code, let's make sure the concept is crystal clear.

When VBA compiles your code, it converts your human-readable instructions into something the computer can execute. Conditional compilation means you can instruct the compiler itself to include or exclude entire blocks of code before that compilation happens. The excluded code doesn't just get skipped at runtime — it doesn't even exist in the compiled output. It's as if you never wrote those lines.

This is different from a regular If...Then statement. With a normal If, the code inside every branch is compiled and present in memory. VBA just decides at runtime which branch to execute. With conditional compilation, entire branches can be surgically removed before the code runs at all.

Here's a simple analogy: imagine you're writing a recipe book with two versions — one for people who have a food processor and one for those who don't. With a regular If, you print both versions in every book and tell the cook "ignore the section that doesn't apply to you." With conditional compilation, you print two separate books, each containing only the instructions that are actually relevant. Less confusion, fewer errors, smaller package.

---

The Syntax: Compiler Directives

Conditional compilation uses special compiler directives that start with a # (hash/pound) symbol. The most important ones are:

• #Const — defines a compile-time constant
• #If — begins a conditional compilation block
• #ElseIf — adds an alternative condition
• #Else — catches everything that didn't match
• #End If — closes the block

Notice that these are not regular VBA statements. They don't go inside Sub or Function procedures — they operate at the module level during compilation. Here's the basic structure:

#Const MY_FLAG = True

#If MY_FLAG Then
    ' This code exists in the compiled output
    Sub DoSomethingSpecial()
        MsgBox "Special mode is on."
    End Sub
#Else
    ' This code is completely excluded
    Sub DoSomethingSpecial()
        MsgBox "Standard mode."
    End Sub
#End If

You can have two procedures with the same name in #If and #Else blocks, which would be illegal in regular VBA. This is exactly the point — only one of them will ever be compiled, so there's no conflict.

---

The Most Important Built-In Constant: `Win64`

The single most common use of conditional compilation in real-world VBA is handling the transition between 32-bit and 64-bit Excel. This became critical when Microsoft introduced the PtrSafe keyword requirement in Excel 2010 for 64-bit Office, and it affects any code that calls the Windows API (the low-level functions built into the operating system).

VBA provides a built-in constant called Win64 that equals True when the code is running in a 64-bit process, and False when it's 32-bit. There's also Win32 for 32-bit, and Mac for the Mac platform.

Here's the textbook example — declaring the Windows Sleep function, which pauses execution for a specified number of milliseconds. The declaration syntax is completely different between 32-bit and 64-bit:

' Declare Sleep API correctly for both 32-bit and 64-bit Excel
#If Win64 Then
    Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
#Else
    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Sub PauseForThreeSeconds()
    Sleep 3000
    MsgBox "Done waiting."
End Sub

Without the conditional compilation, you'd have to choose one declaration or the other. If you chose the 64-bit version and a user runs the macro in 32-bit Excel, they get a compile error before the code even starts. The #If Win64 block means exactly the right declaration is in place for each environment, automatically.

Let's look at another realistic example. Suppose you're building a tool that needs to access the Windows clipboard. The API declarations are very different between architectures:

#If Win64 Then
    Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
    Private Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
    Private Declare PtrSafe Function EmptyClipboard Lib "user32" () As Long
    Private Declare PtrSafe Function SetClipboardData Lib "user32" (ByVal uFormat As Long, ByVal hMem As LongPtr) As LongPtr
#Else
    Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
    Private Declare Function CloseClipboard Lib "user32" () As Long
    Private Declare Function EmptyClipboard Lib "user32" () As Long
    Private Declare Function SetClipboardData Lib "user32" (ByVal uFormat As Long, ByVal hMem As Long) As Long
#End If

Notice the pattern: PtrSafe is added in the 64-bit declaration, and pointer-sized values use LongPtr instead of Long. The LongPtr type is 4 bytes on 32-bit systems and 8 bytes on 64-bit, so it adapts automatically — but only if you've used the conditional compilation to get the right declaration in place first.

---

Runtime Detection: When Compile-Time Constants Aren't Enough

Conditional compilation solves compatibility problems that exist before the code runs. But sometimes you need to make decisions at runtime based on the actual environment. For this, VBA gives you a set of runtime tools.

Detecting the Excel Version

The Application.Version property returns a string representing the Excel version. The mapping isn't obvious, so here's a reference:

Version String	Excel Release
"14.0"	Excel 2010
"15.0"	Excel 2013
"16.0"	Excel 2016, 2019, 2021, Microsoft 365

Notice that Excel 2016 through Microsoft 365 all return "16.0". If you need to distinguish between these, you can use Application.Build for more granularity, but for most purposes, knowing "this is at least version 16" is enough.

Here's a practical use case: you want to use the XLOOKUP function (introduced in Microsoft 365, build 12130) in your VBA code, but you need a fallback for older versions:

Sub RunReportWithFallback()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sales")
    
    Dim excelVersion As Double
    excelVersion = CDbl(Application.Version)
    
    If excelVersion >= 16 And Application.Build >= 12130 Then
        ' Use XLOOKUP directly via WorksheetFunction
        Call RunModernReport(ws)
    ElseIf excelVersion >= 15 Then
        ' Excel 2013/2016: use VLOOKUP or INDEX/MATCH instead
        Call RunLegacyReport(ws)
    Else
        MsgBox "This tool requires Excel 2013 or later. " & _
               "You are running Excel version " & Application.Version & ".", _
               vbCritical, "Version Not Supported"
    End If
End Sub

Private Sub RunModernReport(ws As Worksheet)
    ' Logic using XLOOKUP, dynamic arrays, etc.
    Debug.Print "Running modern report path"
End Sub

Private Sub RunLegacyReport(ws As Worksheet)
    ' Logic using VLOOKUP, traditional formulas
    Debug.Print "Running legacy report path"
End Sub

This pattern — detect, then delegate to a version-appropriate sub — keeps your code clean. Each path is self-contained rather than littered with If version >= X checks throughout.

Detecting the Operating System

The Environ() function retrieves environment variables from the operating system. This is one of the most versatile tools in environment detection. For detecting the OS, two approaches work well:

Function GetOperatingSystem() As String
    ' Application.OperatingSystem is the simplest approach
    Dim osString As String
    osString = Application.OperatingSystem
    
    If InStr(osString, "Windows") > 0 Then
        GetOperatingSystem = "Windows"
    ElseIf InStr(osString, "Macintosh") > 0 Then
        GetOperatingSystem = "Mac"
    Else
        GetOperatingSystem = "Unknown"
    End If
End Function

Sub ShowOSInfo()
    Dim os As String
    os = GetOperatingSystem()
    
    Select Case os
        Case "Windows"
            MsgBox "Running on Windows. Full feature set available."
        Case "Mac"
            MsgBox "Running on Mac. Some Windows API features are disabled."
        Case Else
            MsgBox "Unknown operating system. Proceeding with caution."
    End Select
End Sub

You can also use Environ() to get specific system-level information useful for path construction:

Sub DetectEnvironmentPaths()
    ' Get the current user's name
    Dim userName As String
    userName = Environ("USERNAME")          ' Windows
    If userName = "" Then userName = Environ("USER")  ' Mac/Linux fallback
    
    ' Get common paths
    Dim tempPath As String
    Dim desktopPath As String
    
    tempPath = Environ("TEMP")              ' Windows temp folder
    desktopPath = Environ("USERPROFILE") & "\Desktop"
    
    Debug.Print "User: " & userName
    Debug.Print "Temp: " & tempPath
    Debug.Print "Desktop: " & desktopPath
End Sub

Combining Mac and Windows Detection with Compile-Time and Runtime Checks

The Mac built-in compiler constant lets you exclude entire sections of code that are Windows-only. This is particularly important because Mac Excel doesn't support Windows API calls at all — if the declaration even appears in your module on a Mac, it may cause errors.

' Module-level declarations — only compiled on Windows
#If Not Mac Then
    Private Declare PtrSafe Function GetSystemMetrics Lib "user32" _
        (ByVal nIndex As Long) As Long
    
    Private Const SM_CXSCREEN As Long = 0
    Private Const SM_CYSCREEN As Long = 1
#End If

Function GetScreenWidth() As Long
    #If Mac Then
        ' Mac doesn't support GetSystemMetrics
        ' Fall back to a reasonable default or use alternative
        GetScreenWidth = 1920  ' Reasonable modern default
    #Else
        GetScreenWidth = GetSystemMetrics(SM_CXSCREEN)
    #End If
End Function

Notice how we use #If Mac both at the module level (for declarations) and inside a function body (for the implementation). Both are valid places for compiler directives.

---

Building an Environment Detection Module

Rather than scattering environment checks throughout your codebase, the professional approach is to centralize everything in a dedicated module. Here's a complete, reusable modEnvironment module you can drop into any project:

' ============================================================
' modEnvironment — Centralized environment detection
' For use in workbooks deployed across multiple environments
' ============================================================
Option Explicit

' ---- Compile-time constants --------------------------------
' These are evaluated by the compiler and always accurate

Public Function IsWindows() As Boolean
    #If Mac Then
        IsWindows = False
    #Else
        IsWindows = True
    #End If
End Function

Public Function Is64Bit() As Boolean
    #If Win64 Then
        Is64Bit = True
    #Else
        Is64Bit = False
    #End If
End Function

' ---- Runtime detection functions ---------------------------

Public Function ExcelVersionNumber() As Double
    ExcelVersionNumber = CDbl(Application.Version)
End Function

Public Function ExcelBuildNumber() As Long
    ExcelBuildNumber = Application.Build
End Function

Public Function SupportsXLOOKUP() As Boolean
    ' XLOOKUP requires Microsoft 365 (build 12130+)
    SupportsXLOOKUP = (ExcelVersionNumber() >= 16 And ExcelBuildNumber() >= 12130)
End Function

Public Function SupportsDynamicArrays() As Boolean
    ' Dynamic arrays require the same threshold as XLOOKUP
    SupportsDynamicArrays = (ExcelVersionNumber() >= 16 And ExcelBuildNumber() >= 12130)
End Function

Public Function SupportsModernCharts() As Boolean
    ' Funnel, map charts, etc. require Excel 2016+
    SupportsModernCharts = (ExcelVersionNumber() >= 16)
End Function

Public Function CurrentUserName() As String
    Dim name As String
    name = Environ("USERNAME")
    If name = "" Then name = Environ("USER")  ' Mac fallback
    CurrentUserName = name
End Function

Public Function IsProduction() As Boolean
    ' Define "production" as running on the designated server
    ' Adjust COMPUTERNAME to match your actual server name
    IsProduction = (UCase(Environ("COMPUTERNAME")) = "CORP-REPORT-SRV01")
End Function

Public Function GetSafeTempPath() As String
    Dim tempDir As String
    
    If IsWindows() Then
        tempDir = Environ("TEMP")
        If Right(tempDir, 1) <> "\" Then tempDir = tempDir & "\"
    Else
        ' Mac uses a different temp location
        tempDir = "/tmp/"
    End If
    
    GetSafeTempPath = tempDir
End Function

Public Sub PrintEnvironmentReport()
    Debug.Print "==============================="
    Debug.Print "Environment Report"
    Debug.Print "==============================="
    Debug.Print "Platform: " & IIf(IsWindows(), "Windows", "Mac")
    Debug.Print "Architecture: " & IIf(Is64Bit(), "64-bit", "32-bit")
    Debug.Print "Excel Version: " & Application.Version
    Debug.Print "Excel Build: " & Application.Build
    Debug.Print "Supports XLOOKUP: " & SupportsXLOOKUP()
    Debug.Print "Supports Dynamic Arrays: " & SupportsDynamicArrays()
    Debug.Print "User: " & CurrentUserName()
    Debug.Print "Is Production: " & IsProduction()
    Debug.Print "Temp Path: " & GetSafeTempPath()
    Debug.Print "==============================="
End Sub

With this module in place, every other module in your project calls these functions instead of duplicating the detection logic. When something changes — say, you need to update the production server name — you change it in one place. This kind of centralised design is exactly what makes larger VBA projects maintainable, and it pairs naturally with error handling and debugging practices you should already have in place.

---

Practical Application: A File Operation That Works on Both Platforms

Let's put it all together with something concretely useful. This subroutine saves a report to a standard location, handling the path differences between Windows and Mac. It also uses the environment module we built above. For more context on file operations in VBA, see Automating Email and File Operations with VBA.

Sub ExportReportToStandardLocation()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Monthly Summary")
    
    ' Build a cross-platform output path
    Dim outputDir As String
    Dim fileName As String
    Dim fullPath As String
    Dim pathSep As String
    
    pathSep = IIf(IsWindows(), "\", "/")
    
    If IsWindows() Then
        outputDir = Environ("USERPROFILE") & "\Documents\Reports"
    Else
        ' Mac home directory
        outputDir = Environ("HOME") & "/Documents/Reports"
    End If
    
    ' Ensure directory exists (Windows only — Mac requires different approach)
    #If Not Mac Then
        If Dir(outputDir, vbDirectory) = "" Then
            MkDir outputDir
        End If
    #End If
    
    fileName = "SalesReport_" & Format(Now(), "YYYYMMDD") & ".xlsx"
    fullPath = outputDir & pathSep & fileName
    
    ' Copy the sheet to a new workbook and save
    Dim newWb As Workbook
    ws.Copy
    Set newWb = ActiveWorkbook
    
    Application.DisplayAlerts = False
    newWb.SaveAs fileName:=fullPath, FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    newWb.Close SaveChanges:=False
    
    MsgBox "Report saved to:" & vbNewLine & fullPath, vbInformation, "Export Complete"
End Sub

This isn't a theoretical example — this exact pattern (detect OS, build appropriate path, handle platform differences in directory creation) is something you'll use constantly when writing macros for distributed teams.

---

Hands-On Exercise

Work through these tasks in a fresh Excel workbook:

Task 1: Build the Environment Module Create a new module called modEnvironment and paste in the code from the section above. Open the Immediate Window (press Ctrl+G) and type PrintEnvironmentReport then press Enter. Verify that all the values look correct for your machine.

Task 2: Version-Conditional Chart Code Write a subroutine called InsertSmartChart that checks SupportsModernCharts(). If it returns True, display a message saying "Modern chart types available" (in a real project, you'd insert a Funnel or Map chart here). If it returns False, display "Falling back to classic chart types." Test it and confirm the right message appears.

Task 3: Cross-Platform Path Builder Write a function called BuildReportPath(reportName As String) As String that constructs a full file path based on the current OS. On Windows it should use %USERPROFILE%\Documents\, on Mac it should use $HOME/Documents/. Return the full path as a string and test it in the Immediate Window.

Task 4: 32/64-bit Compiler Constant In a new module, write a function called GetArchitectureDescription() As String that uses #If Win64 Then inside the function body to return either "64-bit" or "32-bit" as a string. Call it from the Immediate Window and confirm you get the right result.

---

Common Mistakes & Troubleshooting

Mixing #If and regular If for compile-time constants If you write If Win64 Then (without the #), VBA interprets Win64 as an undeclared variable, which evaluates to 0 (False). Your 64-bit API declarations will never activate and you'll get crashes. Always use #If Win64 Then.

Forgetting that #Const scope is per-module A #Const defined at the top of Module1 is not visible in Module2. If you need project-wide constants, set them in Tools → Properties for VBAProject → Conditional Compilation Arguments. They appear there as ConstantName = Value pairs separated by colons.

Assuming Excel 2016 means Microsoft 365 Both report Application.Version as "16.0". If you need to use Microsoft 365-specific features like dynamic arrays or XLOOKUP, check Application.Build as well. This is a surprisingly common gotcha.

Using Windows path separators hardcoded in strings Writing "C:\Users\" & userName & "\Documents" works on Windows and silently fails on Mac. Always build paths using the platform detection pattern shown above, or use Application.PathSeparator which returns \ on Windows and : on classic Mac file systems (though / is safer for modern Mac Excel).

Not testing the fallback paths It's easy to write the conditional logic and only ever run it on your own machine. Test your fallback branches by temporarily setting a #Const to force the alternate path, or by writing a debug mode that simulates an older version.

---

Summary & Next Steps

Conditional compilation and environment detection transform your macros from fragile, single-environment scripts into robust tools that can be deployed with confidence. The core toolkit is small but powerful: #If, #Const, and the built-in constants Win64 and Mac handle compile-time decisions, while Application.Version, Application.Build, Environ(), and Application.OperatingSystem handle runtime detection.

The pattern to internalize is this: use conditional compilation when incompatible code must not be compiled on a given platform (especially Windows API declarations), and use runtime detection when you need to choose between paths that are all syntactically valid but functionally different.

The modEnvironment module pattern is worth taking into every project you build. The small investment of centralizing your environment detection pays dividends every time you need to debug a user's issue or extend your code to a new context.

From here, your natural next steps are:

• If you're distributing macros to multiple users, look at Building Excel Add-Ins with VBA: Package and Deploy Custom Tools Across Your Organization — that's where conditional compilation really earns its keep in a production setting.
• If you're using Windows API calls for file or system operations, Automating Email and File Operations with VBA goes deep on those patterns.
• If you want to build a systematic quality gate around your macros before deployment, Building a VBA Testing Framework for Excel will show you how to test across environment contexts programmatically.

Environment-aware code isn't just a nice-to-have. It's the difference between a macro that you're proud to ship and one that you dread having to support.