none
Problem trying to run 32 bit VBA program on a 64 bit machine RRS feed

  • Question

  • I am a new programmer using VBA in Excel. I have used code that refers to a kernel32 in the VBA code. This code fails on a 64 bit machine. Runs perfectly fine on 32 bit machine. Is there code I can add that will allow the program to run on both systems? The code is being used to determine the UTC time of a cell from a report.

    Thank you in advance. Code below:

    Option Explicit

    Public Declare Function SystemTimeToFileTime Lib _
        "kernel32" (lpSystemTime As SYSTEMTIME, _
        lpFileTime As FILETIME) As Long

    Public Declare Function LocalFileTimeToFileTime Lib _
        "kernel32" (lpLocalFileTime As FILETIME, _
        lpFileTime As FILETIME) As Long

    Public Declare Function FileTimeToSystemTime Lib _
        "kernel32" (lpFileTime As FILETIME, lpSystemTime _
        As SYSTEMTIME) As Long

    Public Type FILETIME
        dwLowDateTime As Long
        dwHighDateTime As Long
    End Type
    Public Type SYSTEMTIME
        wYear As Integer
        wMonth As Integer
        wDayOfWeek As Integer
        wDay As Integer
        wHour As Integer
        wMinute As Integer
        wSecond As Integer
        wMilliseconds As Integer
    End Type


    • Edited by Dean_T Tuesday, September 9, 2014 3:57 PM
    Tuesday, September 9, 2014 3:56 PM

Answers

  • I don't have Office-64 to test but try this

    #If VBA7 Then
    Declare PtrSafe Function SystemTimeToFileTime Lib "kernel32" Alias "SystemTimeToFileTime" (lpSystemTime As SYSTEMTIME, lpFileTime As FILETIME) As Long
    
    Declare PtrSafe Function LocalFileTimeToFileTime Lib "kernel32" Alias "LocalFileTimeToFileTime" (lpLocalFileTime As FILETIME, lpFileTime As FILETIME) As Long
    
    Declare PtrSafe Function FileTimeToSystemTime Lib "kernel32" Alias "FileTimeToSystemTime" (lpFileTime As FILETIME, lpSystemTime As SYSTEMTIME) As Long
    
    #Else
    Public Declare Function SystemTimeToFileTime Lib _
        "kernel32" (lpSystemTime As SYSTEMTIME, _
        lpFileTime As FILETIME) As Long
    
    Public Declare Function LocalFileTimeToFileTime Lib _
        "kernel32" (lpLocalFileTime As FILETIME, _
        lpFileTime As FILETIME) As Long
    
    Public Declare Function FileTimeToSystemTime Lib _
        "kernel32" (lpFileTime As FILETIME, lpSystemTime _
        As SYSTEMTIME) As Long
    #End If

    For a good intro

    Declaring API Functions In 64 Bit Office

    • Marked as answer by Dean_T Wednesday, September 10, 2014 11:35 PM
    Tuesday, September 9, 2014 4:31 PM
    Moderator

All replies

  • I don't have Office-64 to test but try this

    #If VBA7 Then
    Declare PtrSafe Function SystemTimeToFileTime Lib "kernel32" Alias "SystemTimeToFileTime" (lpSystemTime As SYSTEMTIME, lpFileTime As FILETIME) As Long
    
    Declare PtrSafe Function LocalFileTimeToFileTime Lib "kernel32" Alias "LocalFileTimeToFileTime" (lpLocalFileTime As FILETIME, lpFileTime As FILETIME) As Long
    
    Declare PtrSafe Function FileTimeToSystemTime Lib "kernel32" Alias "FileTimeToSystemTime" (lpFileTime As FILETIME, lpSystemTime As SYSTEMTIME) As Long
    
    #Else
    Public Declare Function SystemTimeToFileTime Lib _
        "kernel32" (lpSystemTime As SYSTEMTIME, _
        lpFileTime As FILETIME) As Long
    
    Public Declare Function LocalFileTimeToFileTime Lib _
        "kernel32" (lpLocalFileTime As FILETIME, _
        lpFileTime As FILETIME) As Long
    
    Public Declare Function FileTimeToSystemTime Lib _
        "kernel32" (lpFileTime As FILETIME, lpSystemTime _
        As SYSTEMTIME) As Long
    #End If

    For a good intro

    Declaring API Functions In 64 Bit Office

    • Marked as answer by Dean_T Wednesday, September 10, 2014 11:35 PM
    Tuesday, September 9, 2014 4:31 PM
    Moderator
  • Peter,

    Thank you very much for your assistance. It worked great. Dropped the code in and had my counterpart with the 64 bit machine test the code.

    Your an gentlemen and a scholar!

    I greatly appreciate your help.

    Cheers.



    Wednesday, September 10, 2014 11:35 PM
  • You're welcome, mine's a pint :)

    I forgot to include Private or Public in the PtrSafe versions

    Thursday, September 11, 2014 10:14 AM
    Moderator
  • Would it be possible to post what the full new code looks like. I tries using it but does not work. I have the same code to convert to UTC
    • Edited by ibhu Friday, April 3, 2015 6:26 PM
    Friday, April 3, 2015 6:16 PM