Greg Truby
MrExcel MVP
- Joined
- Jun 19, 2002
- Messages
- 10,025
A client from my consulting days is upgrading hardware and the new notebooks are coming w/ 64-bit Excel and so's I need to make the add-in I wrote for them mind its manners and play nice under 64-bit Excel. I had thought this would be relatively straightforward. It turned out to be a bit more convoluted than I expected. Everything I'd seen on the internet told me I could use the conditional compiling constants WIN64 and VBA7 to handle compiling in various environments. But when I got to a "boots-on-the-ground" situation, it proved pretty inconsistent. Eventually I grew so frustrated that I wrote code to see what in tarnation was going on and then ran that code in as many environments as I personally had access to. I am going to share my results and the code that I used to obtain them. I would be ever so grateful if anyone who is interested would try dropping the code into a workbook and running the macro on their computer and then posting the output.
Thanks in advance to anyone who joins the experiment!
Here's the VBA:
Comments/Notes: Since the built-in conditional compiling constants were behaving like obnoxious drunks, I used a conditional compiling constant of my own. This was only needed when I was trying to compile in Excel 2003 or 2007. For Excel 2010 or better, the LongPtr variable declarations would compile, even when Excel itself was 32-bit. If you are testing under 2007 or lower, then you'd need to flip the compiling constant to false (0). In the VBE it's under Tools | VBAProject Properties...
Here are the results I obtained in various environments color coded to reflect whether the results were correct or incorrect. As you can see, in my testing the WIN64 and VBA7 conditional compiling constants proved about a trustworthy as a politician's oath:
I should comment that the 64-bit Excel is running in a virtual machine. I'm wondering if that might not be the reason the WOW64 test for the OS bitness failed.
Thanks in advance to anyone who joins the experiment!
Here's the VBA:
VBA Code:
Option Explicit
#If gccc_XL64 Then
Const c_strTest As String = "sixty four"
Private Declare PtrSafe Function GetProcAddress _
Lib "kernel32" _
(ByVal hModule As LongPtr, _
ByVal lpProcName As String) _
As LongPtr
Private Declare PtrSafe Function GetModuleHandle _
Lib "kernel32" _
Alias "GetModuleHandleA" _
(ByVal lpModuleName As String) _
As LongPtr
Private Declare PtrSafe Function GetCurrentProcess _
Lib "kernel32" () _
As LongPtr
Private Declare PtrSafe Function IsWow64Process _
Lib "kernel32" _
(ByVal hProcess As LongPtr, _
ByRef Wow64Process As Long) _
As Long
#Else
Const c_strTest As String = "thirty two"
Private Declare Function GetProcAddress _
Lib "kernel32" _
(ByVal hModule As Long, _
ByVal lpProcName As String) _
As Long
Private Declare Function GetModuleHandle _
Lib "kernel32" _
Alias "GetModuleHandleA" _
(ByVal lpModuleName As String) _
As Long
Private Declare Function GetCurrentProcess _
Lib "kernel32" () _
As Long
Private Declare Function IsWow64Process _
Lib "kernel32" _
(ByVal hProcess As Long, _
ByRef Wow64Process As Long) _
As Long
#End If
#If Win64 Then
Const mc_bytWin64 As Byte = 64
#Else
Const mc_bytWin64 As Byte = 32
#End If
#If VBA7 Then
Const mc_bytExcel As Byte = 64
#Else
Const mc_bytExcel As Byte = 32
#End If
Rem "foobar" - in memory of the legendary nateo
Private Sub foobar(): MsgBox c_strTest, vbInformation: End Sub
Sub Show32or64_Main()
PutResultsInCells
MsgBox fnBitnessMessage, vbInformation, "A bit o' this and a bit o' that"
End Sub
Private Sub PutResultsInCells()
Const c_strStartAddr As String = "B3", _
c_strNbrFmt As String = "0 "" bit"""
'// Note: using unqualified references, so this will
'// ¯¯¯¯¯ output to the activesheet.
Dim r As Range
Set r = Range(c_strStartAddr)
With r
.CurrentRegion.Clear
.Value = "Windows"
.Resize(, 2).Style = "Heading 3"
With .Offset(1)
.Value = "#If Win64"
.IndentLevel = 1
With .Offset(, 1)
.Value = mc_bytWin64
.NumberFormat = c_strNbrFmt
End With
End With
With .Offset(2)
.Value = "Environ"
.IndentLevel = 1
With .Offset(, 1)
.Value = fnWindowsBitnessEnviron
.NumberFormat = c_strNbrFmt
End With
End With
With .Offset(3)
.Value = "WOW64"
.IndentLevel = 1
With .Offset(, 1)
.Value = fnWindowsBitnessIsWow64
.NumberFormat = c_strNbrFmt
End With
End With
With .Offset(4)
.Value = "Excel"
.Resize(, 2).Style = "Heading 3"
.RowHeight = 30
End With
With .Offset(5)
.Value = "#If VBA7"
.IndentLevel = 1
With .Offset(, 1)
.Value = mc_bytExcel
.NumberFormat = c_strNbrFmt
End With
End With
With .Offset(6)
.Value = "Dimensioning test"
.IndentLevel = 1
With .Offset(, 1)
.Value = fnExcelBitnessDim
.NumberFormat = c_strNbrFmt
End With
End With
With .Offset(7)
.Value = "Address test"
.IndentLevel = 1
With .Offset(, 1)
.Value = fnExcelBitnessDummyAddress
.NumberFormat = c_strNbrFmt
End With
End With
With .Offset(8)
.Value = "HInstance test"
.IndentLevel = 1
With .Offset(, 1)
.Value = fnExcelBitnessHInstance
.NumberFormat = c_strNbrFmt
End With
With .Resize(, 2).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End With
With .Offset(9)
.Value = "Version Nbr"
.IndentLevel = 1
.Offset(, 1).Value = Application.Version
End With
With .Offset(10)
.Value = "Build Nbr"
.IndentLevel = 1
.Offset(, 1).Value = Application.Build
End With
End With '// starting cell
End Sub '// PutResultsInCells
Function fnBitnessMessage() As String
Let fnBitnessMessage _
= "COND COMPILE says Windows is " & vbTab & CStr(mc_bytWin64) & " bit" & vbCrLf _
& "ENVIRON test says Windows is " & vbTab & CStr(fnWindowsBitnessEnviron) & " bit" & vbCrLf _
& "WOW64 test says Windows is " & vbTab & vbTab & CStr(fnWindowsBitnessIsWow64) & " bit" & vbCrLf _
& String(24, "—") & vbCrLf _
& "COND COMPILE says Excel is " & vbTab & vbTab & CStr(mc_bytExcel) & " bit" & vbCrLf _
& "DIM bitness test says Excel is " & vbTab & vbTab & CStr(fnExcelBitnessDim) & " bit" & vbCrLf _
& "ADDRESS bitness test says Excel is " & vbTab & CStr(fnExcelBitnessDummyAddress) & " bit" & vbCrLf _
& "HINSTANCE bitness test says Excel is " & vbTab & CStr(fnExcelBitnessHInstance) & " bit" & vbCrLf
End Function
Function fnWindowsBitnessEnviron() As Byte
Let fnWindowsBitnessEnviron = IIf(CBool(Len(Environ("ProgramW6432"))), 64, 32)
End Function
Function fnWindowsBitnessIsWow64() As Byte
#If gccc_XL64 Then
Dim h As LongPtr
#Else
Dim h As Long
#End If
Dim lngIs64
Let h = GetProcAddress(GetModuleHandle("kernel32"), "IsWow64Process")
'// IsWow64Process function exists. Now use the function
'// to determine if we are running under Wow64
If h > 0 Then IsWow64Process GetCurrentProcess(), lngIs64
Let fnWindowsBitnessIsWow64 = IIf(CBool(lngIs64), 64, 32)
End Function
Function fnExcelBitnessDim() As Byte
#If gccc_XL64 Then
Dim ptrTest As LongPtr
#Else
Dim ptrTest As Long
#End If
Let fnExcelBitnessDim = IIf(LenB(ptrTest) = 4, 32, 64)
End Function
Function fnExcelBitnessDummyAddress() As Byte
Let fnExcelBitnessDummyAddress = IIf(TypeName(AddressOf fnDummy) = "Long", 32, 64)
End Function
Function fnDummy()
End Function
Function fnExcelBitnessHInstance() As Byte
Dim vntHinstance As Variant
On Error Resume Next
vntHinstance = Application.Hinstance
Let fnExcelBitnessHInstance = IIf(Err = 0, 32, 64)
End Function
'// just a utility 'cuz I forget 'em
Private Sub AllEnvironVariables()
Dim strEnviron As String
Dim VarSplit As Variant
Dim i As Long
For i = 1 To 255
strEnviron = Environ$(i)
If LenB(strEnviron) = 0& Then GoTo TryNext:
VarSplit = Split(strEnviron, "=")
If UBound(VarSplit) > 1 Then Stop
Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1).Value = i
Range("B" & Range("B" & Rows.Count).End(xlUp).Row + 1).Value = VarSplit(0)
Range("C" & Range("C" & Rows.Count).End(xlUp).Row + 1).Value = VarSplit(1)
TryNext:
Next
End SubComments/Notes: Since the built-in conditional compiling constants were behaving like obnoxious drunks, I used a conditional compiling constant of my own. This was only needed when I was trying to compile in Excel 2003 or 2007. For Excel 2010 or better, the LongPtr variable declarations would compile, even when Excel itself was 32-bit. If you are testing under 2007 or lower, then you'd need to flip the compiling constant to false (0). In the VBE it's under Tools | VBAProject Properties...
Here are the results I obtained in various environments color coded to reflect whether the results were correct or incorrect. As you can see, in my testing the WIN64 and VBA7 conditional compiling constants proved about a trustworthy as a politician's oath:
| Show32or64 Excel and OS.xlsm | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | Windows | Win 7 | Win 7 | Win 7 | Win 10 P | Win 7 | Win 10 H | Win 10 P | ||
| 2 | Excel | 2003 | 2007 | 2010 | 2010 | 2013 | O365 / 32 | O365 / 64 | ||
| 3 | Windows | |||||||||
| 4 | #If Win64 | 32 bit | 32 bit | 32 bit | 32 bit | 32 bit | 32 bit | 64 bit | ||
| 5 | Environ | 64 bit | 64 bit | 64 bit | 64 bit | 64 bit | 64 bit | 64 bit | ||
| 6 | WOW64 | 64 bit | 64 bit | 64 bit | 64 bit | 64 bit | 64 bit | 32 bit | ||
| 7 | Excel | |||||||||
| 8 | #If VBA7 | 32 bit | 32 bit | 64 bit | 64 bit | 64 bit | 64 bit | 64 bit | ||
| 9 | Dimensioning test | 32 bit | 32 bit | 32 bit | 32 bit | 32 bit | 32 bit | 64 bit | ||
| 10 | Address test | 32 bit | 32 bit | 32 bit | 32 bit | 32 bit | 32 bit | 64 bit | ||
| 11 | HInstance test | 32 bit | 32 bit | 32 bit | 32 bit | 32 bit | 32 bit | 64 bit | ||
| 12 | Version Nbr | 11 | 12 | 14 | 14 | 15 | 16 | 16 | ||
| 13 | Build Nbr | 8404 | 6787 | 7244 | 7244 | 5207 | 11929 | 11929 | ||
Consolidated | ||||||||||
I should comment that the 64-bit Excel is running in a virtual machine. I'm wondering if that might not be the reason the WOW64 test for the OS bitness failed.
Last edited:
